Skip to main content

Posts

Showing posts from September, 2012

Insert a duplicate in IDENTITY column!!!!!

Let me create a table having Identity column. CREATE   TABLE IDENT (ID INT IDENTITY(1,1), NAME VARCHAR(100)) As above query, I created two columns where ID is one of column name which is having IDENTITY constraint and it will generate 1,2,3,,,,m as records are inserted automatically. Continuing my work, let me insert a value into NAME column. INSERT   INTO IDENT VALUES ('DHINAKARAN') SELECT   *   FROM IDENT. Here we can see that after running the above query, the result is as shown in below snippet. Let me create a table having Identity column. CREATE   TABLE IDENT (ID INT IDENTITY(1,1), NAME VARCHAR(100)). As above query, I created two columns where ID is one of column name which is having IDENTITY constraint and it will generate 1,2,3,,,,m as records are inserted automatically. Continuing my work, let me insert a value into NAME column. INSERT   INTO IDENT VALUES ('DHINAKARAN') SELECT   *   FROM IDENT ...

Difference Between SYSDATETIME and GETDATE( )

If we run the following Query, SELECT GETDATE () AS GTDATE SELECT SYSDATETIME () as SYSDATE   We get same result but difference is GETDATE the precision is till miliseconds and of SYSDATETIME the precision is till nanoseconds. In SQL Server 2005, We had only one function, GETDATE( ) which returns a DATETIME value. Its precision was till milliSeconds. But later in SQL Server 2008, onwards, to get Time precision to Nano seconds, they introduced a new Data type called DATETIME2. Getdate ( ) with returning  DATETIME2 had a problem with precision with nanoseconds. So SYSDATETIME is introduced which supports till NanoSeconds, which will return DATETIME2 Type and where as Getdate ( ) will return DATETIME type. DATETIME value has precision upto 3 milliseconds.  DATETIME2 value has precision upto 100 Nanoseconds.

Restore AdventureWork2012 Database MDF file and common encountered issues!!!

We can restore AdventureWork2012 Database onto our SQL SERVER 2012. We can easily do this by writing a Simple T-SQL query. We can get and download MDF file for SQL Server 2012 in the following link. http://msftdbprodsamples.codeplex.com/releases/view/55330#DownloadId=165399 CREATE DATABASE   AdventureWorks2012  ON   ( FILENAME   =   'C: \dhinakaran\acer\AdventureWorks2012_Data.mdf' ) FOR   ATTACH_REBUILD_LOG   Where 'C: \dhinakaran\acer\AdventureWorks2012_Data.mdf’ is the path of my MDF file.  The log files are also available on Microsoft site and if it’s not found, no worries, this query however will start with new Log File. While restoring AdventureWork2012, we may come across some errors. Most common one is Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file “ 'C: \dhinakaran\acer\AdventureWorks2012_Data.mdf’ ” failed with the operating system error 5(Access is denied.). Solution: Go to your MDF file and Ri...