I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage
First the advantages
Advantage
When scripting out a database you don’t have to generate if exists.....drop statements
When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)
Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.
Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either
So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Wednesday, May 07, 2008
Sunday, June 03, 2007
Did You Know SQL Server Has A Black Box Like An Airplane?
Paul Randal writes:
Read here how to turn it on: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx
Cross-posted from SQLBlog! - http://www.sqlblog.com/
"Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an
aircraft flight-recorder, which I'd never heard of. It's an internal trace that
has the last 5MB of various trace events and it's dumped to a file when SQL
Server crashes. This can be really useful if you're troubleshooting an issue
that causing SQL Server to crash or someone or something is telling SQL Server
to shutdown and its unclear who or what is doing it."
Read here how to turn it on: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx
Cross-posted from SQLBlog! - http://www.sqlblog.com/
Monday, May 14, 2007
Make Your Case Sensitive Searches 1000 Times Faster
I had an case sensitive update query that ran in about 9 minutes. Since it was a case sensitive query it did an index scan not an index seek.
Once I modified my WHERE clause the update took a little less than 3 seconds
Let's get started and see what I did
First create this table
CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))
INSERT #CaseSensitiveSearchTemp VALUES('A')
INSERT #CaseSensitiveSearchTemp VALUES('B')
INSERT #CaseSensitiveSearchTemp VALUES('C')
INSERT #CaseSensitiveSearchTemp VALUES('D')
INSERT #CaseSensitiveSearchTemp VALUES('E')
INSERT #CaseSensitiveSearchTemp VALUES('F')
INSERT #CaseSensitiveSearchTemp VALUES('G')
INSERT #CaseSensitiveSearchTemp VALUES('H')
Now we will insert some lowercase characters
INSERT #CaseSensitiveSearchTemp
SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp
Now we will create our real table which will have 65536 rows
CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))
We will do a couple of cross joins to generate the data for our queries
INSERT CaseSensitiveSearch
SELECT t1.val + t2.val + t3.val + t4.val
FROM #CaseSensitiveSearchTemp t1
CROSS JOIN #CaseSensitiveSearchTemp t2
CROSS JOIN #CaseSensitiveSearchTemp t3
CROSS JOIN #CaseSensitiveSearchTemp t4
This should give you 65536 rows
SELECT * FROM CaseSensitiveSearch
Create an index on the table
CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)
This is how you do a case sensitive search
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)
run these 2 queries in one batch by highlighting them both and hitting F5
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'ABCD'
Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster
Now try it with a lowercase a
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'aBCD'
You see it all works without a problem, the correct result is returned
Once I modified my WHERE clause the update took a little less than 3 seconds
Let's get started and see what I did
First create this table
CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))
INSERT #CaseSensitiveSearchTemp VALUES('A')
INSERT #CaseSensitiveSearchTemp VALUES('B')
INSERT #CaseSensitiveSearchTemp VALUES('C')
INSERT #CaseSensitiveSearchTemp VALUES('D')
INSERT #CaseSensitiveSearchTemp VALUES('E')
INSERT #CaseSensitiveSearchTemp VALUES('F')
INSERT #CaseSensitiveSearchTemp VALUES('G')
INSERT #CaseSensitiveSearchTemp VALUES('H')
Now we will insert some lowercase characters
INSERT #CaseSensitiveSearchTemp
SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp
Now we will create our real table which will have 65536 rows
CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))
We will do a couple of cross joins to generate the data for our queries
INSERT CaseSensitiveSearch
SELECT t1.val + t2.val + t3.val + t4.val
FROM #CaseSensitiveSearchTemp t1
CROSS JOIN #CaseSensitiveSearchTemp t2
CROSS JOIN #CaseSensitiveSearchTemp t3
CROSS JOIN #CaseSensitiveSearchTemp t4
This should give you 65536 rows
SELECT * FROM CaseSensitiveSearch
Create an index on the table
CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)
This is how you do a case sensitive search
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)
run these 2 queries in one batch by highlighting them both and hitting F5
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'ABCD'
Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster
Now try it with a lowercase a
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'aBCD'
You see it all works without a problem, the correct result is returned
Wednesday, April 11, 2007
Does Anyone Use ~ (Bitwise NOT)?
I have Used ¦ (Bitwise OR) and & (Bitwise AND) but I have never used ~ (Bitwise NOT)
Lets' see what it does
SELECT ~170
this returns -171, OK Fair enough.
SELECT ~convert(tinyint,170)
this returns 85, mmmmmmm
Here is some more interesting stuff
DECLARE @i int, @i2 tinyint,@i3 smallint, @i4 bigint
SELECT @i =170,@i2 = 170,@i3 =170,@i4 = 170
SELECT ~@i int, ~@i2 tinyint,~@i3 smallint, ~@i4 bigint
output
-171 85 -171 -171
So for the tinyint it is 85, for the other datatypes it is -171
What does BOL say?
Remarks
The bitwise ~ operator performs a bitwise logical NOT for the expression, taking each corresponding bit. The bits in the result are set to 1 if one bit (for the current bit being resolved) in expression has a value of 0; otherwise, the bit in the result is cleared to a value of 1.
The ~ bitwise operator can be used only on columns of the integer data type category.
Important
When performing any kind of bitwise operation, the storage length of the expression used in the bitwise operation is important. It is recommended that you use the same number of bytes when storing values. For example, storing the decimal value of 5 as a tinyint, smallint, or int produces a value stored with different numbers of bytes. tinyint stores data using 1 byte, smallint stores data using 2 bytes, and int stores data using 4 bytes. Therefore, performing a bitwise operation on an int decimal value can produce different results as compared to a direct binary or hexidecimal translation, especially when the ~ (bitwise NOT) operator is used. The bitwise NOT operation may occur on a variable of a shorter length that, when converted to a longer data type variable, may not have the bits in the upper 8 bits set to the expected value. It is recommended that you convert the smaller data type variable to the larger data type, and then perform the NOT operation on the result.
There you have it.
Lets' see what it does
SELECT ~170
this returns -171, OK Fair enough.
SELECT ~convert(tinyint,170)
this returns 85, mmmmmmm
Here is some more interesting stuff
DECLARE @i int, @i2 tinyint,@i3 smallint, @i4 bigint
SELECT @i =170,@i2 = 170,@i3 =170,@i4 = 170
SELECT ~@i int, ~@i2 tinyint,~@i3 smallint, ~@i4 bigint
output
-171 85 -171 -171
So for the tinyint it is 85, for the other datatypes it is -171
What does BOL say?
Remarks
The bitwise ~ operator performs a bitwise logical NOT for the expression, taking each corresponding bit. The bits in the result are set to 1 if one bit (for the current bit being resolved) in expression has a value of 0; otherwise, the bit in the result is cleared to a value of 1.
The ~ bitwise operator can be used only on columns of the integer data type category.
Important
When performing any kind of bitwise operation, the storage length of the expression used in the bitwise operation is important. It is recommended that you use the same number of bytes when storing values. For example, storing the decimal value of 5 as a tinyint, smallint, or int produces a value stored with different numbers of bytes. tinyint stores data using 1 byte, smallint stores data using 2 bytes, and int stores data using 4 bytes. Therefore, performing a bitwise operation on an int decimal value can produce different results as compared to a direct binary or hexidecimal translation, especially when the ~ (bitwise NOT) operator is used. The bitwise NOT operation may occur on a variable of a shorter length that, when converted to a longer data type variable, may not have the bits in the upper 8 bits set to the expected value. It is recommended that you convert the smaller data type variable to the larger data type, and then perform the NOT operation on the result.
There you have it.
Sunday, February 25, 2007
Failure Trends in a Large Disk Drive Population
The Google engineers published a paper on Failure Trends in a Large Disk Drive Population. Based on a study of 100,000 disk drives over 5 years they found some interesting stuff:
Six percent, that is higher than I expected. I must say (and I am knocking on wood as I write this) that I only saw a drive die once (within a month of deploying) on a blade server. The only major problem I had was when consulting for a client in NYC. They had a SQL Server box which was running for 2 years without a problem. We upgraded the machine to an active/passive cluster and a week later the motherboard died (downtime 20 seconds ;-) ), talking about good timing.....
So what failure rates do you see? Does stuff break down a lot?
While drive manufacturers often quote yearly failure rates below 2%,
user studies have seen rates as high as 6%.
We find, for example, that after their first scan error, drives are 39
times more likely to fail within 60 days than drives with no such errors. First
errors in reallocations,offline reallocations, and probational counts are also
strongly correlated to higher failure probabilities.
Six percent, that is higher than I expected. I must say (and I am knocking on wood as I write this) that I only saw a drive die once (within a month of deploying) on a blade server. The only major problem I had was when consulting for a client in NYC. They had a SQL Server box which was running for 2 years without a problem. We upgraded the machine to an active/passive cluster and a week later the motherboard died (downtime 20 seconds ;-) ), talking about good timing.....
So what failure rates do you see? Does stuff break down a lot?
Thursday, February 22, 2007
How To Find Out Which Columns Have Defaults And What Those Default Values Are
Okay so many many moons ago you created a bunch of tables and those tables have columns of course. You want to know how to find the columns that have defaults.
There are a couple of ways to do this
Below is a list:
1 INFORMATION_SCHEMA.COLUMNS view (2000 and 2005)
2 sysobjects,syscolumns and syscomments (2000 only)
3 sys.default_constraints and sys.sysobjects (2005 only)
4 sp_help (2000 only)
So let's get started with some code
CREATE TABLE blah(id INT DEFAULT 0,
SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586C
Instead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.
DROP TABLE blah
CREATE TABLE blah(id INT,SomeDate DATETIME)
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_ID_0
DEFAULT 0 FOR id
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_SomeDate_Now
DEFAULT CURRENT_TIMESTAMP FOR SomeDate
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
And last we have sp_help
You can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )
Execute the following
sp_help 'blah'
The defaults will be in the last resultset (the one where the first column name = constraint_type)
And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not
There are a couple of ways to do this
Below is a list:
1 INFORMATION_SCHEMA.COLUMNS view (2000 and 2005)
2 sysobjects,syscolumns and syscomments (2000 only)
3 sys.default_constraints and sys.sysobjects (2005 only)
4 sp_help (2000 only)
So let's get started with some code
CREATE TABLE blah(id INT DEFAULT 0,
SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586C
Instead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.
DROP TABLE blah
CREATE TABLE blah(id INT,SomeDate DATETIME)
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_ID_0
DEFAULT 0 FOR id
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_SomeDate_Now
DEFAULT CURRENT_TIMESTAMP FOR SomeDate
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
And last we have sp_help
You can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )
Execute the following
sp_help 'blah'
The defaults will be in the last resultset (the one where the first column name = constraint_type)
And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not
Wednesday, February 14, 2007
How Is SQL Server Books On Line Created?
Buck Woody one of the Technical Writers in the SQL Product Group has started a blog about documentation. His first post SQL Server Books Online explains how Books On Line is created. Did you know that Books Online has over 58,000 pages of content?
Friday, February 09, 2007
Use A Combination OF NULLIF and COALESCE TO Display A Custom Value
Let's you inherit a table with a column that can contain blanks, empty strings or NULL values. I am saying inherit because surely you would have a constraint on the column that wouldn't allow those values to begin with right? For all the values that are '',' ' or NULL you want to display 'N/A'.
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
Thursday, February 01, 2007
Give Me One Good Reason Why You Would Store Documents In The Database?
Why would you ever store PDF, Doc or Excel files in the database? What is the point? I don't see any advantages only disadvantages
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.
So here is the question:
Would you store images/documents/spreadsheets in the Database and why?
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.
So here is the question:
Would you store images/documents/spreadsheets in the Database and why?
Tuesday, January 23, 2007
Microsoft SQL Server Database Publishing Wizard 1.0,SharePoint Server 2007 SDK And Windows SharePoint Services v3 SDK Available For Download
Microsoft SQL Server Database Publishing Wizard 1.0
SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.
Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.
SharePoint Server 2007 SDK
The Microsoft Office SharePoint Server 2007 Software Development Kit (SDK) contains conceptual overviews, programming tasks, code samples, references, and an Enterprise Content Management (ECM) starter kit to guide you in developing solutions based on Microsoft Office SharePoint Server 2007.
Windows SharePoint Services v3 SDK
The Windows SharePoint Services 3.0 software development kit (SDK) contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Windows SharePoint Services 3.0.
SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.
Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.
SharePoint Server 2007 SDK
The Microsoft Office SharePoint Server 2007 Software Development Kit (SDK) contains conceptual overviews, programming tasks, code samples, references, and an Enterprise Content Management (ECM) starter kit to guide you in developing solutions based on Microsoft Office SharePoint Server 2007.
Windows SharePoint Services v3 SDK
The Windows SharePoint Services 3.0 software development kit (SDK) contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Windows SharePoint Services 3.0.
Monday, January 22, 2007
Check If Auto Update Statistics Is Enabled With DATABASEPROPERTY
How do you check if auto update statistics is enabled on your database? It is pretty easy to check that, you can use the DATABASEPROPERTY function
Run the following line of code
SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics
If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled
Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code
DECLARE @v VARCHAR(55)
SELECT @v = 'pubs'
SELECT
DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
DATABASEPROPERTY(@v,'Version') AS Version
So what do all these values mean? Here is a list of all the properties
IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsBulkCopy
Database allows nonlogged operations.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsDboOnly
Database is in DBO-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsDetached
Database was detached by a detach operation.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsInLoad
Database is loading.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsInRecovery
Database is recovering.
1 = TRUE
0 = FALSE
NULL1 = Invalid input
IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsNotRecovered
Database failed to recover.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsOffline
Database is offline.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsReadOnly
Database is in a read-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsShutDown
Database encountered a problem at startup.
1 = TRUE
0 = FALSE
NULL1 = Invalid input
IsSingleUser
Database is in single-user access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsSuspect
Database is suspect.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE
0 = FALSE
NULL = Invalid input
Version
Internal version number of the Microsoft® SQL Server™ code
Run the following line of code
SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics
If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled
Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code
DECLARE @v VARCHAR(55)
SELECT @v = 'pubs'
SELECT
DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
DATABASEPROPERTY(@v,'Version') AS Version
So what do all these values mean? Here is a list of all the properties
IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsBulkCopy
Database allows nonlogged operations.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsDboOnly
Database is in DBO-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsDetached
Database was detached by a detach operation.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsInLoad
Database is loading.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsInRecovery
Database is recovering.
1 = TRUE
0 = FALSE
NULL1 = Invalid input
IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsNotRecovered
Database failed to recover.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsOffline
Database is offline.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsReadOnly
Database is in a read-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsShutDown
Database encountered a problem at startup.
1 = TRUE
0 = FALSE
NULL1 = Invalid input
IsSingleUser
Database is in single-user access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsSuspect
Database is suspect.
1 = TRUE
0 = FALSE
NULL = Invalid input
IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE
0 = FALSE
NULL = Invalid input
Version
Internal version number of the Microsoft® SQL Server™ code
Wednesday, January 17, 2007
A Witchbrew Of SQL Server News And Interesting Links
Since I have nothing interesting or fascinating to say I decided to put some links up to some posts of people who do have something interesting to say.
We will start with Hugo Kornelis who has two posts about why clustered index ordering is not guaranteed in SQL Server. The first article can be found here: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
And the second article that explains the behaviour can be found here: Beatles vs Stones Explanation
Developer.com has announced their Database Tool or Add-in winner and the winner is......SQL Server 2005. You can read the whole article here http://www.developer.com/db/article.php/10920_3653956_1
Euan Garden has a post telling us that SQL Server Compact Edition launches (finally)
Tim O'Reilly posted his latest State of the Computer Book Market article and SQL server is still going strong. You can fnd that article here: State of the Computer Book Market, Q4 06, Part 1, Overall Market Trend
And we will end with Internet Maverick Marc Cuban who wrote an interesting article titled Why I Don't Wear a Suit and Can't Figure Out Why Anyone Does !
I don't wear a suit either, I actually wear jeans and sneakers most of the time. When i used to work in Silicon Alley (Broadway and 21st street in New York City) I actually came to work in shorts in the summer but then again what do you expect when you have 2 lizards and a cat roaming around the office?
We will start with Hugo Kornelis who has two posts about why clustered index ordering is not guaranteed in SQL Server. The first article can be found here: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
And the second article that explains the behaviour can be found here: Beatles vs Stones Explanation
Developer.com has announced their Database Tool or Add-in winner and the winner is......SQL Server 2005. You can read the whole article here http://www.developer.com/db/article.php/10920_3653956_1
Euan Garden has a post telling us that SQL Server Compact Edition launches (finally)
Tim O'Reilly posted his latest State of the Computer Book Market article and SQL server is still going strong. You can fnd that article here: State of the Computer Book Market, Q4 06, Part 1, Overall Market Trend
And we will end with Internet Maverick Marc Cuban who wrote an interesting article titled Why I Don't Wear a Suit and Can't Figure Out Why Anyone Does !
I don't wear a suit either, I actually wear jeans and sneakers most of the time. When i used to work in Silicon Alley (Broadway and 21st street in New York City) I actually came to work in shorts in the summer but then again what do you expect when you have 2 lizards and a cat roaming around the office?
Subscribe to:
Posts (Atom)