SQL Server Transact-SQL (SSQA.net)

SQL Server 2008 - TSQL fundamentals, get a great resource on this topic!

SQL Master — Wed, 15 Oct 2008 08:32:00 GMT

I thought I can blog this post in SQL 2008 blog section, but the subject is more appealing towards Transact SQL so here it is. Do you want to know about Transact-SQL (TSQL) fundamentals? Are you interested to get insight of SQL Server 2008 TSQL topic...(read more)

Export to EXCEL with column names

Madhivanan — Tue, 14 Oct 2008 10:03:00 GMT

In the post Import/Export to Excel , I showed how to export data to EXCEL The problem that most users find it is it wont include column names in the file and file should exists already with headings This procedure would solve that problem create procedure...(read more)

How to write or deploy Secure Transact-SQL - gotchas & tips?

SQL Master — Thu, 18 Sep 2008 07:16:00 GMT

I have been thiking to write this topic in this blog since last 3 months, before that I have done fair amount of work to get you sensible & helpful resource in securing your SQL Server environment. This was the question asked by one of the user when...(read more)

Return TOP N rows

Madhivanan — Mon, 15 Sep 2008 14:22:00 GMT

The TOP Clause returns top rows from the table based on the number or percentage value What if you want to have TOP N rows for each group? The following explains it ( The purpose is to return top 3 orders for each customer based on the most recent orderdate...(read more)

Splitting delimited data to columns - Set based approach

Madhivanan — Thu, 11 Sep 2008 14:58:00 GMT

In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns Simon in his post Set-based-splitting-of-delimited-strings-to-columns showed how to use that in a set based approach which would work on a set of data.In...(read more)

Should alias names be preceded by AS? - Part 2

Madhivanan — Tue, 09 Sep 2008 14:10:00 GMT

In the Part 1 , I expressed my opinion on having the alias names preceded by the keyword AS Adding to the examples given at the link, consider this example too SELECT 10number , 10.number , 10 number , 10 [number] , 10[number] , 10 as number It gives...(read more)

Fun with GO

Madhivanan — Fri, 05 Sep 2008 10:58:00 GMT

In this blog post , I explained different uses of GO command Here is a Fun when you use GO as object name CREATE PROCEDURE GO ( @I INT ) AS SELECT @I AS number Well. The procedure is created and let us try to execute GO 2 What we see is Command ( s )...(read more)

Empty string and Default values

Madhivanan — Tue, 02 Sep 2008 10:56:00 GMT

Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not be stored as empty string or NULL. It actually depends on the datatype of the column Consider this example DECLARE @t TINYINT , @s SMALLINT , @i INT...(read more)

Dynamic PIVOT in SQL Server 2005

Madhivanan — Wed, 27 Aug 2008 14:58:00 GMT

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results Consider this example select * from ( select Year ( OrderDate ) as pivot_col , e . lastname , o . OrderDate FROM northwind .. Employees as e INNER JOIN northwind...(read more)

Common migration issues (version 2000 to 2005)

Madhivanan — Tue, 12 Aug 2008 09:12:00 GMT

Many users complain that some new features are not working in SQL Server 2005 when the server is migrated from SQL Server 2000 These are the common tasks that should be done to avoid some migration issues 1 Change the compatibility level to 90 EXEC sp_dbcmptlevel...(read more)

Lighter side of a TSQL query :: Wedding Style

SQL Master — Sat, 09 Aug 2008 16:45:00 GMT

Writing a better TSQL query will get you better performance, in terms of system & life-style too. Here is the email I received from my BIL, too get a lighter side of blending a TSQL query as Stored Procedure (from а BrideGroom) .... guess what would...(read more)

Another use of GO command in SQL Server 2005

Madhivanan — Wed, 06 Aug 2008 09:07:00 GMT

As you all know, GO command signals the end of the batch of T-SQL statements However in SQL Server 2005, it is also used to execute set of commands for a specified number of times Consider that you want to create a table that should have hundred random...(read more)

SQL Server get current executing statements - SP_WHO or SP_WHO2 or sys.dm_exec_requests

SQL Master — Wed, 30 Jul 2008 16:43:00 GMT

As a SQL Server user or DBAs using SP_WHO or undocumented stored procedure SP_WHO2 is a common occurrence. For this there are times when you will need to run sp_who on your SQL Server to figure out who is on and what are they doing. The output is quite...(read more)

Outputting DBCC results

Madhivanan — Sat, 26 Jul 2008 11:29:00 GMT

Sometimes it may be useful to reuse the result of the DBCC commands. If the DBCC command resturns a resultset, it can be outputted to a table. Consider the following command DBCC useroptions It returns a single resultset. To copy the resultset to a table,...(read more)

Ordering Interger values stored in Varchar column

Madhivanan — Mon, 21 Jul 2008 14:21:00 GMT

I have seen many newbies asking "How do I sort the numbers stored in varchar columns?" Here are some methods declare @t table ( data varchar ( 15 )) insert into @t select '6134' union all select '144' union all select '7345' union all select '109812'...(read more)