Category Archives: TSQL

MS SQL TSQL PadLeft

Ever wanted a PadLeft for SQL server
Here’s the next best thing, a scalar function we wrote and implemented into all our databases
Enjoy

SQL Server Monitor Changes to SCHEMA

If you need to prevent changes to your SQL Server Schema, or at least monitor them, then this post is worth reading

I needed to monitor/control changes made to SQL server databases at server instance level. There are plenty of ways of doing this but there is nothing as simple as a DDL level trigger. It’s clean, can be disabled quickly for rapid change implementation and its a central location which means deploying to sever SQL Servers or a server farm is that much easier.

So the basic requirements
1. We must be able to monitor CREATE,DROP and ALTER on Tables, Views, Stored Procedures, Triggers and Functions
2. We must be able to filter based on user, applying the rule or not
3. We must have a log of each schema change regardless of success or failure
4. We need to send an email when a change has been attempted and was disallowed

So how are we going to do this. Well we can use the ROLLBACK inside our trigger to prevent the changes from occurring, and we can use the RAISERROR to display the appropriate message we want to user/program to receive when the attempt fails. We can also take advantage of the code SQL Server query execution sequence such that after a rollback has been called in a trigger, the parent transaction has been dumped, but any further transactions will continue to be executed and committed. See here http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

Some Key fundamentals we are going to be using
First off the

, this will allow us to create the trigger at server level, so it affects all databases. It will hence be located in the Server Objects >> Triggers within SQL Server Management Studio
Next comes

which contains all the details we want about what is changing and who is trying to change it
We will be using “

” to identify the current logged in connection that is behind the event and also filter on this to make a decision to reject or approve the change.
Finally we use the

and

commands to initiate a rejection of the event, followed with our logging insert and in my case an email send method, I won’t get into the details of the mail sending in this article, however its worth mentioning that I have a fully fledged SSIS package driven mail sending engine integrated with MS Exchange, its possible to send from any mailbox to any mail group or address in the active directory or individual mail address book, you can attach files from the network or from binary within the database, you can send with importance in text format or html as this example demonstrates, Its a very powerful and very handy engine to have in the systems topology, took 3 days to write and about a month to fine tune, suddenly sending mail is very easy from anywhere in your infrastructure. Anyway I digress

Enough background, here’s the Code. You will obviously need to adjust bits to suite your needs before you use this
This should be executed against the master

SQL Handling Null Values

Having Null Values in your database is a good thing despite those that might tell you otherwsie. Not only does it save space but it is a definitive “im empty” signature. There are of course proper and improper ways to handle SQL Nulls. Just remember never ever try to compare to a null using “=”.

1. A NULL value represents an UNKNOWN value

2. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

Any time a NULL value is present in a column that an aggregation is performed on, such as a COUNT, AVG, or SUM function, those values will be ignored and therefore not included in the functions result.

The COALESCE function returns the first non-NULL value in the field list that it accepts. The COALESCE function is very useful in a join operation or in queries where you are comparing a single value from a list of possible fields to a single value.