Posted by Prashant on December 3, 2012
This post describes how to use result of an user defined function with CHECK constraint in SQL Server. For demonstration, considered a situation where it is not allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.
So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.
Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).
Now try to insert a record where calculated age is less than 18 years and see what happens.
As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record…
View original post 32 more words
Posted in SQL Server | Leave a Comment »
Posted by Prashant on December 2, 2012
When one of our projects deployed to production application was throwing a strange error and while debugging it is found that one of the stored procedure getting failed because of collation conflict issue.
The reason behind this error is the stored procedure used a temp table to join a table on string column. Temp tables are created on TempDB, so if you don’t specify collation for a string columns they inherit the collation of TempDB. If the collation of user database doesn’t match with the collation of TempDB and you are comparing and/or joining on string columns, it may cause this collation conflict issue.
There are many ways to resolve this issue. However the best way as per me is to change to specify the collation of the string column of temp table to database default, so that it will inherit the collation of .user database.
CREATE TABLE #tmpTable(IDCol INT, StringCol VARCHAR(100) COLLATE DATABASE_DEFAULT)
Smart coding 🙂
Posted in Interview Questions, SQL Server | Tagged: collation conflict, SQL Journey, SQL Server, temp table | Leave a Comment »
Posted by Prashant on December 1, 2012
Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!
Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?
…and that inspired me to write this post.
We spend some time researching system tables and here is the findings to find list of tables used in a procedure.
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
INNER JOIN sysobjects o2
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
SELECT proc_name, table_name
WHERE row = 1
ORDER BY proc_name, table_name
Let me know if you have any better idea for this.
Posted in SQL Server | Tagged: list of tables in a procedure, SQL Server, SQLJourney, tabel dependency, tsql | Leave a Comment »