Almost all SQL Server database developers know this issue, in other word, limitation. You cannot insert values lower than 01/01/1753 into datetime column. Do you know the reason? I was unaware of this reason but yesterday, one of my friends (He insisted not mention his name :)) explained me one of possible reasons. And guess what, it is not a technical reason, it is historical reason.

You might know that there had been two calendar types; Julian and Gregorian. When the people who used Julian decided move to Gregorian, they had to skip couple of days (10 to 14 days). This was happen around 1752. Because of this, the dates earlier than 1753 are different from country to country and have to maintain different procedures for different countries (or cultures) if the system need to support for them. That’s why SQL Server treats 01/01/1753 as the earliest date.

Is new datatype introduced? Is it just like varchar? I thought yes. But it is not. Vardecimal is a storage format, not a datatype. Because of that it is not available for columns when design tables. I have been trying to find what it is, yes after Gogula's comment for my one of previous posts. Finally I figured it out how to use it; see the sample code I used for testing.

-- create a table with decimal column
CREATE TABLE TestTable
(ID int IDENTITY(1,1) PRIMARY KEY, Number decimal(20,3))
GO

-- insert some values
DECLARE @i int
SET @i = 1
WHILE @i < 100000
BEGIN

   INSERT INTO TestTable
   VALUES (rand()*1000000)

   SET @i = @i + 1
END

-- check the space used for table
sp_spaceused 'TestTable'

-- result
-- reserved: 2632KB
-- data: 2576KB

Since vardecimal is a storage format, it cannot be set it to column itself. It has to be set for; first database and then table. But before all of these, it is possible to check whether the size reduction after enabling the format is really a worth enough.

-- check the estimated reduction for the row.
sys.sp_estimated_rowsize_reduction_for_vardecimal 'TestTable'

*Note: BOL shows the name of SP as sys.sp_estimate_ro... . Make sure you use it as sys.sp_estimated_ro.... .

-- result
-- avg_rowlen_fixed_format: 24.00
-- avg_rowlen_vardecimal_format: 17.98

The result tells us that the row size can be reduced by 25%. Isn't good? Yes, It is worthwhile to change the storage format of decimals. Enable it on the database and then the table.

-- enable the database for vardecimal storage format
sp_db_vardecimal_storage_format 'TestDatabase', 'ON'

-- enable the table
sp_tableoption 'TestTable', 'vardecimal storage format', true

Done. Lets check the space usage again.

-- check the space used for table
sp_spaceused 'TestTable'

-- result
-- reserved: 2248KB
-- data: 2176KB

Great. We just saved the disk space. And this is just for 100000 records. If you have millions of records with decimal values, Then it is high time to set this. Note that it is better to check the size reduction estimate before enabling it on the table because it might give you an unexpected result.

Had a chance to check this new trigger, wow it's cool and worked fine for me. This fires only after the authentication process is completed but before the session is estabilished. This is what I wrote to test this new trigger, it checks and accpets only windows users not SQL users.

CREATE TRIGGER ValidateLogon
ON ALL SERVER FOR LOGON
AS
BEGIN

   IF EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)') != 'Windows (NT) Login'
   BEGIN

      ROLLBACK
   END
   -- only for Windows login
   -- INSERT INTO TestTable VALUES (EVENTDATA())
END

I wanted to execute the INSERT statement even for SQL login but seems it is not executed if ROLLBACK is fired; because it is the original implicit transaction?

If you have used “SQL Server Scripts” project templates for maintaining scripts for databases, you must have noticed that order of the queries are not ordered (either ascending or descending). If you add a query to the project, it will be added to the end of the list. I used to manually change the order of added quarries by opening project file through one of text editors but as it is a painful task, most of the time, I didn’t do it.

When I open one of my projects today, I wondered, at last, list is automatically sorted. How did it happen? Yes, this is after installing CTP for SP2.

But it is not as smart as I thought. When I add a new query, it is added to correct place in the list (again, there is no way of giving the name of the query when adding so it is always “SQLQuery….”), but once it is renamed, it does not adjust the order of the list. Hope we will see the fix soon.

Saw two new menu items for table objects; Edit (If I am not mistaken) and Reports, Edit creates the table CREATE script, Reports does: No idea yet.

Guys, The SP2 for SQL Server 2005 is available now. This contains cool new stuff and fixes. One of the new stuff is, vardecimal storage format. And seem it has fixed scriping operation (DROP before CREATE) too, and much more.

You can download it from here. For more info, click this.

This is a very simple operation but couldn't load it into my mind the way of doing it even though Dr. Nitin S Paranjape showed @ the TechEd Sri Lanka. This is what I wanted to do;

In Excel file, I have list of values on A1 column.

A1
Value1
Value2
Value3
....
....
....

Now I want move them in to A1, A2, A3,....... like below.

A1        A2         A3        ......
Value1  Value2   Value3   ....

How do I do it? Copy each values on A1 and paste? No there is a way of doing it simply that cannot remember. Luckly it was in Prasanna's head. All I have to do is;

Copy all A1 values,
Open Paste Special dialog box from Edit menu,
Select Transpose and click on OK.

It may help you too. So, remember it :)