Think SQL...

creating an Identity column

2011-03-01T09:14:00.002+03:00

This will create an identity column in the following table.

CREATE TABLE dbo.MyTable (    ID INT IDENTITY(1,1)    ,MyShortDesc varchar(20)     ,MyLongDesc varchar(500)    );

Data file locations

2010-06-03T14:13:00.000+03:00

Here's a query which displays the .mdf and .ldf location of all the databases in the server.

SELECT physical_name
FROM master.sys.master_files

Sequence number within a group

2010-04-10T15:10:00.000+03:00

The following query can be used to generate the sequence numbers within a group.

select

GROUP_ID

, GROUP_COUNTER= ROW_NUMBER()

OVER ( PARTITION BY [GROUP_ID]

ORDER BY [GROUP_ID]

)

from GROUP_TABLE

This query can generate a output like the following.

GROUP_ID GROUP_COUNTER

-------------------------------------------

AAA 1

AAA 2

AAA 3

BBB 1

BBB 2

CCC 1

SQL Server Video tutorials

2009-10-20T14:31:00.000+03:00

There are some good video tutorials available here.Check it out.

An interesting article - Selecting Duplicate Rows in a table

2009-10-18T13:08:00.001+03:00

Hi All, we are back after a pause.Here's an interesting article which talks about the different ways of selecting the duplicate rows from a table.Hope it will be useful to you.

Article

Question of the day

2009-09-25T11:37:00.000+03:00

Can you instantiate a COM object by using T-SQL?

1. True
2. False

T-SQL best practices - 4

2009-09-23T23:59:00.000+03:00

- The dynamic SQL Performs slower because the optimizer has to create an execution plan for every execution.

-Use table aliasing, even if there is no joining in the query. Aliasing eliminates ambiguity, eases subsequent modification, and further reduces the chance of a typo not being recognized.

-When querying a table, prefix it with the owner (schema), typically dbo. There is a little performance improvement and query becomes more readable

Question of the Day

2009-09-23T07:27:00.003+03:00

What are the disadvantages of cursors?

1. Cursor results in a network roundtrip
2. Cursor requires more resources and temporary storage
3. Cursor results in more IO operations
4. 1 & 2
5. 1, 2 & 3
6. None

T-SQL best practices - 3

2009-09-22T22:02:00.000+03:00

Today's tips are not really related to T-SQL,but I hope these will improve the way you approach SQL SERVER.

- Get Certified

- Subscribe to News Groups and blogs to get regular updates

- Read White papers

T-SQL best practices - 2

2009-09-21T21:38:00.002+03:00

Today's best practice techniques are

- NOLOCK helps in concurrency and performance but can be dangerous for data integrity

- Use XML datatype to pass table valued parameters to procedures.

- Use SQL Server Management Console with integrated Source Control to do the development

T-SQL best practices - 1

2009-09-20T15:45:00.005+03:00

Hi All!...we decided to write some posts on the best practices in writing T-SQL,which we think helpful to the newbies.We will list three best practices each day for the next seven days and hope this series will be helpful to all the readers.So today's best practices tips are,

Document your database

–Use Extended properties of the table structures to document the database.

–Use tools to generate the documentation and correct where necessary

–Maintain the script for populating the extended properties.

Use Cursors only when

–You cannot find a set based solution in 4 Hours

–You want to utilize already existing logic in other SP

–You are writing a database maintenance task

Use STATISTICS IO ON

–This will reduce the logical reads

We need your comments on this post and share the best practices that you are using in the script(related to the above 3 techniques).

Left Padding(LPAD) using REPLICATE

2009-09-19T14:30:00.002+03:00

Here's a SQL UDF to do the left padding using REPLICATE.

create function [dbo].[leftpad]

(

@csrting nvarchar(4000)

, @nlen smallint

, @cpadcharacter nvarchar(4000) = ' '

)

returns nvarchar(4000)

begin

declare @nleftlen smallint, @nrightlen smallint

declare @length smallint, @lenpadcharacter smallint

select @length = datalength(@csrting)/(case sql_variant_property(@csrting,'basetype') when 'nvarchar' then 2 else 1 end)

select @lenpadcharacter = datalength(@cpadcharacter)/(case sql_variant_property(@cpadcharacter,'basetype') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nlen

set @csrting = left(@csrting, @nlen)

else

begin

set @nleftlen = @nlen - @length

set @csrting = left(replicate(@cpadcharacter, ceiling(@nleftlen/@lenpadcharacter) + 2), @nleftlen)+ @csrting

end

return (@csrting)

end

Working with Hijri Date in SQL server

2009-09-12T15:00:00.001+03:00

Here's an example to convert the Hijri Date to Gregorian date in SQL Server

1430/09/23 is today's Hijri date.Lets see how to convert this into Gregorian date

SELECT CONVERT(DATETIME, '23/09/1430', 131)

It would return 2009-09-12 00:00:00.000

The vice-versa,conversion from Gregorian date to Hijri date would be,

SELECT CONVERT(NCHAR, GETDATE(), 131).And this returns 23/09/1430 2:52:17:920PM

Top 10 SQL server Performance tuning tips - 10

2009-09-12T04:00:00.001+03:00

Use of performance monitoring tools:

SQL server ships with many tools like Profiler,Database Engine Tuning Advisor,Index Tuning Wizard,PerfMon,SysMon.Make use of these tools to moniter the performance of your query and take the nessesary actions,if required.

Well, with this post we come to an end of our series of Performance Tuning Steps.I hope its helpful to you to you and we will discuss each steps in detail in coming days.

Top 10 SQL server Performance tuning tips - 9

2009-09-11T04:00:00.000+03:00

Use of NOLOCK hint

NOLOCK hint is useful to improve the query performance when selecting the rowset from a table which is being accessed by multiple transaction.But this leads to dirty read.A dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.

Top 10 SQL server Performance tuning tips - 8

2009-09-10T04:00:00.001+03:00

Reduce the NULLs

Reduce the use of NULLs in the database. The use of NULLs in a database can greatly reduce database performance, especially in WHERE clauses.Highly normalized database can achieve this.

Top 10 SQL server Performance tuning tips - 7

2009-09-09T04:00:00.000+03:00

Remove unwanted columns from the SELECT statement

In queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. In addition, using SELECT * may prevent the use of covered indexes, further potentially affect query performance.

Finding long running queries in SQL server

2009-09-08T22:58:00.000+03:00

Here's a query to find the longest running queries.This works in SQL Server 2005 and 2008.

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0)AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC

Top 10 SQL server Performance tuning tips - 6

2009-09-08T04:00:00.002+03:00

Set Based Approach

Even though procedural approach is easier for most of the programmers,it (Use of Cursor, or, UDF to process rows in a result set) should be avoided when working with large result set.Most of the times cursors can be replaced by CTE(Common Table Expression). Avoiding Cursors with SQL Server 2005

Top 10 SQL server Performance tuning tips - 5

2009-09-07T04:00:00.004+03:00

Avoid cursors

Avoid using cursors.It may result in Deadlocks.Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. During this kind of situation, SQL Server identifies the trouble and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue.

Top 10 SQL server Performance tuning tips - 4

2009-09-06T05:00:00.000+03:00

Use of Indexes

NON CLUSTERED index should be created on a highly selective field.
Order of the fields in the WHERE clause should be same as the index order.
Every table in the DB should have a CLUSTERED index.
Primary Key may not be suitable for clustered index if the table is extensively used for SELECT.

Top 10 SQL server Performance tuning tips - 3

2009-09-05T00:34:00.000+03:00

Use of OPTIMIZE FOR

OPTIMIZE FOR a little known but powerful SQL Server feature.

DECLARE @ProdCode NVARCHAR(20)
SET @ProdCode = N’100’
SELECT ProdId,MfnDate
FROM Product
WHERE ProdCode = @ProdCode
OPTION (OPTIMIZE FOR (@ ProdCode = N’100’))

The OPTIMIZE FOR hint will force an index seek when the specific value is provided.

More about OPTIMIZE FOR/OPTIMIZE FOR UNKNOWN here

A Milestone : 2000 visits crossed today

2009-09-04T23:11:00.003+03:00

It's time to cheer and rejoice as our SQL server Blog completes a milestone of 2000 visitors, from all over the world. The blog has 12 subscribers and 10 followers to its credit, if we are to count numbers, or otherwise has a huge base of loyal supporters and visitors greeting every day. In a very short duration, the blog seems to achieve a great milestone, while people still keep visiting and adding to the increasing quantitative and qualitative aspect of it.

At this achievement, let’s not stop just at celebrations; instead let’s put our hands together in making this blog more readable, accessible and popular, just up to the mark of SQL Server Professionals. One small way we can help is by contributing anything possible and relevant at individual level and the blog shall, by itself march to become successful for the professionals.

We appreciate the efforts of all those who are contributing to this blog in any way, big or small, post or comment. The easiest way to show liking for the blog is by making a comment, whenever an article interests one. Comment is not mere an opinion rather reflects one’s involvement for the cause. So let’s

initiate the changes and put our efforts to make this blog more popular and the best among the lot for the sake of community service.

I once again congratulate the whole team for this successful journey.

Regards,
Mohammad Shahanshah Ansari
SQL Server Professional

Top 10 SQL server Performance tuning tips - 2

2009-09-04T10:34:00.000+03:00

Today's performance tuning tip.

Use of UNION/UNION ALL

UNION move the results to the tempdb, sort it, identify unique entries and retrieve the results.

UNION ALL will not do the sort, and will not identify unique entries. The results will not be unique. This can be used when the results from two queries are distinctly different

UNION will have adverse impact on the number of Reads and Writes and hence the performance.

Top 10 SQL server Performance tuning tips

2009-09-03T14:18:00.000+03:00

I planned to post some performance tuning steps ,mostly DB design related, for the next 10 days.One each day so that the readers wont get bored.So...Here we go..

Use SQL Profiler

Use SQL Profiler check the Duration, Reads and Writes for each Stored Procedure and for each statement within the stored procedure.Typically Reads should not exceed four times the number of rows returned. If reads are abnormally high then indexes may require a re-look or the query has to be split depending on the complexity.Set the duration filter before running the trace to identify the queries that exceed a specific duration.

SQL Azure

2009-08-31T14:50:00.000+03:00

What is SQL Azure?

Microsoft® SQL Azure delivers on the Microsoft Data Platform vision of extending the SQL Server capabilities to the cloud as web-based services, enabling you to store structured, semi-structured, and unstructured data. SQL Azure will deliver a rich set of integrated services that enable you to perform relational queries, search, reporting, analytics, integration and synchronize data with mobile users, remote offices and business partners. Currently, SQL Azure offers relational database service called Microsoft® SQL Azure Database.

Here's an interesting article on SQL Azure.

Introduction to SQL Azure

Azure Video tutorials

Removing Duplicates using CTE

2009-08-31T14:26:00.000+03:00

Here's a code snippet which removes the duplicate records,using CTE

WITH CTE ([col1], cnt)

(

SELECT [col1],

ROW_NUMBER() OVER(PARTITION BY [col1] ORDER BY [col1]) AS cnt

FROM table1

)

DELETE

FROM CTE

WHERE cnt > 1

SQL Server 2008 Developer Training Kit

2009-06-22T23:11:00.000+03:00

SQL server 2008 Developer Training Kit which is released recently is available here.

Contents
The training kit includes the following content:

Presentations (6)

Filestream
Spatial
T-SQL
Date and Time Types
SQLCLR
Reporting Services

Demos (12)

AdventureWorks Racing All-Up SQL Server 2008 Demo
SQL Server 2008 All-Up Spatial Demo
Spatial Types Demo
Intro to Filestream Demo
SQL CLR Nullable Types Demo
Programming with Filestream Demo
Reporting Services Web Application Integration Demo
Date and Time Support in SQL Server 2008 Demo
T-SQL Table-Valued Parameters Demo
T-SQL Row Constructors Demo
T-SQL Grouping Sets Demo
T-SQL Merge Demo

Hands-on Labs (3)

Using Spatial Data in TSQL
Using Spatial Data in Managed Code
Using SQL CLR in SQL Server 2008

-----------------------------------------------------------------------------------------------------------------------Reference : download.microsoft.com

Inserting values into an identity column

2009-06-19T00:36:00.000+03:00

The identity column gets value assigned automatically when a new records inserted into the table.

Its possible to insert a value into an identity column by setting the IDENTITY_INSERT to ON.

Lets look at the following example,

--Test table with an identity column

CREATE TABLE #IDENTESTTABLE

(

ROWNO_COL INT IDENTITY(1,1),

VALUE_COL VARCHAR(15)

)

--Inserting a row into the table

INSERT #IDENTESTTABLE(ROWNO_COL, VALUE_COL) VALUES (1, 'TEST VALUE')

The above insert statement would terminate with the following error,

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table '#IdenTestTable____000000000004'

when IDENTITY_INSERT is set to OFF.

To avoid this error set IDENTITY_INSERT to ON.Here we go,

SET IDENTITY_INSERT #IDENTESTTABLE ON

INSERT #IDENTESTTABLE(ROWNO_COL, VALUE_COL)

VALUES (1, 'FIRST ROW')

SET IDENTITY_INSERT #IDENTESTTABLE OFF.

More fun with IDENTITY here

SQL Server 2008 Wallpaper

2009-06-04T14:41:00.001+03:00

Here's a high resolution SQL Server 2008 wallpaper!

Error handling in UDF - I

2009-06-02T11:31:00.000+03:00

There's no straight forward way for exception handling in a UDFand programmers often let the caller code to handle the error in such cases.You can still write exception handling blocks in the CLR(The common language runtime) UDFs in SQL server 2005/later.Apart from the giving better performance than T-SQL ,the CLR,allows the programmer to perform funtionality which are not available in T-SQL.Will see how to implement this in the upcoming posts!

Funny SQL Query!

2009-06-02T00:31:00.000+03:00

Just found this funny PL/SQL wedding query.Check it out.

CREATE PROCEDURE MYMARRIAGE

@ BRIDEGROOM CHAR(NOTBAD) ,

@ BRIDE CHAR(GOOD)

AS BEGIN

SELECT BRIDE FROM INDIA_BRIDES

WHERE FATHERINLAW = 'MILLIONAIRE'

AND CARCOUNT > 2 AND HOUSESTATUS ='TWOSTOREYED'

AND BRIDEEDUSTATUS='PG ORABOVE'

AND HAVINGBROTHERS='NO'

AND HAVINGSISTERS ='NO'

AND ALLOWRELOCATE ='YES'

SELECT GOLD ,CASH,CAR,BANKBALANE

FROM FATHERINLAW

UPDATE MYBANKACCOUT SET MYBAL = MYBAL + FATHERINLAWBAL

UPDATE MYLOCKER SET MYLOCKERCONTENTS = MYLOCKERCONTENTS + FATHERINLAWGOLD

INSERT INTO MYCARSHED VALUES ('BMW')

Then the wife writes the below query:

DROP HUSBAND

COMMIT;

ha ha ha ha..Enjoy!!!!!

Finding unused tables in SQL Server

2009-06-01T09:12:00.000+03:00

Here's a query which can give you a clear idea about the unused tables in your database.

select

t.name as 'table',

sum(i.user_seeks + i.user_scans + i.user_lookups) as 'total accesses',

sum(i.user_seeks) as 'seeks',

sum(i.user_scans) as 'scans',

sum(i.user_lookups) as 'lookups'

from

sys.dm_db_index_usage_stats i right outer join

sys.tables t on (t.object_id = i.object_id)

group by

i.object_id,

t.name

order by [total accesses] desc

This works in SQL server 2005/later

SET vs SELECT !

2009-05-22T14:37:00.000+03:00

SQL programmers generally use SET/SELECT to assign the values to the variables.The following are the notable differences between SET and SELECT.

1.In SELECT,more than one variable can be used

declare @name varchar(36),@grade varchar(3)

select @name='john',@grade = '10'

on the other hand in SET ,only one variable can be used at a time

declare @name varchar(36),@grade varchar(3)

set @name='john'

set @grade='10'

2. SELECT can perform better than SET statement,

here's the test code to check the performance of the SET and SELECT.Its better test this in your local machine instead of the production server.

declare @startime datetime ,@i varchar(1000),@j bigint

set @startime = current_timestamp

set @j = 50000

while @j > 0

begin

set @i='myteststring'

set @j = @j -1

end

SELECT datediff(ms, @Startime, CURRENT_TIMESTAMP) AS 'Time taken by SET in ms'

select @startime = current_timestamp

select @j = 50000

while @j > 0

begin

select @i='myteststring'

select @j = @j -1

end

select datediff(ms, @Startime, CURRENT_TIMESTAMP) AS 'Time taken by SELECT in ms'

3.When assigning from a query if there's no value returned SET will assign a NULL ,whereas SELELCT will ignore the statement, the variable will return the previous value.

declare @myvar varchar(10)

set @myvar = 'TEST'

set @myvar = (select name from sysobjects where 1<>1)

select @myvar

this block of code will return NULL,but the below code will return 'TEST'.

declare @myvar varchar(10)

select @myvar = 'TEST'

select @myvar= name from sysobjects where 1<>1

select @myvar

Conclusion : SET can be used when you want follow the coding standard and performance is not an issue.SELECT can be used when performance is a concern.

How to Find Recently Executed Queries In SQL Server 2005

2009-05-20T14:21:00.000+03:00

Today I came across a situation which required to find out recently executed queries to analyse the modifications carried out on a database server.

Here is the query which we used to find out the recently executed queries along with the date and time at which they were executed

SELECT
    DMExQryStats.last_execution_time AS [Executed At],
    DMExSQLTxt.text AS [Query]
FROM
    sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
    sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
    DMExQryStats.last_execution_time DESC

Hope this will be useful to you also.

Copying table with the Indexes

2009-05-19T21:36:00.001+03:00

SELECT INTO statement can be useful in the following scenarios.

1.Create a new table with same structure of the existing table

2.Copy all/some records from existing table to a new table

This leads to the question, will SELECT INTO copy the indexes and keys?.No.The new table created using SELECT INTO will not have indexes,primary key,foreign keys,default values and triggers.I find these reasons why Microsoft not implemented this feature of copying indexes in SELECT INTO:

i). Its not the best practice to create the indexes while creating the tables.You can determine the columns to be indexed while facing the serious performance issue.

ii).Indexes cannot be created in the same name like how column names are similar while copying a table using SELECT INTO.

But SSIS provides the solution to copy the table with the indexes.We will see how to implement this in the future post.When you want to copy the huge amount of data , better go for DTS or Import/Export wizard (make sure that you script out the table structure).It is certainly faster than SELECT INTO.

Different Options for Importing Data into SQL Server

2009-05-19T09:57:00.001+03:00

Different Options for Importing Data into SQL Server

Problem
Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis. One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use either DTS (SQL 2000) or SSIS (SQL 2005). In this tip we take a look at some of these other options for importing data into SQL Server.

Solution
In addition to using the Import / Export wizards and/or DTS or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server. Some these other options include bcp, BULK INSERT, OPENROWSET as well as others. The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

BCP
This is one of the options that is mostly widely used. One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command. This command allows you to both import and export data, but is primarily used for text data formats. In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a DTS or SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

BULK INSERT
This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL. This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

OPENROWSET
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest. The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

OPENDATASOURCE
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

OPENQUERY
Another option is OPENQUERY. This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table. There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command. This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:\ImportData.xls',
NULL,
'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

Linked Servers
Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server. This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:\ImportData.xls',
NULL,
'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

SQL Server 2008 certification path

2009-05-18T21:41:00.000+03:00

Download the SQL server (2008) certification path here.

It will be handy if you have any idea of taking Microsoft SQL server certification.

Don't forget, there is a second shot offer available for selected exams till June 30,2009.

You can take these exams in

Bangalore @

NIIT

#143-B,II Floor , 5th Block,

60 Feet Road,Koramangala

Bangalore.

In chennai @,

NIIT,

Shah Complex, 59, L B Road,Adyar,

Near Adyar Bus terminal.

Chennai.

Homson prometric testing (p) ltd

No 15,II floor,

Romar house,

Jagannathan road,

Nungambakkam,

Chennai.

Check the nearest Prometric services for more information about exam centers.

Text Concatenation in SQL Server

2009-05-17T10:46:00.000+03:00

To concate the Text for a combination of keys there might be many ways.

Initially I was using either function or cursor for concatenation. But finally I found the below query most useful for the same.

First Create a table called Product with the following data.

Don't forget to create index if your table has huge data.

Then run the below Query:

SELECT
DISTINCT T.ID
,(SELECT ' ' + [DESC] FROM PRODUCT WHERE ID = T.ID FOR XML PATH('')) AS [DESC] FROM PRODUCT T

You will get the output as:

I hope this will help you guys. Share if you have any other good alternative.

Selecting random records in SQL Server

2009-05-17T10:23:00.000+03:00

Here's a way to select the random records from a table

select top 5 name

from sys.objects

order by newid()

This query will return 5 random tables from sys.objects each time you execute it.

Know more about newid() here

Inserting multiple records using single insert statement

2009-05-15T19:10:00.000+03:00

An Interesting topic with Insert statement,

Is it possible to insert multiple records using one single insert statement?

Most of the programmers will say 'NO' But

Let's see,

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='EMP' AND TYPE='U')

DROP TABLE EMP

CREATE TABLE EMP(ID INT, NAME VARCHAR(10))

INSERT INTO EMP

SELECT 1,'ANSARI'
UNION ALL
SELECT 2,'MAGESH'
UNION ALL
SELECT 3,'ANOOP'
UNION ALL
SELECT 4,'SARAVANA'
UNION ALL
SELECT 5,'PRASANNA'
UNION ALL
SELECT 6,'FAHAD'
UNION ALL
SELECT 7,'KARTHICK'
UNION ALL
SELECT 8,'VIJAY'
UNION ALL
SELECT 9,'SAHIL'

SELECT * FROM EMP

Copy table with Indexes

2009-05-15T17:10:00.001+03:00

Hey Guys,

We create a copy of table using SELECT INTO .. query, but we know that it doesn't carry the indexes to the new table.

Suggest the different ways for carrying the Indexes to the new table that we are copying...

Thanks in advance

Finding user tables

2009-05-15T13:11:00.000+03:00

The are three best possible ways to find the "user tables" in SQL Server.

> SELECT NAME

FROM SYS.OBJECTS

WHERE TYPE='U'

> SELECT *

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE='BASE TABLE'

> EXEC SP_TABLES @TABLE_TYPE="'TABLE'"

Comment the one that you use often and the other ways to find the user tables.

Case Sensitivity in SQL Server

2009-05-15T01:15:00.001+03:00

It’s always pain in the neck when it comes to case sensitive programming. A very common question would come across in every SQL server newbie: Is T-SQL case-sensitive?

The Answer is “NO”. The following two statements will produce the same results without any error.

i)
SELECT *
FROM SYS.OBJECTS

ii)
select *
from sys.objects

But will the following statements produce the same result?

SELECT *
FROM STUDENT
WHERE NAME = 'JOHN'

SELECT *
FROM STUDENT
WHERE NAME = 'john

The answer is “it depends on the collation” as our search string differs in case sensitivity.

In simple words collation in SQL server is a set of rules that determine how data is sorted and compared. We can make the search string as case sensitive in SQL server. Let’s check the collation of the current database.

SELECT DATABASEPROPERTYEX(DB_NAME(),'COLLATION')

The collation in my test database is,
SQL_Latin1_General_CP1_CI_AS

Here CI specifies Case Insensitive.

SP_HELP can be used to identify the table & column level collation.Some of the other possible collations are:

SQL_Latin1_General_Cp437_CS_AS (CS specifies case-sensitive).
SQL_Latin1_General_Pref_CP1_CI_AS
SQL_Hungarian_Cp1250_CI_AS
SQL_Croatian_Cp1250_CI_AS
SQL_Latin1_General_Cp1254_CI_AS

Our counterpart PL/SQL is also not case sensitive, so lower case letters are equivalent to corresponding upper-case letters except within string and character literals. This is what differentiates PL/SQL and T_SQL in case-sensitivity. The Collation can be changes as the project requires. But when its comes to whether to use all Caps or all smalls in T-SQL , its better be consistent throughout the scripts.It facilitates more readability.

Happy reading!

Why SQL Server ?

2009-05-14T09:16:00.000+03:00

Dear Friends,

Just to begin with this blog I will share some information on what is SQL server and why we choose SQL Server. Let us know if you like this post.

SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

Codenamed Yukon in development, SQL Server 2005 was released in November 2005. The 2005 product is said to provide enhanced flexibility, scalability, reliability, and security to database applications, and to make them easier to create and deploy, thus reducing the complexity and tedium involved in database management. SQL Server 2005 also includes more administrative support.

The original SQL Server code was developed by Sybase; in the late 1980s, Microsoft, Sybase and Ashton-Tate collaborated to produce the first version of the product, SQL Server 4.2 for OS/2. Subsequently, both Sybase and Microsoft offered SQL Server products. Sybase has since renamed their product Adaptive Server Enterprise.

Generically, any database management system (DBMS) that can respond to queries from client machines formatted in the SQL language. When capitalized, the term generally refers to either of two database management products from Sybase and Microsoft. Both companies offer client-server DBMS products called SQL Server.

SQL Server 2005 is the Data Platform leader
• Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. SQL Server 2005 allows companies to gain greater insight from their business information and achieve faster results for a competitive advantage.
• SQL Server is the fastest growing Database and Business Intelligence vendor
• SQL Server ships more units than Oracle and IBM combined.
• SQL Server is the #1 OLAP Server on the market.
• SQL Server is more secure than Oracle. Since July 2003 more than 100 critical Oracle database security vulnerabilities have been identified, compared to ZERO for SQL Server for that period.
• SQL Server is best in price/performance for TPC-H 1TB & 3TB (non-clustered).
• Microsoft SQL Server and Unisys are the first to publish TPC-E benchmark results.
• Gartner 2006 worldwide RDBMS market share reports highest growth rate in market share for Microsoft SQL Server. With 28% growth in market share, growth is almost twice the nearest competitor at 14.8% growth.
Microsoft SQL Server is a relational model database server produced by Microsoft. Its primary query languages are T-SQL and ANSI SQL.