Browse by Tags

All Tags » TSQL (RSS)

SQL 2016 Technical Deep Dive Video – SQL Engine and TSQL

This is the set of videos from the SQL Server Engineering Team on SQL Server 2016. This post is covering The SQL Engine, TSQL and High Availability . For the other groups of videos have a look at. Customer Stories And End To End Implementations Azure...

Performance Tuning and Query Optimisation–SQLBits Training Day

I will be doing a training day at SQLbits in April on Performance Tuning and Query Optimisation. This is the outline for the day. Its going to be an intense day, I look forward to seeing you there. To register go to http://www. sqlbits .com/information...

SQL Server Denali – Whats new for TSQL?

This is a placeholder for content about what’s new in SQL Server vNext aka Denali. I will be updating this as more content is published.
Posted by simonsabin | with no comments

What line does the error line number refer to?

You’ve got an error and it gives you a line number Msg 50000, Level 16, State 1, Procedure TestToSeeWhatTheErrorLineNumberRefersTo, Line 14 Some Error What line does that actually refer to? Well its the line in the original batch that compiled the procedure...
Posted by simonsabin | 2 comment(s)
Filed under: ,

Avoid SQL Injection with Parameters

The best way to avoid SQL Injection is with parameters. With parameters you can’t get SQL Injection. You only get SQL Injection where you are building a SQL statement by concatenating your parameter values in with your SQL statement. Annoyingly many TSQL...

Something for the weekend - Whats the most complex query?

When ever I teach about SQL Server performance tuning I try can get across the message that there is no such think as a table. Does that sound odd, well it isn't, trust me. Rather than tables you need to consider structures. You have

1. Heaps

2. Indexes (b-trees)

Some people split indexes in two, clustered and non-clustered, this I feel confuses the situation as people associate clustered indexes with sorting, but don't associate non clustered indexes with sorting, this is wrong. Clustered and non-clustered indexes are the same b-tree structure(an even more so with SQL 2005) with the leaf pages sorted in a linked list according to the keys of the index.. The difference is that non clustered indexes include in its structure either, the clustered key(s), or the row identifier for the for the row in the table (see http://sqlblog.com/blogs/kalen_delaney/archive/2008/03/16/nonclustered-index-keys.aspx for more details). Beyond that they are the same, they have key columns which are stored on the root and intemediary pages, and included columns which are on the leaf level.

The reason this is important is that this is how the optimiser sees the world, this means it can use any of these structures to resolve your query. Even if your query only accesses one table, the optimiser can access multiple structures to get your results. One commonly sees this with a non-clustered index scan and then a key lookup (clustered index seek), but importantly its not restricted to just using one non-clustered index and the clustered index or heap, and thats the challenge for the weekend.

So the challenge for the weekend is to produce the most complex single table query.

For those clever bods amongst you that are thinking, great I will just use lots of xquery functions, sorry these are the rules...

...

Accessing system views in global stored procedures

Having utility functions that do helpful stuff is great, just look at sp_who2, sp_help, sp_helpindex as examples. What if you want to write your own. Well you can you just stick the procedure in master and prefix it with sp. You can now call the procedure...
Posted by simonsabin | 2 comment(s)
Filed under: ,

User defined function performance is awful, Micorosft please sort this out

User defined functions looked like something great when they were introduced in SQL 2005. They are a very logical step to make more code reuseable by wrapping common code in functions. It is an approach that is in development 101 and something everyone does and looks to doing.

So what is the problem with SQL Server.

Well the issue is that ......

...

TSQL Challenge - Remove duplicates from a string

I have a table where a coloumn contains the ordering of some preferences. Each preference is represented by a letter ,i.e. AYFT represents preferences A, Y, F and T in that order. All 26 characters are possible preferences. A preference can only appear...

Enforcing parent child relationship with Path Hierarchy model

With the classic adjacency model its dead easy to validate that a parent exists, its a bit more difficult with path model. The adjacency model you have a table with a self join you can easily add a foreign key between the two columns. When you store a...
More Posts Next page »