Maximum Capacity Specification for all editions of SQL Server

While creating a table or a writing a query, many T-SQL Developers and DBA’s often have a confusion with one common question.

What is the maximum size/numbers/bytes that can be stored in a SQL Server column?

Here are three documents from SQL Server BOL containing tables that specify the maximum sizes and numbers of various objects defined in Microsoft SQL Server 2000, Microsoft SQL Server 2005 and Microsoft SQL Server 2008 components.

Maximum Capacity Specifications for SQL Server 2000

Maximum Capacity Specifications for SQL Server 2005

Maximum Capacity Specifications for SQL Server 2008

Hope you find them useful!

Count Rows in all the Tables of a SQL Server Database

To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. Here’s a query that first uses DBCC UPDATEUSAGE and then count all the rows in all the tables of a database using the undocumented stored procedure sp_msForEachTable

USE AdventureWorks
GO
DECLARE @DynSQL NVARCHAR(255) 
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@DynSQL) 

EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1) as TableName,
COUNT(*) as NumberOfRows FROM ?'

OUTPUT (Partial)

Find Tables with No Primary Key – SQL Server

Sometime back I had done a post called Find all tables in a database without a primary key where I used the INFORMATION_SCHEMA.TABLES.

Megan commented asking how to rewrite the same query using sys.objects. Well here it is!

USE NerdDinner;
GO
SELECT name as [TableName], create_date
FROM sys.objects
WHERE type = 'U'
AND OBJECTPROPERTY([object_id], 'TableHasPrimaryKey') = 0;
GO

OUTPUT

If you do not get any results, then all your tables in your database have primary keys

You may also want to check the following related posts written some time ago:

Find Primary Key of a SQL Server Table

List all the Non-Clustered Indexes in a SQL Server Database

Find out all the Primary Key and Foreign Key Constraints in a table

Using Table Value Constructors aka Row Constructors in SQL Server 2008 to insert multiple rows using a single statement

Although its been a while since SQL Server 2008 has been released, I see developers working on SQL Server 2008 inserting data in a table ‘the 'old way’ like this:

DECLARE @TT TABLE (ID int, Name varchar(10))
INSERT INTO @TT (ID, Name) VALUES (1, 'Jack')
INSERT INTO @TT (ID, Name) VALUES (2, 'Jill')
INSERT INTO @TT (ID, Name) VALUES (3, 'Hill')
INSERT INTO @TT (ID, Name) VALUES (4, 'Bill')

If you are using SQL Server 2008, then you must use the Table Value Constructors (earlier known as Row Constructors) wherever you can to populate data. So the same code can be rewritten using Table Value Constructors as:

DECLARE @TT TABLE (ID int, Name varchar(10))
INSERT INTO @TT (ID, Name)
VALUES (1, 'Jack'),
       (2, 'Jill'),
       (3, 'Hill'),
       (4, 'Bill')

Check the syntax! Simple, readable and less typing!

Note: You can construct a maximum of 1000 rows using this syntax. To insert more than 1000 rows, use multiple inserts, each containing 1000 rows at a time.

Read more about Table Value Constructors over here http://msdn.microsoft.com/en-us/library/dd776382.aspx

SQL Server 2008 R2 RTM Released

Microsoft today released SQL Server 2008 R2 RTM

The key pillars of the R2 release include:

· Powerful new business intelligence capabilities, supporting managed self-service BI for all users in an organization – giving them access to information through Microsoft Excel 2010 with PowerPivot and SharePoint 2010

· Enterprise-class scalability with support for the most advanced hardware and features like Live Migration with Hyper-V, and greater IT efficiency through enhanced tools for DBAs and developers

· The industry’s most complete and integrated platform spanning the datacenter to the cloud with SQL Azure

Download Links

Download R2 for Intel 32-bit, English

Download R2 for Intel 64-bit, English

SQL Server 2008 R2 Guide

SQL Server 2008 R2 Express Download

Microsoft SQL Server2008 R2 Express with Advanced Services

Microsoft SQL Server 2008 R2 Express with Management Tools

Microsoft SQL Server 2008 R2 Express with Management Studio Express

Convert Character string ISO date to DateTime and vice-versa

Here’s how to convert ISO8601 character data to DateTime and DateTime back to ISO8601 in SQL Server

DECLARE @IsoDate nvarchar(35)
SET @IsoDate = '2010-03-16T19:20:30.45123+01:00'

-- CONVERT TO DATETIME in SQL 2008
SELECT CAST(CONVERT(datetimeoffset, @IsoDate) AS datetime) as SQL2008

-- CONVERT TO DATETIME in SQL 2005 and earlier
SELECT CAST(LEFT(@IsoDate, 23) AS datetime) as SQL2005


-- CONVERT DateTime to ISO Date format
SELECT CONVERT(nvarchar(35), GETDATE(), 127) as ISO8601 ;

OUTPUT

Select dates within the Current Week using SQL Server

One of the most common date queries I have seen is to select the dates matching the current week. Here’s how to do it

SAMPLE DATA

DECLARE @TT TABLE (CheckOut DATETIME)
INSERT INTO @TT VALUES ('March 15, 2010 11:25pm')
INSERT INTO @TT VALUES ('April 11, 2010 9:55am')
INSERT INTO @TT VALUES ('April 07, 2010 11:24am')
INSERT INTO @TT VALUES ('April 05, 2010 11:22pm')
INSERT INTO @TT VALUES ('April 13, 2010 5:27am')
INSERT INTO @TT VALUES ('April 16, 2010 8:21pm')
INSERT INTO @TT VALUES ('April 12, 2010 11:55am')
INSERT INTO @TT VALUES ('April 02, 2010 10:16am')
INSERT INTO @TT VALUES ('April 06, 2010 11:35am')
INSERT INTO @TT VALUES ('April 18, 2010 9:49pm')
INSERT INTO @TT VALUES ('April 12, 2010 7:55pm')

QUERY

SELECT GETDATE() as CurrentDateTime

SELECT * FROM @TT
WHERE CheckOut >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
AND CheckOut < DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)

OUTPUT

As you can see, the query selects the dates only matching the current week.

Introducing Microsoft SQL Server 2008 R2 – Free eBook

Microsoft recently announced the release of a free ebook: Introducing Microsoft SQL Server 2008 R2, by Ross Mistry and Stacia Misner!

The book contains 10 chapters and 216 pages:

PART I Database Administration

CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements
CHAPTER 2 Multi-Server Administration
CHAPTER 3 Data-Tier Applications
CHAPTER 4 High Availability and Virtualization Enhancements
CHAPTER 5 Consolidation and Monitoring

PART II Business Intelligence Development

CHAPTER 6 Scalable Data Warehousing
CHAPTER 7 Master Data Services
CHAPTER 8 Complex Event Processing with StreamInsight
CHAPTER 9 Reporting Services Enhancements
CHAPTER 10 Self-Service Analysis with PowerPivot

Download the eBook here

Introducing Microsoft SQL Server 2008 R2 (PDF)

Introducing Microsoft SQL Server 2008 R2 (XPS)

List all the Non-Clustered Indexes in a SQL Server Database

Here’s a simple script that can list all the Non-Clustered Indexes in a SQL Server 2005/2008 Database

USE AdventureWorks
GO
SELECT systb.name as UserTable,
idx.name as IndexName,
idx.type_desc, idx.is_primary_key
FROM sys.indexes idx, sys.tables systb
WHERE idx.object_id = systb.object_id
and idx.type = 2
ORDER BY UserTable

If you want to avoid doing a join with sys.tables, my colleague and SQL Server MVP Madhivanan suggested an alternate approach that produces the same output. Here it is:

SELECT
object_name(object_id) as UserTable,name as IndexName,
type_desc, is_primary_key from sys.indexes
WHERE type = 2 and objectproperty((object_id),'IsUserTable')=1
ORDER BY UserTable

OUTPUT (Partial)

List of Undocumented Stored Procedures in SQL Server

I have been searching for a list of Undocumented Stored Procedures in SQL Server for quite a while. Here is a list collected from forums and other sites for your reference. Remember most of these stored procedures are deprecated and can be removed from future versions of SQL Server, so use them cautiously!

Undocumented Stored Procedures in SQL Server

sp_checknames
sp_columns_rowset
sp_enumoledbdatasources
sp_fixindex
sp_gettypestring
sp_ms_marksystemobject
sp_msaddguidcolumn
sp_msaddguidindex
sp_msaddlogin_implicit_ntlogin
sp_msadduser_implicit_ntlogin
sp_mscheck_uid_owns_anything
sp_msdbuseraccess
sp_msdbuserpriv
sp_msdependencies
sp_msdrop_object
sp_msforeachdb
sp_msforeachtable
sp_msget_qualified_name
sp_msgettools_path
sp_msgetversion
sp_msguidtostr
sp_mshelpcolumns
sp_mshelpindex
sp_mshelptype
sp_msindexspace
sp_msis_pk_col
sp_mskilldb
sp_msloginmappings
sp_mstablekeys
sp_mstablerefs
sp_mstablespace
sp_msunc_to_drive
sp_msuniquecolname
sp_msuniquename
sp_msuniqueobjectname
sp_msuniquetempname
sp_tempdbspace
sp_who2
xp_delete_file
xp_dirtree
xp_enum_oledb_providers
xp_enumcodepages
xp_enumdsn
xp_enumerrorlogs
xp_enumgroups
xp_fileexist
xp_fixeddrives
xp_get_mapi_default_profile
xp_get_mapi_profiles
xp_getnetname
xp_qv
xp_readerrorlog
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
xp_subdirs
xp_varbintohexstr

You may also want to read some more blog posts of mine around undocumented stored procedures

6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

How to exclude databases while using the sp_MSforeachdb stored procedure

Quickly Add and Drop a Column from all Tables in SQL Server

Enable and Disable All the Triggers in a SQL Server Database

Recompiling Stored Procedures in SQL Server – All, Few or One at a time

Did I miss out on any of the undocumented stored procedures? If yes, let me know via the comments section and I will add them to the list! Also let me know if you have created a similar list. I may link it here!

Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls

I had recently posted on Using CASE Expression in SQL Server to create a Computed Column. Burt C commented asking if it was possible to avoid NULLS for the rows that did not match a condition. The answer is that it is possible to avoid NULLS and can be done easily using COALESCE

Here’s the modified query:

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
 COALESCE(
        CASE
            WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
            WHEN Quantity BETWEEN 20 and 30  THEN Price - Price * .20
            WHEN Quantity >=30 THEN Price -  Price * .40
        END
  ,0)
    AS Money)
)
   
   
INSERT INTO @tmp values(4, 4000)
INSERT INTO @tmp values(16, 3500)
INSERT INTO @tmp values(12, 5000)
INSERT INTO @tmp values(22, 6620)
INSERT INTO @tmp values(5, 6400)
INSERT INTO @tmp values(21, 1000)
INSERT INTO @tmp values(37, 7000)

SELECT * FROM @tmp

OUTPUT

As you can see, all the NULLS got replaced with 0

Using CASE Expression in SQL Server to create a Computed Column

Here’s a practical example of using CASE expression in SQL Server. I am creating a computed column using CASE expression where I will automatically calculate the value of a column based on the value of other columns:

The psuedocode is as follows:

If the Quantity of items ordered is in between 10 and 20, give a 10% discount

If the Quantity of items ordered is in between 20 and 30, give a 20% discount

If the Quantity of items ordered is greater than 30, give a 40% discount

Here’s the query using a CASE expression:

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
    CASE
        WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
        WHEN Quantity BETWEEN 20 and 30  THEN Price - Price * .20
        WHEN Quantity >=31 THEN Price -  Price * .40
    END AS Money)
)
  
  
INSERT INTO @tmp values(4, 4000)
INSERT INTO @tmp values(16, 3500)
INSERT INTO @tmp values(12, 5000)
INSERT INTO @tmp values(22, 6620)
INSERT INTO @tmp values(5, 6400)
INSERT INTO @tmp values(21, 1000)
INSERT INTO @tmp values(37, 7000)

SELECT * FROM @tmp

OUTPUT

[Update] : If you want to avoid nulls, check this updated post Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls

Restoring a SQL Server database marked as suspect

Have you encountered this error “spid13 Bypassing recovery for database ‘yourdb’ because it is marked SUSPECT”. One of my friends got this error on his SQL Server 2000 database. This error usually occurs when SQL Server is not able to access a database

If you plan to recover a corrupt database on your own, here are some simple solutions that may work:

1. Run this command

UPDATE master.dbo.sysdatabases
SET status=status & ~256
WHERE name= 'yourdbname'

2. If that does not solve the problem, here’s a way to do it in emergency mode (untested)

UPDATE master.dbo.sysdatabases
SET status = status  -32768
where name = 'yourdbname'

If you know of any other way, drop in a comment. Thanks!

Find the Nth Maximum and Minimum Value in a Column

Here’s a simple query to find the Nth Maximum and Minimum values in a SQL Server table column using the Row_Number() function. We will find the 3rd highest and 3rd lowest values in the column.

DECLARE @tmp TABLE(id integer, amount integer)

INSERT INTO @tmp values(4, 9543)
INSERT INTO @tmp values(6, 34)
INSERT INTO @tmp values(3, 54)
INSERT INTO @tmp values(2, 6632)
INSERT INTO @tmp values(5, 645)
INSERT INTO @tmp values(1, 1115)
INSERT INTO @tmp values(7, 345)

-- FIND Nth Maximum value
SELECT id, amount
FROM
(
SELECT id, amount, Row_Number() OVER(ORDER BY amount DESC) AS highest
FROM @tmp
) as x
WHERE highest = 3

-- FIND Nth Minimum value
SELECT id, amount
FROM
(
SELECT id, amount, Row_Number() OVER(ORDER BY amount ASC) AS lowest
FROM @tmp


) as x
WHERE lowest = 3

OUTPUT


The first output shows the 3rd maximum value in the column whereas the second output shows the 3rd minimum value in the column

Automatically Round off numbers to the nearest digit

I was solving a requirement where data entered into a table contained decimals. However the user had a requirement of automatically rounding off the decimals to the nearest digit before inserting into the table. The SQL statements could not be changed to make use of the Round() function. So one of the ways of solving this was to use the numeric datatype in the table definition as shown below:

DECLARE @TmpTable TABLE
(
id int,
amount numeric(7,0)
)

INSERT INTO @TmpTable VALUES  (1, 5.556233)
INSERT INTO @TmpTable VALUES  (1, 2.123953)
INSERT INTO @TmpTable VALUES  (1, 3.753433)

SELECT  * FROM  @TmpTable

OUTPUT

As you can see, data stored into the table automatically gets rounded off.