I was playing around with instead of triggers to solve a problem with two tables being treated as one, and I came upon the following solution for making a view that behaves almost exactly like a single table.

 

For example, I will use the following tables, both sharing the same primary key:

 

 

Why would you do this?  I could see various reasons, none too common.  You might want to attach your table to a third party table.  For example, the tables needn''t be in the same database.  Or one of the tables might have a couple of varchar(max) values which are rarely used.  It might be valuable to keep one of the tables small.  (I was just answering a newsgroup message a while back, and the reader wanted to do something like this, so I popped this solution together.

 

First, create a view that FULL OUTER joins the tables. A row does not need to exist on both sides to use this view. The FULL OUTER gets all rows from both tables, where there is a match.

 

Now, I create an instead of trigger to insert the rows.  If you insert a duplicate in either side it will fail:

 

 

Not insert data, noting that you treat the primary key as if it is just a single column.

 

Then viewing the data:

 

It looks just like a normal table:

 

Then you can create an update trigger to manage changes.  It is more tricky, because you have to consider that one of the objects may or may not exist:

 

 

 

 

Then you can see the changes here (including the fact that no changes were affected by the statement with the non-existant tableNameId value.

 

 

This returns:

 

 

Finally, we implement the delete:

 

Then test it out:

 

 

Check that:

 

 

 

Now delete the rest:

 

 

Is it useful?  Only if it is...  Which is pretty much why I write stuff like this.  I find that if I have thought of it, sooner or later it will come in handy.  And I haven't thought of much, that's for sure.

When writing a trigger, it is important to consider the case where no rows are modified by the statement involved.  Furthermore, knowing how many rows are affected can be useful in other circumstances as well.  In some validation routines, I will count the number of valid rows and the number of null rows and sum them up to see if they match the number of total rows affected by the trigger (a trick I learned at least from ERwin macros over the years.

There are a couple of ways to get the number of rows affected, the most obvious being to count the number of rows in the inserted or deleted table (inserted for insert triggers, deleted for delete triggers, and either for an update trigger.  I rarely advocate having a trigger do more than one action, to avoid any code that does one action on a DELETE, and another on an UPDATE, and yet another on an INSERT.  It isn't a hard and fast rule by any means (and one that I sometimes break when I am building code by hand and not code generators :)

Anyhow, back to rowcount.  The easy way to do this is to use the @@rowcount system function (or sometimes thought of as a global variable, thought it is really a function.)  The important thing to make sure is that no code that can change the value of @@rowcount is executed before fetching the value of @@rowcount (which will also change the value :)  Pretty much the only thing you can do is declare a variable to hold the value.

So, consider the following table:

create table test
(
    testId  int primary key
)

And the following trigger, that simply takes the value, puts it in a varariable and the selects that value as a result set (not something typically done in a trigger, but I am doing it here for demonstration purposes.)

create trigger testRowcount
on test
after insert
as
  declare @rowcount int
  set @rowcount = @@rowcount

  select @rowcount

Now, enter a row:

insert into test(testId)
select 1

returns:

-----------
1

(1 row(s) affected)

(1 row(s) affected)

 

Or for two rows:

insert into test(testId)
select 2
union all
select 3

-----------
2

(1 row(s) affected)

(2 row(s) affected)

But, you think, I want to turn off those pesky row(s) affected messages for the trigger, so you make the following change:

alter trigger testRowcount
on test
after insert
as
  set nocount on

  declare @rowcount int
  set @rowcount = @@rowcount

  select @rowcount

And try to enter the data again:

insert into test(testId)
select 4
union all
select 5

Returns:

-----------
0

(2 row(s) affected)

This turned off the message alright, but now the value of @rowcount is 0.  Turns out, SET commands affect the rowcount, so you change it to:

alter trigger testRowcount
on test
after insert
as
  declare @rowcount int
  set @rowcount = @@rowcount

  set nocount on

  select @rowcount

Executing this:

insert into test(testId)
select 6
union all
select 7

Now returns:

-----------
2

(2 row(s) affected)

So now rows affected messages from the trigger are not returned, but those from your current connection are.  If you have written stored procedures, this probably feels wierd, as it is common practice to always start a proc with set nocount on.

What you can do from here is use this value to simply quit the trigger if no data is modified (ie @rowcount = 0).  Of course if it is interesting to you that no rows were affected, you instead let it go, or log the fact that it occurs:

alter trigger testRowcount
on test
after insert
as
  declare @rowcount int
  set @rowcount = @@rowcount
  set nocount on

  if @rowcount = 0
     return

  select @rowcount

Now if you insert 0 rows, the select statement will never be reached:

insert into test
select 100
where  1=2

Just returns:

(0 row(s) affected)

We have all written queries that return a column list where you see the error:

 

 

If you don't qualify the names of columns (because you are just writing a quick ad-hoc statement of course) then a couple of the tables use the same column name, it is normal enough.  

 

But it can also crop up in query with only a single table.  For example, take this table:

 

 

Load a bit of data:

 

And you run some query like this:

 

 

But the data is so long that you can't see the end of it:

 

So you take the sort column and add it to the select clause:

 

 

Wa, wa, waaaa.  Error:

 

 

What is sort of surprising is the fix.  Of course you can type out a proper column list (and in your production code, you really ought to,) but you can also use the positional syntax:

 

 

Though the same caveats apply and you shouldn't do this in your production code if at all possible. However, the following works just fine, and should be what you would do to allow the rest of your code to execute unchanged.

 

 

At first, this is kind of surprising, but really it isn't. In a SELECT statement, the ORDER BY clause executes after the SELECT clause, so it sees two columns named sortColumn in the set.  You can see this by executing this statement:

 

This will sort by the value of testId.  But change it to:

 

And instead it virtually adds the test.sortColumn value to the SELECT set, sorts by it, and then discards the value.  Of course the best policy is to just go ahead and qualify your names in your SELECT statements, but that isn't always reasonable when writing ad-hoc statements.

First off, I had started this post before I saw this post: http://sqlservercode.blogspot.com/2006/07/three-ways-to-get-time-portion-of.html and mine is a bit different anyhow.  (For full disclosure, the blogger in question was the inspiration for part of this post.)

 

Sometimes it is just advantageous to format a piece of data in SQL, if for no other reason than sometimes you might want to look at data in an administrative capacity.  A good place for that is formatting a date to show the amount of time between some events.

 

So here is a basic table that might be used to record an event (obviously there would be more information, for that you will have to engage your imagination.)

And some basic test data

go

 

As shown in the blog referred to above, you can use convert and 108 style to get the time part (or 114 if you want milliseconds).  For example, executing this:

 

select convert(varchar(10),getdate(),108)

 

Tonight, returns:

 

23:32:22

 

To get the event time, I use the followng query (well, parts of it anyhow.) 

 

Returns: 

 

Breaking this down the derived table: gets the difference in hours, so I can determine days and hours difference, and then I do something that looks really odd, and that is subtract one time value from another. 

 

If you execute:

 

 

You will see that this returns the time difference offset from '1900-01-01 00:00:00.000':

 

1900-01-01 02:14:23.000
1900-01-02 03:22:23.233
1900-06-02 13:10:30.233

The time part is all we care about, so using right(), I peel off the values and plop onto the end of the N day(s) and or simply the hours.  Months is too dangerous an equation because of variable length months, but you could easily extend to weeks if you cared.

 

What makes this significant (at least to me) is that when I first answered the question, I posited the following solution:

 

Someone asked a while back how to drop a table and foreign key constraints in one statement, and while that is not possible, you can use a procedure to do this pretty easily.   I have had this proc around for a while as I actually create it before I drop a table and drop it afterwards in a tool that I have built in ERwin.  So I figured I would post it here too.

 

The procedure works on a cursor, since I have to execute multiple statements. When I first posted the procedure this was the select clause of the procedure:

 

 

This suffers from two major problems.  One, people can name objects using quoted names that might contain spaces, causing my code to crash. Second, Steve Kass (one of my favorite SQL Server minds because of his incredibly detailed mind) busted me on the fact that it would be possible for a hacker to name a foreign key constraint in such a way to do bad stuff to your table (like drop it.)  For example:

 

 

My code would produce the following, that will drop the constraint by dropping the table as well.

 

So I had to make proper use of the quotename function:

 

 which changes the output to:

 

 

Which is perfectly safe (if a somewhat stupid name for a constraint.) The reason I make note of this is that I generally use quotename for all dynamic SQL.  However, I never even thought of doing it for names of objects.  I NEVER name objects with spaces or special characters (NEVER), and I just never even considered this kind of hack.  It just highlights the importants of thinking globally when posting code, rather than how you do things in your limited scope of your "home turf".  (This is the kind of learning experience that I was talking about in my article "Why answer questions".  Learning bits and pieces like this are a key part.)

 

So, without further adeiu, here is the procedure:

 

 

 

This question came up in the forums, so I wrote this to answer the question. 

There are two ways to do this using a trigger (as well as using column level security, but that won't keep programmers from writing code to modify values!)

First a bit of sample data:

create table testTriggerNoUpdateOfColumns
(                                 --no update of pk or this is a very hard problem
    testTriggerNoUpdateOfColumnsId  int NOT NULL PRIMARY KEY, 
    canChange varchar(10) NOT NULL,
    dontChange varchar(10) NOT NULL,
    dontChangeNullable varchar(10) NULL
)
go
insert into testTriggerNoUpdateOfColumns(testTriggerNoUpdateOfColumnsId, canChange,
                                                                     dontChange, dontChangeNullable)
values (1,'changeMe','don''t',null)
insert into testTriggerNoUpdateOfColumns(testTriggerNoUpdateOfColumnsId, canChange,
                                               dontChange, dontChangeNullable)
values (2,'changeMe','don''t','not')

The first method won't even let DML statements reference the data.  This is the most efficient method, but might  be too annoying if you have existing code (note this trigger is just barebones, you may need to add error handling depending on how your other code works with this):

create trigger testTriggerNoUpdateOfColumns_afterUpdate
on testTriggerNoUpdateOfColumns
after update
as
 begin
    --just see if the column is referenced by the DML statement
    if update(dontChange) or update(dontChangeNullable)
      begin
            raiserror ('You may not change the value of the dontChange or dontChangeNullable columns',16,1)
            rollback transaction
      end
  end
go

So this is allowed:

update testTriggerNoUpdateOfColumns
set    canChange = 'changed'

But this fails:

update testTriggerNoUpdateOfColumns
set    dontChangeNullable = 'not'
where  testTriggerNoUpdateOfColumnsId = 2

With the error coded in the trigger

Msg 50000, Level 16, State 1, Procedure testTriggerNoUpdateOfColumns_afterUpdate, Line 8
You may not change the value of the dontChange or dontChangeNullable columns

The second method allows DML statements to reference the columns, but it validates the data to see if it has changed.:

alter trigger testTriggerNoUpdateOfColumns_afterUpdate
on testTriggerNoUpdateOfColumns
after update
as
 begin
    --still see if the column is part of the DML
    if update(dontChange) or update(dontChangeNullable)
      begin             --join inserted and deleted tables, no need to touch real tables, so for reasonable sized sets
                             --will be very fast.  If you update thousands of rows this could be trouble though
                             --so leaving it out of the DML would be best in that case, or disable the trigger
            if exists ( select *
                             from   deleted
                                            join inserted
                                                   on deleted.testTriggerNoUpdateOfColumnsId =
                                                                                      inserted.testTriggerNoUpdateOfColumnsId
                                     --not null columns are simple. 
                        where  inserted.dontChange <> deleted.dontChange
                                    --nullable columns require checking for mismatched nulls too
                           or  (inserted.dontChangeNullable <> deleted.dontChangeNullable
                                or (inserted.dontChangeNullable is null and deleted.dontChangeNullable is not null)
                                or (inserted.dontChangeNullable is not null and deleted.dontChangeNullable is null)))
              begin
                    raiserror ('You may not change the value of the dontChange or dontChangeNullable columns',16,1)
                    rollback transaction
              end
      end
  end

So this allowed

update testTriggerNoUpdateOfColumns
set    canChange = 'changed'

As well as this is also now allowed, since the data doesn't change

update testTriggerNoUpdateOfColumns
set    dontChange = 'don''t'
where  testTriggerNoUpdateOfColumnsId = 1

Changing the value will however fail

update testTriggerNoUpdateOfColumns
set    dontChange = 'do'
where  testTriggerNoUpdateOfColumnsId = 1

Same error

Msg 50000, Level 16, State 1, Procedure testTriggerNoUpdateOfColumns_afterUpdate, Line 16
You may not change the value of the dontChange or dontChangeNullable columns

--the next statements shows that the NULL business works:

update testTriggerNoUpdateOfColumns
set    dontChangeNullable = null
where  testTriggerNoUpdateOfColumnsId = 1

update testTriggerNoUpdateOfColumns
set    dontChangeNullable = 'notnull'
where  testTriggerNoUpdateOfColumnsId = 1

Msg 50000, Level 16, State 1, Procedure testTriggerNoUpdateOfColumns_afterUpdate, Line 16
You may not change the value of the dontChange or dontChangeNullable columns

update testTriggerNoUpdateOfColumns
set    dontChangeNullable = 'not'
where  testTriggerNoUpdateOfColumnsId = 2

update testTriggerNoUpdateOfColumns
set    dontChangeNullable = null
where  testTriggerNoUpdateOfColumnsId = 2

Msg 50000, Level 16, State 1, Procedure testTriggerNoUpdateOfColumns_afterUpdate, Line 16
You may not change the value of the dontChange or dontChangeNullable columns

 

 

Though I will almost guarantee you will have forgotten about it:

 

Yesterday, when I was looking at disk space utilization for a server, I had a need to see what objects were placed on what filegroups, and these queries were the outcome.  The queries should be pretty self explanatory, but if you want more info, email me or leave a comment and I will add comments.

 

 

 

Both queries return something like:

 

 

 

I was writing some procedures today, and getting kind of annoyed that I had to keep changing from CREATE PROCEDURE to ALTER PROCEDURE in my database create scripts, or DROP the procedure first, losing the original create date, and when it was altered, and I came up with this as a possible way around that.

 

Instead of looking for the procedure's existance to drop it, I look for the procedure's existance and if it hasn't been created yet I create a "dummy" procedure.   Then I use ALTER to modify the procedure, so it won't lose any permissions that some other user has created, plus the date it was first created, and in 2005, the date it was modified is also maintained:

 

 

Edit:  As commenter Scott notes, we are generaly more interested in when the procedure was modified, not when it was created.  In the 2005 metadata, you get a modified date as well as a create_date:

After running this script a few times while writing this post, the create date stays constant, but the modify_date changes:

 

 

This returns (for me):

 

For 2005, It might have a downside, but I haven't thought of one yet (this clearly doesn't mean there isn't one :)  I probably wouldn't use this in 2000 and earlier.

I had never heard of this function until the other day when Steve Kass (check out his SQL page too.  Steve has a very detailed and awesome mind) and Denis The SQL Menace, (who has a very nice SQL blog too.)

 

Anyhow, a person asked in the forums about how to sort by an IP address:

 

So, I took this data:

 

Which was a fun query, yet htere were better ways to do it. Steve and Denis noted that:

 

 

To which I replied, "parse-who-now?"  PARSENAME is a built-in function that was created for parsing object names, like 'servername.databasename.schemaname.objectname'.  Because of this it only will get the first four values, seperated by periods, which just happens to be how ipAddresses are constructed (in case you didn't know :)

 

A few additional articles on using PARSENAME:

 

 

Of course, by the looks of this search, I am the last person on the planet to hear of PARSENAME:

 

 

The thing is, this is just not the best way to store an ip address.  I generally prefer to store ipAddresses as a bigint, or another great way is as four tinyint values.   Check this article for a more complete reference: http://www.aspfaq.com/show.asp?id=2450. 

If you find yourself using substring in a substantial number of queries on the data in your database, you may have a normalization problem. And this means any functionality that uses a substring-like interface (replace, stuff, charindex, patindex, etc). Truly there is very little use for these functions in a well normalized system.

 

This article will discus the first of the types of issues, the multi-purpose character column.

The first kind of example is something like a name column that contains a full persons name.  Seems reasonable so you can put ever how many parts of the name in there.  So you code something like:

 

 

Or some variation on this theme. Seems innocent enough, but now the SQL engine cannot effectively act on this query effectively using an index to perform the search. And what if the person is a 'Jr' like John Smith Jr. Now you are really in a mess trying to substring your way out of a mess.

 

Take this to the next level what about updating this value, say to change a persons last name. You are really hosed and are stuck writing ridiculous code to ignore Junior, Jr and anything else that looks like a suffix, and dealing with people with cool names to look at and pronounce, but a nightmare to code against, like 'Van Der Mullen' or 'Mac Donald'   Just try to write the query that decides if this is this a guy named Mac, last name Donald, or perhaps the data entry person screwed up Mrs Delores Mac Donalds name?

 

It is a very common type of mistake that every one of us has made at one time or another because one column seems easier than two (or three, or whatever).

 

The rule of thumb here is that if you will "likely" be dealing with part of a column seperately, give it a different place for storage.  You can deal with usage in a more elegant manner by building tools in the client layer, or you can use a view or computed column.  I often use a computed column when I have this kind of need.  For example, take the following abbreviated name table (no primary key, and only the very basic name bits.)

 

 

I have included two versions of the name that will allow you to display the data for the user, as well as the three parts of the name (very common in the United States to record these three names)

 

 

Now view the data:
  
select * from nameExample

 

 

The next question that this data puts to mind is "what about the middle initial?"  Should there be a column for just the middle initial?  And what of the case where Bob Leonard only gave a single character response the question of middleName (something I frequently do when I don't feel like sharing!) I would most likely say add a computed column that gets the substring of the first character of the middleName, since the middle initial is functionally dependent on the middleName column, even if the user only put in one character.

 

 

Now you can persist this data by indexing it, or in SQL Server 2005 using the PERSIST keyword for faster access.  The key here is that you have presented the least surface space to be edited, and you will rarely if ever need, in SQL code, to look for parts of a name.  You might need to look for people whose last name starts with 'F', or the such, but it would be rare to look for values that end in 's', but you still have the SQL tools to go there if the user wants to.

The next in this series will discuss bitmask columns, followed by multi-valued columns (such as '1,2,3')

For many years, I had a technique that more or less worked.  Declare a cursor on sysprocesses (or in 2005, sys.dm_exec_requests) and kill each user individually.  Then go back and make sure that they are all dead, or try again.  Not a perfect system, but it does relatively work.

 

 

This is really handy, especially when you drop and recreate a database multiple times during testing scripts.  Often I will end up with five connections to the database and when I try to drop the database, this message appears:

 

 

The cost of this is that other connections to the server for the affected database will be immediately severed, so the first time you try something you will have to reconnect.  In SSMS you will likely get this error the first time you try something since the tools don't realize that they have been cut off:

 

But the second time will automatically reconnect.

If you answered 5 (or even thought it) you would do yourself a favor to go read Euan Garden's SQL Mythbuster blog MSDE/SQL Express has 5 concurrent user limit.  Excellent history lesson, and good clarification on the limits (and lack of the limits you may have thought existed) in the free versions of SQL Server.

This question came up today in the newsgroups, so I answered it there, but it is probably something that might be interesting to more than one or two people, so I expanded it here for "future use." 

Say you have the following database where the collation was case sensitive and you want to change this to case insensitive:

create database testCollationChange
collate SQL_Latin1_General_CP1_CS_AS
go
use testCollationChange
go

Then check out the collation to make sure:

select databasepropertyex(db_name(),'collation') as collation_name
go

collation_name
---------------------------------
SQL_Latin1_General_CP1_CS_AS

Then create a table:

create table test
(
      value varchar(20),
      textValue text,
      varchar_max varchar(max)
)

And check the collation of the columns:

select column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'test'

This returns:

column_name    collation_name

---------------     ----------------------------------

value       SQL_Latin1_General_CP1_CS_AS
textValue   SQL_Latin1_General_CP1_CS_AS
varchar_max SQL_Latin1_General_CP1_CS_AS

Now you decide to change the collation of the database to case insensitive, just alter the database:

alter database testCollationChange
collate SQL_Latin1_General_CP1_CI_AS
go

select databasepropertyex(db_name(),'collation') as collation_name

This returns:

collation_name
--------------------------------
SQL_Latin1_General_CP1_CI_AS

But the tables stay in the previous collation:

select column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'test'

Which returns:

column_name collation_name
----------- ----------------------------------
value       SQL_Latin1_General_CP1_CS_AS
textValue   SQL_Latin1_General_CP1_CS_AS
varchar_max SQL_Latin1_General_CP1_CS_AS

To change this, I will use a query that I Adapted from Justin Mallier's blog: (http://mallier.blogspot.com/2005/11/how-to-change-collation-against-all.html) that builds a script to alter the columns:

--I added the variables to make it more safe (Some collations may be desired
--to stay the same. I did use like on the from so you could change them all pretty
--easily. Also I added text and max datatype support

declare  @fromCollation sysname,
         @toCollation sysname

set      @fromCollation = 'SQL_Latin1_General_CP1_CS_AS'
set      @toCollation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT  

'ALTER TABLE ' + TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL'

END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
  and COLLATION_NAME like @fromCollation

This is the result. Execute this query and you can change all of the collations.

ALTER TABLE test ALTER COLUMN value varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE test ALTER COLUMN textValue text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE test ALTER COLUMN varchar_max varchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

go

Checking them again:

select column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'test'

Returns:

column_name collation_name
----------- ----------------------------------
value       SQL_Latin1_General_CP1_CI_AS
textValue   SQL_Latin1_General_CP1_CI_AS
varchar_max SQL_Latin1_General_CP1_CI_AS

 

Then clean up after yourself

use tempdb

go

drop database testCollationChange

 

A couple of gotchas prompted by Adam Machanic's comments

1. If you use user defined types, this technique won't work.  It only works on base datatypes.  As Adam Machanic commented, in these cases use DTS to take the data out of one database and into another.

2. You will have to drop indexes, constraints, etc that are dependent on the columns being altered.