SQL Archives - Page 510 of 519 - SQL Authority with Pinal Dave

SQL SERVER – 2005 Take Off Line or Detach Database

April 25, 2007

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

EXEC sp_dboption N'mydb', N'offline', N'true' OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback…

SQL SERVER – TRIM() Function – UDF TRIM()

April 24, 2007

Pinal Dave

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

SQL Function, SQL Scripts

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL…

SQL SERVER – Six Properties of Relational Tables

April 24, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Relational tables have six properties: Values Are Atomic This property implies that columns in a relational table are not repeating group or arrays. The key benefit of the one value property is that it simplifies data manipulation logic. Such tables are referred to as being in the “first normal form”…

SQL SERVER – 2005 Collation Explanation and Translation

April 24, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Collation

Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like ‘Dictionary order, case-insensitive, uppercase preference’. I was confused for little while as I am used…

SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio

April 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Server Management Studio, SQL Utility

Following may be very simple to some and helpful to other type of question. I have seen this in my server log as well as this has been always first question in my Developer Team. Where is SQL SERVER 2005 Query Analyzer? SQL SERVER 2005 has combined Query Analyzer and…

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

April 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System Table

Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table. SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE…

SQL SERVER – Raid Configuration – RAID 10

April 22, 2007

Pinal Dave

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

Database, SQL Data Storage, SQL Utility

I get question about what configuration of redundant array of inexpensive disks (RAID) I use for my SQL Servers. The answer is short is: RAID 10. Why? Excellent performance with Read and Write. RAID 10 has advantage of both RAID 0 and RAID 1. RAID 10 uses all the drives…