SQL Server Engine Tips

Guidelines, Best Practices, TSQL and SQL Programming Tips & Tricks.

Browse by Tags

Tagged Content List
  • Blog Post: Differences between ISNULL and COALESCE

    I came across a question in the SQL Server MVP newsgroup recently about ISNULL and COALESCE usage. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences...
  • Blog Post: Use of SCHEMABINDING option for TSQL UDFs can improve performance in SQL Server 2005...

    SQL Server 2005 has new optimization logic to use the SCHEMABINDING option to derive certain properties about the TSQL UDF. This can greatly improve performance of queries that use scalar UDFs in a SELECT statement for example. You can read all about it at the blog entry below from one of our development...
  • Blog Post: Determining optimal MAXDOP setting from TSQL in SQL Server 2005

    For optimal performance of multi-processor installations, we recommend that the MAXDOP setting remain equal to the number of physical processors that are being used. For example, if the system is configured for two physical processors and four logical processors, MAXDOP should be set to 2. This is documented...
  • Blog Post: Renaming sa account in SQL Server 2005

    SQL Server 2005 introduces a revamped security model including users and schema separation & more granular grantable permissions. ALTER LOGIN statement in SQL Server 2005 allows you to disable the sa account and rename it. This is a good security measure to take on SQL Servers running in mixed authentication...
  • Blog Post: Getting SQL Server registry settings via SQLCLR table-valued user-defined function...

    SQL Server stores several configuration values like data path root, program directory, default domain etc in the registry. These values can be obtained by using the SMO Settings object but you can use it only from the client side. SMO is alo not supported in SQLCLR so it is not possible to leverage the...
  • Blog Post: Feedback requested on database mirroring...

    Database mirroring is one of the new high availability features in SQL Server 2005. More details on the database mirroring feature can be found at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx . Configuring security for database mirroring involves the process of enabling each instance...
  • Blog Post: Ordering guarantees in SQL Server...

    Ordering guarantees of queries in various context is a common source of confusion. For example, a common workaround to make the results from querying a view ordered is to introduce TOP 100 PERCENT and ORDER BY in the view definition. But this however does not guarantee order in the actual results sent...
  • Blog Post: Using ANSI SQL style DECLARE CURSOR statement in UDF...

    Today I encountered a post in the Microsoft Technical forums where a user was trying to use the ANSI SQL style DECLARE CURSOR statement which will raise error # 1049 in SQL Server. This behavior is true in SQL Server 2000. Here is a code sample that demonstrates the problem: create function cur_test...
  • Blog Post: Performing batched updates...

    Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work...
Page 1 of 1 (9 items)