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 = ‘abcedf012439’; select cast(” as xml).value(‘xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )’, ‘varbinary(max)’) from…

5

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 cast(N” as xml).value(‘xs:base64Binary(sql:variable("@str"))’, ‘varbinary(20)’); — Convert binary…

2

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 bottleneck on their system, and if a…

1

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 in each database or plan cache so…

2

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…

10

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 assumes a continuous distribution model. It takes a percentile value…

7

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…

0

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…

10

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 proved…

12

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 in the KB article:   http://support.microsoft.com/default.aspx/kb/322385  …

12