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

'

T-SQL Dynamic SQL Variable replacement

Overview of technique

A nice way of doing SQL Injection is to:

1. Copy the code you want dynamic into the script
2. Put quotes on each end
3. Replace the parameters with <parametername>
4. Using the sql REPLACE function, switch out with the value of the parameter
5. Query your little heart out

This T-SQL script shows an example

/*
 Declare variables
*/
DECLARE @wcSQL AS NVARCHAR(max)
DECLARE @columnWeWantToReturn VARCHAR(50)
SET @columnWeWantToReturn = 'name'

/*
 Prepare SQL Statement
*/
SET @wcSQL = 'SELECT <name> 
 FROM sys.sysobjects'
/*
 Replace parameters
*/      
SET @wcSQL = REPLACE(@wcSQL, '<name>', @columnWeWantToReturn)

/*
 Execute the dynamic sql
*/
EXEC sp_executeSQL @statement = @wcSQL

Hello World with SQLCLR

Overview

This article will show you how to create a very simple "Hello World" stored procedure using VB.NET or C# to help introduce this feature and demonstrate how to load a .Net assembly into SQL Server 2005.

.NET Code

The first thing we need to do is write our .Net code for our Stored Procedure and compile it. In this case, we are simply going to send the string "Hello World from SQLCLR !!" back to the client. We do this by using the SqlPipe.Send method of the SqlContext object. This can be thought of as the context of the spid currently executing the stored procedure. The Send method of the SqlPipe object can be used to send messages back to the client.

[C#]
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class SQLCLR{

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void HelloWorld()
   {
      SqlContext.Pipe.Send("Hello World from SQLCLR !!");   
   }
}

In order for us to use this code in SQL Server 2005 we must first compile it into an assembly. We can then load it into SQL Server and create our TSQL Stored Procedure header for our .Net code. The command below demonstrates how to compile the code into an assembly. For the purposes of this article we will assume that the code has been saved in a source file called c:\HelloWorld.vb or c:\HelloWorld.cs. To compile this open a command prompt and execute the relavent lines to change to the .NET 2.0 directory and compile the code.

[C#]
cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727"
csc /target:library c:\HelloWorld.cs

TSQL Code

Now that we have created our assembly (c:\HelloWorld.dll) we can load it into SQL Server and create our Stored Procedure. We do this by using the new CREATE ASSEMBLY command as below.We need to ensure that execution of user defined CLR code is enabled using sp_configure

exec sp_configure 'clr enabled', 1
reconfigure
go
create assembly HelloWorld from 'c:\HelloWorld.dll'
with permission_set = safe

Now that we have loaded our assembly, we can create our Stored Procedure. To do this we simply use the standard TSQL CREATE PROCEDURE syntax but instead of defining a TSQL procedure body, we specify an EXTERNAL NAME that points to our .NET Code. We specify the assembly, class and method name as shown below.

create procedure HelloWorld as external name HelloWorld.SQLCLR.HelloWorld

Now that the Stored Procedure is created, we can simply execute it and see the result.

exec HelloWorld

[Results]

Hello World from SQLCLR !!

Accessing External Resources

ALTER DATABASE SCRIBEINTERNAL
SET TRUSTWORTHY ON

CREATE assembly HelloWorld from 'c:\SQLCLR\ClassLibrary1.dll' 
with permission_set = UNSAFE

References

see http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/58b01172-1487-46a9-88bc-bd0970782d1d

Creating an Assembly

http://msdn.microsoft.com/en-us/library/ms189524.aspx

Supported Assemblies

http://msdn.microsoft.com/en-us/library/ms403279.aspx