A primary key is simply a candidate key labelled as the ‘primary’ (see more in A Key to Keys), and they are enforced by the same mechanism in Sql Server – the unique index. We can query the system tables to get a quick list of these.
Sql Server
Numeric and Weighted Distribution Queries
Spreading values across rows (or subsets of rows within a set of rows) was once cumbersome and difficult, but the invention of window functions (which have been around for a while now) have made it much easier. Here are some examples you can take and tweak to fit your needs. (more…)
5 Great Articles on Data – Oct. 2015
The Unbearable Lightness of Data
Have you ever tried explaining your job to family or friends and seen their eyes glaze over? Will Thrash explains it better than we did. In the first 15 minutes or so of this video he talks about how he first got interested in data at a young age, and why it still holds his interest to this day. You can tell he gets why we do what we do, and why we all love it. (more…)
View a Query Plan Without Running the Query
Viewing execution plans is useful for troubleshooting queries and stored procedures. But sometimes the query or stored procedure takes a long time to run, or you cannot run it yourself because it modifies data.
You can view these plans by pulling them from the plan cache. SQL Server provides a way to do this:
UPDATE 7/18/2016 – When I first wrote this, I thought that this would show the actual execution plans. I have since learned that this shows the estimated execution plans. Hopefully some readers may still find it useful.
(more…)
Query for Queries
This is a query to view all current activity on the database server (assuming you have the proper permissions). You can see who is connected, if they have any open transactions, if they are blocked or blocking, and what exactly they are executing. (more…)
How to Prevent Overlapping Date Ranges in a Table
Have you ever seen a table where each row had a ‘start date’ and an ‘end date’? I’m sure most of us have seen one or created one.
These tables tend to pose a key problem (no pun intended) if the following business logic applies: “If two or more rows represent different date ranges for a single item, the ranges cannot overlap.” In other words, the date range must be unique from the start date until the end date for any particular item represented by the table. (more…)
