All Articles - SQL Authority with Pinal Dave

SQL SERVER – Recompile All The Stored Procedure on Specific Table

June 29, 2007

I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles…

SQL SERVER – 2005 Improvements in TempDB

June 28, 2007

Pinal Dave

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

Best Practices, SQL Download, SQL TempDB

Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000 Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value” everytime. E.g. For INSERT it does not log after value on log as that will…

SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

June 27, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Computer Network, SQL Scripts, SQL Utility

In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure. The questions are 1) What is the usage of xp_cmdshell and 2) How to execute BAT file using T-SQL? I really like the follow up questions of my posts/articles. Answer is xp_cmdshell can execute…

SQL SERVER – 2005 List All Tables of Database

June 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table

This is very simple and can be achieved using system table sys.tables. USE YourDBName GO SELECT * FROM sys.Tables GO This will return all the tables in the database which user have created. Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

SQL SERVER – Explanation and Example Four Part Name

June 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

What is four part name? Explanation : ServerName.DatabaseName.DatabaseOwner.TableName Example : localhost.AdventureWorks.Person.Contact Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – Repeate String N Times Using String Function REPLICATE

June 25, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

I came across this SQL String Function few days ago while searching for Database Replication. This is T-SQL Function and it repeats the string/character expression N number of times specified in the function. SELECT REPLICATE( ' https://blog.sqlauthority.com/ ' , 9 ) This repeats the string https://blog.sqlauthority.com/ to 9 times in…

SQLAuthority News – Book Review – Microsoft(R) SQL Server 2005 Unleashed (Paperback)

June 24, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

SQLAuthority Book Review

SQLAuthority.com Book Review : Microsoft(R) SQL Server 2005 Unleashed (Paperback) by Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein Link to book on Amazon Short Review : SQL Server 2005 Unleashed is focused on Database Administration and day-to-day administrative management aspects of SQL Server. All the chapters of this…

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

June 24, 2007

Pinal Dave

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.