Vikram Kamath

August 9, 2011

SQL Server Version and Build Number

Filed under: SQL Server — Tags: , , , — Vikram @ 15:59

I try to link various SQL Server versions (RTM, Service Packs, Cumulative Updates) with respective build numbers. Here are a few links that I use which might come in handy for many of you database developers out there. Some of these links are official and some aren’t.

  1. How to determine the version and edition of SQL server and its components
  2. SQL Server Version
  3. SQL Server Version Database
  4. sqlserverbuilds.blogspot.com

Some of the above links are comprehensive enough for most developers.

Some useful SQL Server functions to determine the version and edition:

@@VERSION
SERVERPROPERTY('productversion')
SERVERPROPERTY('productlevel')
SERVERPROPERTY('edition')
Advertisements

SQL Server identities: identity seed, increment and current value

Filed under: SQL Server — Tags: , , , , — Vikram @ 12:39

I have been trying to get the current seed value for an identity column using SQL server object metadata. The only luck I have had so far is to get the seed value and the increment value that was used when a table with the identity was created. I thought the queries would come in handy at some time or the other in future. So, here it is.

SELECT  TABLE_SCHEMA,
	TABLE_NAME,
	IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_SEED_VALUE,
	IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_INCR_VALUE,
	IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_CURRENT_VALUE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'TableHasIdentity') = 1
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'IsMSShipped') = 0
ORDER BY TABLE_SCHEMA, TABLE_NAME

Another variation of the above query also retrieves the name of the column with the identity specified.

select  sch.name as TABLE_SCHEMA,
	obj.name as TABLE_NAME,
	col.name as COLUMN_NAME,
	col.seed_value as IDENT_SEED_VALUE,
	col.increment_value as IDENT_INCR_VALUE,
	col.last_value as IDENT_CURRENT_VALUE
from sys.schemas as sch
join sys.objects as obj on obj.schema_id = sch.schema_id
join sys.identity_columns as col on col.object_id = obj.object_id
where obj.type = 'U' and obj.is_ms_shipped = 0
order by sch.name, obj.name

The SQL function IDENT_CURRENT() returns the current value or the last identity value generated for the table.

However, note that the SQL functions IDENT_SEED() and IDENT_INCR() only return the values that were used during the creation of the table. If you have reseeded the table identity since its creation, then the functions would still show the original values.

I would like to know if anyone has found a way to get the last seed value specified for any given table when it was reseeded after the table was created.

April 9, 2010

DateTime Manipulation: Continued

Filed under: SQL Server — Tags: , , , — Vikram @ 16:55

In my previous post DateTime Manipulation: Date Part or Time Part I had mentioned about how we can get the date part and time part from a given DateTime. A couple of days back I came across an article in sqlservercentral.com where another solution has been provided to get the date and time part out a given datetime using the DATEADD and DATEDIFF functions

Here is the link to the article Date Manipulation with DATEADD/DATEDIFF

Note: You may need to be logged into sqlservercentral.com to view this article

Create a free website or blog at WordPress.com.