SQL Performance Archives - Page 94 of 100 - SQL Authority with Pinal Dave

SQL SERVER – 2005 Row Overflow Data Explanation

June 23, 2007

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server Security, SQL String

In SQL Server 2000 and SQL Server 2005 a table can have a maximum of 8060 bytes per row. One of my fellow DBA said that he believed that SQL Server 2000 had that restriction but SQL Server 2005 does not have that restriction and it can have a row…

SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

June 17, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Computer Network, DBA, SQL Index, SQL Scripts

This issues was brought to me by our Sr. Network Engineer. While running operating system level de-fragmentation using either windows de-fragmentation or third party tool it always skip all the MDF file and never de-fragment them. He was wondering why this happens all the time. The reason MDF file are…

SQL SERVER – Explanation SQL SERVER Hash Join

June 14, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Joins

Hash Join works with large data set. I have seen this join used many times in data warehouses applications as well as data mining algorithms. While its characteristics are similar to merge join it does not required ordered result set to join. Hash join requiresequijoin predicate to join tables. Equijoin…

SQL SERVER – Fix : Error 8629 The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

June 13, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Server

Error : 8629 The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

SQL SERVER – Recovery Models and Selection

June 13, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Coding Standards, SQL Data Storage, SQL Scripts, SQL Server Security

SQL Server offers three recovery models: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable and determines whether and how transaction logs can be backed up. Select Simple Recovery Model if: * Your data is not critical. * Losing all transactions since…

SQL SERVER – LEN and DATALENGTH of NULL Simple Example

June 12, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL NULL, SQL Scripts

Simple but interesting – In recent survey I found that many developers making this generic mistake. I have seen following code in periodic code review. (The code below is not actual code, it is simple sample code) DECLARE @MyVar VARCHAR(10) SET @MyVar = NULL IF (LEN(@MyVar) = 0) … I…

SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

May 26, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily. I frequently run following query to verify that all the Jr. DBAs are…