Sql-Server

I’ve done a lot of Microsoft certification exams over the years. Most of those have been SQL Server exams. The quality of the questions in those exams though, varies enormously.

One of the exams that really drove me crazy a while back was one of the intro-level ones. The problem was with the number of errors in the exam. When the question-writer gives you five substantial blocks of T-SQL, and asks which one is correct, yet they are all actually wrong in some way, that makes it a very, very tough question to answer. I find these types of exams very tiring because I’m endlessly trying to second guess what the question writer actually meant to ask. Some exams have a large number of these.

2018-10-22

This is probably one of the simplest tips that I’ve published, yet I’m endlessly surprised how many people do not realize that it’s available.

When you have a script loaded in SQL Server Management Studio (SSMS), and you execute the script, you might run into an error like this:

To find where the error is, just double-click the error down in the Messages tab. I double-clicked it, and it took me directly to the error and highlighted it:

2018-10-18

In SQL Server Management Studio, there’s no great way to list all the foreign keys that are defined in a database.

In our free SDU Tools for developers and DBAs,  we added a function ListForeignKeys to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

2018-10-17

SDU Tools is a toolkit that’s simple to use, has many useful functions, procedures, and views, and is updated regularly. Even if you aren’t using the tools as a set, they provide wonderful examples of how to do things in T-SQL. We have released version 12.0 of these popular tools.

The tools are free tools for DBAS and Developers. Tools for comparing databases, tables, finding unused indexes, manipulating strings, performance tuning converting data, and so much more.

2018-10-15

When you right-click a table in SQL Server Management Studio, you get options for selecting or editing but the number of rows is limited:

Those values can be changed. By default, these numbers are both 200, but I’ve decided to change the default number of rows selected to 1000.

In Tools, Options, SQL Server Object Explorer, then Commands, you can set the values to whatever suits you:

I don’t tend to ever use the Edit option but I’d suggest not making it too large.

2018-10-11

XACT_ABORT is one of the least well understood options that you can configure in a SQL Server session. Yet it’s very important. XACT_ABORT makes statement-terminating errors become batch-terminating errors. Without it, even within a transaction, many errors only terminate the statement that they occur in, and control passes to the next statement within the transaction, not out of the transaction.

In nearly every stored procedure that I write, the template includes the following lines:

2018-10-10

Do you need to understand SQL Server indexing in detail? Do you know someone else who needs to? Either way, our new SQL Server Indexing for Developers course could be just what you’re looking for.

It’s detailed. It has a full set of practical exercises (hands-on labs) with downloadable test data and scripts.

The best part is it’s on sale until October 31st for $195 USD as an introductory special.

2018-10-08

Most people use SQL Server Management Studio (SSMS) to edit SQL queries. No big surprise there. The files will have a file type of .sql.

But what many people don’t understand is that SSMS inherits many of its underlying Visual Studio’s abilities to edit other document types.

For example, if you open a .txt text file, you can edit it just fine, and you can also include files like this in SSMS script projects. That can be useful for additional notes and documentation.

2018-10-04

I often need to write scripts that interact with SQL Server Agent jobs. One thing that I regularly need to know is if a particular job is currently running.

In our free SDU Tools for developers and DBAs,  we added a function IsJobRunning to work that out.

You can see the outcome in the main image above.

You can see it in action here:

2018-10-03

Simple answer: No

SQL Server has a concept of boolean data type but it doesn’t have an actual boolean data type. I can’t declare a column with a boolean data type, and I can’t use it that way. However, the concept of boolean data is present.

For example, if I write the following:

SELECT * FROM Sometable WHERE Somecolumn = Someothercolumn

the outcome of that comparison is TRUE, FALSE, or (don’t forget) NULL. And remember that NULL isn’t a value; it’s the lack of a value. WHERE clauses are satisfied when the value of the predicate is TRUE.

2018-10-01