Press "Enter" to skip to content

Category: T-SQL

Comparing INSERT INTO and SELECT * INTO

Haripriya Naidu runs an experiment:

Instead of looking at which option is faster, you may want to look at which option is better suited for a given context. Let’s take a look at 2 common options to insert data and analyze them.

INSERT INTO TARGETTABLE
SELECT * FROM SOURCETABLE

OR

SELECT * INTO TARGETTABLE 
FROM SOURCETABLE 

Click through for a comparison of the two, not just for which is faster but also the pros and cons of each approach.

Comments closed

The WINDOW Clause in SQL Server 2022

Andy Brownsword takes a look at one quality of life improvement in SQL Server 2022:

Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined.

You can do some neat things with the WINDOW clause, including partial matches. For example, you could define a window r1 AS (PARTITION BY x.MyColumn) and then another window r2 AS (r1 ORDER BY x.SomeOtherColumn). I don’t think there are a huge number of scenarios in which this is helpful, especially because performance typically depends upon minimizing the unique number of window functions in your query, but every once in a while it’s a really neat thing.

Comments closed

Finding Object Dependencies in SQL Server

Vlad Drumea is looking for matches:

Figuring out object dependencies in a SQL Server database, especially one you’re not familiar with, can be a challenge.
Luckily, Microsoft provides the sys.sql_expression_dependencies system catalog view to help DBAs and developers track down various object dependencies.

I leveraged this catalog view in a query that returns the necessary info in a more user-friendly way, while also allowing easy filtering for referenced or referencing object.

Click through for the script. It’s been a while since I’ve used this DMV in anger, though my recollection is that I had problems finding dependencies over linked servers.

Comments closed

Using DATETRUNC() in SQL Server

Rajendra Gupta shows off a nice feature in SQL Server 2022:

Suppose you are a data strategist or analyst for an organization. You have been tasked with getting actionable insights from customers who want to track customer patterns at different intervals, such as hourly, daily, or weekly. To do this, you need to use several date functions such as DATEADDDATEDIFFDATEPART, and DATEFROMPARTS to get the required date format.

In SQL Server 2022, this got a lot easier to do using the DATETRUNC function.

Solutions using DATETRUNC() are significantly easier to read and understand than the alternative of combiningDATEADD() and DATEDIFF()

Comments closed

Handling NULL in T-SQL

Chad Callihan deals with non-existent data:

Handling NULL values can be a challenge and may lead to unexpected query results when mixed in with non-NULL values. Thankfully, there are a few SQL Server functions devoted to handling NULL values. Let’s look at two of them. The first, NULLIF, will help you to return a NULL value. The second, ISNULL, will help you with an alternative to a NULL value if you need another value in its place.

For the longest time, I strongly preferred COALESCE() over ISNULL() because of how it can handle multiple scenarios, as well as it being ANSI SQL syntax. But it turns out that, if you do only have two things to compare, ISNULL() is a little bit faster.

Comments closed

Setting TEXTSIZE in SQL server

Steve Jones limits results:

There is a SET command in SQL Server that changes how much data is returned from some fields. This short post shows what I learned about the SET TEXTSIZE command.

I’m pretty sure that, if I’ve ever used this command before, it was by accident. It’s kind of a neat concept in how it can limit the result size of larger blocks of text, but I think it’s too limiting to be a good solution save for some very specific circumstances.

Comments closed

RegEx Performance in Azure SQL DB

Brent Ozar breaks the bad news:

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations.

This post isn’t about their complexity, though. This post is about Azure SQL DB & SQL Server 2025’s regex performance.

Brent’s testing hurts, because I want to use regular expressions, and based on what he’s seen so far, we’re probably still better off using CLR-based regex in SQLSharp.

Comments closed

Linting SQL with SQLFluff

Josephine Bush busts out a linter:

I thought I didn’t care about linting, and lately, I haven’t written a lot of SQL, but for the SQL I do write, I have SQLFluff to help me format it. A friend of mine is big into SQLFluff and finally talked me into installing and using it. For more information about SQLFluff itself, visit here.

Josephine shows off some of the configuration for PostgreSQL’s psql as well as SQL Server’s T-SQL.

Comments closed