ASP.NET News from Blogger:
SQL Server Engine Tips
Total News: 39
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): --...
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: --...
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...
I came across a question in the relationalserver.performance newsgroup where a customer was wondering about the spools seen in a recursive query execution plan. The query is shown below: USE Northwind...
Bob Ward from PSS has a wonderful blog article that explains the details about the re-release of SQL Server 2005 SP2 and fixes posted later. This is a must read for anyone deploying SQL Server 2005 SP...
Check out the new Books Online search functionality online. The link below provides a scoped search of Books Online that returns a more precise and targeted result set. You can use it...
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 M...
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 d...
I would like to announce the blog from my team - SQL Server Performance Engineering. Feel free to visit our blog at http://blogs.msdn.com/sqlperf for your performance related questions and needs. Use ...
SQL Server 2005 Service Pack 2 has been released. You can find more information from the service pack 2 page. This service pack extends the functionality of SQL Server in many ways. The What's New doc...
SQL Server 2005 Service Pack 2 December CTP is available now for download. You can download it from: http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&D...
SQL Server 2005 Service Pack 2 (SP2) CTP has been released. You can provide feedback on the CTP release via http://connect.microsoft.com/sqlserver. The link for the various downloads is: http://www.mi...
Hello Everyone,
We are currently looking at top performance issues for various workloads and how we can improve those in next version of SQL Server. We are collecting feedback from various customer...
Hello Everyone,
We are currently looking at top performance issues for various workloads and how we can improve those in next version of SQL Server. We are collecting feedback from various customer so...
Hello Everyone, We are currently looking at top performance issues for various workloads and how we can improve those in next version of SQL Server. We are collecting feedback from various customer ...
Did you know that the catalog views in SQL Server 2005 exposes metadata for various objects in a database and at the server-level? This is the preferred method of accessing metadata. It is a much rich...
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 typ...
Below are links to two new whitepapers from the storage engine team. Please check it out to get better understanding of the new features in SQL Server 2005 and enhancements.
Working with TempDB...
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 SE...
SQL Server 2005 adds new capabilities to troubleshoot deadlocks. I talked about the new trace flag #1222 in a previous post. The blog entry below from the SQL Server Storage Engine team talks about th...
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 ...
I recently moved to the Central Performance Team in the SQL Server product group. My group is responsible for box-wide performance, benchmark and scalability. Given my new role I will be posting more ...
Did you know that SQL Server 2005 adds a new trace flag (-T1222) for deadlock trace output? This deadlock output has numerous improvements over older versions of SQL Server. For example, some of the e...
Index Tuning Wizard (ITW in SQL Server 2000) or Database Engine Tuning Advisor (DTA in SQL Server 2005) allow you to analyze a workload and make recommendations for the database based on the workload....
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 obviou...
Here is an opportunity to discuss about the top 5 features that you would like to see in the next version of SQL Server. It would be nice if you can include a sentence for each feature explaining why...
An MVP recently sent us an email asking how to use the INFORMATION_SCHEMA views to access temp tables. This MVP thought the session ID (spid) was needed to construct the suffix. Here was o...
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 des...
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 dyn...
Please post requests for topics or features that you would like to know about. This can be any of the SQL/TSQL language features or programmability in general. There are so many things to discuss abou...
With SQL Server 2005, there are new ways to obtain richer metadata in a database and more efficiently. We have introduced new catalog views that exposes all the metadata that SQL Server uses and ...
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 con...
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 disabl...
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...
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...
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 PERCEN...
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 interes...
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 ...
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 colu...
View Other bloggers