Owner: Journey to SQL Authority with Pinal Dave URL:http://blog.sqlauthority.com/ Join Date: Fri, 31 Aug 2007 23:20:20 -0500 Rating:0 Site Description: Pinalkumar Dave is the author of over hundreds of SQL Server articles. He has five years experience as a Director of Technology and Principal Database Administrator in MS SQL Server 2005/2000 and ColdFusion MX. He has a Masters of Science degree in Comput Site statistics:Click here
SQL SERVER - 2005 - Find Database Status Using sys.databases or DATABASEPROPERTYEX 2007-08-31 09:00:38 While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.
Following are main database status: (Reference: BOL DatabaseStatus
)
ONLINE
Database is available for access.
OFFLINE
Database is unavailable.
RESTORING
One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline.
RECOVERING
Database is being recovered.
RECOVERY PENDING
SQL
Server has encountered a resource-related error during recovery.
SUSPECT
At least the primary filegroup is suspect and may be damaged.
EMERGENCY
User has changed the database and set the status to EMERGENCY.
Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.
1) Using T-SQL (My Recommendation)
Example:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc Databas
SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS 2007-08-30 09:00:34 This article is written based on feedback I have received on SQL
SERVER - Cannot resolve collation conflict for equal to operation. Many reader asked me how to find collation of current database. There are two different ways to find out SQL Server database collation.
1) Using T-SQL (My Recommendation)
Run following Script in Query Editor
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
ResultSet:
SQLCollation
————————————
SQL_Latin1_General_CP1_CI_AS
2) Using SQL Server Management Studio
Refer the following two diagram to find out the SQL Collation.
Write Click on Database
Click on Properties
Reference : Pinal Dave (http://www.SQLAuthority.com)
SQL SERVER - Difference and Explanation among DECIMAL, FLOAT and NUMERIC 2007-08-29 09:00:52 The basic difference between Decimal and Numeric :
They are the exactly same. Same thing different name.
The basic difference between Decimal/Numeric and Float :
Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.
Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.
Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL
Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL DataTypes
SQL SERVER - Actual Execution Plan vs. Estimated Execution Plan 2007-08-28 09:00:40 I was recently invited to participate in big discussion on one of the online forum, the topic was Actual Execution
Plan vs. Estimated
Execution Plan. I refused to participate in that particular discussion as I have very simple but strong opinion about this topic. I always use Actual Execution Plan as it is accurate.
Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running query. I just run query and have correct and accurate Execution Plan.
Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M
You can use SSMS to include the execution plan as well.
Reference : Pinal Dave (http://www.SQL
Authority.com)
SQL SERVER - 2005 - Use Always Outer Join Clause instead of (*= and =*) 2007-08-27 09:00:38 Yesterday I wrote about how SQL
Server 2005 does not support named pipes. Today, my friend called me asking some of his query does not work. I asked him to send me the queries. I asked him to send me query. I noticed in his queries something, I have never practiced before and I never had any issue therefore.
Instead of using LEFT OUTER JOIN clause he was using *= and similarly instead of using RIGHT OUTER JOIN clause he was using =*. Once I replaced did necessary modification, queries run just fine.
I wish I can give you example of *= or =*, however, I had decided to not to add any more SQL Server 2000 code to this blog few months ago.
Reference : Pinal Dave (http://www.SQLAuthority.com), SQL Joins
Read more:Clause
SQL SERVER - 2005 - No Backup Support For Named Pipes 2007-08-26 09:00:43 While helping one of my DBA friend (who works in big company in LA) to upgrade SQL
Server 2000 to SQL Server 2005 I just found one thing, which I have not paid attention before.
SQL Server 2000 supported named pipe backup device. SQL Server 2005 does not support named pipe backup device, however SQL Server 2005 supports disk and tape devices.
I receive following question many times, I have answered this question earlier on this blog. I will still answer it again.
What is my preferred method of backup?
We use SAN with RAID 10 configuration. Some industry experts suggested we are overprotecting our database backup. Well, Better safe than sorrow.
Reference : Pinal Dave (http://www.SQLAuthority.com)
Read more:Support
SQL SERVER - FIX : Error : msg 2540 - The system cannot self repair this error 2007-08-25 09:00:17 SQL SERVER - FIX : Error : msg 2540 - The system cannot self repair this error
This is most annoying error. I have only faced this error twice so far. I solved this error restoring the database back up. Read here for additional help on SQL Backup And Restore. This error is occurs when database is in state when it can not be heal itself, i.e. corrupted metadata or corrupted important system database files.
Fix/WorkAround/Solution:
My prefered order to fix the problem.
1) Restored database from backup.
2) Run DBCC with repair option, which will not bring much favorable answer.
3) Check if there is any hard ware problem with hard disk or Raid control.
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL
Read more:SQL
SQL SERVER - 2005 - T-SQL Script to Attach and Detach Database 2007-08-24 09:00:39 Following script can be used to detach or attach database. If database is to be from one database to another database following script can be used detach from old server and attach to new server.
Process to move database :
--Step 1 : Detach Database
using following script
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks',
@keepfulltextindexfile=N'true'
GO
--Step 2 : Move Data files and Log files to new location
--Step 3 : Attach Database using following script
USE [master]
GO
CREATE DATABASE [AdventureWorks] ON
( FILENAME = N'C:DataAdventureWorks_Data.mdf' ),
( FILENAME = N'C:DataAdventureWorks_Log.ldf' )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N'AdventureWorks'
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [AdventureWorks].dbo.sp_changedbowner @loginame=N'sa',
@map=false
GO
Reference : Pinal Dave (http://www.SQL
Authority.com), SQL SERVER - 2005 Take Off Line or Detach Database
SQL SERVER - 2005 - Use of Non-deterministic Function in UDF - Find Day Difference Between Any Date and Today 2007-08-23 09:00:21 While writing few articles about SQL
Server DataTime I accidentally wrote User Defined Function (UDF), which I would have not wrote usually. Once I wrote this function, I did not find it very interesting and decided to discard it. However, I suddenly noticed use of Non-Deterministic function in the UDF.
I always thought that use of Non-Deterministic function is prohibited in UDF. I even wrote about it earlier SQL SERVER - User Defined Functions (UDF) Limitations. It seems like SQL Server 2005 either have removed this restriction or it is bug. I think I will not say this is bug but I will categorized it as feature.
GETDATE() is Non-Deterministic function and it can be used in User Defined Function in SQL Server 2005.
T-SQL Script to create UDF:
CREATE FUNCTION dbo.udf_DateDifference (@dtBeginDate datetime)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @rtMessage VARCHAR(100)
SET @rtMessage = 'Different between ' + CAST(@dtBeginDate AS VARCHAR(11)) + ' and ' +
CAST(getdate() AS VARCHAR(11)) Read more:Today
SQL SERVER - T-SQL Script to Insert Carriage Return and New Line Feed in Code 2007-08-22 09:00:09 Very simple and very effective. We use all the time for many reasons - formatting, while creating dynamically generated SQL
to separate GO command from other T-SQL, saving some user input text to database etc.
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of carriage return';
SET @strPrint = @strPrint + CHAR(13);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO
PRINT '---------------------------------'
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of new line feed';
SET @strPrint = @strPrint + CHAR(10);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO
ResultSet:
Example of carriage return
SQLAuthority.com
———————————
Example of new line feed
SQLAuthority.com
Reference : Pinal Dave (http://www.SQLAuthority.com)
Read more:Return
SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects 2007-08-21 09:00:39 Following quick tutorial demonstrates how to create T-SQL
script to copy complete database schema and all of its objects such as Stored Procedure
, Functions
, Triggers
, Tables
, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
Step 1 : Start
Step 2 : Welcome Screen
Step 3 : Select One or Multiple Database
If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)
Step 4 : Select database options
Step 5 : Select output option
Step 6 : Review Summary
Step 7 : Observe script generation process
Step 8 : Database object script generation completed in new query window
Reference : Pinal Dave (http://www.SQLAuthority.com), All images are protected by copyright owner SQLAuthority.com
SQLAuthority News - Principles of Simplicity 2007-08-20 09:00:49 Yesterday I came across Principles
of Simplicity
by Mads Kristensen. I think this is good write up and I enjoyed reading it. This are very generic and applies to all programming language and databases applications.
Principles of Simplicity by Mads Kristensen
1. Simplicity or not at all
Some developers tend to over-complicate a task and ends up writing too many classes to solve a simple problem.
2. Don’t build submarines
It’s a common fact that IT projects take longer than scheduled even if you schedule for delays.
3. Test when appropriate
Testing is one very important factor of the development cycle and there are many different tests to perform.
4. Be precise when naming methods
A method must have a name that tells exactly what it does.
5. Comment your code the simple way
Code commenting can be done in a myriad of ways, but there really is only one that keeps your code simple at the same time.
6. Steal borrow and simplify
We all use code pieces found on the Internet all the time.
SQL SERVER - Find Monday of the Current Week 2007-08-20 09:00:07 Very Simple Script which find Monday
of the Current
Week
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) MondayOfCurrentWeek
Reference :Pinal Dave (http://www.SQL
Authority.com)
SQLAuthority News - Book Review - Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes 2007-08-19 09:00:55 Sams Teach YourselfMicrosoftSQL
Server T-SQL in 10 Minutes (Sams Teach Yourself)
by Ben Forta
Link to Amazon - This is not affiliation link.
Short Review:
If T-SQL (Transact-Structured Query Language) is foreign tongue to you, after reading this book, you will speak T-SQL. This book is SQL Server
version of best-selling book Sams Teach Yourself SQL in 10 Minutes. This book teaches what a SQL developer must know methodically, systematically, and exactly. Anybody who are new to SQL Server and wants to learn most of T-SQL which can be implemented in short time in their application – BUY this book immediately.
Detail Review:
This is the one book I was awaiting eagerly. I claim that I am very experience Database Administrator and Database Developer, however, I have learned something new from this book of acclaimed author Ben Forta.
This book is focused on T-SQL only. It begins with simple data retrieval and continues to develop complex topics. It addresses various topics that are
SQL SERVER - Find Last Day of Any Month - Current Previous Next 2007-08-18 09:00:04 Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month
is similar question. I have received it many times and I enjoy answering it as well.
I have answered this question twice before here:
SQL
SERVER - Script/Function to Find Last Day of Month
SQL SERVER - Query to Find First and Last Day of Current
Month
Today, we will see the same solution again. Please use the method you find appropriate to your requirement.
Following script demonstrates the script to find last day of previous, current and next month.
--Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) LastDay_PreviousMonth
--Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) LastDay_CurrentMonth
--Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————&nda
SQL SERVER - 2005 - Explanation and Script for Online Index Operations - Create, Rebuild, Drop 2007-08-17 09:00:55 SQL Server 2005 Enterprise Edition supports online index operations. Index
operations are creating, rebuilding and dropping indexes.
The question which I receive quite often - what is online operation? Is online operation is related to web, internet or local network?
Online operation means when online operations are happening the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to database. In case of Online Indexing Operations, when Index operations (create, rebuild, dropping) are occuring they do not require exclusive access to database, they do not lock any database tables. This is major important upgrade in SQL Server from previous versions.
Previous versions of SQL Server required exclusive access to database, and database table were not accessible by other normal processes. This is the reason of time-outs and lock-outs when index operations are performed on SQL Server. I have seen many time Read more:SQL
SQLAuthority News - Subscribed to SQLAuthority Emails 2007-08-16 09:00:52 I have got many request about alert system when new post is published on this blog. I use feedburner email service, which sends email whenever new post is published on my blog. Many times, I update my post based on feedback from comments or news. If you want updated information, visit the blog.
Subscribe to SQLAuthority.com Email
Read more:Emails
SQL SERVER - 2008 - Book On-Line Link 2007-08-16 09:00:33 I am researching SQL
Server 2008. Those who are asking me questions about SQL Server 2008, please refer following link. I will post my tutorials and articles very soon.
Here is SQL Server 2008 Book Online link.
SQL SERVER - 2005 - Difference and Similarity Between NEWSEQUENTIALID() and NEWID() 2007-08-16 09:00:06 NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier. NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order.
Let us see example first demonstrating both of the function.
USE AdventureWorks;
GO
--Create Test Table for with default columns values
CREATE TABLE TestTable
(NewIDCol uniqueidentifier DEFAULT NewID(),
NewSeqCol uniqueidentifier DEFAULT NewSequentialID())
--Inserting five default values in table
INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
--Test Table to see NewID() is random
--Test Table to see NewSequentialID() is Incremental Sequence
SELECT * FROM TestTable
--Clean up database with droping column
DROP TABLE TestTable
GO
ResultSet:
NewIDCol NewSeqCol
------------------------------------ ------------------------------------
D Read more:Similarity
, SQL
SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE 2007-08-15 09:00:51 Following three questions are many time asked on this blog.
How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?
There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.
Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
--Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
--INSERT Read more:SQL
SQL SERVER - Stored Procedure to Know Database Access Permission to Current User 2007-09-23 09:00:49 Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable and provides accurate information.
Sytanx:
EXEC sp_MShasdbaccess
GO
ResultSet: ( I have listed only one column)
AdventureWorks
AdventureWorksDW
master
model
msdb
MyDB
ReportServer
ReportServerTempDB
tempdb
Reference : Pinal Dave (http://www.SQL
Authority.com)
Read more:Access
, Current
, Database
, Permission
, Procedure
SQL SERVER - 2005 - Version Information and Additional Information - Extended Stored Procedure xp_msver 2007-09-22 09:00:35 I was glad when I discovered this Extended
Stored Procedure
myself. I always used different syntax to retrieve server information. Many of information I was looking up using system information of the windows operating system.
Syntax:
EXEC xp_msver
ResultSet:
Index Name Internal_Value Character_Value
------ -------------------------------- -------------- -------------------------------------
1 ProductName NULL Microsoft SQL
Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.
SQL SERVER - 2005 - Multiple Language Support 2007-09-21 09:00:53 SQL Server supports multiple languages. Information about all the languages are stored in sys.syslanguages system view. You can run following script in Query Editor and see all the information about each language. Information about Months and Days varies for each language.
Syntax:
SELECT Alias, *
FROM sys.syslanguages
ResultSet: (* results not included)
Alias
————–
English
German
French
Japanese
Danish
Spanish
Italian
Dutch
Norwegian
Portuguese
Finnish
Swedish
Czech
Hungarian
Polish
Romanian
Croatian
Slovak
Slovenian
Greek
Bulgarian
Russian
Turkish
British English
Estonian
Latvian
Lithuanian
Brazilian
Traditional Chinese
Korean
Simplified Chinese
Arabic
Thai
Reference : Pinal Dave (http://www.SQLAuthority.com)
Read more:Multiple
, SQL
, Support
SQL SERVER - FIX : ERROR : 3260 An internal buffer has become full 2007-09-20 09:00:13 ERROR : 3260 An internal buffer has become full
The reason I have picked to write about this error is because we have encountered this error many times in one of our older server.
Fix/WorkAround/Solution:
We were not able to absolutely reduce this error but following changes helped.
1) Rebooted server if error is happening frequently.
2) Increased RAM to Server.
3) Increased RAM allocation to SQL
Server application.
Reference : Pinal Dave (http:\www.SQLAuthority.com)
SQL SERVER - Rename Database to New Name Using Stored Procedure by Changing to Single User Mode 2007-09-19 09:00:20 In my organization we rename the database on development server when are refreshing the development server with live data. We save the old database with new name and restore the database from live with same name. If developer/Jr. DBA have not saved the SQL
Script from development server, he/she can go back to old Server and retrieve the script.
There are few interesting facts to note when the database is renamed.
When renamed the database, filegroup name or filename (.mdf,.ldf) are not changed.
User with SA privilege can rename the database with following script when the context of the database is master database.
USE master;
GO
EXEC sp_dboption AdventureWorks, 'Single User', True
GO
EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'
GO
EXEC sp_dboption AdventureWorks, 'Single User', False
GO
Reference : Pinal Dave (http://www.SQLAuthority.com)
Read more:Changing
, Database
, Procedure
, Rename
SQL SERVER - UDF - Validate Positive Integer Function - Validate Natural Integer Function 2007-09-18 09:00:42 Few days ago I wrote SQL
SERVER - UDF - Validate
Integer Function. It was very interesting to write this and developers at my company started to use it. One Jr. DBA modified this function to validate only positive integers. I will share this with everybody who are interested in similar functionality.
Code:
CREATE FUNCTION [dbo].[udf_IsNatural
]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND @Number NOT LIKE '%-%')
SET @Ret = 1
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
Example:
SELECT '999' TestValue,dbo.udf_IsNumeric('999') NumericTest;
SELECT '-999' TestValue,dbo.udf_IsNumeric('-999') NumericTest;
SELECT 'abc' TestValue,dbo.udf_IsNumeric('abc') NumericTest;
SELECT '9+9' TestValue,dbo.udf_IsNumeric('9+9') NumericTest;
SELECT '$9.9' TestValue,dbo.udf_IsNumeric('$9.9') NumericTest;
SELECT 'SQLAuthority' Tes Read more:Positive
SQLAuthority News - NASDAQ Uses SQL Server 2005 - Reducing Costs through Better Data Management 2007-09-17 09:00:37 I just came across PDF published by Microsoft to promote SQL
Server 2005. I find few things very interesting. I will list them here.
NASDAQ
NASDAQ, the world’s first electronic stock market replaced its aging mainframe computers with Microsoft® SQL Server
™ 2005 on two 4-node clusters to support its Market Data Dissemination System (MDDS).
Every trade processed in the NASDAQ marketplace goes through the system with Microsoft® SQL Server™ 2005 handling some 5,000 transactions per second at market open.
The system also responds to about 10,000 queries a day and is able to handle real-time queries against data without slowing the database down.
SQL Server 2005 Features
Scalability
Availability
Manageability
Built-in Business Intelligence
Management
.htm">Data Management
Capability: Business Benefits
Faster results
Better decisions
Trusted Platform
Total cost of ownership
Reference : ReducingCosts
through Better Data Management
SQL SERVER - Difference Between UPDATE and UPDATE() 2007-09-17 09:00:05 What is the difference between UPDATE and UPDATE()?
UPDATE is syntax used to update the database tables or database views.
USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO
UPDATE() is used in triggers to check update/insert to the database tables or database views.
Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() is used anywhere inside the body of a Transact-SQL
INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
USE AdventureWorks ;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE
AS
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO
Please read additional details on BOL - UPDATE, BOL - UPDATE()
Reference : Pinal Dave (http://www.SQLAuthority.com)
SQLAuthority News - Active Directory Integration Sample Script 2007-09-16 09:00:35 A sample script that enables you to extract a list of computer names from your custom SQL Server database and add them to an ActiveDirectory
security group. The security group can then be referenced in the Agent Assignment and Failover Wizard to automate agent assignments to Management Servers.
1. Queries customer SQL asset database.
2. Populates custom security group with computer accounts of computers returned by the SQL query.
Download from MSDN
Reference : Text from MSDN
Read more:Active Directory
, Sample
Recommend Site To A Friend
