So over the last couple of posts I’ve talked about the fact that the ROLLBACK command will roll back an entire transaction no matter how many layers down the ROLLBACK is executed. Well this has an interesting implication with a stored procedure. If a ROLLBACK command is issued inside of… Read more
Happy New Years! It’s the first day of the year and it’s a day known for setting goals. I had several goals last year and did pretty well over all. At least in my opinion which is really the only one that counts for this particular type of thing.
First… Read more
On the first day after release my developer gave to me
- a performance problem on a crucial query
On the second day after release my developer gave to me
- 2 deadlocked queries
- and a performance problem on a crucial query
On the third day after release my developer… Read more
I went and voted for #tribalawards and when I was finished they offer you links to 6 different free PDFs. Not sure if they are the same for everyone but for me it included Grant Fritchey’s SQL Server Execution Plans 2nd Edition which I highly recommend. It also included a… Read more
In my previous post I mentioned the fact that the ROLLBACK command rolls back the entire transaction all the way to the top level. If that is the case then can we roll back an inner transaction and still maintain and commit the rest of the transaction? Yes as… Read more
Transactions are great and wonderful things. They make sure that our work stays atomic, consistent, isolated and durable (yes ACID). And if we use an explicit transaction (created by BEGIN TRANSACTION) and we make a mistake we can always roll the transaction back. Unfortunately rolling back a transaction and committing… Read more
It’s T-SQL Tuesday again and this time it’s being hosted by the SQL Soldier. He’s picked the subject of Waits. As in, why am I having to wait for my query to finish. Or more specifically what is my query waiting for. Waits are a popular subject. You can… Read more
I was reading a rather interesting post on stackexchange “Why is % a forbidden char in a THROW message?” (which is an interesting read in and of itself) and noticed something interesting in the OP’s example.
FORMATMESSAGE('Procedure input %s is wrong', @ProcParam)
That’s pretty cool. I remember using… Read more
A while back I talked about the DEFAULT keyword and using it to tell SQL to use the default value without having to specify an actual value. Well along that same note, what do you do if you want all of the columns to use their defaults? Or if you… Read more
Well the short answer is that they just don’t have enough information to give a definitive answer. You will notice that “it depends” is almost always followed by a series of questions. This is very similar to the Chaos theory that as I understand it basically says that if you… Read more
DTS is dead, long live SSIS. Way back when SQL 2005 was announced then released DTS was pronounced DOA. SSIS was the wave of the future. Everyone was so thrilled that posts like SSIS’ 15 faults and Why I hate SSIS: Part N+1 were all over the place, and I… Read more
You know I occasionally find it interesting how I learn new things. I was running through a practice test for the 70-461 and one of the questions had an odd command as one of the possible answers.
SET FMTONLY ON
Not something I had ever seen before, and as I… Read more
Beginning of period calculations are common things. Rolling month reports, queries to pull everything from last month etc require beginning of period calculations. Well here is a very simple pattern to get them.
DECLARE @DateConst datetime SET @DateConst = '1/1/2000' SELECT DATEADD(mm, DATEDIFF(mm, @DateConst, DateCol), @DateConst) FROM TableName
Dissecting it… Read more
When connecting to a SQL Server instance in SSMS there are a fair number of options. Most DBAs I know have only ever looked beyond the basics so I thought I would mention a few of the others.
First open a connection window in SSMS: BOL
This is what most… Read more
Every morning we have a daily meeting and my manager asks who’s on call. And every morning some form of the following pops into my head.
The new IT manager (Lou) walks into the DBA manager’s office (Bud).
Lou: Hey Bud I’m trying to get a feel for what’s going… Read more
I recently attended SQL Saturday #255 and specifically the performance tuning pre-con put on by Grant Fritchey. It was a great seminar and well worth the time (a very long day including travel) and money (not much). One of the many interesting discussions was on the missing index DMVs.… Read more
For those who don’t know the three physical join operators are Hash Match, Loop Join and Nested Loops. Understanding these operators is an important part of query tuning and understanding query plans. I had the privilege to watch Ami Levin do his presentation on physical join operators at SQL Saturday… Read more
These are a couple of stored procedures I wrote to help me with security research. Each sp returns three data sets.
- A list of principals and some basic properties about them.
- Role membership
- Object/Database/Server level permissions
Each row of each dataset has not only the appropriate properties but a… Read more
There are lots of ways to learn new things, or be reminded of old things. One of my favorites is the QotD on sql server central. Recently there was an interesting question on defaults. It pointed out that there is a difference between adding a column with a… Read more
It’s one of those things that always aggravated me but not really enough to complain about. When you run a query to grid in SSMS then copy and paste the value to a query window it stripped the carriage return/line feed.
-- Setup code CREATE TABLE GridTest (String varchar(50)) GO…