Maximum Capacity Specifications for SQL Server

One day, one of my team member asked me about what is maximum no of column that one table can have?

so when i got time i have done some googling on that & find some useful information that i am sharing with you all.

Here is some useful Maximum size limits for SQL Server 2005. Hope this will help you !!!

Database Engine object	Max Size
Columns per nonwide table	1024
Columns per wide table	30,000
Columns per SELECT statement	4,096
Columns per INSERT statement	4,096
Columns per foreign key	16
Columns per primary key	16
Bytes per foreign key	900
Bytes per primary key	900
Bytes per row	8,060
Foreign key table references per table	253
Bytes per GROUP BY, ORDER BY	8,060
Clustered indexes per table	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10
Length of a string containing SQL statements (batch size)	65,536 * Network packet size
Nested stored procedure levels	32
Nested subqueries	32
Nested trigger levels	32
Nonclustered indexes per table	999
Parameters per stored procedure	21,00
Parameters per user-defined function	21,00
REFERENCES per table	253
Tables per SELECT statement	Limited only by available resources
Columns per UPDATE statement (Wide Tables)	4096

 

Reference link : Click here

Jai Ganesh

LINQPad

Tired of querying in antiquated SQL?

Well, you don’t have to!  LINQPad lets you interactively query SQL databases in a modern query language: LINQ.  Say goodbye to SQL Management Studio!

LINQPad supports everything in C# 3.0 and Framework 3.5:

    * LINQ to Objects
    * LINQ to SQL
    * Entity Framework
    * LINQ to XML
    * (Even old-fashioned SQL!)

LINQPad is also a great way to learn LINQ: it comes preloaded with 200 examples from the book, C# 3.0 in a Nutshell.  There’s no better way to experience the coolness of LINQ and functional programming.

And LINQPad is more than just a LINQ tool: it’s a highly ergonomic code snippet IDE that instantly executes any C#/VB expression, statement block or program – the ultimate in dynamic development. Put an end to those hundreds of Visual Studio Console projects cluttering your source folder!

Best of all, LINQPad standard edition is free and can run without installation (or with a low-impact setup). The executable is 3MB and is self-updating.

Download Now : http://www.linqpad.net/

SQL Database Publishing wizard is now in Visual Studio 2008.

The SQL database publishing wizard is a very popular web-downloadable add-in for Visual Studio 2005. The publishing wizard addresses the issue where a developer needs to deploy a local database from his development machine to a hosting environment on a remote machine. We received a lot of positive feedback for this wizard and we decided to integrate this with Visual Studio 2008. This is a feature that was added post Beta2 and will be available with Visual Studio 2008 RTM.

The wizard supports two key database hosting deployment scenarios:

1. It generates a single .SQL script file which can be used to recreate a database on a remote machine

Using the Database Publishing Wizard  you can point to a database on your local machine, and then automatically create a .SQL script file that contains the setup logic needed to re-create a replica of the database on any remote system – for example an external hosting system. This .SQL script includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc). You also have the option of populating the new database with the same data contents as your local tables. Most hosters today support the upload and running of .SQL files to their hosted environments using their admin control panels. So, all you need to do is upload and run the .SQL script generated by the Database Publishing Wizard, and you will have a working database in your hosted environment. This should considerably reduce the effort required to deploy your databases.

2. It connects to a web service provided by your hoster and directly creates objects on a specified hosted database.

The Database Publishing Wizard also enables you to point at a database you are working with on your local system, and then use web-services to transfer and recreate the database in your remote hoster environment (without you having to create the .SQL file or use the hoster admin control panel to run it).  This publishing option does require that a SQL Publishing web-service be exposed in the hosting environment, and the SQL Server Hosting Toolkit includes a free implementation of this SQL Publishing web-service that we’ll be working with hosters to aggressively deploy

 

Clicl here to Read more ….

Dynamic creation of Insert, Update, Delete Stored procedure

Introduction

It is easy to write Insert, Update and Delete stored procedures for a table, but it may take lots of time and effort to write these basic stored procedure. Especially if you have lots of tables with lots of fields. That time might be better spent on other tasks.

I have written a stored procedure to help do this automatically, and this article explains how it works. This stored procedure, sp_et_create_sps_for_table, automates the creation of stored procedures for a table using the table name as a parameter. It dynamically creates the scripts for insert, update and delete stored procedures and then creates them. It gathers the primary key information of the table and creates the necessary scripts for the where clause of the update & delete stored procedures of the given table.

 

Click here to Read more….

Intersect, Except, Union, All and Any

I was reviewing the Microsoft training courses for SQL2008 for a set of application developers who wanted to learn more about SQL Server. In doing so I came across some commands that I either had not heard of or had not used before.

• INTERSECT
• EXCEPT
• ALL
• ANY

ALL and ANY have been around for donkeys years but INTERSECT and EXCEPT were new to me.

Whenever I find a new set of commands in T-SQL I fire up my developer edition and go straight to Adventureworks to start playing around.

 

Visit this link to read more…

Debugging SQL Server 2005 Stored Procedures in Visual Studio

Here is 2 important link :

• With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query Analyzer (see Debugging a SQL Stored Procedure from inside SQL Server 2000 Query Analyzer for more information). With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. Using this technique, it is possible to step into your stored procedures, one statement at a time, from within Visual Studio. It is also possible to set breakpoints within your stored procedures’ statements and have these breakpoints hit when debugging your application…

  Read more…

• This article walks through the basics of debugging stored procedures using Visual Studio 2005. It covers breakpoints, watches and stepping through code. It was written by Chris Rock and includes his very unique sense of humor.

  One of my favorites activities while working is debugging. A little weird yes, but I’ve always had an affinity towards taking things apart and putting them back together. I haven’t always had success. As a kid I destroyed many toys and pieces of furniture (office chairs) just to find out how they work. I am a curious person by nature and like to figure out how something works OR why something isn’t working. Alright, enough about my lame personality…

  Read more…

Functional difference between “NOT IN” vs “NOT EXISTS” clauses

“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately. This blog post outlines how these commands are executed and discusses when it is appropriate to use them.

CREATE TABLE EMP_MASTER
(
EMP_NBR NUMBER(10) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR2(20 CHAR),
MGR_NBR NUMBER(10) NULL
)

INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 5);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 5);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 5);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 5);

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

NOT IN

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );
COUNT(*)
———-
0

NOT EXISTS

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );
COUNT(*)
———-
9

Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back. (in MS SQL Server, depending upon the ANSI NULLS setting, the behavior can be altered but this post only talks about the behavior that is same in Oracle, DB2 LUW and MS SQL Server).

Performance implications:

When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, query can use an index within the sub-query.

Query to Find Column Name From All Tables

Question : How many tables in your database have column name like  ‘CategoryID’  ?

Solution :

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%CategoryID%’
ORDER BY schema_name, table_name;

Question : How to find all the column name from your database ?

Solution :

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

Hope this will help

Jay Ganesh

SET NOCOUNT

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

syntax:

SET NOCOUNT { ON | OFF }

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

The following example prevents the message about the number of rows affected from being displayed.

USE AdventureWorks;
GO
SET NOCOUNT OFF;
GO
– Display the count message.
SELECT TOP(5)LastName
FROM Person.Contact
WHERE LastName LIKE ‘A%’;
GO
– SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT TOP(5) LastName
FROM Person.Contact
WHERE LastName LIKE ‘A%’;
GO
– Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

@@ROWCOUNT

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

* Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
* Preserve @@ROWCOUNT from the previous statement execution.
* Reset @@ROWCOUNT to 0 but do not return the value to the client.

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT ‘Generic Text’.

Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

EXECUTE statements preserve the previous @@ROWCOUNT.

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

EXAMPLE :

The following example executes an UPDATE statement and uses @@ROWCOUNT to detect if any rows were changed.

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET Title = N’Executive’
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT ‘Warning: No rows were updated’;
GO