SQL

Back To Basics: Service Broker Message Types

The message type is the first of the service broker specific objects.  The message type defines the name of the message, and performs any validation that the service broker needs to do on the contents of the message prior to sending the message.  If the message is being sent to another database (on the same server or a different server) then the message type must exist on both databases.

Validation can be one of four options.

1. None (My personal favorate especially for sample code)
2. Empty
3. Well_Formed_XML
4. Valid_XML With Schema Collection

1 and 2 are pretty straight forward.  None = No validation is done.  Empty = The message must be empty.  Well_Formed_XML requires that the XML be a valid XML document.  The fourth option requires that not only the XML be valid, but that it fits within a pre-existing XML schema collection (an XSD which has been loaded into the SQL Server).

The syntax to create a Message Type is pretty basic.

CREATE MESSAGE TYPE [MessageTypeName]
VALIDATION = NONE

That’s it.  If you need to change the schema use the AUTHORIZATION clause to set the owner of the object.

When using the validation of NONE it is up to the receiving code or application to verify that the data within the message exists and is valid.

Denny

Data Normalization, with storage being so cheap what’s the point?

Back in the old days one of the big reasons that people enforced such strict rules on data normalization was because it would greatly increase the amount of information you could fit on the hard drive.  Back when everything was measured in kilobytes or megabytes storing redundant information simply wasn’t an option.  You had to be able to cram as much information into that little database.

These days space isn’t the issue.  Storage is very cheep these days, and if you have the money you can get absolutely massive amounts of storage.  1 TB hard drives are easily found, and when you start talking about RAIDing drives together the storage limits start to become just insane.  100 TB of storage just isn’t what it used to be.

The problem now becomes that with so much data for the database platform to search through we need to make sure that the indexes which SQL Server is searching as as small as possible so that we can get those indexes read from the disk and into memory as quickly as possible.

Say we have an Employee table which looks like this.

CREATE TABLE Employee
(EmployeeId INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(100),
Manager_First_Name NVARCHAR(50),
Manager_Last_Name NVARCHAR(50),
Location NVARCHAR(50),
Active VARCHAR(5))

Now this table is clearly not setup as a normalized table.  We are assuming that this table has best case indexing.  If we have 1000 employees records and we are going to search the Department column the index must be read from disk.  Now assuming that the department names are nice long bureaucratic names which average 30 characters each we have about 30000 bytes (more if you take the fill factor into account) which needs to be read from the disk.  Now if we had a relational version of the table.

CREATE TABLE Employee
(EmployeeId INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
DepartmentID INT,
ManagerEmployeeID INT,
LocationId INT,
Active BIT)

When we search the Employee table we now are only loading an integer value from the disk into memory. This means that we only have to load 8000 bytes of data from the disk into memory. Now knowing that SQL Server reads data from the disk in 8k blocks even with a 50% fill factor the index only takes 2 blocks on the disk and can then be completed in two read operations to the disk. Now our original table with no fill factor will require 30 data pages (20.297 to be specific), about 60 if we use the same fill factor.

With tables of this size this isn’t much. But as your database grows the number of additional data pages which would have to be read for a simple search of the table grows exponentially.

Denny

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