SQL Server with Mr. Denny


April 11, 2008  3:00 PM

Back To Basics: How do I use sp_change_users_login?

Denny Cherry Denny Cherry Profile: Denny Cherry

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

April 11, 2008  12:00 PM

Back To Basics: Logins and Users, what’s the difference?

Denny Cherry Denny Cherry Profile: Denny Cherry

Usually Logins and Users are words which are interchangeable with each other.  However in Microsoft SQL Server they are very different things.  Because everyone assumes that they are the same thing, it can get a little confusing.

Logins are created at the database server instance level, while uses are created at the database level.  In other words logins are used to allow a user to connect to the SQL service (also called an instance).  You can have a login defined without having access to any databases on the server.  In this case you would have a login, but no users defined.  The user is created within the database and when it’s created is mapped to a login (users can be created without mapping them to a login, but we’ll talk about that at some point in the future).  This mapping is what allows the person connecting to the instance to use resources within the database.

If the login was created directly within the database, each database would have to keep track of the usernames and passwords of everyone who needed access to the database, which would cause a security nightmare.  Using the login in each database idea, lets create a login in each database called user1.  We set the password for user1 the same on all the databases on the server.  We then backup the database, change the password for that user on all the databases, then restore the database.  We now have an out of sync password for a single database on the server.

 Because of this mapping between logins and users, if you create a SQL Login on your server and grant it rights to a database via a user then backup the database, and restore the database to another server after creating a login on the second server with the same name.  You would think that the login would have access to the database.  However you would be wrong.  This is because the SID of the login and the user are different.  You have to use the sp_change_users_login procedure to sync the user with the login.

Denny


April 11, 2008  5:00 AM

Back To Basics: Functions, the good and the bad

Denny Cherry Denny Cherry Profile: Denny Cherry

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 = @ObjectType
RETURN
END
GO
To 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


April 10, 2008  8:00 PM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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)
AS
INSERT 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


April 10, 2008  3:00 PM

Back To Basics: Views, what exactly are they?

Denny Cherry Denny Cherry Profile: Denny Cherry

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


April 10, 2008  10:00 AM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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


April 7, 2008  11:00 AM

SQL Server 2008 changes the way that CONVERT/CAST works

Denny Cherry Denny Cherry Profile: Denny Cherry

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


April 3, 2008  11:00 AM

New INSERT syntax in SQL Server 2008

Denny Cherry Denny Cherry Profile: Denny Cherry

One of the very cool new feature which SQL Server 2008 gives us is an change to the INSERT statement.  Now you can specify multiple rows to insert into a table from a single insert command.

The syntax is:
CREATE TABLE TableName (Column1 INT, Column2 VARCHAR(10))
INSERT INTO TableName
(Column1, Column2)
VALUES
(1, 'test1'), (2, 'test2'), (3, 'test4')

I see this as being a very handy especially when doing an initial data load into a table as you can now load lots of data without having to run a lot of seperate insert statements.

Denny


April 1, 2008  6:43 PM

New Article: Tips for scheduling and testing SQL Server backups

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve just published a new tip on SearchSQLServer.com called Tips for scheduling and testing SQL Server backups.  In it I’m talking about server backups including how to schedule those backups on SQL Server Express edition which doesn’t have the SQL Server Agent.

Denny


March 31, 2008  10:00 AM

Back To Basics: The UPDATE Statement

Denny Cherry Denny Cherry Profile: Denny Cherry

After you’ve inserted the data into the table, it’s time to update the data.  We do this by using the UPDATE statement.  The update statment can be used in two ways.  The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.

UPDATE TableName
SET Column1 = 'Value'
WHERE AnotherColumn = 'AnotherValue'

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = AnotherTable.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = ThirdTable.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: