Tag: how to

How to Create a Schema Bound Stored Procedure in SQL Server

Posted on by Ian

If you’ve ever created a schema bound UDF, you’ll know that schema binding it is just a matter of adding WITH SCHEMABINDING to your definition. The same applies when you create a schema bound view.

Stored procedures are a bit different.

Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound. You can’t create a natively compiled stored procedure without schema binding it.

But if you try to schema bind a regular (non-natively compiled) procedure, you’ll get an error.

Continue reading →

How to Check if a T-SQL UDF is Schema Bound (Even When it’s Encrypted)

Posted on by Ian

Schema binding an object such as a user-defined function (UDF) is considered good practice, as it prevents changes being done to any objects that it references that could inadvertently break the function.

You can schema bind a user-defined function at the time you create it, or you can alter later on.

Normally, you can check if a UDF is schema bound in SQL Server by viewing its definition. You can usually do this via the GUI by selecting “Script as Create” or similar.

You can also do it using T-SQL by selecting the definition column of the sys.sql_modules system catalog view.

But this will only work if the UDF isn’t encrypted.

However, there is another column in the sys.sql_modules view that serves our purpose whether the UDF is encrypted or not: is_schema_bound

Continue reading →

How to Encrypt a User-Defined Function in SQL Server

Posted on by Ian

When creating a user-defined function in SQL Server, you have the option of encrypting it.

To create a user-defined function with T-SQL, you use the CREATE FUNCTION syntax. To encrypt it, you add the WITH ENCRYPTION argument.

You can also use the same argument to encrypt an existing function when using ALTER FUNCTION.

Continue reading →

How to Tell if a Computed Column is Deterministic in SQL Server

Posted on by Ian

When you create a computed column in SQL Server, the expression you use for the column will either be deterministic or nondeterministic. This can have implications, such as whether or not you can use it in an index or flag it as “persisted”.

A deterministic column is one that will return the same value for a specific set of input values and given the same state of the database. A nondeterministic column can return a different value even when given the same input even if the database state remains the same. For example, a function that returns the current date is nondeterministic, because it will return a different value each day.

You can use the COLUMNPROPERTY() function with the IsDeterministic argument to find out whether or not a computed column is deterministic.

Continue reading →

How to Check if a Computed Column is “Persisted” in SQL Server

Posted on by Ian

When you create a computed column in SQL Server, you have the option of flagging it as “persisted”. A persisted computed column is one that is physically stored in the table. If you don’t specify that it’s persisted, then the column’s value will be calculated each time you run a query against it.

You can query the sys.computed_columns system catalog view to find out whether a computed column is marked as persisted.

Continue reading →

Change an Existing Column to a Computed Column in SQL Server (T-SQL Example)

Posted on by Ian

This article demonstrates how to use T-SQL to change an existing column to a computed column in SQL Server.

A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.

Continue reading →

Add a Computed Column to an Existing Table in SQL Server

Posted on by Ian

This article demonstrates how to use T-SQL to add a computed column to an existing table in SQL Server.

A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.

Continue reading →