Getting output parameters when executing reader in ADO.NET

Interestingly, the values of output parameters are not available after ExcecuteReader() is called, even after all records were fetched.

To get these values it is required to either close reader (call reader.Close()) or at least call NextResult() method.

Linq to SQL performance

There is a new hype about Linq to SQL now. And honestly, I don't get it. May be I am too old :)

Anyway, I would not care about this much if it was just about how you write your code. The problem is that to save few lines of code you loose performance. I wanted to compare how much is lost when I found that someone already did it here.

Aliases work in order clause in MS-SQL 2005

Not that I solved any problem but, today I accidentally found that aliases work in order clause if not used in functions. For example, this will work.

select c.Name, l.Value Lookup
from Customer c
left outer join LookupValue l on l.id = c.LookupId
order by Lookup

This didn't work in Oracle four years ago, where l.Value had to be used instead. And this is really nice.

Alter column set default doesn't work in T-SQL 2005

Looking to the MSDN help http://msdn2.microsoft.com/en-us/library/ms174123(SQL.100).aspx (Same topic I have in local MSDN Library) I could not understand why following commands did not work:

alter table [Albums] alter column [Comment] set default 'default';
alter table [Albums] alter column [Comment] set not null;

I was getting error message:

Incorrect syntax near the keyword 'set'.

First I found this solution for default:

alter table [Albums] add constraint DF_Albumns_Comment default 'default' for [Comment];

constraint name is optional;

For not null similar solution:

alter table [Albums] add constraint NN_Albumns_Comment check ([Comment] is not null);

produces slightly different result. This new constraint is added over existing table definitions. The definition for column [Comment] is that it still allows nulls.

But then I noticed that online help was in fact for SQL 2008 compact edition, and later I found updated version (February 2008) for SQL 2005
http://msdn2.microsoft.com/en-us/library/ms190273.aspx

Now ther is no option for default here, so solution with new constraint should be used. But for not null constraint I can use this:

alter table [Albums] alter column [Comment] varchar(30) not null;

And no mistery.

Extracting Date from DateTime in MS SQL

I don't know may be you know better way of extracting Date from DateTime in MS SQL, but the one I like is

DATEADD(d, 0, DATEDIFF(d, 0, [DateTime Column]))

Using conversion would be converting datetime to number, then to integer, then back to date. I didn't measure performance, but don't see much problems with arithmetic.

Semicolon in SQL Startup parameters

It took me some time to find that when more than one parameter is used as Startup Parameters in SQL Sever, they should be separated by semicolon rather than space.

For example:
-dC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf;-T4616