Intermediate T-SQL

SELECT INTO with IDENTITY

This is a fun one because I’ve seen numerous people struggling with identity columns in temp or freshly generated tables. For example, let’s take a look at the Person.Person table in AdventureWorks: Now, if we just want to make a copy of this table then it’s nice and simple:

That’s it… we’re done. BUT …

SELECT INTO with IDENTITYRead More »

Row Level Security Example

This follows on from the last post in which we discussed Row Level Security.  This post provides a nice and simple example based on the example proffered in the previous blog post. We have the following query:

What we now want is to allocate managers to some of the staff and seamlessly, behind the …

Row Level Security ExampleRead More »

Using In-Memory Temp Tables

Following on from the few posts I’ve written about in-memory tables I wanted to mention this useful tip you can use in order to potentially speed up your temp tables whether that be within stored procedures or maybe within an ETL process. Let’s look at the example of a stored procedure:

Now, let’s run …

Using In-Memory Temp TablesRead More »

Which Table or Index is Hogging the Buffer Pool?

In the last post I showed how we can easily tell which database is hogging our precious resources but, although that’s useful in helping us narrow down rogue processes, it would be a lot more helpful to be able to go a step further, picking out the exact table or index. Therefore I’ve written some …

Which Table or Index is Hogging the Buffer Pool?Read More »

Scroll to Top