You can put pretty much any character you want into an object or schema name by enclosing the name in ‘s. This does not however mean that you should. There are two specific cases that I’ve seen that are in general a bad idea.
I’ve seen names that actually have… Read more
SQL Fiddle is a free website that you can use to demonstrate and save a query example in any one of 13 different DMBSs (as of this posting) including two different versions of SQL Server (2008 & 2012).
First you set up your work environment and “Build Schema”.
Then… Read more
Warnings up front, this has some serious security implications. The method I’m going to use minimizes that somewhat but it’s really easy to shoot yourself in the foot here, so be careful!
Impersonation allows you to grant a user the ability to mimic another user and gain access to all… Read more
I recently saw an answer to this question on dba.stackexchange.com written by Martin Smith. It was probably one of the most complete answers to this question I have ever seen. In fact it’s probably one of the most complete answers possible. I highly recommend that you read it. In… Read more
I feel like an old man opening a computer for the first time but I’ve finally signed up on twitter. @sqlstudent144
Howdy #sqlfamily. Say hi if you get a chance!
Filed under: SQLServerPedia Syndication, Uncategorized
Query plans are an essential tool when doing performance tuning. When looking at a query plan you should be aware that there are two different types of query plans. There are Estimated and Actual query plans (also called Execution Plans). Estimated and Actual query plans have the following differences:
An… Read more
Not true. (Or I guess probably wouldn’t be posting about it would I?)
Probably the first thing I should point out is that just because you can doesn’t mean you should. I can only think of a few very edge cases where an index on just a bit column would… Read more
It’s always a gamble when buying a new piece of software. You pay your money, you throw the dice and you hope that you didn’t just buy a POS (Piece Of non-functioning Software). However there are… Read more
A little while back I was doing some research into a failed job and ran into a slight problem. The Agent history settings were such that I was only seeing the last 2-3 runs of the job. This job is run “on demand” and I really wanted to see the… Read more
Transactions are great tools that every DBA and developer should learn how to use. Unfortunately not everything can be put inside a transaction. There are a handful of commands that won’t work inside a transaction. CREATE, ALTER and DROP DATABASE for example. The full list of commands can be… Read more
This is a disaster and recovery trick I’ve found to be useful for developers with batch processes that hit multiple databases. If you have read up much on either the BEGIN TRANSACTION or RESTORE statements you will probably have noticed the MARK option. If you mark a transaction in the… Read more
I frequently rely on joining sys.dm_exec_requests and sys.dm_exec_sql_text() to know what queries are running on a system and when I have a blocking situation I like to look and see what query is running that is blocking everything else. I’ve mentioned recently that you can also use sys.dm_exec_connections.most_recent_sql_handle to see… Read more
I probably had the most fun all week when a query I was running came up blocked. Sounds strange right? Well the blocking_session_id was a negative 2 (-2)! I’ve never seen anything like it before. Once I had resolved my problem (see below). I started doing some research on negative… Read more
While researching my last post I ran across an interesting column I hadn’t noticed before, sys.dm_exec_connections.most_recent_sql_handle. I mentioned it in my previous post but I felt it was interesting enough that I would point it out specifically. Here is the BOL definition:
The SQL handle of the last request…
Recently we had a scenario where we had a handful of queries being blocked. Nothing unusual there but when I looked into sys.dm_exec_requests I could see all of the blocked requests, but could not find a request with a session_id matching the blocking_session_id. The session showed up in sys.dm_exec_sessions but… 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 set… Read more
I came across an interesting question on SE last week. Guid vs INT – Which is better as a primary key? In addition to the quite good accepted answer I thought I would throw in my own take.
- GUIDs are 16 bytes and hold more values you then could…
I’ve done a couple of posts now talking about how rolling back a transaction works. I thought this time I would back up a bit and talk about what exactly a transaction is and why we have them. A transaction is simply a unit of work. A unit of work… Read more
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