Default size for VARCHAR

What is the output of this statement?
select cast('abcdefghijklmnopqrstuvwxyz1234567890' as varchar)

It's
abcdefghijklmnopqrstuvwxyz1234

Why?
Because, CAST uses a default size of 30 for varchar!
Always make sure you specify the size of your dataypes to avoid such gotchas.

System objects

If we want to find text in a stored proc definition, we can use this
select * from sysobjects where id in (
select id from syscomments where text like '%TextToFind%')

This approach seems to be outdated though. These system tables were available in SQL Server 2000, and are available in SQL Server 2005/8 as compatibility views for backward compatibility.

The recommend approach is to use the new system views such as sys.sql_modules and sys.procedures. The above query can now be written as
select * from sys.procedures
where object_definition(object_id) like '%TextToFind%'

Reference:
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

DateTime difference

In C#, if I have this
DateTime startedOn = DateTime.MinValue;
DateTime endedOn = DateTime.Now;
then
endedOn - startedOn is a valid expression that returns a TimeSpan with the difference between the 2 dates.

However, the same doesnt work in SQL Server. We need to use
DATEDIFF(s, startedOn, endedOn)
where s stands for seconds. So, if the 2 datetime values have a difference of 10 seconds and 1 minute, and if you expect 10, you are thinking Excel. The value returned here would be 70.

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

Dumb-me values

I wanted to perform some load testing of my app. And for which I had to insert some dummy data into a SQL table. Since we wanted to put in maximum data into the fields, it followed that each of the varchar fields should have data with its size limit. Instead of the usual dummy data, I put in some thought to come up with this instead. Am sharing this  since I thought this was nice. :)

First Name	16 characters	MyFirstNameIsBig
Last Name	16 characters	MyLastNameIsLong
Address1	30 characters	My Address1 field is humongous
Address2	30 characters	My Address2 field is huge too!
City	30 characters	I live in a pretty cloudy city
State	30 characters	I'm in a const state of denial
Postal Code	14 characters	what zip code?
Email Address	46 characters	writingCodeCanBeFunIfUKnowHow000000@iMGeek.com

I didnt come up with the size limits, just the values. The last one is a 6-digit 0-prefixed number that is used to store a million possible values.

Identity field - int or not

I have an identity field in my SQL Server database's table. And I have this stored procedure which inserts a row in this table. The stored proc also returns a few other calculated values other than the new id created. So, I have a SELECT query at the end of the stored proc which goes like this

SELECT SCOPE_IDENTITY() as SomeId, @bar as SomethingImportant

And I use this code in C# to extract this id
int id = (int)rows[0]["SomeId"];

This gives me an invalid cast. Upon inspection, I notice that the value returned from the db call is decimal, not int! Why? Because SCOPE_IDENTITY, and even @@IDENTITY and IDENT_CURRENT(tablename) have the return type defined as NUMERIC(38, 0). This causes .Net to consider it as a decimal.

http://msdn.microsoft.com/en-us/library/ms190315.aspx
http://msdn.microsoft.com/en-us/library/ms187342.aspx
http://msdn.microsoft.com/en-us/library/ms175098.aspx

I ended up using it like this
int id = (int)(decimal)rows[0]["SomeId"];

UPDATE: In my second version, I assigned the value from SCOPE_IDENTITY() to a locally declared INT variable in the stored procedure, and returned that instead. That way, the C# code also takes it as in int.

SQL Server in-built functions

Some basic programming in SQL Server that comes handy.
Need to pad your number with zeroes? Use this
RIGHT('00000' + cast(@i as varchar(6)), 6)

Need a random 3-digit access code? Use this
RIGHT('00' + CAST(floor(rand()*1000) as VARCHAR(3)), 3)

Need your guid without the hyphens? Use this
REPLACE(CAST(NEWID() AS varchar(36)), '-', '')

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

Reset IDENTITY field

Let us say you have an an IDENTITY field in your table in SQL Server, and have deleted all rows from it, and now wish to insert fresh data. But on doing so, you would notice that this table still remembers the identity value of the last row inserted into it. To start afresh, you would need to do an identity reset. And here is the syntax to do it
DBCC CHECKIDENT (tablename, RESEED, currentvalue)

So, if you want your orders table's identity field to start from 1, you need to use this as
DBCC CHECKIDENT (orders, RESEED, 0)

Reference: http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/

SQL Server 2008 Editions

This is where you can get SQL Server 2008 Management Studio Express (free SQL client)
http://www.microsoft.com/download/en/details.aspx?id=7593

This is where you can get SQL Server 2008 Express (free SQL host)
http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

And this is where you can find more about the other editions
http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

I just noticed today that some people actually write (not just say) SQL as Sequel. :)
Found a discussion about it here as well.
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=62158

New datatype - DateTime2

Want to know more about the datetime data type in SQL Server?http://technet.microsoft.com/en-us/library/ms187819.aspx

The first thing they will tell you is about the new datetime2 data type.
http://technet.microsoft.com/en-us/library/bb677335.aspx
"Use the datetime2 data type for new work. This type aligns with the SQL Standard. It is more portable."

The interesting thing is that it has more seconds precision with the same storage size of 8 bytes. In fact, it takes only 7 bytes for a precision of 3 or 4 digits, and 6 bytes for less than 3 digits.

datetime2 in SQL Server also has the same date range as that of DateTime defined in .Net, from
January 1, 0001 00:00:00.0000000
through
9999-12-31 23:59:59.9999999
as defined by DateTime.MinValue and DateTime.MaxValue
http://msdn.microsoft.com/en-us/library/system.datetime.minvalue.aspx
http://msdn.microsoft.com/en-us/library/system.datetime.maxvalue.aspx

The earlier datatype (datetime) in SQL Server has a lesser range and lesser precision.
January 1, 1753 00:00:00.000
through
December 31, 9999 23:59:59.997

So, beware when you use DateTime.MinValue or MaxValue in .Net and send it to a datetime parameter in SQL Server. Use datetime2 instead.

It seems System.Data.DbType.DateTime2 is also defined in .Net from framework 3.5 onwards.
http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx

Transfer data from remote server to local

We have a stored procedure on a SQL Server 2005 box which returns us a result-set. We wanted to execute this stored procedure from a SQL Server 2008 box and store the results into a table locally. So, we created a SQL user on the local (2008) box, a linked server to the 2005 box and executed the stored procedure

exec[linkedserver].[db].[schema].[storedproc]
This works fine.

But, if I try to insert this data into a table variable or a temp table, it gives me an error.
DECLARE @temp ASTABLE (column1 datatype, column2 datatype)
INSERT INTO @temp exec [linkedserver].[db].[schema].[storedproc]
The error is

OLE DB provider "SQLNCLI10" for linked server "linkedserver" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "linkedserver" was unable to begin a distributed transaction.

We didnt want to turn on MSDTC for this since there were no distributed transactions involved. So, we decided to use OPENQUERY, like this.
DECLARE @temp ASTABLE (column1 datatype, column2 datatype)
INSERT INTO @temp exec [linkedserver].[db].[schema].[storedproc]
INSERT INTO @temp SELECT * FROM OPENQUERY([linkedserver],'[db].[schema].[storedproc]')

Note that the last argument to OPENQUERY is the command that gets executed on the remote server locally. So, make sure you dont have the linked server prefix in the stored proc's name.
Reference http://msdn.microsoft.com/en-us/library/ms188427.aspx

Also posted this on
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/2da5983c-2256-4394-82e0-c0c26c663a7e/

Clustering, log shipping or mirroring

Good details on the different options available in SQL Server for high availability.
http://sql-articles.com/articles/high-availability/comparison-between-mirroring-vs-log-shipping-and-mirroring-vs-clustering/

Sending table data to a stored procedure

For context, please refer my earlier post at http://wilth.blogspot.com/2011/10/cant-open-symmetric-keys-in-udfs.html

So, since we couldnt use our UDF and had to use a stored proc instead, the trouble with using a stored proc is that it cant directly go into the VALUES clause of an INSERT INTO---SELECT statement, like a UDF could. This means we would need to execute multiple (about a million, seriously) INSERT statements by looping on a temp table.

An alternative would be to have the remote stored proc instead do all the work of opening the key, executing the INSERT INTO---SELECT statement and then closing the key. This has the added benefit of not exposing the TABLE to the remote user. How do we do it? Using TABLE-type parameters. They just came up in SQL Server 2008.
http://msdn.microsoft.com/en-us/library/bb510489.aspx
explained more in
http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

And so, we made this stored proc. But sadly, the DB server that makes the call is still on SQL Server 2005, and so could not invoke this stored procedure. The alternative, it seems, is to send an XML parameter.
http://www.sqlservercentral.com/articles/News/3182/

Cant open symmetric keys in UDFs

We had planned to abstract the use of our encryption logic inside a user-defined function in SQL Server 2008 R2. The caller would send the plain text and we could just send back the encrypted content without having the user to worry about with keys, certificates, encryption algorithms and the daily grind.

But, it appears its not so straightforward, since one would get this error if one attempted to create such a UDF:
"Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function."

The workaround is to write this logic in a stored procedure and have that executed by the caller. Of course, it isnt as nice as using the UDF in an INSERT statement directly. But, I guess we can live with that for now.

Reference:
http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/b56f3ef4-10d4-46c1-ade1-dedeee36ac33