What Is The Difference Between Set And Select

Here is The Combined answer that i have collected from various articles and experience.

Here is the Difference.
1. SET is the ANSI standard for variable assignment, SELECT is not. 2.You can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here's how:

/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int

/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2

/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
   
3. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value). 
4. When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and this type of bugs is hard to track down too. Here is an example:

/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Test (i int, j varchar(10))
INSERT INTO #Test (i, j) VALUES (1, 'First Row')
INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
GO
/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
This may not be what you were expecting. Since no error is returned,
you will never know that two rows existed for the condition, WHERE i = 1 */

DECLARE @j varchar(10)
SELECT @j = j FROM #Test WHERE i = 1
SELECT @j
GO
/* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j
Server: Msg 512, Level 16, State 1, Line -1074284106
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Based on the above results, when using a query to populate variables, I suggest you always use SET, if you want to be sure that only one row is returned. If you hate SET for some reason, you could get the same behavior of SET, using SELECT, as shown below:

DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j
Here is another difference with respect to assigning values based on a query, especially when the query doesn't return any rows. Run the following example in the pubs database, and you will see what I mean:

/* Returns NULL */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'

SET @Title =
(
SELECT title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'
)

SELECT @Title
GO
/* Returns the string literal 'Not Found' */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'

SELECT @Title = title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'

SELECT @Title
GO
   

  Performance Wise  This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables. In this scenario, using a SELECT is at least twice as fast, compared to SET. So, the conclusion is, if you have a loop in your stored procedure that manipulates the values of several variables, and if you want to squeeze as much performance as possible out of this loop, then do all variable manipulations in one single SELECT statement (or group the related variables into few SELECT statements) as show below:

SELECT @TestVar1 = @TestVar1 + 1, @TestVar2 = @TestVar2 - 1, @CTR = @CTR + 1



Thanks..

SQL Server Import and Export Wizard

Yesterday, I was to Upload very large data. So I opened my Sql Server management studio & tried Import/Export wizard. & Surprise !!,

I was not able to start Import/Export wizard.

The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate.
ADDITIONAL INFORMATION:
Cannot create a task with the name "STOCK:PipelineTask". Verify that the name is correct.
({1A768FBA-F8F7-4147-B1CD-2953FAC6781E}).


I searched the  DTSPipeline.dll  in GAC (Shared Assembly folder). But there are both MSIL & x86 version.
After that I searched Program Files SQL Server folder & found the above dll.

I just registered this dll & my problem has been resolved.


Regsvr32.exe "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtspipeline.dll"
Above command will register the required dll.
Thanks,
Anuj Rathi

string.IsNullOrWhiteSpace()

.NET 4 adds new method called string.IsNullOrWhiteSpace() which checks for spaces, empty or null. This is a nice time-saver for developers.. This static method returns true if a string is full of whitespace characters. Let us consider the below example .

static void Main()
{
string strTest = "Simple Talk";
string strNull = null;
string strEmpty = string.Empty;
string strWhiteSpace = "\t\r\n\n ";
Console.WriteLine("Is null or whitespace Exmaple!!");
Console.WriteLine("TestSting: " + string.IsNullOrWhiteSpace(strTest));//false
Console.WriteLine("NullString: " + string.IsNullOrWhiteSpace(strNull)); //true
Console.WriteLine("EmptyString: " + string.IsNullOrWhiteSpace(strEmpty)); //true
Console.WriteLine("WhiteSpaceString: " + string.IsNullOrWhiteSpace(strWhiteSpace)); //true
Console.ReadLine();
}

Stored Procedures vs. User Defined Functions in Microsoft SQL Server

                 SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:
  • Reusing code from one program to another, cutting down on program development time.
  • Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages
  • Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications
          At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:
  • Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
  • Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables. Stored procedures provide the ability to restrict user actions at a much more granular level than standard SQL Server permissions. For example, if you have an inventory table that cashiers must update each time an item is sold (to decrement the inventory for that item by 1 unit), you can grant cashiers permission to use a decrement_item stored procedure, rather than allowing them to make arbitrary changes to the inventory table.
  • Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
Overall, stored procedures are one of the greatest treasures available to SQL Server developers. I use them heavily in my databases and encourage you to do the same. The efficiency and security benefits you’ll reap are well worth the upfront investment in time.

Benefits of SQL Server Stored Procedures

Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.

Benefits of Stored Procedures

Why should we use stored procedures? Let's take a look at the key benefits of this technology:
  • Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
  • Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  • Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
  • Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
Stored procedures are very similar to user-defined functions, but there are subtle differences.

Structure

              Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.

List all Constraints in Sql Server

Hi All,
          Yesterday, I was facing a problem. Actually I was to rename all the constraints as our Standards. When I create tables in query analyzer, Sql Server create Constraints name itself. Now, I have to change all constraints according to naming conventions. This is the below script by which I get all the constraints in my database.


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName,  OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT'

Above script list all constraints. But If I want to see only Primary keys or Foreign Keys or default constraints the I will use the where clause like this:


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT';


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT';


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'DEFAULT_CONSTRAINT';


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'UNIQUE_CONSTRAINT';


I hope this will be very useful to see all database constraints.

Find all dependent objects of an Object

You can use this system Stored Procedure to find the all dependencies of an Object.

EXEC sp_depends @objname = N'StoredProcedureName' ;

How to check the SQL Server Varsion, Name & Edition

WE can see the Sql Server Name, version, edition etc using below query:


SELECT @@SERVERNAME , 
SERVERPROPERTY('productversion'), 
SERVERPROPERTY ('productlevel'), 
SERVERPROPERTY ('edition')

View all Database File Sizes

SELECT s_mf.[name], s_mf.physical_name, s_mf.[size]/128 AS SizeInMB, s_mf.max_size/128 AS MaxSizeInMB, s_mf.growth
FROM sys.master_files s_mf
ORDER BY s_mf.[size] DESC

Above query will return Database Name, its physical path & database files size in MB.

Happy Coding !!

How to Encrypt Stored procedures in Sql Server

At times, it is needed that you encrypt the text of stored procedures containing sensitive information. SQL Server provides the WITH ENCRYPTION to encrypt the text of the stored procedure.

CREATE procedure [dbo].[TestProc]
WITH ENCRYPTION
AS
BEGIN
SELECT 'TEST' as TestColumn
END

Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:

EXEC sp_helptext TestProc

'The text for object 'Ten Most Expensive Products Encyrpt' is encrypted.'

One note of caution. Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.