SQL Scripts Archives - Page 300 of 318 - SQL Authority with Pinal Dave

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

June 24, 2007

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

DBA, SQL Index, SQL Scripts, SQL Server DBCC, SQL Server Security, SQL Utility

Index Fragmentation: When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new data, SQL Server must move half of the data from the full page to a new page. The new page that…

SQL SERVER – 2005 Row Overflow Data Explanation

June 23, 2007

Pinal Dave

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 – Explanation and Comparison of NULLIF and ISNULL

June 22, 2007

Pinal Dave

SQL Tips and Tricks

DBA, SQL Function, SQL NULL, SQL Scripts, SQL Server

Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

SQL SERVER – Retrieve Current DateTime in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

June 21, 2007

Pinal Dave

SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Server

There are three ways to retrieve the current datetime in SQL SERVER.
CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER – Find Length of Text Field

June 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

To measure the length of VARCHAR fields the function LEN(varcharfield) is useful. To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field. Example: SELECT DATALENGTH(yourtextfield) AS TEXTFieldSize Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – Delay Function – WAITFOR clause – Delay Execution of Commands

June 18, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts

Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row. This is very useful. Every day when I restore the database to backup server for reports post processing, I use…

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…