SQL

Back To Basics: The SQL Server Service Broker

The SQL Server Service Broker is a fantastic new addition to SQL Server.  For those who have used Microsoft Message Queue (MSMQ) the service broker will be easy to understand as it’s the same basic concept.  Messages are sent from one place to another, within a database, from database to database, or from server to server.

 While the configuration can be a daunting task, once the service broker is setup it is a very solid system which can handle a large message load.

The Service Broker give you guaranteed delivery order, with single processing of messages always in the order received.  Messages can be processed on demand (you write software which queries the service broker on a schedule) or automatically via the queue activation.

Check back for information about all the various service broker object an how to configure each of them.

Denny

Checking free disk space from T/SQL

I’ve seen some very creative ways to see how much free disk space SQL Servers have.  Most make use of xp_cmdshell which some very complex dos commands or vbscripts.

However there is a much easier way.  Microsoft has included the system extended stored procedure xp_fixeddrives.  It returns the all the fixed drives and the amount of free space in megs.

Denny

My ERRORLOG shows some spids have an ’s’ after them. What’s going on?

That “s” is new in SQL Server 2005.  It means that the SPID is a system process.  For example my database is having a problem with tempdb running out of space for no reason.  When I look in the log I see that SPID 118s is the offending SPID.  When I watch SPID 118 using sp_who3 I see that the output looks a little strange.  I see a lot of CPU and disk load and that the thread and been logged in for ever using the sa account but with no hostname, host process, or mac address listed.  This is because this is the service broker process which is what runs the activated procedures.  Starting in SQL Server 2005 system processes no longer need to run with SPIDs less than 50.  Because of this, Microsoft has decided to make live easier when trying to identify the system processes by putting the s after the SPID number in the logs.

This same information appears to hold true for SQL Sever 2008 (so far at least).

Denny

Back To Basics: The DELETE Statement

When you have data in your table that you need to remove the DELETE statement is the way to do that.  Using the DELETE statement without any WHERE clause will remove all the data from your table.  The WHERE clause works the same way as the WHERE clause for SELECT and UPDATE statements.

DELETE FROM Employee
WHERE EmployeeId = 4

You can also JOIN to a table when you delete data from a table which allows you to use the second table within the filter.  This requires a slightly different syntax than your normal DELETE syntax.

DELETE e
FROM Employee e
JOIN Department ON Employee.DepartmentId = Department.DepartmentId
  AND Department.DepartmentName = ‘Sales’

As you can see you need to set an alias for the table you are deleting from, and put that alias between the DELETE and FROM words.

Denny

P.S. Sorry this didn’t come up sooner, I could have sworn that I wrote it and published it back when I started the series.

Back To Basics: How do I use sp_change_users_login?

The sp_change_users_login procedure has a specific purpose.  It’s used to identify and correct users within a database which do not have a corresponding logins.

You can specify the value of Report for the @Action input parameter to see any users which do not have a corresponding login.  This only needs to be done when you are restoring a database from one server to another, and the logins on each server were created with seperate SIDs.  An example of when this would happen is when you are restoring a database from Production to QA.

After identifying the users which are not synced to a login, use the update_one value for the @Action parameter and with the username of the user as the value for the @UserNamePattern input parameter, and the login name as the value of the @LoginName input parameter.  If the login doesn’t exist you can pass the password to the procedure for the @Password input parameter and the login will be created for you and mapped to the user.

Denny

Back To Basics: Functions, the good and the bad

Functions are create little blocks of code.  They are fantastic for converting data from one format to another, or for looking up other values based on a lookup.  However this comes at a price.  That price is CPU power.  Doing all these additional lookups can cause extra strain on the database server.  It’s often better for the database server to simply join to the table which you are doing the lookup against.  However you can guarantee the same lookup is done every time when a function is used.

There are two kinds of functions.  One is a scalar function and one is a table function.  A scalar function is used as a column of a select statement, or a value in an update statement.  A table function is used in place of a table, and can be called directly or as a member of a JOIN.  While functions are fairly simple in concept, they are in my opinion the most complex objects to create.

 A scalar function accepts one or more input parameters and returns a single value of a predetermined data type.  The CREATE FUNCTION command is used to create both scalar and table functions.  To create a scalar function we use code along these lines.

CREATE FUNCTION dbo.FindObjectId
(
  @ObjectName nvarchar(512)
)
RETURNS INT
AS
BEGIN
  DECLARE @ObjectId INT
  SELECT @ObjectId = ObjectId
  FROM sysobjects
  WHERE name = @ObjectName
END
GO

We use this function to return the object id of an object in the database (yes I’m aware that there are system functions to do this, but it’s an easy function to use as an example).

SELECT dbo.FindObjectId('sysobjects')

As we can see a single value is returned in a record set. We can also return more than one value in the record set by using the function against a table.

SELECT dbo.FindObjectId(name)
FROM sysobjects

Creating a table function using much the same syntax. In this case we will return all the object names which have an object type which matches our input parameter.


CREATE FUNCTION dbo.ShowObjects
(
  @ObjectType CHAR(2)
)
RETURNS @TableNames TABLE (name nvarchar(512))
AS
BEGIN
  INSERT INTO @TableNames
  (name)
  SELECT name
  FROM sysobjects
  WHERE xtype = @ObjectTypeRETURN
END
GOTo use this function we use a basic select statement.SELECT *
FROM dbo.ShowObjects(’U')

We can also use it as a JOIN member.


SELECT sysobjects.name, a.name
FROM sysobjects
JOIN dbo.ShowObjects(’U') a ON sysobjects.name = a.name


If you want to get very complex you can use what’s called an inline table function or single statement table function. This is basically a regular table function, but you can only put a single select command within the table function. As you can see from the code below the results will be the same, but the single line table function is a bit shorter. Because we are not declaring a table variable and loading the data into that table variable, then reading from that table variable a single statement table function should have a slightly cheaper execution plan compared to the same SELECT statement within a multi-statement table function.


CREATE FUNCTION dbo.ShowObjects
(
  @ObjectType CHAR(2)
)
RETURNS TABLE
AS
BEGIN
  RETURN (SELECT name
  FROM sysobjects
  WHERE xtype = @ObjectType)
END
GO


Denny

Back To Basics: Stored Procedures, the work horse of the database

Stored procedures are extremely useful objects.  Not only do they store T/SQL scripts for later execution, but they also provide us with an extremely important security barrier between the user interface and the database.  The security barrier is used to prevent the users from needing SELECT, INSERT, UPDATE and/or DELETE rights directly to the database tables and views. 

This is done through what is called permissions chaining.  When a user has rights to execute a stored procedure they are given temporary rights to use the table objects within the procedures which are used by the table.

Creating stored procedures is very easy.  Take your Transact SQL code and put it below the CREATE PROCEDURE command, and end the batch.  Like all other database objects the name of the stored procedure must be unique within the schema (or owner for SQL 2000 and below).  As an example lets create a stored procedure which returns the names of all the tables in the current database.

CREATE PROCEDURE ShowTables AS
SELECT schema_name(schema_id), name
FROM sys.tables
GO

As you can see the basic syntax is very simple.  To run this stored procedure we simply run the stored procedure name.

exec ShowTables

You can add in input parameters to handle filtering, or which would need to be inserted into a table.  An input parameter is simply a variable which you set when you run the procedure.  You can access the value of the input parameter within the stored procedure as you would any other variable.  Let’s look at the same procedure but this time we want to filter the tables by the first letter.

CREATE PROCEDURE ShowTables
  @FilterChar NVARCHAR(2)
AS
SET @FilterChar = @FilterChar + ‘%’
SELECT schema_name(schema_id), name
FROM sys.tables
WHERE name LIKE @FilterChar
GO

In this example as you can see we take the input parameter, and add the % wild card, then use the variable to filter down the records to see only the records which start with the character we supply.  Running the stored procedure with an input parameter is just as easy.

exec ShowTables @FilterChar=N'C'

We can also use output parameters to get values back from the stored procedures.  Output parameters are used basically in the same way that input parameters are, however you add the OUTPUT keyword after the parameter.  Within the stored procedure simply set the output variable to the value you want it to return to the calling code.  This can be done anywhere within the stored procedure, as long as the variable still holds the value when the stored procedure has completed it’s execution the value will be returned to the calling code.  First lets look at the code to create the stored procedure.

 CREATE PROCEDURE ShowTables
  @FilterChar NVARCHAR(2),
  @RowCount INT OUTPUT
AS
SET @FilterChar = @FilterChar + ‘%’
SELECT schema_name(schema_id), name
FROM sys.tables
WHERE name LIKE @FilterChar

SET @RowCount = @@ROWCOUNT
GO

We run the stored procedure in much the same way we do with the input parameter.

DECLARE @RowCount INT
exec ShowTables @FilterChar=N’C', @RowCount=@RowCount OUTPUT
SELECT @RowCount 

In this case we are simply returning the row count as a second record set, but you’ll get the basic idea.

As I wrote earlier you can add records to a table with the stored procedure.

CREATE PROCEDURE InsertTable
  @Id INT,
  @Value VARCHAR(20)
ASINSERT INTO SomeTable
(Id, Value)
VALUE
(@Id, @Value)
GO

As you can see it’s a very basic method.  It’s a regular insert statement with the parameters passed to it.

I know that this was a bit longer than the other posts, but I hope that you found it worth while.

Denny

Back To Basics: Views, what exactly are they?

Views are strange objects.  They look like tables, can be queried like a table, but they don’t store any actual data.  Think of them as a virtual table which has pointers back to the source tables.  Views can combine data from one or more tables via joins just like a select statement.

Using views does not help or hurt your query performance.  However if you create an index on your view, it will help your performance, however it will increase your storage as SQL will now need to keep the data for the columns of the index from the view on the disk within the index.

Personally I’m not a fan of views, as it distorts the schema of the database.  However like all objects within the database there is a time and a place for thier use.

Denny

Back To Basics: Tables, without them we have nothing to do

Tables are the core of any database platform.  Without tables we would be able to process data, but we would have no way to store the data.  In the simplest terms tables look like Excel sheets.  They both have columns and rows.  When you view a table in the client tools it looks much like an Excel sheet does.  While the basic concept is the same tables are very, very different that Excel sheets.  SQL Server is optomized to store large quanties and data and search through that data as quickly as possible.

Unlike Excel, tables have indexes.  Indexes are copies of the column which comprise the index.  The reason that we use indexes is to speed up the processing of the query.  Whle the index is a copy of the column, the copy within the index in sorted in order while the table is not.  Because the data is sorted SQL Server can search through the data much easier and there for faster.

To make this easier to understand think of an Excel sheet with 1000 rows of data in it.  Each row has a single value in column A.  Each value is a random number between 1 and 1000.  Now try to find all the rows with the value of 25.  You need to search down the column looking for the data.  This is how your table work.  When you tell SQL Server to search the table for the value of 25 using this statement SQL has to look at every record in the table.

SELECT *
FROM Table
WHERE Column1 = 25

Now when you create an index on this column think of sorting the Excel sheet in order.  Now find the records with the value of 25.  You can simply scan down the column and find the records and not look any further.  This is the same thing that SQL Server does when you us the same command but with the index.  SQL Server uses the index automatically, so no changes to code are required when you create the index.

Denny

SQL Server 2008 changes the way that CONVERT/CAST works

Normally when running a query against a table and using a CAST or CONVERT function against a datetime field any index is made useless.  In SQL Server 2008 this problem is fixed.  Microsoft has come up with a way for SELECT statements which use CAST or CONVERT against a column of the datetime datatype to continue to use the index.  Now keep in mind that this only works for the datetime datatypes and not other data types.

I believe that this feature showed up in CTP 5 (November).

Denny