A blog about SQL Server, Books, Movies and life in general
Friday, December 29, 2017
The 15 most popular posts in 2017
Another year is almost over. As the last post of this year I decided to share with you the 15 most popular posts in 2017. I just used Google Analytics to grab this info, I excluded the home page from the top 15. Some of these post are so old, we didn't even have windowing functions in SQL Server when these were written.,....
Here they are in order of popularity
01. Ten SQL Server Functions That You Hardly Use But Should
A post from 2007 showing some hardly used functions like NULLIF, PARSENAME and STUFF
02. Five Ways To Return Values From Stored Procedures
A very old post that shows you five ways to return values from a stored proc
03. Your lack of constraints is disturbing
A post showing the type of constraints available in SQL Server with examples
04. Use T-SQL to create caveman graphs
One of the shortest post on this site, show you how you can make visually appealing output with a pipe symbol
05. Convert Millisecond To "hh:mm:ss" Format
A very old post showing you how to convert from milliseconds to "hh:mm:ss" format
06. T-SQL Tuesday #92, Lessons learned the hard way
Some of my mistakes put together in 1 post
07. SQL Server 2017: SQL Graph
Me messing around with the Graph functionality in SQL Server 2017
08. Query Analyzer Trick
A very old post explaining how you can drag the columns into the query window from object explorer. I think by now everyone knows this one right?
09. ISO-11179 Naming Conventions
A very old post linking to the ISO-11179 Naming Conventions document
10. Not sure that I like the message from Online Resumable Index Rebuild in SQL Server 2017
After playing qround with resumable index rebuilds I think the kill state and severe error occurred is a little over the top
11. Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection
This is my recap of the chalkboard session with the SQL Server team at the SQL Server PASS summit in Seattle.
12. Five great SQL Server GitHub repos that every SQL Server person should check out
What the titles says, 5 GitHub repos you need to use
13. Chaos Isolation Level In SQL Server
This is linked from dba.stackexchange.com, it is kind of interesting because it was a silly post I made
14. T-SQL Tuesday #86: String or binary data would be truncated
A pet peeve of many people
15. Microsoft releases SQL Server Horizontica, a Vertica killer
Probably the best version of SQL Server ever created, was only available for download for 24 hours.
That is all for this year... see you in 2018... the year of Linux on the desktop, DevOps, Ethereum, AR/VR, bendable phones and much more......
,
Wednesday, November 29, 2017
Use T-SQL to create caveman graphs
I found this technique on Rich Benner's SQL Server Blog: Visualising the Marvel Cinematic Universe in T-SQL and decided to play around with it after someone asked me to give him the sizes of all databases on a development instance of SQL Server
The way it works is that you take the size of the database and then divide that number against the total size of all databases. You then use the replicate function with the | (pipe) character to generate the 'graph' so 8% will look like this ||||||||
You can use this for tables with most rows, a count per state etc etc. By looking at the output the graph column adds a nice visual effect to it IMHO
Here is what the final query looks like
SELECT database_name = DB_NAME(database_id) , total_size_GB = CAST(SUM(size) * 8. / 1024/1024 AS DECIMAL(30,2)) , percent_size = (CONVERT(decimal(30,4),(SUM(size) / (SELECT SUM(CONVERT(decimal(30,4),size)) FROM sys.master_files WITH(NOWAIT)))) *100.00) , graph = replicate('|',((convert(decimal(30,2),(SUM(size) / (SELECT SUM(CONVERT(decimal(30,2),size)) FROM sys.master_files WITH(NOWAIT)))) *100))) FROM sys.master_files WITH(NOWAIT) GROUP BY database_id ORDER BY 3 DESC
And here is the output (I blanked out the DB name in the output below), there are 48 databases, 15 of them show a bar, the rest don't because they use less than 0.5% of space.
Do you see how you can quickly tell visually that the top DB is about twice as large as the next DB?
Those guys in Lascaux would have been so proud, only if they could see this :-)
Wednesday, October 25, 2017
How to update 2 tables with 1 statement in SQL Server..the hard way....
Every now and then you will get someone asking how to update two tables with one statement in SQL Server. The answer is usually, no that is not possible... the person then walks away muttering something about how MySQL allows it.
So I decided to try to see if I could update two tables with one statement. I decided to try a couple of different things
- view
- common table expression
- indexed view
- instead of trigger
In order to begin we need two tables, each table will have one row of data so we can update those rows
CREATE TABLE test1(id int primary key, someVal char(1) not null) CREATE TABLE test2(id int primary key, someVal char(1) not null) INSERT test1 VALUES(1,'a') INSERT test2 VALUES(1,'a') Go
Now we can start with plan A... the mighty view
CREATE VIEW Testview1 AS SELECT t1.*,t2.SomeVal as SomeVal2 FROM test1 t1 JOIN test2 t2 on t1.id = t2.id
Running a simple select against the view
SELECT * FROM testview1
id someVal SomeVal2
1 a a
Time to update the view
UPDATE testview1 SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
Msg 4405, Level 16, State 1, Line 1
View or function 'testview1' is not updatable because the modification affects multiple base tables.
As you can see that didn't work since even though you are updating one view, you are still trying to update two tables.
Time to implement plan B... the versatile common table expression
Since you can update a common table expression, can you update a common table expression if it updates more than one table? Let's try it out
;WITH cte AS (SELECT t1.*,t2.SomeVal as SomeVal2 FROM test1 t1 JOIN test2 t2 on t1.id = t2.id) UPDATE cte SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
Msg 4405, Level 16, State 1, Line 1
View or function 'cte' is not updatable because the modification affects multiple base tables.
So plan B ended like plan A... also pretty much the same error message
Plan C.... the mighty indexed view.
If you ever tried working with indexed views you are probably busy cursing at the moment.
Let's create this view and add an index
CREATE VIEW testviewIndexed WITH SCHEMABINDING AS SELECT t1.id, t1.someVal, t2.SomeVal as SomeVal2 FROM dbo.test1 t1 JOIN dbo.test2 t2 on t1.id = t2.id GO CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON testviewIndexed (id);
Fingers crossed....but if the regular view didn't work..why would this?
UPDATE testviewIndexed SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
Msg 4405, Level 16, State 1, Line 2
View or function 'testviewIndexed' is not updatable because the modification affects multiple base tables.
That is right... plan C is also bad, very bad.
So we are left with one more thing... plan D (as in Denis)....
We will now use an instead of trigger on the regular view from before
CREATE TRIGGER InsteadTrigger on testview1 INSTEAD OF UPDATE AS BEGIN UPDATE t SET t.SomeVal = i.SomeVal FROM INSERTED i JOIN test1 t on i.id = t.id UPDATE t SET t.SomeVal = i.SomeVal2 FROM INSERTED i JOIN test2 t on i.id = t.id END GO
Let's see what happens now.. fingers crossed
UPDATE testview1 SET SomeVal = 'b', SomeVal2 = 'b' WHERE id = 1
No error, let's see what is in the table
SELECT * FROM test1 SELECT * FROM test2
id someVal SomeVal2
1 b b
And as you can see, you can update two tables with one statement.
Should you really go through all this trouble because you don't want to do something like this?
BEGIN TRAN UPDATE test1 SET SomeVal = 'c' WHERE id = 1 UPDATE test2 SET SomeVal = 'c' WHERE id = 1 COMMIT-- hmm where is the error checking/begin try xact_state()?
Nope I would not go this route, if the table changes you now need to also update the trigger. What if someone drops the trigger? There are too many ways this can go wrong
Foreign Keys don't always need a primary key
In the post Your lack of constraints is disturbing we touched a little upon foreign key constraints but today we are going to take a closer look at foreign keys. The two things that we are going to cover are the fact that you don't need a primary key in order to define a foreign key relationship, SQL Server by default will not index foreign keys
You don't need a primary key in order to have a foreign key
Most people will define a foreign key relationship between the foreign key and a primary key. You don't have to have a primary key in order to have a foreign key, if you have a unique index or a unique constraint then those can be used as well.
Let's take a look at what that looks like with some code examples
Let's take a look at what that looks like with some code examples
A foreign key with a unique constraint instead of a primary key
First create a table to which we will add a unique constraint after creation
First create a table to which we will add a unique constraint after creation
CREATE TABLE TestUniqueConstraint(id int)
GO
Add a unique constraint to the table
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
GO
Insert a value of 1, this should succeed
INSERT TestUniqueConstraint VALUES(1)
GO
Insert a value of 1 again, this should fail
INSERT TestUniqueConstraint VALUES(1)
GO
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'ix_unique'. Cannot insert duplicate key in object 'dbo.TestUniqueConstraint'. The duplicate key value is (1).
The statement has been terminated.
Violation of UNIQUE KEY constraint 'ix_unique'. Cannot insert duplicate key in object 'dbo.TestUniqueConstraint'. The duplicate key value is (1).
The statement has been terminated.
Now that we verified that we can't have duplicates, it is time to create the table that will have the foreign key
CREATE TABLE TestForeignConstraint(id int)
GO
Add the foreign key to the table
ALTER TABLE dbo.TestForeignConstraint ADD CONSTRAINT FK_TestForeignConstraint_TestUniqueConstraint FOREIGN KEY (id) REFERENCES dbo.TestUniqueConstraint(id)
Insert a value that exist in the table that is referenced by the foreign key constraint
INSERT TestForeignConstraint VALUES(1)
INSERT TestForeignConstraint VALUES(1)
Insert a value that does not exist in the table that is referenced by the foreign key constraint
INSERT TestForeignConstraint VALUES(2)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TestForeignConstraint_TestUniqueConstraint". The conflict occurred in database "tempdb", table "dbo.TestUniqueConstraint", column 'id'.
The statement has been terminated.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TestForeignConstraint_TestUniqueConstraint". The conflict occurred in database "tempdb", table "dbo.TestUniqueConstraint", column 'id'.
The statement has been terminated.
As you can see, you can't insert the value 2 since it doesn't exist in the TestUniqueConstraint table
A foreign key with a unique index instead of a primary key
This section will be similar to the previous section, the difference is that we will use a unique index instead of a unique constraint
This section will be similar to the previous section, the difference is that we will use a unique index instead of a unique constraint
First create a table to which we will add a unique index after creation
CREATE TABLE TestUniqueIndex(id int)
GO
Add the unique index
CREATE UNIQUE NONCLUSTERED INDEX ix_unique ON TestUniqueIndex(id)
GO
Insert a value of 1, this should succeed
INSERT TestUniqueIndex VALUES(1)
GO
Insert a value of 1 again , this should now fail
INSERT TestUniqueIndex VALUES(1)
GO
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.TestUniqueIndex' with unique index 'ix_unique'. The duplicate key value is (1).
The statement has been terminated.
Cannot insert duplicate key row in object 'dbo.TestUniqueIndex' with unique index 'ix_unique'. The duplicate key value is (1).
The statement has been terminated.
Now that we verified that we can't have duplicates, it is time to create the table that will have the foreign key
CREATE TABLE TestForeignIndex(id int)
GO
Add the foreign key constraint
ALTER TABLE dbo.TestForeignIndex ADD CONSTRAINT FK_TestForeignIndex_TestUniqueIndex FOREIGN KEY
(id) REFERENCES dbo.TestUniqueIndex(id)
Insert a value that exist in the table that is referenced by the foreign key constraint
INSERT TestForeignIndex VALUES(1)
INSERT TestForeignIndex VALUES(1)
Insert a value that does not exist in the table that is referenced by the foreign key constraint
INSERT TestForeignIndex VALUES(2)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TestForeignIndex_TestUniqueIndex". The conflict occurred in database "tempdb", table "dbo.TestUniqueIndex", column 'id'.
The statement has been terminated.
That failed because you can't insert the value 2 since it doesn't exist in the TestUniqueIndex table
As you have seen with the code example, you can have a foreign key constraint that will reference a unique index or a unique constraint. The foreign key does not always need to reference a primary key
Foreign keys are not indexed by default
When you create a primary key, SQL Server will by default make that a clustered index. When you create a foreign key, there is no index created
Scroll up to where we added the unique constraint to the TestUniqueConstraint table, you will see this code
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
All we did was add the constraint, SQL Server added the index behind the scenes for us in order to help enforce uniqueness more efficiently
Now run this query below
SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName,
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestUniqueIndex','TestUniqueConstraint')
AND name IS NOT NULL
You will get these results
TableName IndexName StorageType --------------------- ----------- -------------- TestUniqueConstraint ix_unique NONCLUSTERED TestUniqueIndex ix_unique NONCLUSTERED
As you can see both tables have an index
Now let's look at what the case is for the foreign key tables. Run the query below
SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName,
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestForeignIndex','TestForeignConstraint')
Here are the results for that query
TableName IndexName StorageType --------------------- --------- ------------- TestForeignConstraint NULL HEAP TestForeignIndex NULL HEAP
As you can see no indexes have been added to the tables. Should you add indexes? In order to answer that let's see what would happen if you did add indexes. Joins would perform faster since it can traverse the index instead of the whole table to find the matching join conditions. Updates and deletes will be faster as well since the index can be used to find the foreign keys rows to update or delete (remember this depends if you specified CASCADE or NO ACTION when you create the foreign key constraint)
I wrote about deletes being very slow because the columns were not indexed here: Are your foreign keys indexed? If not, you might have problems
So to answer the question, yes, I think you should index the foreign key columns
Thursday, October 19, 2017
Your lack of constraints is disturbing
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.
SQL Server supports the following types of constraints:
NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY
Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer will also use constraint definitions to build high-performance query execution plans.
NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY
Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer will also use constraint definitions to build high-performance query execution plans.
When I interview people, I always ask how you can make sure only values between 0 and 9 are allowed in an integer column. I get a range of different answers to this question, here are some of them:
- Convert to char(1) and make sure it is numeric
- Write logic in the application that will check for this
- Use a trigger
- Create a primary key table with only the values from 0 till 9 then make this column a foreign key in the table you want to check for this
Only 25% of the people will tell you to use something that you can use from within SQL, and only 10% will actually know that this something is called a check constraint, the other ones know that there is something where you can specify some values to be used.
Why do we need constraints at all?
So why do we need constraints? To answer that question, first you have to answer another question: how important is it that the data in your database is correct? I would say that that is most important, after all you can have all the data in the world but if it is wrong it is useless or it might even ending up costing you money. To make sure that you don't get invalid data, you use constraints.
Constraints work at the database level, it doesn't matter if you do the data checking from the app or web front-end, there could be someone modifying the data from SSMS. If you are importing files, constraints will prevent invalid data from making it into the tables.
Constraints work at the database level, it doesn't matter if you do the data checking from the app or web front-end, there could be someone modifying the data from SSMS. If you are importing files, constraints will prevent invalid data from making it into the tables.
Constraints don't just have to have a range, constraints can handle complex validations. You can have regular expressions in check constraints as well, check out SQL Server does support regular expressions in check constraints, you don't always need triggers for some examples
Constraints are faster than triggers
The reason that check constraints are preferable over triggers is that they are not as expensive as triggers, you also don't need an update and an insert trigger, one constraint is enough to handle both updates and inserts.
Constraints are making it hard for us to keep our database scripts from blowing up
This is a common complaint, when you script out the databases and the primary and foreign key tables are not in the correct order you will get errors. Luckily the tools these days are much better than they were 10 years ago. If you do it by hand just make sure that it is all in the correct order. Another complaint is that constraints are wasting developers time, they can't just populate the tables at random but have to go in the correct order as well.
Some examples of constraints
First create this table
CREATE TABLE SomeTable(code char(3) NOT NULL)
GO
Now let's say we want to restrict the values that you can insert to only accept characters from a through z, here is what the constraint looks like
ALTER TABLE SomeTable ADD CONSTRAINT ck_bla
CHECK (code LIKE '[a-Z][a-Z][a-Z]' )
GO
If you now run the following insert statement....
INSERT SomeTable VALUES('123')
You get this error message back
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "tempdb", table "dbo.SomeTable", column 'code'.
The statement has been terminated.
The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "tempdb", table "dbo.SomeTable", column 'code'.
The statement has been terminated.
What if you have a tinyint column but you want to make sure that values are less then 100? Easy as well, first create this table
CREATE TABLE SomeTable2(SomeCol tinyint NOT NULL)
GO
Now add this constraint
ALTER TABLE SomeTable2 ADD CONSTRAINT ck_SomeTable2
CHECK (SomeCol < 100 )
GO
Try to insert the value 100
INSERT SomeTable2 VALUES('100')
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_SomeTable2". The conflict occurred in database "tempdb", table "dbo.SomeTable2", column 'SomeCol'.
The statement has been terminated.
The INSERT statement conflicted with the CHECK constraint "ck_SomeTable2". The conflict occurred in database "tempdb", table "dbo.SomeTable2", column 'SomeCol'.
The statement has been terminated.
Okay, what happens if you try to insert -1?
INSERT SomeTable2 VALUES('-1')
Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '-1' overflowed an INT1 column. Use a larger integer column.
The statement has been terminated.
The conversion of the varchar value '-1' overflowed an INT1 column. Use a larger integer column.
The statement has been terminated.
As you can see you also get an error, however this is not from the constraint but the error is raised because the tinyint datatype can't be less than 0
Check constraint can also be tied to a user defined function and you can also use regular expressions. Ranges can also be used, for example salary >= 15000 AND salary <= 100000
For a post about foreign key constraints, go here: Foreign Keys don't always need a primary key
For a post about foreign key constraints, go here: Foreign Keys don't always need a primary key
Wednesday, October 18, 2017
SQL Server does support regular expressions in check constraints, you don't always need triggers
Someone posted the following question
I need to add table called group with a column called code How do I add a check constraint to the column so it will only allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters.Someone posted the following answer
You cannot do this out of the box - MS SQL Server does support CHECK CONSTRAINTS - but for things like a maximum or minimum INT value, or a string length or such. What you're looking for would be a CHECK based on a regular expression - and out of the box, SQL Server does not offer that capability. You could theoretically write a .NET assembly, deploy it inside SQL Server, and then use it to enforce the CHECK - not a trivial undertaking.While SQL server does not support a full implementation of regular expression, you can do what the person asked for without a problem in T-SQL. Here is what the regular expression looks like
[DMOPT][0-9][0-9]
A constraint like that will allow allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters. Enough talking let's look at some code, first create this table
CREATE TABLE blatest(code char(3))
Now add the check constraint
ALTER TABLE blatest ADD CONSTRAINT ck_bla
CHECK (code like '[DMOPT][0-9][0-9]' )
GO
Now we can run some tests by inserting some dataINSERT blatest VALUES('a12') --fails
INSERT blatest VALUES('M12') --good
INSERT blatest VALUES('D12') --good
INSERT blatest VALUES('DA1') --fails
As you can see we got the following message twiceServer: Msg 547, Level 16, State 1, Line 1 The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "Test", table "dbo.blatest", column 'code'. The statement has been terminated.
If you want to insert D12 but not d12, in other words you need the constraint to be case sensitive.
You will need to create the constraint like this check (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS ) What we did is used the SQL_Latin1_General_CP1_CS_AS collation, to find out what this collation does, run the following
SELECT * FROM ::fn_helpcollations()
WHERE name = 'SQL_Latin1_General_CP1_CS_AS'
Here is what is returned as the description
Latin1-General, case-sensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 51 on Code Page 1252 for non-Unicode DataLet's create the constraint, first we need to drop the old constraint
ALTER TABLE blatest DROP CONSTRAINt ck_bla
GO
Now we will create the new case sensitive constraint
ALTER TABLE blatest ADD CONSTRAINT ck_bla
CHECK (code LIKE '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )
GO
INSERT blatest VALUES('D12') --good
INSERT blatest VALUES('d12') --fails
The insert with D12 will succeed but the one with d12 will not.
As you can see you can use regular expressions in check constraints, there is no need to use a trigger in a case like this.
Subscribe to:
Posts (Atom)