A Fine Slice Of SQL Server
Marek Masko has some commands which will help DBAs get familiar with Docker:
Stop container
To stop container:
- docker stop <container ID>
To stop all running containers:
docker stop $(docker ps -a -q)
Most of the commands are straightforward but it’s nice to have a reference guide.
Comments closedNow, while the SQL transformation from
FETCH FIRSTtoROW_NUMBER()filtering is certainly correct, the execution plan doesn’t really make me happy. Consider theROWNUMbased query:--------------------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | COUNT STOPKEY | | | | 2 | VIEW | | 1 | | 3 | TABLE ACCESS BY INDEX ROWID| FILM | 1000 | | 4 | INDEX FULL SCAN | PK_FILM | 1 | --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1)And compare that to the
FETCH FIRSTquery:------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | VIEW | | 1 | |* 2 | WINDOW SORT PUSHED RANK| | 1000 | | 3 | TABLE ACCESS FULL | FILM | 1000 | ------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "FILM"."FILM_ID")<=1)
Lukas digs into this and is not the biggest fan of OFFSET-FETCH. On the SQL Server side, my anecdotal experience has been that it doesn’t perform nearly as well as you’d like either.
Comments closedKenneth Fisher explains the difference between a string and an identifier:
A common mistake, and one I make frequently myself is to use a string in place of an identifier, or vise-versa. So to start, let’s have some definitions, shall we?
String
a linear sequence of characters, words, or other data.
Identifier
a sequence of characters used to identify or refer to a program or an element, such as a variable or a set of data, within it.
And because I always find examples fairly useful.
Click through for the example as well as additional explanation.
Comments closedSo far so good. Now let’s throw in a twist. Let’s call it through a linked server.
INSERT INTO [(local)\sql2014cs].Test.dbo.IdentTest VALUES ('Col1','Col2');Msg 213, Level 16, State 1, Line 4
Column name or number of supplied values does not match table definition.Well that’s a bit odd, right? I mean I used that exact command in the previous test. Turns out that when you do an insert across a linked server that identity column is not ignored. Which means we just need to include the identity value right? Nope.
INSERT INTO [(local)\sql2014cs].Test.dbo.IdentTest VALUES (1,'Col1','Col2');Msg 7344, Level 16, State 1, Line 4
The OLE DB provider “SQLNCLI11” for linked server “(local)\sql2014cs” could not INSERT INTO table “[(local)\sql2014cs].[Test].[dbo].[IdentTest]” because of column “Id”. The user did not have permission to write to the column.
Click through to see how to do this.
Comments closedMatthew McGiffen notes that there is only one way to order, and that is to use the ORDER BY clause:
Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed.
But then you run queries a lot of times that don’t need a specific order – and you see that they (at least seem to) come out in the same order every time. You could (almost) be forgiven for thinking you can rely on that.
There was even a question on a Microsoft SQL certification exam a few years ago that asked what the default order was for records returned by a simple SELECT – the answer it was looking for was that it would be according to the order of the clustered index. So you can rely on that – right?
Wrong. The question was a bad question, and the answer was incorrect. Let’s look at this in action.
Order is never guaranteed to be stable unless you specify a unique ordering using ORDER BY.
Comments closedLukas Eder points out a subtlety of the GROUP BY clause:
SELECT count(*) FROM film GROUP BY ()This will yield:
count | ------| 1000 |What’s the point, you’re asking? Can’t we just omit the
GROUP BYclause? Of course, this will yield the same result:SELECT count(*) FROM filmYet, the two versions of the query are subtly different.
Great post and also shows a case when GROUP BY () isn’t supported.
Kendra Little explains the concept of deferred name resolution in SQL Server:
In this case, I’m creating a temporary stored procedure (out of laziness, it means I don’t have to clean up a quick demo) –
CREATE OR ALTER PROCEDURE #test AS IF 1=0 EXECUTE dbdoesnotexist.dbo.someproc; GOThe database dbdoesnotexist does NOT exist, but I’m still allowed to create the procedure.
When I do so, I get an informational message:
The module ‘#test’ depends on the missing object ‘dbdoesnotexist.dbo.someproc’. The module will still be created; however, it cannot run successfully until the object exists.
This can be useful in some cases where you’ll be querying a table or procedure that may not exist all the time, but which will exist when a certain code block is run.
But, as Kendra points out, deferred name resolution doesn’t work everywhere, so it’s important to know the rules around when it will or will not work.
Comments closedSteve Stedman walks us through a bit of T-SQL proprietary syntax:
|
|
Names have been changed to protect the innocent.
In the above delete statement which table will have rows deleted from it?
A: Table1
B: Table2
C: Both Table1 and Table2
D: Neither Table1 and Table2
Got it in one. I like having this syntax available to me when I need it, even though it’s not ANSI standard.
Comments closedRandolph West shares his thoughts on three functions he’d rather you avoid:
CURRENT_TIMESTAMPis the ANSI-equivalent ofGETDATE(). ANSI is an acronym for the American National Standards Institute, and sometimes vendors will include ANSI functions in their products so they can say that they’re ANSI-compliant (which is not a bad thing, in most cases).There are three main problems with
CURRENT_TIMESTAMP:
- No brackets. It goes against the rules about functions. So much for standards!
- It’s functionally equivalent to
GETDATE(), which usesDATETIME, which we previously identified is old and bad.- It’s too similar to the poorly-named
TIMESTAMPdata type, which has nothing to do with dates and times and should be calledROWVERSION.Bottom line: don’t use
CURRENT_TIMESTAMP.
At one point I used CURRENT_TIMESTAMP over GETDATE() with the thought of portability in mind. Since then, my thoughts on code portability have changed and regardless, as Randolph mentions, it’s better to use DATETIME2 functions to avoid precision issues with DATETIME.
Shane O’Neill covers an annoying but necessary thing to remember around window functions:
Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId.
They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves
Wow! This is great! I can do this with Windows Functions!
They also work for a company that has invested in RedGate’s SQL Prompt so they know that they can rely on SQL Prompt to help iron out any inconsistencies in their script.
So they take the SELECT script above and type in
WHERE… and the auto complete pops up
And that popup isn’t quite accurate… Shane covers this in the guise of a SQL Prompt bug, but it’s a good thing to remember regardless of which tooling you use.
Comments closed