SQL Server Technology Thirst

OOPS. I dont know... I though like... we need to post like this... Any way I will start writing as a blog... I will move this to seperate blogs. cheers, Venkatesan Prabu .J

Prabu definitly your codes are informative but why are you posting these as comments you should post them each as different blog. Nikhil dotnetask.blog.co.in

I think you should publish each item as separate blogs by clicking on "Submit a new Blog" instead of posting comments.

Very informative Thanks Manish

Shortcuts in SQL Server: Am seeing a frequent questions in the forums about creating shortcuts in SQL Server for frequent queries. How can we achieve it? Considering am using sp_who query for more than 100 times in a day. In that case, I am fed up with writing this query and execute it. Instead we can create short cuts in the SSMS. So that, we can use it easily. To achieve it. Goto Tools -> Options ->Environment -> Key board. You can type your query and press Ok button. That particular query will be set for the corresponding short cuts. Simple to use... Enjoy.... Cheers, Venkatesan Prabu .J

Here are some SQL queries asked frequently in interviews, Write an SQL query to display the records whose date is having time stamp of "14" hrs. CREATE TABLE VENKAT_TABLE(ID INT, MOBILE_NUMBER VARCHAR(15),AMOUNT INT, TIME_STAMP DATETIME) INSERT INTO VENKAT_TABLE VALUES(1,'9840578690',10000,'1/1/2010 15:20:20') INSERT INTO VENKAT_TABLE VALUES(1,'9840578690',5000,'1/1/2010 14:00:00') -- The below command will fetch all the entries happened around 14 hours. SELECT * FROM VENKAT_TABLE WHERE DATEPART(HH,TIME_STAMP) =14 -- The below command will fetch all the entries happened exactly at 14 hours. SELECT * FROM VENKAT_TABLE WHERE DATEPART(HH,TIME_STAMP) =14 and DATEPART(mi,TIME_STAMP) =00 and DATEPART(s,TIME_STAMP) =00 Write an SQL query to display the unique number series (Starting 4 digits is the series) present in the table. -- DISTINCT WILL PROVIDE YOU THE UNIQUE DATA SELECT DISTINCT ID FROM VENKAT_TABLE Write an SQL query to postfix '0' to the mobileno whose balance is more than 6000. UPDATE VENKAT_TABLE SET MOBILE_NUMBER = MOBILE_NUMBER +'0' WHERE AMOUNT>6000 Cheers, Venkatesan Prabu .J

Here are some SQL queries asked frequently in interviews, Write an SQL query to display the records whose date is having time stamp of "14" hrs. CREATE TABLE VENKAT_TABLE(ID INT, MOBILE_NUMBER VARCHAR(15),AMOUNT INT, TIME_STAMP DATETIME) INSERT INTO VENKAT_TABLE VALUES(1,'9840578690',10000,'1/1/2010 15:20:20') INSERT INTO VENKAT_TABLE VALUES(1,'9840578690',5000,'1/1/2010 14:00:00') -- The below command will fetch all the entries happened around 14 hours. SELECT * FROM VENKAT_TABLE WHERE DATEPART(HH,TIME_STAMP) =14 -- The below command will fetch all the entries happened exactly at 14 hours. SELECT * FROM VENKAT_TABLE WHERE DATEPART(HH,TIME_STAMP) =14 and DATEPART(mi,TIME_STAMP) =00 and DATEPART(s,TIME_STAMP) =00 Write an SQL query to display the unique number series (Starting 4 digits is the series) present in the table. -- DISTINCT WILL PROVIDE YOU THE UNIQUE DATA SELECT DISTINCT ID FROM VENKAT_TABLE Write an SQL query to postfix '0' to the mobileno whose balance is more than 6000. UPDATE VENKAT_TABLE SET MOBILE_NUMBER = MOBILE_NUMBER +'0' WHERE AMOUNT>6000 Cheers, Venkatesan Prabu .J

sp_who2 is an Undocumented/Upgraded extension of sp_who. SP_WHO : This will provide the following options, 1. System process ID. 2. Status of the process. 3. Login name of the user. 4. Name of the user. I 5. f the process is blocked, the SPID of the blocking process. 6. Database the process is using. 7. Command currently being executed. SP_WHO2: Along with the above options, sp_who2 will provide the following additional linformations 1. Total CPU time of each process. 2. Total amount of disk reads for each process. 3. Last time a client called a procedure or executed a query. 4. Application connected. Cheers, Venkatesan Prabu .J

Char data type: It is a fixed length data type and allows character datatype. For instance, if you have char(5) then it occupies fixed length of 5 bytes even though you have a string which have less than 5 character's length. The rest of the character are treated as blank spaces. Varchar datatype: It is a variable length data type and it occupies length for each row dynamically. So it doesn't have fixed length. In most of the cases, varchar type is preferred due to its proper memory usage. It occupies 1 bytes for each character. varchar (max) will have the max of 8000 in lower versions and 2 GB is upper versions. Nvarchar data type : To support different language beyond English language, Nvarchar data type is used. Basically, it supports unicode characters. It has the base property of varchar with memory of 2 bytes for each character. nvarchar (max) will have the max of 4000 in lower versions and 2 GB is upper versions. Cheers, Venkatesan Prabu .J

Cofiguring xp_cmd shell option can be done using two methods. Explicit xp_cmdshell option enabling is introduced in SQL Server 2005 and above. In SQL Server 2000, we should provide more access to the specific sql login user. In 2005 and above, 1. Using Surface Area configuration. Enable the xp_cmdshell option. 2. Use the below queries to achieve it. -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 0 -- 0 for disable, 1 for enable GO -- To update the currently configured value for this feature. RECONFIGURE GO Cheers, Venkatesan Prabu .J

This is one of my best short article in this blog. A very interesting one and I like the most. Usually, we will face this scenario like -> Fetch an employee and get the entire hierarchy of an employee like. A is reporting to B, B is reporting to C, C is reporting to D. If I need to find who is reporting to C, we need to fetch B followed by A and its depth. A fantastic Optimized solution to retrieve this information is, drop table Venkat_SampleTable create table Venkat_SampleTable(id int, nam varchar(10),bossid int) insert into Venkat_SampleTable values(1,'venkat',0) insert into Venkat_SampleTable values(2,'Arun',1) insert into Venkat_SampleTable values(3,'Suba',1) insert into Venkat_SampleTable values(4,'Karthi',2) insert into Venkat_SampleTable values(5,'Krishiv',3) insert into Venkat_SampleTable values(6,'Santhi',3) select * from Venkat_SampleTable DECLARE @boss_id int SET @boss_id = 2; WITH Venkat_CTE_Table (id, nam, BossID, Depth) AS ( SELECT id, nam, BossID, 0 AS Depth FROM Venkat_SampleTable WHERE id = @boss_id UNION ALL SELECT Venkat_SampleTable.id, Venkat_SampleTable.nam, Venkat_SampleTable.BossID, Venkat_CTE_Table.Depth + 1 AS Depth FROM Venkat_SampleTable JOIN Venkat_CTE_Table ON Venkat_SampleTable.BossID = Venkat_CTE_Table.id ) SELECT * FROM Venkat_CTE_Table Cheers, Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/

Convert a integer value with multiple decimals to fixed number of decimals(2 decimals): Consider a scenario, am having a decimal data and I need to restrict the decimal values. In this case, we need to use convert operator. create table aa(id decimal(10,5)) insert into aa values(1.90955) select convert(decimal(10,2),id) from aa Cheers, Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/

Stored procedure execution on SQL Server startup: I have studied a very interesting topic in SQL Server and wish to blog the same in my site. Scenario: On each SQL Server database startup, I need to execute a procedure in my database. It's a very basicscenario in all places. Solution: For this, SQL Server is providing an option of using a system stored procedure sp_procoption create procedure Venkatesan_Insert_Procedure as begin insert into venkat1(id,val) values (5,'F') end EXEC sp_procoption @ProcName = 'Venkatesan_Insert_Procedure',@OptionName = 'startup',@OptionValue = 'true' Now, your stored procedure is set as a initial startup which will execute on DB start. Cheers, Venkatesan Prabu .J

I got an issue with my database. Entire database is hanging and I didn't find an option to think the reason for this issue. We are unable to do any transaction. On checking the log file, we found the issue lies with log files. 1. Log file is around 20MB size with restricted growth option set for the database. Solution: 1. I have changed the option to have un restricted growth. 2. Backup the database before doing truncate option. 3. Truncated the log file. USE VenkatDB; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE VenkatDB SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (VenkatDB _Log, 1); GO -- Reset the database recovery model. ALTER DATABASE VenkatDB SET RECOVERY FULL; GO Cheers, Venkatesan Prabu .J

DBCC SQL Perf : DBCC SQL Perf function is used for the following purposes, 1. To list down the percentage of log file used. 2. Clear the OS waits information (sys.dm_os_wait_stats) 3. Clear the Latch waits information ( sys.dm_os_latch_stats ) Considering the database VenkatDB is having 1 MB for the log file, DBCC SQLPERF(LOGSPACE) -- This command is used to list down the spaces available for the database + How much % of log file is used + status of the log file(either its available and working or not) - 0 indicates, there is no potenition issue with the database. Clearing OS wait/Latch statistics: Below is the command to clear all the statistics collected for OS wait and Latch wait. DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); DBCC SQLPERF("sys.dm_os_latch_stats ",CLEAR); Cheers, Venkatesan Prabu .J

Compatibility Level is a very nice feature which decides the nature of the database. Considering am having a SQL Server 2000 database. I want this database to be work as a SQL Server 2005 in 2005 environment. Is it possible with out doing actual big migration work? Yes, it's possible with this option -> Compatibility Level Compatibility Levels in SQL Serve 2008: SQL will support 3 versions of database at their each releases. SQL Server 2005 supports (SQL Server 7.0/2000 and 2005). Now, SQL Server 2008 is supporting 3 versions (2000/2005/2008). **************************************** Versions Compatibility Level **************************************** SQL Server 2000 80 SQL Server 2005 90 SQL Server 2008 100 **************************************** Right Click on the database -> Properties -> Options -> Compatibility Level Cheers, Venkatesan Prabu .J

SQL Server 2008 SSMS enhancements: One of the fantastic enhancement with SQL Server 2008 is their enhancement in Reporting services. Everything is given in your hand in a very friendly manner. 1. You can get the statistics of each individual databases. Even granular information is given to you in a very detailed reports. 2. Considering, I want some information about a database like -> Disk usage of this database, blocking transactions in the database, Users in the database. 3. Right click on the database -> Reports -> Standard Reports -> Required reports. Below report shows me the detailed memory structure for my database. Now, I have selected "All blocking transaction". This report will provide a detailed analysis on the transactions which is blocking. User statistics provides the users available in the database. I think, sp_who stored procedure is used to fetch this report :-) Cheers, Venkatesan Prabu .J

Sometimes, we used to face the below error while trying to restart the database services or during login with a specific user credentials. "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." It's due to a setting in SQL Server. User instance in SQL Server is not enabled. Enter into your database with administrative access possibly windows account. Step 1: Enabling User Instances on your SQL Server installation First, you need to enable User Instances for SQL Server installation. Query Window in SQL Server Management Studio and execute the below query exec sp_configure 'user instances enabled', 1 Go Reconfigure Restart the SQL Server. Step 2: We need to delete all the old User Instances. Go to your C drive search for the below path, C:\Documents and Settings\YOUR_USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS Step3: Restart your machine. If the above steps didn't resolve your issue. Cheers, Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/

I faced a very different scenario, Copy huge amount of data(Billion records) from one database table to another database table. Do I need to consider anything or Just put select * into or Insert Into query for moving the data.? It's a very good challenging issue. We can think about some possible options for this issue. 1. Change the database property to "Simple recovery model" Else, you ldf file willbe bombarded due to huge transaction. 2. Do not do the transaction as a whole. Instead, do it in batches. This will provide room to SQL Server to process it. 3. Else you can put it in while loop. So that, your records will be processed in batches. 4. If the database is identical except that particular table. In that case, restore the database. 5. We can think about database mirroring to mirror the database. Cheers, Venkatesan Prabu .J

While checking one of the forums, I got the below information on the recent changes done in the SQL Server releases. SQL Server will be released along with the key word PCU and CU. PCU - It denotes Server packs. Now we are having PCU2 for SQL Server 2008 CU - It denotes hot fixes. To my knowledge, there are 7 CU's released for SQL Server 2008. Cheers, Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/