Tips & Tricks Archives - Page 2 of 2

Microsoft SQL Server

Multi Server Query

Post author:Tomas Lind
Post published:2014/01/16
Post category:Management Studio Tips & Tricks
Post comments:1 Comment

In SQL Server Management Studio, it is possible to execute a multi server query against several servers at the same time. The description from TechNet says most of it:

This topic describes how to query multiple servers at the same time in SQL Server 2012, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group. The results that are returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login that is used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.

Here I’ll show how to create a multi server query from a group under registered servers.

(more…)

Large Inserts and count(*)

Post author:Tomas Lind
Post published:2013/10/17
Post category:Execution Plan Index T-SQL Tips & Tricks
Post comments:0 Comments

If you insert lots of rows into a table with a clustered index, a SELECT COUNT(*) statement on the destination table can initially show 0 (or remain unchanged) for a long time.
This is because the rows needs to be sorted before the actual insert starts.

(more…)

Line numbers in t-sql error messages

Post author:Tomas Lind
Post published:2013/10/15
Post category:Management Studio T-SQL Tips & Tricks Uncategorized
Post comments:0 Comments

When you run scripts in SSMS and get an error, the error references a line number in the t-sql statement that caused the error. If you double click the error, SSMS will take you to the error. This works even if you have multiple statements in your query window. So it can be a really helpful feature. But if the error is in code that isn’t in the query window (for instance in a referenced stored procedure), nothing happens when you double click. In that case it can be good to know how SSMS calculates the line number in error messages.

(more…)

Rowlock can block more than a row

Post author:Tomas Lind
Post published:2013/10/15
Post category:Blocking Concurrency T-SQL Tips & Tricks
Post comments:0 Comments

To avoid the blocking of other users, rowlocks are sometimes used to create locks on the most granular locking level in a table. In theory, when user A makes changes to a row, user B can read, change or delete any other row than that. But in practice, user B can still become blocked by user A altough they are not accessing the same row. It all depends on how data is accessed.

(more…)

Missing comma between columns in select statement

Post author:Tomas Lind
Post published:2013/10/15
Post category:T-SQL Tips & Tricks
Post comments:1 Comment

Hopefully, when you make an typing error while writing a SQL statement, you get the red curly lines under the offending part of the query showing you where the error is. If you execute the following statement, you’ll get an error message:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'FROMTABLE'. — Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘FROMTABLE’.

However, some typos doesn’t create an syntax error. Instead it alters the meaning of the t-sql statement in an perhaps unwanted way. Check out the following example.

(more…)