Advertisements

Archive

Archive for February, 2011

Generate SCRIPT or SOURCE CODE of DB Objects: Functions, Stored Procedures, Views, Triggers, ect

February 24, 2011 Leave a comment

The metadata in SQL Server contains the information and code of the Database Objects, like Functions, Stored Procedures, Views, Triggers, etc.

Following are the 3 main ways you can get or generate Script or Source Code of these DB Objects:

USE [AdventureWorks]
GO
-- Method #1
SELECT ROUTINE_DEFINITION, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ufnGetContactInformation';
GO
-- Method #2
select c.text, object_name(c.id), o.type
from sys.syscomments c
join sys.sysobjects o
on o.id = c.id
where c.id = object_ID('ufnGetContactInformation');
GO
<p>-- Method #3
exec sp_helptext 'dbo.ufnGetContactInformation';
GO

More about SQL Server metadata: https://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/

Advertisements

Using ORDER BY with CASE in SQL Queries

February 22, 2011 2 comments

ORDER BY clause orders the result set of a SQL query for a particular or specific set of columns provided in ORDER BY clause.

Ever wonder to tweak that order. Lets take a scenario when you fill out an online form. When you reach at Country dropdown you get lots of County names to select. But if your Country is not listed there, there an “Other” option and that too at the bottom. Why it is not ordered with other Country names alphabetically? How come it reached that bottom? Lets see how can we get that resultset by using ORDER BY CASE.

-- Create a Country table
CREATE TABLE Country (ID INT IDENTITY(1,1), cname VARCHAR(50))

-- Insert dummy data
INSERT INTO Country
SELECT 'Afghanistan'
UNION
SELECT 'Australia'
UNION
SELECT 'France'
UNION
SELECT 'Oman'
UNION
SELECT 'United States'
UNION
SELECT 'Singapore'
UNION
SELECT 'Other'

-- SELECT with plain ORDER BY
SELECT * FROM Country
ORDER BY cname
ID	cname
1	Afghanistan
2	Australia
3	France
4	Oman
5	Other
6	Singapore
7	United States

Here you will see the "Other" option just below "Oman". It should not be here, 
because it is not a Country name and the user should should see this 
option at the bottom.
-- SELECT with ORDER BY CASE
SELECT * FROM Country
ORDER BY CASE WHEN cname='other' THEN 1 ELSE 0 END
ID	cname
1	Afghanistan
2	Australia
3	France
4	Oman
6	Singapore
7	United States
5	Other

Applying ORDER BY clause with CASE tweaks the "Other" option and places it at the 
bottom. Other than Country this logic can be applied for other things like, 
University Name, City, State, etc.
-- Final Cleanup
DROP TABLE Country
Categories: SQL Tips Tags: ,

Microsoft Community Contributor Award 2011

February 17, 2011 6 comments

Just received an email from Microsoft that I’ve been recognized for this year’s (2011) “MS Community Contributor Award” for my contribution in Microsoft’s online technical communities.

And I can sport this badge now:

MCCA 2011

MCCA 2011

What is MCCA: The Microsoft Community Contributor Award is reserved for participants who have made notable contributions in Microsoft online community forums such as TechNet, MSDN and Answers. The value of these resources is greatly enhanced by participants, who voluntarily contribute your time and energy to improve the online community experience for others.

Becoming a Microsoft Community Contributor Award recipient includes access to important benefits, such as complimentary resources to support you in your commitment to Microsoft online communities.

I’ve been following the MSDN’s TSQL from since 2006-07, but actively answering the question for the past 6-7 months. In the duration I’ve got 2311 points till date (not bad), with total 511 posts & 132 answers marked (image below).


Top Answers in past 30 days
My MSDN’s TSQL forum link: http://social.msdn.microsoft.com/profile/manub22/?type=forum

MCC 2011 Facebook fan page: http://www.facebook.com/pages/Microsoft-Community-Contributor/113784705335192

Import Excel Sheet with multiple Recordsets

February 16, 2011 1 comment

Importing records from an Excel is a very simple task. Load your data in Excel with appropriate headers and Run the Import/Export Wizard, your records are transfered from Excel sheet to a MS SQL table.

But what if a single Sheet contains multiple record sets with variable headers. Like First 100 rows of Customer data with 10 headers. Then just below 50 rows of Order data with less than 10 or more than 10 headers.

Seems a bit difficult but not impossible. Its tricky though, lets see how:

Lets us suppose your Excel file is in following format shown in image below (Fig-1):
1. Contact recordset &
2. Sales Order recordset

Fig-1 Sheet with multiple recordsets

Fig-1 Sheet with multiple recordsets

Now select the Contact recordset including headers as shown in Fig-2 and right-click and select “Name a Range…” option.

Fig-2 Create a named Range

Fig-2 Create a named Range

Fig-3 shows a pop-up box where you can apply and provide a name for that range selection.

Fig-3 Range name for Contacts

Fig-3 Range name for Contacts

Similarly repeat this for Sales Order recordset as shown in Fig-4.

Fig-4 Range name for Sales Order

Fig-4 Range name for Sales Order

Now you can see 2 named ranges Contacts & Sales in the dropdown in Fig-5.

Fig-5 Check both the Named ranges

Fig-5 Check both the Named ranges

Now we are ready to Import the data. As shown in Fig-6 the Import/Export wizard detects the named ranges and explicitly shows them as Source among other 3 sheets of your Excel file. Simply select both of them and make the required changes as you do for Sheets and click the Next button.

Fig-6 Named ranges while Import

Fig-6 Named ranges while Import

Fig-7 Shows both the tables created & data loaded in SQL Server.

Fig-7 Sources getting copied into SQL tables

Fig-7 Sources getting copied into SQL tables

Now check the records and match them with the Excel sheet, as shown in Fig-8

Fig-8 Check the tables in SQL Server finally

Fig-8 Check the tables in SQL Server finally

Wow!!! that was simple.

I was asked this question in an SQL interview and I didn’t knew the answer, obviously you don’t need to know everything… but you should. Discussed this question on MSDN TSQL forum and got the suggestion, thus the blog post. Link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cf849418-d18f-4b7a-99eb-dbfed6269603/#778c0e99-368a-40f2-b9d4-b747c1754853

[NOT] IN, [NOT] EXISTS, JOINS with NULL values

February 15, 2011 3 comments

Using NOT IN could be dangerous and may result to incorrect results at times.

Let’s check this by a step by step example:

use [tempdb]
go

-- Create test tables and fill test data:
CREATE TABLE [dbo].TestTable(AID int NOT NULL)

INSERT INTO TestTable(AID) values (1)
INSERT INTO TestTable(AID) values (2)
INSERT INTO TestTable(AID) values (3)
INSERT INTO TestTable(AID) values (4)
go

create TABLE TableWithNull(BID int NULL DEFAULT(NULL))

INSERT INTO TableWithNull(BID) VALUES(NULL)
INSERT INTO TableWithNull(BID) VALUES(0)
INSERT INTO TableWithNull(BID) VALUES(1)
INSERT INTO TableWithNull(BID) VALUES(2)
GO

-- Check inserted records:
SELECT * FROM TableWithNull -- NULL, 0, 1, 2

select * from TestTable -- 1, 2, 3, 4

 

–> Using IN & NOT IN

select * from TestTable 
where AID in (SELECT BID FROM TableWithNull) -- 1, 2

select * from TestTable 
where AID not in (SELECT BID FROM TableWithNull) -- 0 rows (expected 3, 4)

SQL is a set-based language, any set containing NULL value makes it whole as NULL. Because NULL is unknown and anything added to it also becomes unknown. Thus above SQL statement results 0 rows. To make it work you have to treat the NULL and add a WHERE clause or use EXISTS instead of IN shown below:

select * from TestTable 
where AID not in (SELECT BID FROM TableWithNull where BID is not null) -- 3, 4 (expected result)
go

 

–> Using EXISTS & NOT EXISTS

select * from TestTable A 
where exists (SELECT * FROM TableWithNull B where A.AID = B.BID) -- 1, 2

select * from TestTable A 
where not exists (SELECT * FROM TableWithNull B where A.AID = B.BID) -- 3, 4
go

NOTE: EXISTS keyword is a good way to check sub-queries, this makes them co-related sub-queries. With EXISTS you don’t even need to provide any column name, just use “SELECT *”, as it does not use the SELECT list at all.
With IN you can only compare one column, but with EXISTS you can compare multiple columns within outer & inner queries (sub-queries or derived queries).
 

–> Using JOINS in place of IN() & EXISTS()

select A.* -- 1, 2
from TestTable A
JOIN TableWithNull B ON
A.AID = B.BID

select A.* -- 3, 4
from TestTable A
LEFT JOIN TableWithNull B ON
A.AID = B.BID
WHERE B.BID IS NULL
go

 

–> Here’s another catch, weird behavior:

select * from TestTable 
where AID not in (SELECT AID FROM TableWithNull) -- Query works even when AID column is not in [TableWithNULL] table.
-- [AID] column does not belong to [TableWithNull] table, but the query still work and won't give any error.
-- So its always adviced to use table alias, shown below:

select * from TestTable A 
where A.AID not in (SELECT B.AID FROM TableWithNull B) -- Invalid column name 'AID'.

 

NOTE: So always try to provide ALIAS you your tables and use it with COLUMN names in your queries.

-- Final Cleanup
drop table TableWithNull
drop table TestTable
go