Print all column names in SSMS (SSMS trick)

Every once in a while I come across some “hidden” and nice features in SSMS. One of them is really nice if you want to print all columns names of a table.

So instead of writing all the column names by hand you could follow these easy steps.

– Open SSMS
– Expand you database
– Expand your table

expand-ssms

And then drag the folder called “Columns” to your query window

all-columns

Advertisements

Creating a snapshot of your database (SQL)

I am working quite a lot with virtual machines in Hyper-V and VMWare. In these products you have a really useful feature called “snapshot”.

The snapshot feature is most useful when you want to preserve the state of the database so you can return to the same state repeatedly. For example if you are doing a scary update or something you have the possibility to “roll back” these changes.

Did you know that you could make snapshots even in SQL Server?

Lets start by creating a snapshot of the AdventureWorksDW2012 database and create a “super important” table.

-- change database
use [AdventureWorksDW2012]

-- Create a super important table
create table mySuperImportantTable ([ID] int, [Desc] varchar(2000))

-- Insert some values
insert into mySuperImportantTable values (1, 'Secret 1')
insert into mySuperImportantTable values (2, 'Secret 2')
insert into mySuperImportantTable values (3, 'Secret 3')
insert into mySuperImportantTable values (4, 'Secret 4')
insert into mySuperImportantTable values (5, 'Secret 5')

-- Create a snapshot of the database
CREATE DATABASE AdventureWorksDW2012_Mysnapshot ON
(
NAME = AdventureWorksDW2012_Data,
FILENAME = 'C:\db snapshotfile\AdventureWorksDW2012_Mysnapshot.ss' )
AS SNAPSHOT OF [AdventureWorksDW2012];
GO

Then the big mistake happens. We delete the table by accident.

-- Oh no I did a mistake
drop table mySuperImportantTable

Because snapshot files tend to get big I am really curious about the size of it.

Huge file
Ouch… It looks really big 😦 But lets look at the actual disk usage

disk usage

Ahh… only 960 kB. I can live with that.

Okey back to my snapshot. I can write some basic SQL to list all my snapshots.

select * from sys.databases where source_database_id is not null

Lets try to rescue the day by restoring from my snapshot and then get the data from the database

-- I want to restore my database from snapshot
USE MASTER
RESTORE DATABASE AdventureWorksDW2012
FROM DATABASE_SNAPSHOT = 'AdventureWorksDW2012_Mysnapshot'

-- Is my table there?
use AdventureWorksDW2012
select * from mySuperImportantTable
result

Cool. My snapshots saved my day. Then some cleaning. Delete the snapshot and then my sample table.

 -- Drop my snapshot database
DROP DATABASE AdventureWorksDW2012_Mysnapshot ;

-- Drop my table
drop table mySuperImportantTable

Hot swap table in SSIS / SQL with retry logic

At one of my customers they wanted me to load data into a vital table during daytime. The main problem is that this table is sometimes used in other SSIS packages. Also during daytime. The SSIS package that fills this table is a trunc / fill job.

So I started working on a technique to load data into another table and then switch names. To make this solution more robust I also added a retry logic and a delay.

You might want to change the script so that it fits your environment and needs 🙂

So your SSIS package will fill the table “MainTable_swap”. Then you can add a SQL Task at the end that executes the script below.

Declare @retries int = 0
Declare @numberOfRetries int = 4

RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

        EXEC sp_rename 'MainTable', 'MainTable_switch'
        EXEC sp_rename 'MainTable_swap', 'MainTable'
        EXEC sp_rename 'MainTable_switch', 'MainTable_swap'
	 
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	IF @retries < @numberOfRetries
	BEGIN
		print 'Failed... retry...'
		Set @retries = @retries + 1
		WAITFOR DELAY '00:05:00.00' -- Wait for 5 m
		GOTO RETRY -- Go to Label RETRY
	END
	ELSE
	BEGIN
		RAISERROR (N'Could not rename table... giving up!', 10,  1, N'number', 5);
		select 1/0
	END
END CATCH

The “select 1/0” is to force SQL Task in SSIS to fail if the script fails. It did not response to the RAISEERROR.

Feel free to drop me an email if you have an improvment to the script

Use “between” when comparing dates in SQL

Recently I was at the SQL Saturday in Oslo. In one of the lessons (with Joe Chang) I was told that “between” was faster than using “month”, “year” and similar functions when comparing dates. They use the index in a better way.

I did a quick test this evening. And here is my findings.

--Create and index
CREATE NONCLUSTERED INDEX IX_FactInternetSales_OrderDate 
ON dbo.FactInternetSales
(
	OrderDate
) 
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- Turn on statistics
SET STATISTICS IO ON

-- Query 1
select * from FactInternetSales
where year(OrderDate) = 2005 and month(OrderDate) = 07

-- Query 2
select * from FactInternetSales
where OrderDate between '2005/07/01' and '2005/07/31'

The IO statistics looked like this

(146 row(s) affected)
Table 'FactInternetSales'. Scan count 1, logical reads 689, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(146 row(s) affected)
Table 'FactInternetSales'. Scan count 1, logical reads 459, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

As you can see much less logical read on query 2.

And the actual execution plan looked like this

QueryPlanDate

The conclusion is that mr Chang knows what he is talking about 🙂

Optimize “Insert into” in SQL

In this post I will give you some advice on how to extract data from a large table into a new table. In this case we are using the “INSERT INTO” statement. When working with large tables you want to have a query that runs fast and uses as little logging as possible.

Consider this query

USE [AdventureWorksDW2012]

CREATE TABLE [dbo].[FactInternetSales_new](
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_FactInternetSales_new ON dbo.FactInternetSales_new
(
	CustomerKey
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
insert into [dbo].[FactInternetSales_new]
select * 
from [dbo].[FactInternetSales]
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

drop table [dbo].[FactInternetSales_new]

The output for this query will be

Insert 1

If I change the query so that the destination table is a heap (no index) and adds the “TABLOCK” query hint I will gain a minimally logged operation. And when the data is over I will create my index.

Lets take a look at the script

USE [AdventureWorksDW2012]

CREATE TABLE [dbo].[FactInternetSales_new_fast_one](
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
) ON [PRIMARY]

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
INSERT INTO [dbo].[FactInternetSales_new_fast_one] WITH (TABLOCK)
select * 
from [dbo].[FactInternetSales]

CREATE NONCLUSTERED INDEX IX_FactInternetSales_new ON dbo.[FactInternetSales_new_fast_one]
(
	CustomerKey
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]

SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

drop table [dbo].[FactInternetSales_new_fast_one]

This query will run in about half the time on my laptop

Insert 2

Save your deleted or updated data with output clause in SQL

I am always kind of nervous when I am deleting data from a production environment. Or updating large datasets. To be able of rolling back changes you can save you data into a backup table by using the output clause.

Lets take a look at an example

create table myTest (ID int, name varchar(50))

insert into myTest values (1, 'John')
insert into myTest values (2, 'Frank')
insert into myTest values (3, 'Sammy')
insert into myTest values (4, 'Julie')
insert into myTest values (5, 'Mike')

delete from myTest
output deleted.* 
where ID = 5;

drop table myTest

And this will give us an output on the rows we are deleting

output

If we want to save this to a new table we can change our syntax to this

create table myTest (ID int, name varchar(50))

insert into myTest values (1, 'John')
insert into myTest values (2, 'Frank')
insert into myTest values (3, 'Sammy')
insert into myTest values (4, 'Julie')
insert into myTest values (5, 'Mike')

create table myBackup (ID int, name varchar(50))

delete from myTest 
output deleted.* into myBackup
where ID = 5;

select * from myBackup

drop table myTest;
drop table myBackup;

To do the same for updated rows you can use this syntax (saves the data BEFORE the update)

update myTest 
	Set name = 'Willy'
output deleted.* into myBackup
where Id = 5

And to save the new data

update myTest 
	Set name = 'Willy'
output inserted.* into myBackup
where Id = 5