Making a normal Column, an Identity column in SQL Server

Do you know that you cannot just change a normal column into an identity column in SQL Server? This is because that identity columns can only be created when creating new tables.

Now some people will say that how cannot that be? Because they are used to change using the SQL Server Management Studio (SQLSMS). What management studio will do is, it will create a temporary table with the Identity column and copy all your existing data there. Later it will delete the original table and will rename the temporary table to the original.

For example we will take Sales.Individual table in the AdventureWorks database.



Think that we need to add a column named Id as an identity column. What we will do is just type after ModifiedDate column and make the new column an identity column.

In such a case when we save the change SQLSMS will first create a temporary table which matches the new schema with the identity column.

Then it will set IDENTITY INSERT OFF and will copy the data from the original table to the newly created temporary table.

After this it will delete the original table and will rename the temporary table as the original table.

Did any one thought that the SQLSMS is doing this much of work for us without our knowledge?

Retrieving Schema Information of SQL CE Database

There are very limited features offered in SQL Compact Edition (SQL CE). But with those offered you can do many things if you know what is there and what is not there.

Following are some views available in SQL CE which you can use to retrieve information about the tables in the database.

-- Gets the list of Columns with their associated Tables. You will be able to retrieve the column properties such as Data Type, Allow Null, Default Values, etc.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

-- Retrieves information about the indexes contained in the database.
SELECT * FROM INFORMATION_SCHEMA.INDEXES

-- Retrieves information about the Primary Keys used in the tables.
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

-- Retrieves all the Tables in the database including the System tables.
SELECT * FROM INFORMATION_SCHEMA.TABLES

-- Retrieves all the Constrains in the database.
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

-- Retrieves all the Data Types available in the database.
SELECT * FROM INFORMATION_SCHEMA.PROVIDER_TYPES

-- If you have any referential integrity constraints in your database defined, those will be returned by this.
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Getting Date part only from SQL DateTime Value

Even though I have taken date part only from a datetime field earlier I had to struggle for some minutes yesterday, so I thought why I shouldn't put a blog entry on this so it will help me. Also I think that this will help many of you reading my blogs.

I found two ways to accomplish this without using DATEPART. I believe it is not good practice to use DATEPART and assemble the date by ourselves because it will sometimes change the original year, month and day order and at times that will cause some unnecessary problems.

Method 1

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

What is happening here is CAST(GETDATE() AS FLOAT) will cast the date into a floating point number {2007-04-12 22:12:00.837 = 39182.9236936343}.
Then the FLOOR function will return the largest integer part of the parameter. In our case the result of CAST(GETDATE() AS FLOAT) {39182.9236936343}. The result of this will be 39182.
Now I will caste this value again to a DATETIME value. This time, since there is no fraction part in the value that results in the time part to return as 00:00:00.000. {Fraction part represents the time.}
The final result will be 2007-04-12 00:00:00.000.

Method 2

SELECT CONVERT(NVARCHAR(20), GETDATE(), 101)

In this second approach what I am doing is simply Converting the date into a character. The last parameter of the CONVERT function {101} is the style number which will specify the conversion. Value 101 will return the value in the format of MM/DD/YYYY. {To acquire more information about conversion styles refer CONVERT function in SQL Books Online.} The result of this will be 04/12/2007.

One thing to remember when using second method is that the result will be in the format MM/DD/YYYY. So if you are comparing this value with another date you have to be careful to make the other comparing value also in the format of MM/DD/YYYY. Otherwise it will return wrong results.