Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers.
SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String
Following SQL User Defined Function will extract/parse numbers from the string. CREATE FUNCTION ExtractInteger(@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers =…
Read MoreSQL SERVER – Search Text Field – CHARINDEX vs PATINDEX
We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking…
Read MoreSQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By
This question is asked many times to me. What is difference between DISTINCT and GROUP BY? A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If…
Read MoreSQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is…
Read MoreSQL SERVER – SQL Commandments – Suggestions, Tips, Tricks
Few days ago, while searching for something on web site, I came across a very good article of 25 SQL Commandments. I really enjoyed reading it. It was for Oracle, I re-wrote it for SQL Server. First 18 points are taken from original article and last 2 I added to…
Read MoreSQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running
To determine which version of SQL Server 2000/2005 is running, connect to SQL Server 2000/2005 by using Query Analyzer, and then run the following code: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') The results are: The product version (for example, 8.00.534). The product level (for example, “RTM” or “SP2”). The edition…
Read More