Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Ten SQL Server Functions That You Have Ignored Until Now

From Wiki

Jump to: navigation, search

Below are 10 SQL Server functions that are hardly used but should be used a lot more. I will go in more detail later on but here is a list of the ten functions that I am talking about



BINARY_CHECKSUM

BINARY_CHECKSUM is handy if you want to check for data differences between 2 rows of data

In order to see what rows are in table 1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use BINARY_CHECKSUM. You have to run this example on SQL Server 2000 to see it work, you can, of course, use any tables, just modify the queries Let’s get started…

  1. --let's copy over 20 rows to a table named authors2
  2.     SELECT TOP 20 * INTO tempdb..authors2
  3.     FROM pubs..authors
  4.  
  5.     --update 5 records by appending X to the au_fname
  6.     SET ROWCOUNT 5
  7.  
  8.  
  9.     UPDATE tempdb..authors2
  10.     SET au_fname =au_fname +'X'
  11.  
  12.  
  13.     --Set rowcount back to 0
  14.     SET ROWCOUNT 0
  15.  
  16.     --let's insert a row that doesn't exist in pubs
  17.     INSERT INTO tempdb..authors2
  18.     SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
  19.     FROM tempdb..authors2
  20.     WHERE au_id ='172-32-1176'
  21.  
  22.     --*** The BIG SELECT QUERY --***
  23.  
  24.     --Not in Pubs
  25.     SELECT 'Does Not Exist On Production',t2.au_id
  26.     FROM pubs..authors t1
  27.     RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
  28.     WHERE t1.au_id IS NULL
  29.     UNION ALL
  30.     --Not in Temp
  31.     SELECT 'Does Not Exist In Staging',t1.au_id
  32.     FROM pubs..authors t1
  33.     LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
  34.     WHERE t2.au_id IS NULL
  35.     UNION ALL
  36.     --Data Mismatch
  37.     SELECT 'Data Mismatch', t1.au_id
  38.     FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
  39.     JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
  40.     WHERE CheckSum1 <> CheckSum2
  41.  
  42.     --Clean up
  43.     DROP TABLE tempdb..authors2
  44.     GO

SIGN

Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set. However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing? Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function

For CASE we will do this

  1. RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END


So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0

Using the SIGN function is even easier, all you have to do is this

  1. RETURN SIGN(@@ROWCOUNT)


That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are So let's see this in action

  1. USE pubs
  2.     GO
  3.  
  4.     --Case Proc
  5.     CREATE PROCEDURE TestReturnValues
  6.     @au_id VARCHAR(49) ='172-32-1176'
  7.     AS
  8.     SELECT *
  9.     FROM authors
  10.     WHERE au_id =@au_id
  11.  
  12.     RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
  13.     GO
  14.  
  15.     --Sign Proc
  16.     CREATE PROCEDURE TestReturnValues2
  17.     @au_id VARCHAR(49) ='172-32-1176'
  18.     AS
  19.     SELECT *
  20.     FROM authors
  21.     WHERE au_id =@au_id
  22.  
  23.     RETURN SIGN(@@ROWCOUNT)
  24.     GO
  25.  
  26.  
  27.     --Case Proc, 1 will be returned; default value is used
  28.     DECLARE @Rowcount int
  29.     EXEC @Rowcount = TestReturnValues
  30.     SELECT @Rowcount
  31.     GO
  32.  
  33.     --Case Proc, 0 will be returned; dummy value is used
  34.     DECLARE @Rowcount int
  35.     EXEC @Rowcount = TestReturnValues 'ABC'
  36.     SELECT @Rowcount
  37.     GO
  38.  
  39.     --Sign Proc, 1 will be returned; default value is used
  40.     DECLARE @Rowcount int
  41.     EXEC @Rowcount = TestReturnValues2
  42.     SELECT @Rowcount
  43.     GO
  44.  
  45.     --Sign Proc, 0 will be returned; dummy value is used
  46.     DECLARE @Rowcount int
  47.     EXEC @Rowcount = TestReturnValues2 'ABC'
  48.     SELECT @Rowcount
  49.     GO
  50.  
  51.  
  52.     --Help the environment by recycling ;-)
  53.     DROP PROCEDURE TestReturnValues2,TestReturnValues
  54.     GO


COLUMNPROPERTY

COLUMNPROPERTY is handy if you need to find scale, precision, if it is an identity column and more. I have listed all of them below

  1. CREATE TABLE blah (ID DECIMAL(5,2) not null DEFAULT 99)
  2.     INSERT blah DEFAULT VALUES
  3.  
  4.     SELECT * FROM blah
  5.     SELECT COLUMNPROPERTY( OBJECT_ID('blah'),'ID','AllowsNull') AS AllowsNull,
  6.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsComputed') AS IsComputed,
  7.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsCursorType') AS IsCursorType,
  8.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsDeterministic') AS IsDeterministic,
  9.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsFulltextIndexed') AS IsFulltextIndexed,
  10.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdentity') AS IsFulltextIndexed,
  11.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdNotForRepl') AS IsIdNotForRepl,
  12.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIndexable') AS IsIndexable,
  13.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsOutParam') AS IsOutParam,
  14.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsPrecise') AS IsPrecise,
  15.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsRowGuidCol') AS IsRowGuidCol,
  16.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Precision') AS 'Precision',
  17.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Scale') AS Scale,
  18.     COLUMNPROPERTY( OBJECT_ID('blah'),'ID','UsesAnsiTrim') AS UsesAnsiTrim
  19.     FROM Blah


So what does all that stuff mean?


AllowsNull

Allows null values.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsComputed

The column is a computed column.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsCursorType

The procedure parameter is of type CURSOR.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsDeterministic

The column is deterministic. This property applies only to computed columns and view columns.

1 = TRUE

0 = FALSE

NULL = Invalid input. Not a computed column or view column.


IsFulltextIndexed

The column has been registered for full-text indexing.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsIdentity

The column uses the IDENTITY property.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsIdNotForRepl

The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsIndexable

The column can be indexed.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsOutParam

The procedure parameter is an output parameter.

1 = TRUE

0 = FALSE

NULL = Invalid input


IsPrecise

The column is precise. This property applies only to deterministic columns.

1 = TRUE

0 = FALSE

NULL = Invalid input. Not a deterministic column


IsRowGuidCol

The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property.

1 = TRUE

0 = FALSE

NULL = Invalid input


Precision

Precision for the data type of the column or parameter. The precision of the specified column data type

NULL = Invalid input


Scale

Scale for the data type of the column or parameter. The scale

NULL = Invalid input


UsesAnsiTrim

ANSI padding setting was ON when the table was initially created.

1= TRUE

0= FALSE

NULL = Invalid input



DATALENGTH

Okay so you know the LEN function but do you know the DATALENGTH function? There are two major difference between LEN and DATALENGTH. The first one deals with trailing spaces, execute the following code and you will see that LEN returns 3 while DATALENGTH returns 4

  1. DECLARE @V VARCHAR(50)
  2.     SELECT @V ='ABC '
  3.     SELECT LEN(@V),DATALENGTH(@V),@V


The second difference deals with unicode character data, as you know unicode uses 2 bytes to store 1 character Run the following example and you will see that LEN returns 3 while DATALENGTH returns 6

  1. DECLARE @V NVARCHAR(50)
  2.     SELECT @V ='ABC'
  3.     SELECT LEN(@V),DATALENGTH(@V),@V


If you do DATALENGTH(CONVERT(VARCHAR,@V)) you will get the same as LEN because LEN does a RTRIM and converts to VARCHAR before returning



ASCII, CHAR,UNICODE

ASCII will give you the ascii code for a character so for A you will get 65 CHAR does the reverse of ascii CHAR(65) returns A UNICODE will give you the unicode value for a character NCHAR will give you the character for a unicode or ascii value let's see how this works

  1. SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),
  2.     UNICODE(N'Λ'),NCHAR(923),NCHAR(UNICODE(N'Λ'))




NULLIF

NULLIF Returns a null value if the two specified expressions are equivalent.

Syntax NULLIF ( expression , expression )

  1. DECLARE @v VARCHAR(20)
  2.     SELECT @v = ' '
  3.  
  4.     SELECT NULLIF(@v,' ')


You can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example

  1. DECLARE @v VARCHAR(20)
  2.     SELECT @v = ' '
  3.  
  4.     SELECT COALESCE(NULLIF(@v,' '),'N/A')


Here is another NULLIF example:

  1. CREATE TABLE Blah (SomeCol VARCHAR(33))
  2.  
  3.     INSERT Blah VALUES(NULL)
  4.     INSERT Blah VALUES('')
  5.     INSERT Blah VALUES(' ')
  6.     INSERT Blah VALUES('A')
  7.     INSERT Blah VALUES('B B')
  8.  
  9.     --Using COALESCE and NULLIF
  10.     SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
  11.     FROM Blah
  12.  
  13.  
  14.     --Using CASE
  15.     SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
  16.     WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
  17.     ELSE SomeCol END SomeCol
  18.     FROM Blah

Output for both queries

N/A
N/A
N/A
A
B B

PARSENAME

PARSENAME retrieves parts of string delimited by dots. It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to split IP addresses, names etc

  1. DECLARE @ParseString VARCHAR(100)
  2.     SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'
  3.  
  4.     SELECT PARSENAME(@ParseString,4),
  5.     PARSENAME(@ParseString,3),
  6.     PARSENAME(@ParseString,2),
  7.     PARSENAME(@ParseString,1)
  8.  
  9.  
  10.     CREATE TABLE #Test (
  11.     SomeField VARCHAR(49))
  12.  
  13.     INSERT INTO #Test
  14.     VALUES ('aaa-bbbbb')
  15.  
  16.     INSERT INTO #Test
  17.     VALUES ('ppppp-bbbbb')
  18.  
  19.     INSERT INTO #Test
  20.     VALUES ('zzzz-xxxxx')
  21.  
  22.     --using PARSENAME
  23.     SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
  24.     FROM #Test


Another example:

  1. CREATE TABLE BadData (FullName varchar(20) NOT NULL);
  2.     INSERT INTO BadData (FullName)
  3.     SELECT 'Clinton, Bill' UNION ALL
  4.     SELECT 'Johnson, Lyndon, B.' UNION ALL
  5.     SELECT 'Bush, George, H.W.';


Split the names into 3 columns

Your output should be this: LastName FirstName MiddleInitial Clinton Bill Johnson Lyndon B. Bush George H.W.

  1. SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
  2.     PARSENAME(FullName2,NameLen) AS FirstName,
  3.     COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
  4.     FROM(
  5.     SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
  6.     REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
  7.     FROM BadData) x


However there is one caveat when using the parsename function; if the string you are parsing contains more than 4 sections (3 dots), the function will return NULL.

  1. SELECT PARSENAME('a.b.c.d', 1) -- Returns d
  2.     SELECT PARSENAME('a.b.c.d.e', 1) -- Returns NULL


ParseName is a handy function to use, but you must be aware of what your data looks like.


STUFF

STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765

  1. DECLARE @v VARCHAR(11)
  2.     SELECT @v ='-X-'
  3.  
  4.  
  5.     SELECT STUFF(@v, 2, 1, '98765'),
  6.     STUFF(@v, 2, 0, '98765'),
  7.     STUFF(@v, 2, 2, '98765')


The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right

  1. DECLARE @v VARCHAR(11)
  2.     SELECT @v ='123456789'
  3.  
  4.     SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')



REVERSE

REVERSE just reverses the value, for example the code below returns CBA

  1. SELECT REVERSE('ABC')

Reverse is handy if you need to split values, take a look at this example

  1. CREATE TABLE #TestCityStateZip (csz CHAR(49))
  2.     INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
  3.     INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
  4.     INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
  5.     INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
  6.     INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
  7.     INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
  8.     INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
  9.     INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')
  10.  
  11.  
  12.     SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
  13.     LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
  14.     RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
  15.     FROM #TestCityStateZip




GETUTCDATE

  1. SELECT GETUTCDATE()


Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.


And that is all, those are the ten functions that you should be using but currently you are not using all of them. Look them up in Books On Line so that you can see some more examples


Contributed by: --SQLDenis 02:59, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks

181 Rating: 3.5/5 (53 votes cast)