Understanding the IsNumeric() function in T-SQL

Overview

The isNumeric() function tells you whether or not an expression is a valid numeric data type.
It will return 1 when it is numeric and 0 when it is not.
See http://msdn.microsoft.com/en-us/library/ms186272.aspx

Script Example

This can also be altered to deal with other types of functions
USE tempdb;
GO

CREATE TABLE IsNumericDemo (ID smallint identity primary key,
NumberString varchar(32),[IsNumeric] bit )

GO
INSERT IsNumericDemo(NumberString) VALUES ('3')
INSERT IsNumericDemo(NumberString) VALUES ('$')
INSERT IsNumericDemo(NumberString) VALUES ('$100')
INSERT IsNumericDemo(NumberString) VALUES ('$200.01')
INSERT IsNumericDemo(NumberString) VALUES ('-$300')
INSERT IsNumericDemo(NumberString) VALUES ('$-400')
INSERT IsNumericDemo(NumberString) VALUES ('$123,568.00')
INSERT IsNumericDemo(NumberString) VALUES ('$234.568.00')
INSERT IsNumericDemo(NumberString) VALUES ('3.E4')
INSERT IsNumericDemo(NumberString) VALUES ('FFFF')
INSERT IsNumericDemo(NumberString) VALUES ('2^10')
INSERT IsNumericDemo(NumberString) VALUES ('0000001')
INSERT IsNumericDemo(NumberString) VALUES ('1234+')
INSERT IsNumericDemo(NumberString) VALUES ('+1234')
INSERT IsNumericDemo(NumberString) VALUES ('twenty')
INSERT IsNumericDemo(NumberString) VALUES ('(40)')
INSERT IsNumericDemo(NumberString) VALUES ('-40')
GO

 

-- Set the IsNumeric flag

UPDATE IsNumericDemo 
SET [IsNumeric] = ISNUMERIC(NumberString)

 

-- Check results

SELECT id,numberstring,CASE [isnumeric] WHEN 1 THEN 'yes' ELSE 'no' END AS 'isNumeric'
FROM IsNumericDemo

GO

DROP TABLE IsNumericDemo
go

Analysis Services DMVs

DMV Overview

Analysis Services DMVs are a bit like the SQL DMVs. They allow you to view the inner workings and structure of an Analysis Services cube

Solution

Here's a useful link that gives quite a lot of info about them:

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

• You run them from an MDX query window in SSMS
• They look like SQL statements
• You can use a WHERE clause and and ORDER BY clause
• You can use Instr(Column name) > 0 instead of 'LIKE'
• You can't use '<>'

A couple of useful ones:

The Analysis Services version of sp_who2

select * from $system.discover_connections

or a similar one:

select * from $system.discover_sessions

or

select * from $system.discover_commands

How to take a look at objects in the cube:

select * from $system.discover_object_activity

To see which partitions are being processed

select mid(command_text, instr(command_text, '') + 13, 25) from $system.discover_commands where instr(command_text, 'ProcessFull') > 0

Analysis Services - Clearing the Cache

If you want to clear the analysis services cache for testing purposes you can run the following xmla command while connected to the cube in Management Studio

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
</Batch>

Rebuild all indexes on all tables T-SQL

The command to reindex is different for SQL2000 to SQL2005 and 2008:

• SQL 2000 uses the DBCC REINDEX command and
• SQL2005 uses the ALTER INDEX ALL ON REBUILD

Example:

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL 2000
--DBCC DBREINDEX(@TableName,' ',90)

-- SQL 2005/2008
EXEC('ALTER INDEX ALL ON ' + @tablename + ' REBUILD')

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Select sp_who2 into a temporary table

Select sp_who2 into a temporary table

The following script is useful to filter down sp_who2 results. For example if you want to find all connections to one database you can add a where clause to the following query

CREATE TABLE ##temp_sp_who2
    (
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,
      DiskIO INT NULL,
      LastBatch VARCHAR(1000) NULL,
      ProgramName VARCHAR(1000) NULL,
      SPID2 INT
      , rEQUESTID INT NULL --comment out for SQL 2000 databases

    )


INSERT  INTO ##temp_sp_who2
EXEC sp_who2

SELECT  *
FROM    ##temp_sp_who2
WHERE   DBName = '<database_name>'

DROP TABLE ##temp_sp_who2

Finding Potentially redundant Jobs

Finding potentially redundant jobs

Often if there is a large lists of jobs on a server it can be hard to go through and find the jobs that are redundant or havent been run in a long time.

This script is a handy way of finding these jobs.

In this example we are finding jobs older than 2010. You can easily change this by changing the date at the bottom of the script

DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
            last_run_date         INT              NOT NULL,
            last_run_time         INT              NOT NULL,
            next_run_date         INT              NOT NULL,
            next_run_time         INT              NOT NULL,
            next_run_schedule_id  INT              NOT NULL,
            requested_to_run      INT              NOT NULL, -- BOOL
            request_source        INT              NOT NULL,
            request_source_id     sysname          COLLATE database_default NULL,
            running               INT              NOT NULL, -- BOOL
            current_step          INT              NOT NULL,
            current_retry_attempt INT              NOT NULL,
            job_state             INT              NOT NULL)


INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner


SELECT j.NAME, t.* 
FROM #xp_results t
LEFT OUTER JOIN msdb.dbo.sysjobs j ON t.job_id = j.job_id
WHERE last_run_date < 20081231
AND j.enabled = 0

DROP TABLE #xp_results

T-SQL Running a script for each database using sp_msforeachdb

Here is a quick example of using SQL server stored procedure sp_msforeachdb

exec sp_msforeachdb '

use [?]
select count(*) from sys.all_objects

'