MSBI

If we don't have any identity column and yet, we wish to see on what position does record lies, here is simple Query I did. We are using  ROW_NUMBER ( ) Without using ORDER BY clause. That means, We are suppressing the effect of ORDER BY Clause used in ROW_NUMBER ( ) CREATE TABLE C ( NAME VARCHAR ( 100 )) INSERT INTO C SELECT ( 'ABC' ) UNION ALL SELECT ( 'bharath' ) UNION ALL SELECT ( 'job' ) UNION ALL SELECT ( 'raghavendra' ) UNION ALL SELECT ( 'anita' ) UNION ALL SELECT ( 'prakash' ) UNION ALL SELECT ( 'dhinakaran' ) SELECT * FROM C  We know name ' Raghavendra ' lies at 4th Position and if we want to get this position number, then, we think of ROW_NUMBER ( ) concept but we know ROW_NUMBER ( ) will order the sequence the Records are stored and then It displays it. So in order to avoid this ordering of the records, I will use some dummy values...

Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the SQL server is controlled by Windows NT/2000 account or group used when logged in. this means that Windows username and password are used to access the database server. Mixed mode authentication : - Mixed mode allows use of Windows credentials and the local SQL server account. Admin’s might maintain user accounts in SQL Server.

We know basically when restarting the SQL server, it is basically means clearing Caches along with some other properties to get reset. But since our main Priority lies in Cleaning the Cache files, we can write a SQL script for doing this. SQL server has inbuilt function for clearing Cache Files. The main Cache files required to cleared during Restart is DATA CACHE and PROCEDURE CACHE.  This effectively will restart your server. The Script for clearing these two caches are as follows: DBCC DROPCLEANBUFFERS ---- This will clear the data cache DBCC FREEPROCCACHE ---- This will clear the procedure cache Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

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 ...

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.

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...

I have a table called TEMP and I have inserted values as shown below. We can see word 'Dhinakaran' is written in different formats as 4 records here. CREATE TABLE TEMP ( ID INT , NAME VARCHAR ( 100 )) INSERT INTO TEMP ( ID , NAME ) VALUES ( 1 , 'DHINAKARAN' ), ( 2 , 'dhINAKaRAN' ), ( 3 , 'dhinakaran' ), ( 4 , 'DHinakaRAN' ) SELECT * FROM TEMP  If I wanted to select only 'dhinakaran',  (3rd record here), Then my Query  SELECT * FROM TEMP WHERE NAME = 'dhinakaran' will fail to return 3rd row. The output of the actual query will be as by default while installing, SQL Server will take sentences as Case Insensitive. So we can overcome this problem by using COLLATE. COLLATE is implemented as follows in our Select Statement. SELECT * FROM  TEMP WHERE NAME COLLATE  Latin1_General_CS_AS = 'dhinakaran'   Adding COLLATE Latin...