Showing posts with label SSMS. Show all posts
Showing posts with label SSMS. Show all posts

Monday, October 16, 2017

Do not trust the SSMS designers, learn the T-SQL way



It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Read the following two lines

Question: How do you add a primary key to a table?
Answer: I click on the yellow key icon in SSMS!

Have you ever given that answer or has anyone every answered that when you asked this question?

Technically, yes, that will create a primary key on the table but what will happen when you do that? Let's take a look at some examples.
First create this very simple table

CREATE TABLE TestInt(Col1 tinyint not null)

Now the developers changed their mind and want to insert values that go beyond what a tinyint can hold. If you try to insert 300, you will get an error

INSERT TestInt VALUES(300)

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 300.

The statement has been terminated.


No, problem, I will just change the data type by running this T-SQL statement

ALTER TABLE TestInt ALTER COLUMN Col1 int NOT NULL


But what if you use the SSMS designer by right clicking on the table, choosing design and then changing the data type from tinyint to int?

The answer is it depends on an option and if it is checked or not



If that option is checked, then you will get the following message when clicking on the script icon




If that option is not checked then here is what SSMS will do behind the scenes for you


/* To prevent any potential data loss 
issues, you should review this script in 
detail before running it outside the context
 of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestInt
 (
 Col1 int NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.TestInt)
  EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
  SELECT CONVERT(int, Col1) FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestInt
GO
EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT' 
GO
COMMIT
That is right, it will create a new table, dump all the rows into this table, drop the original table and then rename the table that was just created to match the orgiinal table. This is overkill.

What about adding some defaults to the table, if you use the SSMS table designer, it will just create those and you have no way to specify a name for the default.
Here is how to create a default with T-SQL, now you can specify a name and make sure it matches your shop's naming convention

ALTER TABLE dbo.TestInt ADD CONSTRAINT
 DF_TestInt_Col1 DEFAULT 1 FOR Col1

About that yellow key icon, let's add a primary key to our table, I can do the following with T-SQL, I can also make it non clustered if I want to

ALTER TABLE dbo.TestInt ADD CONSTRAINT
 PK_TestInt PRIMARY KEY CLUSTERED 
 (Col1)  ON [PRIMARY]

Click that yellow key icon and here is what happens behind the scenes, I have not found a way to make it non clustered from the designer

/* To prevent any potential data loss issues, 
you should review this script in detail before running it 
outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestInt
 DROP CONSTRAINT DF_TestInt_Col1
GO
CREATE TABLE dbo.Tmp_TestInt
 (
 Col1 int NOT NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_TestInt ADD CONSTRAINT
 DF_TestInt_Col1 DEFAULT ((1)) FOR Col1
GO
IF EXISTS(SELECT * FROM dbo.TestInt)
  EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
  SELECT Col1 FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestInt
GO
EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT' 
GO
ALTER TABLE dbo.TestInt ADD CONSTRAINT
 PK_TestInt PRIMARY KEY CLUSTERED 
 (
 Col1
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

You might ask yourself why you should care, all the tables are small, this is not a big issue. This might be true now, what if you start a new job and now you have to supply alter, delete and create scripts? Now you are in trouble.

I used to do the same when I started, I used the designers for everything, I didn't even know Query Analyzer existed when I started, I created and modified the stored procedures straight inside Enterprise Manager. Trying to modify a view that had a CASE statement in Enterprise Manager from the designer....yeah good luck with that one....you would get some error that it wasn't supported, I believe it also injected TOP 100 PERCENT ORDER BY in the view as well

I don't miss those days at all. Get to learn T-SQL and get to love it, you might suffer when you start but you will become a better database developer.
Aaron Bertrand also has a post that you should read about the designers: Bad habits to kick : using the visual designers

Thursday, July 13, 2017

SSMS: When did the query finish, when did the query start?



This is a quick post but it might be an item you can add to your Today I Learned list  :-)

Let's say you get to a SSMS query window and you see the following



You can tell it ran really fast and you can tell that 26080 rows were affected.  Can you tell when it started and when it finished?

Yes, you can!!!

Hit the F4 button or select View-->Properties Window from the menu bar

Here is what I see



As you can see there are a bunch of properties about the query

For example

Start time: 7/13/2017 9:58:01 AM
Finish time: 7/13/2017 9:58:02 AM
Elapsed time: 00:00:00.766


There you have it, a quick and easy way to tell when something ran in your query windows

Sunday, October 30, 2016

No, you are not getting the dark theme for SSMS anytime soon

At the SQL Server chalk talk session the question was asked when SQL Server Management Studio would get a dark theme. This is not an unreasonable question, after all, SQL Server Management Studio is built on top of the Visual Studio shell.

Here is what a light theme looks like in Visual Studio


And here is the same when I apply the dark theme


For me the light theme looks better...but what do I know
So why won't we get the dark theme for SQL Server Management Studio anytime soon?

The person answering the questions said that hears this asked all the time, he sees it on twitter, he sees it on facebook. He actually loves the dark theme in Visual Studio.

So the problem is that there are hundreds and hundreds of dialog boxes, wizards and views in SQL Server Management Studio that are not theme aware, those were written somewhere over the last 10 to 13 years.

They just finished the high DPI fix, that took hundreds if not thousand of code changes in files. So this is just basically a huge funding cost, priority wise it is really tough to justify doing this work, compared to feature requests and bug fixes.

So there you have it.... NO DARK THEME FOR YOU!!!!

Friday, September 18, 2015

Whenever you use the designer in SSMS, click on the Generate Change Script icon to verify what it will do



SQL Server Management Studio is pretty good tool to manage and develop against SQL Server instances. You have to be aware of some things that can be dangerous. Just the other day I had to help someone on a development environment who had a problem when he made a column bigger


The table looked like this


CREATE TABLE [TestTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[itemdate] [datetime] NOT NULL ,
[title] [varchar] (30) NOT NULL ,
) ON [PRIMARY]



So what the person wanted to do was make the title column 50 characters instead of 30. He right clicked on the table, clicked on Design, made the change and hit the save button. He then tried to run a query against the table and it was stuck, nothing was being returned. After a couple of minutes all seemed fine.
So what happened? Behind the scenes SSMS actually created a new table, dumped all that data into that table, dropped the original table and then renamed this new table to match the name of the original table



Here is what the script looks like that SSMS generates


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_TestTable
(
id int NOT NULL IDENTITY (1, 1),
itemdate datetime NOT NULL,
title varchar(50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_TestTable SET (LOCK_ESCALATION = TABLE)
GO

SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO

IF EXISTS(SELECT * FROM dbo.TestTable)
EXEC('INSERT INTO dbo.Tmp_TestTable (id, itemdate, title)

SELECT id, itemdate, title FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO

DROP TABLE dbo.TestTable
GO

EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT



All that can be replaced with this simple one line command, it runs much faster and it doesn't recreate anything, no data is being moved around either.


ALTER TABLE [TestTable] ALTER COLUMN [title] [varchar] (50)


The only time you should be using the designer is if the table is empty or if you want to see what kind of T-SQL the designer generates. You can see the T-SQL that SSMS generates by clicking on the Generate Change Script icon, you can then copy the T-SQL into a SSMS query window to look at it. You can see the Generate Change Script icon in the image below

 









By the way, SSMS will by default disable the recreation of tables when clicking on the save button, this can be disabled or enabled by unchecking or checking the highlighted option in the image below. You can get to this by clicking Tools-->Options-->Table and Database Designers




Sunday, September 06, 2015

Awesome SSMS shortcuts that will save you tons of time

How many times have you written a join with a bunch of tables and you wanted to quickly see what was in a table. You would either find the table in object explorer, right click and click on SELECT top 1000 rows

This would produce something like the following
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [lastrun]
      ,[cpu_busy]
      ,[io_busy]
      ,[idle]
      ,[pack_received]
      ,[pack_sent]
      ,[connections]
      ,[pack_errors]
      ,[total_read]
      ,[total_write]
      ,[total_errors]
  FROM [master].[dbo].[spt_monitor]
What if I told you that there is a way, way better and faster way.....
In SSMS click on Tools then Options, from the right site pick Environment-->Keyboard-->Query Shortcuts you should see something like the following


In the CTRL + 5 shortcut space enter the following select top 100 * from 
Make sure you have a trailing space after from
Hit OK, now restart SSMS because it won't take effect right away
Once you have restarted SSMS and have connected to a database, in a new query window type any table name that you want, for example if you are in the master database type spt_values, double click on the table name so that it is highlighted, now press CTRL + 5 and voila you should see 100 rows from this table....

Now you can just click on any table or view and see the 100 rows, this way you can easy see what the table looks like

You can also do this with sp_helptext, here is how you do it, add sp_helptext where CTRL + 6 is, make sure you have a trailing space after the proc name

Restart SSMS again, type a proc name, double click on the name, hit CTRL + 6 and you should see the definition in the output

There you have it..some time saver shortcuts. You can also do this with other queries and procs...what are some of your favorite shortcuts?