Tuesday, June 28, 2011

SQL Server: Where You Can Find Saved Import/Export SSIS Packages

Using SQL Server Import Export Utility, have you ever saved this SSIS package to SQL Server ?



Recently, a friend of mine asked that after saving Import/Export process as SSIS package to SQL Server, Where he can find this saved package to re-execute it.

Once you have saved your SSIS package,open SQL Server Management studio and then on object explorer panel click on connect and select Integration Services. Use valid user name and password to connect.



Once connect, expend Stored Packages node and then expend MSDB node. Here you can find your saved package. Right click on it and select “Run Package” to re-execute your saved import/export steps.

Sunday, June 19, 2011

SQL Serve Videos: Installing SQL Server and Creating Your First Database

If it’s your first day with SQL Server and you need someone who can help you in installation and creation of your first database in SQL Server 2008, then following videos at youtube are uploaded for you.
Length. 3:03
Length. 6:20
Installation of SQL Server 2005 is bit different as compared to new versions. If you are interested in old version, like SQL Server 2005 then, click on following link to get proper help.
Length 9:55
As 90% functions of SQL Server Management Studio are same in 2005 and later versions, following video (which is based on SQL Server 2005) will be helpful for users who are facing SQL Server Management Studio very first time. 
Length 10:47
Once you have installed SQL Server and successfully connected to your first database then, next thing you must learn is T-SQL Queries
Length 8:57

Thursday, June 9, 2011

SQL Server: Quickest Method to Create Single Table Backup

There are several ways to create backup for a single table in SQL Server database. In SQL Server 2008, now you can create insert statements for selected tables. Beside this Export wizard is commonly used to create a flat file backup for a single table. Both methods are reliable but still time consuming. And when I searched for quickest method to take backup of a single table I found following code by SQL Expert Fred.

USE AdventureWorks
GO
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
-- If i need to create CSV file Product table then
SET @table = 'Production.Product'
SET @file = 'D:\BCP_OUTPUT\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112)
+ '.csv'
SET @cmd = 'bcp "AdventureWorks.' + @table + '" out "' + @file + '" -S. -T -c -t,'
EXEC master..xp_cmdshell @cmd
Code basically uses BCP to create a CSV file for a given table. I can create a template of above code, and then just load, change values and execute. So simple but still it has a drawback. It creates a CSV file for all rows but WITHOUT column header row. Now how can I import this table later on, without column header row?

Then I found quickest and simplest way to achieve this goal. Simply type select * query but before execution
• Click on Tools --- > options and change values for Query Result Output Format and Custom Delimiter (I preferred pipe sign “|”)
• Press Ctrl+Shift+F buttons, so it can save result to file.
SELECT * FROM Production.Product
• On execution, provide file name and your desired path and it’s done
Don’t worry about newly created file extension. When need to import just select it from All Files (*,*).

Wednesday, June 8, 2011

SQL Server: Does Unwanted Tables in a Query or View Affect Performance

Recently a friend of mine asked, that is it true that presence of extra tables in joins section of a query, will affect query performance. Extra tables means,tables which can be skipped from query without affecting query result. For example following query has extra tables (other than vendor and contact tables) in join section
USE AdventureWorks
GO

SELECT Vendor.Name,
Contact.Title,
Contact.FirstName,
Contact.MiddleName
FROM Person.Address AS a
INNER JOIN Purchasing.VendorAddress AS VendorAddress
ON a.AddressID = VendorAddress.AddressID
INNER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegion
ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
INNER JOIN Purchasing.Vendor AS Vendor
INNER JOIN Purchasing.VendorContact AS VendorContact
ON VendorContact.VendorID = Vendor.VendorID
INNER JOIN Person.Contact AS Contact
ON Contact.ContactID = VendorContact.ContactID
INNER JOIN Person.ContactType AS ContactType
ON VendorContact.ContactTypeID = ContactType.ContactTypeID
ON VendorAddress.VendorID = Vendor.VendorID
Though this is NOT common to have extra tables in our usual queries but it could be possible in views. A view can be created with multiple tables and selecting columns from each joined table. And later on when we will query this view we can use only few columns in our select statement. So when we will execute above query SQL Server Query Analyzer will skip all those tables which are not part of game. Here is execution plan of above query.



Same query with more columns, pushing all tables in action.

SELECT Vendor.Name,
ContactType.Name AS ContactType,
Contact.Title,
Contact.FirstName,
Contact.MiddleName,
a.AddressLine1,
a.AddressLine2,
a.City,
StateProvince.Name AS StateProvinceName,
a.PostalCode,
CountryRegion.Name AS CountryRegionName,
Vendor.VendorID
FROM Person.Address AS a
INNER JOIN Purchasing.VendorAddress AS VendorAddress
ON a.AddressID = VendorAddress.AddressID
INNER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegion
ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
INNER JOIN Purchasing.Vendor AS Vendor
INNER JOIN Purchasing.VendorContact AS VendorContact
ON VendorContact.VendorID = Vendor.VendorID
INNER JOIN Person.Contact AS Contact
ON Contact.ContactID = VendorContact.ContactID
INNER JOIN Person.ContactType AS ContactType
ON VendorContact.ContactTypeID = ContactType.ContactTypeID
ON VendorAddress.VendorID = Vendor.VendorID



If we create a view using our second query and use our view in following style then execution plan will be same to our first query.

SELECT Name,
Title,
FirstName,
MiddleName
FROM vw_MyView
Hence, SQL Server Query Analyser is quite smart and work on only those tables which are part of actual game and it doesn’t matter that extra tables are part of your query or a view.

Friday, June 3, 2011

SQL Server: Automatic Query Execution at Every Instance Startup


Though production database servers are design to stay up for 24x7, but still when ever these production database servers go down and restart, sometime we need to execute some queries automatically on every start-up, like clean some setup tables or capture some sort of necessary data which is only available at instance start-up.
For such queries which need to be executed automatically at every start-up, we have to create a store procedure to encapsulate all these queries. Then automatic execution of this stored procedure is achieved by using the sp_procoption system stored procedure.
(Note: Best place to store such stored procedure is MASTER database)
Let’s create a stored procedure to store instance start-up time in a log table.
USE MASTER
GO
--Create table to hold startup time
CREATE TABLE dbo.InstanceLog
(StartupTime DATETIME)
GO
--Create stored procedure to execute on startup automatically
CREATE PROCEDURE dbo.Proc_InsertStartupTime
AS
INSERT dbo.InstanceLog
SELECT GETDATE()
GO
Now we will use SP_PROCOPTION to tell SQL Server that we want to execute our stored procedure at every instance start-up. Syntax will be as follow:
EXEC SP_PROCOPTION
@ProcName = 'Proc_InsertStartupTime',
@OptionName = 'STARTUP',
@OptionValue = 'TRUE'
After executing above statement, when ever SQL Server instance will restart, stored procedure will be executed automatically and a new row in our log table dbo.InstanceLog will be inserted.
To revert this option and to stop stored procedure from automatic execution, we will use following syntax.
EXEC sp_procoption
@ProcName = 'Proc_InsertStartupTime',
@OptionName = 'STARTUP',
@OptionValue = 'OFF'

(Applicable for SQL Server 2005 and above versions)

Thursday, June 2, 2011

SQL Server: Changed Data Capture without Using Trigger


Most of SQL Server users think that trigger is the only place where we can use DELETED and INSERTED virtual tables to capture data change in result of any INSERT, UPDATE or DELETE statement.
Old and new data values affected by DML operations can be captured by using OUTPUT clause along with virtual DELETED and INSERTED tables. Here are few examples which will be helpful to understand usage of this OUTPUT clause in simple insert, update and delete queries.

UPDATE:
USE AdventureWorks
GO
-- Create a table variable to hold updated rows
DECLARE @UpdatedRecords TABLE
(AddressID INT, OldAddressLine2 VARCHAR(50), NewAddressLine2 VARCHAR(50))

UPDATE Person.Address
SET AddressLine2 = 'Silver Street new'
OUTPUT DELETED.AddressID, DELETED.AddressLine2,INSERTED.AddressLine2
INTO @UpdatedRecords
WHERE AddressID = 3

SELECT * FROM @UpdatedRecords

DELETE:
USE AdventureWorks
GO
-- Create a table variable to hold deleted rows
DECLARE @DeletedRecords TABLE
(AddressID INT, AddressLine1 VARCHAR(50), AddressLine2 VARCHAR(50)
 ,City VARCHAR(50),StateProvinceID INT,PostalCode VARCHAR(10)
,rowguid UNIQUEIDENTIFIER,ModifiedDate DATETIME)

DELETE FROM Person.Address
OUTPUT DELETED.*
INTO @DeletedRecords
WHERE AddressID = 3

SELECT * FROM @DeletedRecords

INSERT:
INSERT INTO Person.Address
(AddressLine1 , AddressLine2 ,City ,StateProvinceID ,PostalCode
 ,rowguid ModifiedDate)
OUTPUT INSERTED.*
INTO @InsertedRecords
VALUES ('3rd BlackStone rd','wst.51','Bothell',78,98010,NEWID(),GETDATE()

Note: This method is applicable for SQL Server 2005 and above versions.