How to handle a common default value? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to handle a common default value?

What’s the typical approach for creating a default whose value is fairly common such a Y for Yes or N for No? For example, I have some tables with the following colums and corresponding default values. Table1
– Location_ID – has a default of 1
– Employee_Status – has a default of 1
– Manager_Level – has a default of 1 Table 2
– Location_ID – has a default of 1
– Job_ID – has a default of 1 Since they all share a default of 1 should I create a Default called DF_One or should I create one default per column? If it’s one default per column then I assume I should use the Alter Table approach and not the Create Default (backward compatability) approach. Thanks, Dave
Creating a default of df_One should work fine based on the example you provided. I don’t see the need to create one default per column. Even though each column does have a different meaning they each share the commonality of multiple "Locations", "Employee Status", "Job Id" and "Manager Level" such that each column meaning would probably have "1" as there minimum "description". Dbu
One solution would be to use a UDF – at least if you’re not working in SQL 6.5 or 7.0. Also there is a very big catch. The UDF cannot take an input parameter other than a constant expression or a server function (GETDATE(), SUSER_SNAME(), etc.) – but obviously such parameters would return the same result if they are set within the UDF definition. The catch … Once you have created the UDF and set it as the default for columns, then you cannot alter the UDF: you must first drop all the column defaults that use the UDF, then alter the UDF, and finally recreate the column defaults. As long as you’re returning a constant value that will not change over the life of the database, then a UDF is the way to go. Finally, you could handle the default in an INSERT trigger – but note that this will also trigger any UPDATE triggers.
Adriaan, Can you give me an example? I don’t understand how a UDF would be more efficient then binding a default or creating a default constraint. I’ve never used a UDF in the manner you suggest and can’t find any examples in BOL suggesting to use a UDF to define default values. Thanks, Dave
You were asking what other options you would have, and a UDF certainly is one of them. It allows you to do a lookup instead of a fixed default value, for instance an office code for the current login: CREATE FUNCTION dbo.fnDefaultOfficeForLogin()
RETURNS VARCHAR(10)
BEGIN RETURN
(SELECT LO.Office FROM dbo.tblLoginOffice AS LO
WHERE LO.Login = SUSER_SNAME()) END Note that this type of query may only return a single row, so I’m assuming a unique index on the Login column of tblLoginOffice. You could of course do this in an insert trigger as well, but as I said that will trigger any update events – a default is evaluated before the triggers.
]]>