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
And then drag the folder called “Columns” to your query window
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.
Ouch… It looks really big 😦 But lets look at the actual 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
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
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
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
The conclusion is that mr Chang knows what he is talking about 🙂
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
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
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
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
A blog about Analytics, AWS and Azure 