Showing posts with label BestPractices. Show all posts
Showing posts with label BestPractices. Show all posts

Wednesday, October 25, 2017

Foreign Keys don't always need a primary key

In the post Your lack of constraints is disturbing we touched a little upon foreign key constraints but today we are going to take a closer look at foreign keys. The two things that we are going to cover are the fact that you don't need a primary key in order to define a foreign key relationship, SQL Server by default will not index foreign keys

You don't need a primary key in order to have a foreign key

Most people will define a foreign key relationship between the foreign key and a primary key. You don't have to have a primary key in order to have a foreign key, if you have a unique index or a unique constraint then those can be used as well.

Let's take a look at what that looks like with some code examples


A foreign key with a unique constraint instead of a primary key


First create a table to which we will add a unique constraint after creation

CREATE TABLE TestUniqueConstraint(id int)
GO
Add a unique constraint to the table

ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
GO
Insert a value of 1, this should succeed

INSERT  TestUniqueConstraint VALUES(1)
GO

Insert a value of 1 again, this should fail

INSERT  TestUniqueConstraint VALUES(1)
GO

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'ix_unique'. Cannot insert duplicate key in object 'dbo.TestUniqueConstraint'. The duplicate key value is (1).
The statement has been terminated.



Now that we verified that we can't have duplicates, it is time to create the table that will have the foreign key



CREATE TABLE TestForeignConstraint(id int)
GO
Add the foreign key to the table

ALTER TABLE dbo.TestForeignConstraint ADD CONSTRAINT
 FK_TestForeignConstraint_TestUniqueConstraint FOREIGN KEY 
(id) REFERENCES dbo.TestUniqueConstraint(id) 



Insert a value that exist in the table that is referenced by the foreign key constraint


INSERT TestForeignConstraint  VALUES(1)
INSERT TestForeignConstraint  VALUES(1)

Insert a value that does not exist in the table that is referenced by the foreign key constraint


INSERT TestForeignConstraint  VALUES(2)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TestForeignConstraint_TestUniqueConstraint". The conflict occurred in database "tempdb", table "dbo.TestUniqueConstraint", column 'id'.

The statement has been terminated.


As you can see, you can't insert the value 2 since it doesn't exist in the TestUniqueConstraint table



A foreign key with a unique index instead of a primary key

This section will be similar to the previous section, the difference is that we will use a unique index instead of a unique constraint
First create a table to which we will add a unique index after creation

CREATE TABLE TestUniqueIndex(id int)
GO

Add the unique index



CREATE UNIQUE NONCLUSTERED INDEX ix_unique ON TestUniqueIndex(id)
GO
Insert a value of 1, this should succeed


INSERT  TestUniqueIndex VALUES(1)
GO
Insert a value of 1 again , this should now fail


INSERT  TestUniqueIndex VALUES(1)
GO

Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.TestUniqueIndex' with unique index 'ix_unique'. The duplicate key value is (1).
The statement has been terminated.


Now that we verified that we can't have duplicates, it is time to create the table that will have the foreign key


CREATE TABLE TestForeignIndex(id int)
GO

Add the foreign key constraint

ALTER TABLE dbo.TestForeignIndex ADD CONSTRAINT
 FK_TestForeignIndex_TestUniqueIndex FOREIGN KEY 
 (id) REFERENCES dbo.TestUniqueIndex(id)  




Insert a value that exist in the table that is referenced by the foreign key constraint

INSERT TestForeignIndex  VALUES(1)
INSERT TestForeignIndex  VALUES(1)

Insert a value that does not exist in the table that is referenced by the foreign key constraint


INSERT TestForeignIndex  VALUES(2)


Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TestForeignIndex_TestUniqueIndex". The conflict occurred in database "tempdb", table "dbo.TestUniqueIndex", column 'id'.
The statement has been terminated.


That failed because you can't insert the value 2 since it doesn't exist in the TestUniqueIndex table

As you have seen with the code example, you can have a foreign key constraint that will reference a unique index or a unique constraint. The foreign key does not always need to reference a primary key

Foreign keys are not indexed by default

When you create a primary key, SQL Server will by default make that a clustered index. When you create a foreign key, there is no index created

Scroll up to where we added the unique constraint to the TestUniqueConstraint table, you will see this code

ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)

All we did was add the constraint, SQL Server added the index behind the scenes for us in order to help enforce uniqueness more efficiently

Now run this query below


SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName, 
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestUniqueIndex','TestUniqueConstraint')
AND name IS NOT NULL

You will get these results

TableName         IndexName StorageType
---------------------   -----------     --------------
TestUniqueConstraint ix_unique NONCLUSTERED
TestUniqueIndex         ix_unique NONCLUSTERED

As you can see both tables have an index

Now let's look at what the case is for the foreign key tables. Run the query below


SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName, 
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestForeignIndex','TestForeignConstraint')

Here are the results for that query

TableName       IndexName StorageType
--------------------- --------- -------------
TestForeignConstraint NULL HEAP
TestForeignIndex NULL HEAP




As you can see no indexes have been added to the tables. Should you add indexes? In order to answer that let's see what would happen if you did add indexes. Joins would perform faster since it can traverse the index instead of the whole table to find the matching join conditions. Updates and deletes will be faster as well since the index can be used to find the foreign keys rows to update or delete (remember this depends if you specified CASCADE or NO ACTION when you create the foreign key constraint)

I wrote about deletes being very slow because the columns were not indexed here: Are your foreign keys indexed? If not, you might have problems
So to answer the question, yes, I think you should index the foreign key columns



Thursday, October 19, 2017

Your lack of constraints is disturbing

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

SQL Server supports the following types of constraints:

NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY

Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer will also use constraint definitions to build high-performance query execution plans.
When I interview people, I always ask how you can make sure only values between 0 and 9 are allowed in an integer column. I get a range of different answers to this question, here are some of them:
  • Convert to char(1) and make sure it is numeric
  • Write logic in the application that will check for this
  • Use a trigger
  • Create a primary key table with only the values from 0 till 9 then make this column a foreign key in the table you want to check for this
Only 25% of the people will tell you to use something that you can use from within SQL, and only 10% will actually know that this something is called a check constraint, the other ones know that there is something where you can specify some values to be used.


Why do we need constraints at all?

So why do we need constraints? To answer that question, first you have to answer another question: how important is it that the data in your database is correct? I would say that that is most important, after all you can have all the data in the world but if it is wrong it is useless or it might even ending up costing you money. To make sure that you don't get invalid data, you use constraints.

Constraints work at the database level, it doesn't matter if you do the data checking from the app or web front-end, there could be someone modifying the data from SSMS. If you are importing files, constraints will prevent invalid data from making it into the tables.

Constraints don't just have to have a range, constraints can handle complex validations. You can have regular expressions in check constraints as well, check out SQL Server does support regular expressions in check constraints, you don't always need triggers for some examples


Constraints are faster than triggers

The reason that check constraints are preferable over triggers is that they are not as expensive as triggers, you also don't need an update and an insert trigger, one constraint is enough to handle both updates and inserts.


Constraints are making it hard for us to keep our database scripts from blowing up

This is a common complaint, when you script out the databases and the primary and foreign key tables are not in the correct order you will get errors. Luckily the tools these days are much better than they were 10 years ago. If you do it by hand just make sure that it is all in the correct order. Another complaint is that constraints are wasting developers time, they can't just populate the tables at random but have to go in the correct order as well.


Some examples of constraints


First create this table

CREATE TABLE SomeTable(code char(3) NOT NULL)
GO


Now let's say we want to restrict the values that you can insert to only accept characters from a through z, here is what the constraint looks like

ALTER TABLE SomeTable ADD CONSTRAINT ck_bla
CHECK (code LIKE '[a-Z][a-Z][a-Z]' )
GO


If you now run the following insert statement....

INSERT SomeTable VALUES('123')

You get this error message back

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "tempdb", table "dbo.SomeTable", column 'code'.
The statement has been terminated.


What if you have a tinyint column but you want to make sure that values are less then 100? Easy as well, first create this table

CREATE TABLE SomeTable2(SomeCol tinyint NOT NULL)
GO

Now add this constraint

ALTER TABLE SomeTable2 ADD CONSTRAINT ck_SomeTable2
CHECK (SomeCol < 100 )
GO

Try to insert the value 100

INSERT SomeTable2 VALUES('100')

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_SomeTable2". The conflict occurred in database "tempdb", table "dbo.SomeTable2", column 'SomeCol'.
The statement has been terminated.


Okay, what happens if you try to insert -1?

INSERT SomeTable2 VALUES('-1')

Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '-1' overflowed an INT1 column. Use a larger integer column.
The statement has been terminated.


As you can see you also get an error, however this is not from the constraint but the error is raised because the tinyint datatype can't be less than 0
Check constraint can also be tied to a user defined function and you can also use regular expressions. Ranges can also be used, for example salary >= 15000 AND salary <= 100000

For a post about foreign key constraints, go here: Foreign Keys don't always need a primary key



Tuesday, October 17, 2017

Standardized Naming And Other Conventions

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Today we are going to look at standardized naming conventions and other conventions that you should standardize as well. Every company needs to have standards that developers need to follow in order to make maintenance easier down the road. There are several things that you can standardize on, here are just a few:

The naming of objects
The layout of code including comments
The way that error handling is done

The naming of objects

I am not a fan of underscores,  we tend to name our objects CamelCased

Stored procedures are usually prefixed with usp_ or pr but never sp_


One tool that ships with SQL Server that you can use is policy management, you can set it so that it checks if procs start with sp_


And here is what happens after the policy is evaluated





Since this is Adam Machanic's proc.. we will let this fly  :-)


Something like this can also be accomplished with DDL triggers, there are many ways to skin the cat, there is no excuse for having all kind of crazy named objects.

I also wrote about naming conventions in the using the ISO-11179 Naming Conventions post


Never use Hungarian notation on column names or variables, I have worked with tables that looked like this

CREATE TABLE tblEmployee(
strFirstName varchar(255),
strLastName varchar(255),
intAge int,
dtmBirthDate datetime
.......
.......
)
If you have intellisense in SSMS, having every table start with tbl is making it pretty useless. Also sometimes the data type of a column will change but of course nobody goes back to rename the column to reflect this because it will break code all over the place



Instead of having something like the following

-- the salary for the employee
declare @decValue decimal(20,2)

It would be better to have something like this

declare @EmployeeSalary decimal(20,2)

Now I don't have to scroll all the way to the top to figure out what is actually stored in this variable, EmployeeSalary pretty much describes what it is and I can also pretty much assume that this will be some amount and not a date

The layout of code including comments

I have worked with code that was all in lowercase and all in uppercase. I have no problem with either but if you at least standardize on one or the other it will be a little easier to jump from your code to someone else's code


You can setup standard templates in SSMS for your organization, you can get to it from the menu bar, View--> Template Explorer or hit CTRL + ALT + T
Now expand the Stored Procedures folder


The basic stored procedure template looks like this
-- =============================================
-- Create basic stored procedure template
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'
     AND SPECIFIC_NAME = N'<Procedure_Name, sysname, Procedure_Name>' 
)
   DROP PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>
GO

CREATE PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>
 <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
 <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
 SELECT @p1, @p2
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, 
Procedure_Name> <value_for_param1, , 1>, <value_for_param2, , 2>
GO


You can modify this template, give it to every developer and now you all have the same template. What can be done with templates can also be done with snippets, if you do Tools-->Code Snippets Manager, you can see all the snippets that are available, you can add your own snippets so that all developers will have the same snippets for comment tasks.
Standardize on comments as well.  Besides what ships with SSMS, there are also commercial tools that will do an even better job than SSMS

The way that error handling is done

I like to have all the errors in one place, this way I know where to look if there are errors. Capture the proc or trigger that threw the error, it if is a multi-step proc then also note the code section in the proc, this will greatly reduce the time it will take you to pinpoint where the problem is. Michelle Ufford has a nice example here: Error Handling in T-SQL that you can use and implement in your own shop.
There are many more things that you need to standardize on, the thing that bothers me the most is when I see dates in all kind of formats when passed in as strings, use YYYYMMDD, this will make it non ambiguous.


Monday, October 16, 2017

Do not trust the SSMS designers, learn the T-SQL way



It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Read the following two lines

Question: How do you add a primary key to a table?
Answer: I click on the yellow key icon in SSMS!

Have you ever given that answer or has anyone every answered that when you asked this question?

Technically, yes, that will create a primary key on the table but what will happen when you do that? Let's take a look at some examples.
First create this very simple table

CREATE TABLE TestInt(Col1 tinyint not null)

Now the developers changed their mind and want to insert values that go beyond what a tinyint can hold. If you try to insert 300, you will get an error

INSERT TestInt VALUES(300)

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 300.

The statement has been terminated.


No, problem, I will just change the data type by running this T-SQL statement

ALTER TABLE TestInt ALTER COLUMN Col1 int NOT NULL


But what if you use the SSMS designer by right clicking on the table, choosing design and then changing the data type from tinyint to int?

The answer is it depends on an option and if it is checked or not



If that option is checked, then you will get the following message when clicking on the script icon




If that option is not checked then here is what SSMS will do behind the scenes for you


/* To prevent any potential data loss 
issues, you should review this script in 
detail before running it outside the context
 of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestInt
 (
 Col1 int NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.TestInt)
  EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
  SELECT CONVERT(int, Col1) FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestInt
GO
EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT' 
GO
COMMIT
That is right, it will create a new table, dump all the rows into this table, drop the original table and then rename the table that was just created to match the orgiinal table. This is overkill.

What about adding some defaults to the table, if you use the SSMS table designer, it will just create those and you have no way to specify a name for the default.
Here is how to create a default with T-SQL, now you can specify a name and make sure it matches your shop's naming convention

ALTER TABLE dbo.TestInt ADD CONSTRAINT
 DF_TestInt_Col1 DEFAULT 1 FOR Col1

About that yellow key icon, let's add a primary key to our table, I can do the following with T-SQL, I can also make it non clustered if I want to

ALTER TABLE dbo.TestInt ADD CONSTRAINT
 PK_TestInt PRIMARY KEY CLUSTERED 
 (Col1)  ON [PRIMARY]

Click that yellow key icon and here is what happens behind the scenes, I have not found a way to make it non clustered from the designer

/* To prevent any potential data loss issues, 
you should review this script in detail before running it 
outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestInt
 DROP CONSTRAINT DF_TestInt_Col1
GO
CREATE TABLE dbo.Tmp_TestInt
 (
 Col1 int NOT NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_TestInt ADD CONSTRAINT
 DF_TestInt_Col1 DEFAULT ((1)) FOR Col1
GO
IF EXISTS(SELECT * FROM dbo.TestInt)
  EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
  SELECT Col1 FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestInt
GO
EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT' 
GO
ALTER TABLE dbo.TestInt ADD CONSTRAINT
 PK_TestInt PRIMARY KEY CLUSTERED 
 (
 Col1
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

You might ask yourself why you should care, all the tables are small, this is not a big issue. This might be true now, what if you start a new job and now you have to supply alter, delete and create scripts? Now you are in trouble.

I used to do the same when I started, I used the designers for everything, I didn't even know Query Analyzer existed when I started, I created and modified the stored procedures straight inside Enterprise Manager. Trying to modify a view that had a CASE statement in Enterprise Manager from the designer....yeah good luck with that one....you would get some error that it wasn't supported, I believe it also injected TOP 100 PERCENT ORDER BY in the view as well

I don't miss those days at all. Get to learn T-SQL and get to love it, you might suffer when you start but you will become a better database developer.
Aaron Bertrand also has a post that you should read about the designers: Bad habits to kick : using the visual designers

Sunday, October 15, 2017

Triggers, when to use them, when not to use them

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.


 Today we are going to look at triggers. Triggers are a great way to keep your database in a consistent state. There are two types of triggers, DML triggers and DLL triggers. DML triggers respond to Data Manipulation Statements (Insert, Delete, Update) DDL triggers respond to Data Definition Language events.


Some things that DML triggers are used for:
  • Keeps your databases from having wrong data by doing checks that can't be handled with constraints
  • Filling in values that are not supplied and can't be handled through default constraints since these don't fire on updates
  • Calculation summary values and updates the summary table with that value
  • Used as a mechanism to maintain an audit trail for DML statements
Some things that DDL triggers are used for:
  • Automatically add columns to a table if they were not added, for example LastUpdated and InsertedBy columns
  • Notify a DBA when a database has been created, dropped or altered
  • Used as a mechanism to maintain an audit trail for DDL statements, capture every time an object has been created, dropped or altered and by who
Most common mistake people make when first starting writing triggers is that they write it in such a way that it will only work if you insert/update/delete one row at a time. A trigger fires per batch not per row, you have to take this into consideration otherwise your DML statements will blow up. How to do this is explained in this post Coding SQL Server triggers for multi-row operations, there is no point recreating that post here.

Another problem that I see is that some people think a trigger is SQL Server's version of crontab, you will see code that sends email, kicks off jobs, runs stored procedures. This is the wrong approach, a trigger should be lean and mean, it should execute as fast as possible, if you need to do some additional things then dump some data from the trigger into a processing table and then use that table to do your additional tasks. Don't use triggers as a messaging system either, SQL Server comes with Service Broker, use that instead. 

Triggers might look like hammers to some people but I guarantee you not everything is a nail....

You could end up with a real difficult thing to debug, one trigger that kicks off other triggers, now have fun debugging the trigger hell you got yourself into....or worse debug this mess if you inherited this....this is like the GOTO spaghetti code of databases.
Since triggers work besides the scenes you might spend hours debugging something only to find out that a trigger modified the value

One thing I always find interesting is when someone sees two n rows affected statements when they only did one insert, you know a person like that has not been exposed to triggers yet

Some people will say that you don't need triggers for anything and that they do more harm than good, I myself don't agree with that, triggers have a place but they should not be abused and overused, the same can be said of views


Saturday, October 14, 2017

Coding SQL Server triggers for multi-row operations

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Today I decided to revisit the post about coding triggers for multi-row operations

There are many forum posts and questions on stackoverflow where people have trigger code. However  these triggers are coded incorrectly because they don't account for multi-row operations. 

The one thing you have to remember is that a trigger fires per batch not per row, if you are lucky you will get an error...if you are not lucky you will not get an error but it might take a while before you notice that you are missing a whole bunch of data

Let's take a look at exactly what happens, first create these two tables



CREATE TABLE Test(id int identity not null primary key, 
   SomeDate datetime not null)
GO

CREATE TABLE  TestHistory(id int  not null, 
   InsertedDate datetime not null)
GO


Now create the following trigger.


CREATE  TRIGGER trTest
    ON Test
    FOR INSERT
    AS
     
    IF @@ROWCOUNT =0
    RETURN
     
    DECLARE @id int
    SET @id = (SELECT id 
    FROM inserted)
    
    INSERT TestHistory (id,InsertedDate)
    SELECT @id, getdate()
    
    GO

The trigger you just created is very simple, it basically inserts a row into the history table every time an insert happens in the test table


Run this insert statement which only inserts one row


INSERT Test(SomeDate) values(getdate())

Now run this to see what is in the history table


SELECT * FROM TestHistory


1 2017-10-14 08:49:16.227


That all works fine, what happens when we try to insert 2 rows?



INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT  dateadd(dd,1,getdate() )


Here is the error.

Server: Msg 512, Level 16, State 1, Procedure trTest, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


As you can see the trigger blew up with an error. Let's try something else.
What would happen if you coded the trigger in this way


ALTER TRIGGER trTest
    ON Test
    FOR INSERT
    AS
     
    IF @@ROWCOUNT =0
    RETURN
     
    DECLARE @id int
    SELECT @id = id 
    FROM inserted
    
    INSERT TestHistory (id,InsertedDate)
    SELECT @id, getdate()
    
    GO


Now insert one row


INSERT Test(SomeDate) VALUES (getdate())

We look again what is in the history table, as you can see we have id 1 and 4, this is because id 2 and 3 failed and were rolled back when we did the insert earlier



SELECT * FROM TestHistory

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647


Here is where it gets interesting, run this code


INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )


That runs fine but when we look now we are missing one of the rows in the history table


SELECT * FROM TestHistory

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270


let's try that same insert statement again


INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )

Now we are again missing a row in the history table


SELECT * FROM TestHistory

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270
7 2017-10-14 08:52:09.447


The problem is with this line of code


SELECT @id = id FROM inserted


@id will only hold the value for one of the rows that was returned in the result set


Here is how you would change the trigger to work correctly



ALTER TRIGGER trTest
    ON Test
    FOR INSERT
    AS
     
    IF @@ROWCOUNT =0
    RETURN
     
        
    INSERT TestHistory (id,InsertedDate)
    SELECT id, getdate()
    FROM inserted
    
GO


Now run the single insert statement again


INSERT Test(SomeDate) VALUES (getdate())


That row was inserted, we can check the history table to see what is there now



SELECT * FROM TestHistory

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270
7 2017-10-14 08:52:09.447
9 2017-10-14 08:52:57.990


Finally, we can again test with the insert statement that will insert 2 rows


INSERT Test(SomeDate)
SELECT getdate()
UNION ALL
SELECT dateadd(dd,1,getdate() )


Let's check the history table again


SELECT  * FROM TestHistory

1 2017-10-14 08:49:16.227
4 2017-10-14 08:50:37.647
5 2017-10-14 08:51:06.270
7 2017-10-14 08:52:09.447
9 2017-10-14 08:52:57.990
11 2017-10-14 08:53:40.693
10 2017-10-14 08:53:40.693

And as you can see both rows were inserted into the history table

So what is worse in this case? The error message or the fact that the code didn't blow up but that the insert wasn't working correctly? I'll take an error message any time over the other problem.