SQL Server Engine Tips

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

Browse by Tags

Tagged Content List
  • Blog Post: Converting from hex string to varbinary and vice versa

    Converting hexadecimal values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005. The code samples below show how to perform the conversion(s): -- Convert hexstring value in a variable to varbinary: declare @hexstring varchar(max); set @hexstring ...
  • Blog Post: Converting from Base64 to varbinary and vice versa

    Converting Base64 values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005 onwards. The code samples below show how to perform the conversion: -- Convert Base64 value in a variable to varbinary: declare @str varchar(20); set @str = '3qAAAA=='; select...
  • Blog Post: SQL Server 2005 Performance Dashboard Reports

    The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SQL Server 2005 SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current...
  • Blog Post: OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

    SQL Server 2005 SP2 has an important enhancement to OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. I will first describe the old functionality to give some context and demonstrate how the new features help a lot. Please note that the examples uses DMVs that looks at metadata...
  • Blog Post: DATALENGTH optimizations for LOB data types...

    DATALENGTH function in TSQL can be used to find the actual length in bytes of the data in a specific value. The value can be any of the data types. It is often used to determine length of LOB data type columns (text, ntext, image, varchar(max), nvarchar(max) and varbinary(max)) in a table. One of the...
  • Blog Post: Oracle's PERCENTILE_CONT implementation using SQL Server 2005 analytic functions...

    I saw an interesting question today in the MSDN Transact-SQL forum about implementing PERCENTILE_CONT analytic function that is available in Oracle in SQL Server. The function description as noted in the Oracle docs is below: The PERCENTILE_CONT function is an inverse distribution function that...
  • Blog Post: Renaming logins in SQL Server 2005...

    Did you know that SQL Server 2005 supports renaming logins? This can be done via ALTER LOGIN statement. I posted a sample about renaming sa login and disabling it before. But it is probably not obvious if you can do it for all logins. Ex: ALTER LOGIN sqluser WITH NAME = [newuser]; You can also...
  • Blog Post: SQL Server 2005 features that are dependent on Windows Server 2003...

    The question about what features are supported by SQL Server 2005 running on Windows Server 2003 comes up quite often. So below are some of the features that are depends on the OS and brief description about them. 1. Password policy/expiration check for SQL logins - CREATE LOGIN is a new DDL for creating...
  • Blog Post: Find Top N costly query plans in adhoc batches or modules...

    I encountered a problem about a query not using a particular indexed view in SQL Server 2005. To investigate this issue, I figured that I would go about writing a query using the execution related dynamic dynamic management views to get the cost of the query that should have used the indexed view. This...
  • 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: Direct dependencies on a column...

    I came across a question recently about discovering the constraints that are dependent on a column like CHECK constraint, defaults and so on. In trying to solve that problem, I came across few interesting solutions that uses some new relational features and the catalog views available in SQL Server 2005...
  • Blog Post: OUTPUT clause in INSERT/UPDATE/DELETE statements

    SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions...
  • Blog Post: Test linked server connection settings...

    Have you ever encountered a scenario in SQL Server 2000 where you want to test the connectivity of a linked server configuration via TSQL? This was not possible easily and best handled from client side. But we have now a system stored procedure " sp_testlinkedserver " in SQL Server 2005 that allows you...
  • 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 (17 items)