Using bigint with FORMATMESSAGE

SQL Server 2016 added the FORMATMESSAGE function.  According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

So let's take a look at this new function, run the following

SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50) 
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50); 

Here is the output if you run that

--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246

Here is what the type specifications that you can use are

Type specification		Represents
d or i		Signed integer
o		Unsigned octal
s		String
u		Unsigned integer
x or X		Unsigned hexadecimal

We used i to denote a signed integer, we also used u to denote a unsigned integer


Let's look at another example, this time we are using a variable. The variable will be an integer and we  are using i as the type specification

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------
The value you supplied 1 is incorrect!


That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).
The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.


As you can see that did not work, so what can we do?

One thing we can do is converting the value to a varchar and then use s as the type specification

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
   CONVERT(VARCHAR(100),@Val));

You will again get this as output

---------------------------------------
The value you supplied 1 is incorrect!

So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?

Another way is to use I64d as the type specification

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);

You will get this

---------------------------------------
The value you supplied 1 is incorrect!

So there you have it, if you want to use bigint with FORMATMESSAGE use I64d as the type specification, or convert to varchar and use s as the type specification

How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008

How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)


--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases


--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases

Summer SQL Teaser #13 Numeric

Hi and welcome to another fascinating SQL summer teaser. Summer it is except inPrinceton where it was 50 degrees this week.
There was no teaser last week because of a death in the family, I had to go to a wake and a funeral last week. That is why the teaser will be posted on a Thursday this week ;-)



look at these values

$55.69
1.4e35
2d4
3.7
412

How many numeric values do you see? What do you think SQL Server's ISNUMERIC function will return for those values?

Let's find out, run the following code

CREATE TABLE #Temp (Data varchar(18))

INSERT INTO #Temp VALUES('$55.69')
INSERT INTO #Temp VALUES('1.4e35')
INSERT INTO #Temp VALUES('2d4')
INSERT INTO #Temp VALUES('3.7')
INSERT INTO #Temp VALUES('412')
INSERT INTO #Temp VALUES(CHAR(9)) --tab

Now without running this try to guess which values will be 1 and which 0. I added a bonus ISNUMERIC(ColumnName + 'e0') function. What do you think that will do? Remember first try to guess and then run the code. Any surprises?

SELECT Data,
ISNUMERIC(Data) AS [IsNumeric],
ISNUMERIC(Data + 'e0') AS IsReallyNumeric
FROM #Temp

C# IsNullOrEmpty Function In SQL Server

Mladen Prajdic has created a SQL equivalent of the C# IsNotNullOrEmpty
I looked at it and thought that there was way too much code

Here is my version which I have modified, you pass an additional parameter in to indicate whether you want blanks only to count or not


CREATE FUNCTION dbo.IsNotNullOrEmpty(@text NVARCHAR(4000),@BlanksIsEmpty bit)
RETURNS BIT
AS

BEGIN
DECLARE @ReturnValue bit

IF @BlanksIsEmpty = 0
BEGIN
SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(@text),0))
END
ELSE
BEGIN
SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(RTRIM(@text)),0))
END

RETURN @ReturnValue
END
Go


Here are some calls where we want blanks to return as empty or null
The function returns = if it is empty and 1 if it is not empty

SELECT dbo.IsNotNullOrEmpty(null,1),dbo.IsNotNullOrEmpty('azas',1),
dbo.IsNotNullOrEmpty(' ',1),dbo.IsNotNullOrEmpty('',1)


Here are some calls where we don't want blanks to return as empty or null

SELECT dbo.IsNotNullOrEmpty(null,0),dbo.IsNotNullOrEmpty('azas',0),
dbo.IsNotNullOrEmpty(' ',0),dbo.IsNotNullOrEmpty('',0)

My function is the opposite of Mladen's I check for is NOT null or empty instead of IS null or empty (easier to code it with the SIGN function)

Why Does OBJECTPROPERTY Have A TableIsFake Property?

You can run the following function (OBJECTPROPERTY(object_id, N'TableIsFake') ) on an object and it will return 1 if the table is fake and 0 otherwise.
What does BOL say? The table is not real. It is materialized internally on demand by SQL Server. So does this mean it is a table valued function?
Well one way to find out.

Run this

USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO


And we get back a table valued function in the resultset

name: ufnGetContactInformation
object_id: 439672614
type_desc: SQL_TABLE_VALUED_FUNCTION
object_definition; CREATE FUNCTION .... (I truncated the rest)


Now create another function

CREATE FUNCTION [dbo].[ufnGetSomeTable]()
RETURNS @SomeTable TABLE (
[ContactID] int PRIMARY KEY NOT NULL)
BEGIN
INSERT @SomeTable VALUES(1)
INSERT @SomeTable VALUES(2)

RETURN
END
GO

Run this to make sure it works
SELECT * FROM ufnGetSomeTable()

run the same query again

USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO


And yes we get 2 functions back ;-)

Now we will create some temp tables to see if those are fake

CREATE TABLE #testFakeTable1234 (id int)
CREATE TABLE ##testFakeTable1234 (id int)

Now run this and you will see that those are real and not fake

USE tempdb;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') as IsFake
FROM sys.objects
WHERE name LIKE '#testFakeTable1234%'
OR name LIKE '##testFakeTable1234%'
ORDER BY type_desc, name;
GO

DROP TABLE #testFakeTable1234,##testFakeTable1234


Now create this function

USE AdventureWorks;
GO

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1) ,
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L0)
SELECT n FROM Nums WHERE n <= @n;

Test it out to make sure it works
SELECT * FROM dbo.fn_nums(2)


Run the following 2 queries

USE AdventureWorks;
GO

SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake, OBJECTPROPERTY(object_id, N'IsTableFunction') IsTableFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') <> OBJECTPROPERTY(object_id, N'IsTableFunction')
ORDER BY type_desc, name;


USE AdventureWorks;
GO

SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake,
OBJECTPROPERTY(object_id, N'IsInlineFunction') IsInlineFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') <> OBJECTPROPERTY(object_id, N'IsInlineFunction')
ORDER BY type_desc, name;


So a SQL_table valued function is a fake table but a SQL inline table valued function is not a fake table????
So there you have it, confused? Good!

SQL Server 2005 SP2 Has Added The OBJECT_SCHEMA_NAME Function, OBJECT_NAME Has Been Enhanced

SQL Server 2005 SP2 has an important enhancement to the OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. Because you pass object_id, and database_id to the OBJECT_NAME function it is not needed anymore to write dynamic SQL to get multi DB results.

The OBJECT_SCHEMA_NAME metadata function can be used to return the schema name of a schema-scoped object like a table or a view by specifying the object identifier and optional database identifier.

Read more about these 2 functions here, there is also SQL code available that show you how to use the functions on the site.

Ten SQL Server Functions That You Hardly Use But Should

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

I also cross posted this here: http://dotnetsamplechapters.blogspot.com/2007/09/ten-sql-server-functions-that-you.html


BINARY_CHECKSUM
SIGN
COLUMNPROPERTY
DATALENGTH
ASCII, UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE


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 o SQL Server 2000 to see it work, you can ofcourse use any tables just modify the queries
Let’s get started…

--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors

--update 5 records by appending X to the au_fname
SET ROWCOUNT 5


UPDATE tempdb..authors2
SET au_fname =au_fname +'X'


--Set rowcount back to 0
SET ROWCOUNT 0

--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'

--*** The BIG SELECT QUERY --***

--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2

--Clean up
DROP TABLE tempdb..authors2
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

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

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


USE pubs
GO

--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id

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

--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id

RETURN SIGN(@@ROWCOUNT)
GO


--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO

--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO

--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO

--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO


--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
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

CREATE TABLE blah (ID DECIMAL(5,2) not null DEFAULT 99)
INSERT blah DEFAULT VALUES

SELECT * FROM blah
SELECT COLUMNPROPERTY( OBJECT_ID('blah'),'ID','AllowsNull') AS AllowsNull,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsComputed') AS IsComputed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsCursorType') AS IsCursorType,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsFulltextIndexed') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdentity') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdNotForRepl') AS IsIdNotForRepl,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIndexable') AS IsIndexable,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsOutParam') AS IsOutParam,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsPrecise') AS IsPrecise,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Precision') AS 'Precision',
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Scale') AS Scale,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','UsesAnsiTrim') AS UsesAnsiTrim
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

DECLARE @V VARCHAR(50)
SELECT @V ='ABC '
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
DECLARE @V NVARCHAR(50)
SELECT @V ='ABC'
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

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




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

Syntax
NULLIF ( expression , expression )

DECLARE @v VARCHAR(20)
SELECT @v = ' '

SELECT NULLIF(@v,' ')

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

DECLARE @v VARCHAR(20)
SELECT @v = ' '

SELECT COALESCE(NULLIF(@v,' '),'N/A')


Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))

INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')

--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah


--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
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

DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'

SELECT PARSENAME(@ParseString,4),
PARSENAME(@ParseString,3),
PARSENAME(@ParseString,2),
PARSENAME(@ParseString,1)


CREATE TABLE #Test (
SomeField VARCHAR(49))

INSERT INTO #Test
VALUES ('aaa-bbbbb')

INSERT INTO #Test
VALUES ('ppppp-bbbbb')

INSERT INTO #Test
VALUES ('zzzz-xxxxx')

--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test



Another example:

CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
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.

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



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
DECLARE @v VARCHAR(11)
SELECT @v ='-X-'


SELECT STUFF(@v, 2, 1, '98765'),
STUFF(@v, 2, 0, '98765'),
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

DECLARE @v VARCHAR(11)
SELECT @v ='123456789'

SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')



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

SELECT REVERSE('ABC')

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

CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')


SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip





GETUTCDATE
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

Check If Auto Update Statistics Is Enabled With DATABASEPROPERTY

How do you check if auto update statistics is enabled on your database? It is pretty easy to check that, you can use the DATABASEPROPERTY function
Run the following line of code

SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics

If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled

Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code


DECLARE @v VARCHAR(55)
SELECT @v = 'pubs'

SELECT
DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
DATABASEPROPERTY(@v,'Version') AS Version

So what do all these values mean? Here is a list of all the properties

IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsBulkCopy
Database allows nonlogged operations.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDboOnly
Database is in DBO-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDetached
Database was detached by a detach operation.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInLoad
Database is loading.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInRecovery
Database is recovering.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNotRecovered
Database failed to recover.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsOffline
Database is offline.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsReadOnly
Database is in a read-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsShutDown
Database encountered a problem at startup.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsSingleUser
Database is in single-user access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsSuspect
Database is suspect.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE
0 = FALSE
NULL = Invalid input

Version
Internal version number of the Microsoft® SQL Server™ code

SQL Server Doesn't Like Cheaters

I emailed a joke(see below) to some friends and one of them replied: "So what you're saying is that I should take a mistress?"

So that got me thinking, what would SQL say about this? Is mistress equal to mistrust. Well according to SQL server it is, run this in Query Analyzer

SELECT SOUNDEX('mistress'),SOUNDEX('mistrust'),DIFFERENCE('mistress','mistrust')

And here is the joke:

Two Ladies talking in heaven

1st woman: Hi! My name is Wanda.

2nd woman: Hi! I'm Sylvia. How'd you die?

1st woman: I Froze to Death.

2nd woman: How Horrible!

1st woman: It wasn't so bad. After I quit shaking from the cold, I
began to get warm & sleepy, and finally died a peaceful death. What
about you?

2nd woman: I died of a massive heart attack. I suspected that my
husband was cheating, so I came home early to catch him in the act.
But instead, I found him all by himself in the den watching TV.

1st woman: So, what happened?

2nd woman: I was so sure there was another woman there somewhere that I started running all over the house looking. I ran up into the attic and searched, and down into the basement. Then I went through every closet and checked under all the beds. I kept this up until I had looked everywhere,and finally I became so exhausted that I just keeled over with a heart attack and died.

1st woman: Too bad you didn't look in the freezer---we'd both
still be alive.


So what is the point of this all? Well it gives you a reason to run those barely used functions like SOUNDEX and DIFFERENCE ;>

ROW_NUMBER, NTILE, RANK And DENSE_RANK

Yesterday I showed how to do ranking in SQL Server 2000, today we will look at how it's done in SQL Server 2005

CREATE TABLE Rankings (Value Char(1),id INT)
INSERT INTO Rankings
SELECT 'A',1 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'B',4 UNION ALL
SELECT 'B',5 UNION ALL
SELECT 'C',2 UNION ALL
SELECT 'D',6 UNION ALL
SELECT 'E',6 UNION ALL
SELECT 'F',5 UNION ALL
SELECT 'F',9 UNION ALL
SELECT 'F',10



ROW_NUMBER()
This will just add a plain vanilla row number

SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
FROM Rankings


The following one is more interesting, besides the rownumber the Occurance field contains the row number count for a given value
That happens when you use PARTITION with ROW_NUMBER

SELECT ROW_NUMBER() OVER( ORDER BY value ) AS 'rownumber',
ROW_NUMBER() OVER(PARTITION BY value ORDER BY ID ) AS 'Occurance',*
FROM Rankings
ORDER BY 1,2


This is just ordered in alphabetical order descending

SELECT ROW_NUMBER() OVER( ORDER BY Value DESC) AS 'rownumber',*
FROM Rankings

RANK()
Rank will skip numbers if there are duplicate values

SELECT RANK() OVER ( ORDER BY Value),*
FROM Rankings


DENSE_RANK()
DENSE_RANK will not skip numbers if there are duplicate values

SELECT DENSE_RANK() OVER ( ORDER BY Value),*
FROM Rankings


NTILE()
NTILE splits the set in buckets
So for 11 values we do something like this: 11/2 =5 + 1 remainder, the first 6 rows get 1 the next 5 rows get 2
If we use NTILE(3) we would have something like this: 11/3 =3 + 2 remainders, so 3 buckets of 3 and the first 2 buckets will get 1 of the remainders each

SELECT NTILE(2) OVER ( ORDER BY Value ),*
FROM Rankings

SELECT NTILE(3) OVER ( ORDER BY Value ),*
FROM Rankings