Overview of the SQL Server Browser service

SQL Server browser service plays important role in multi-instance environment. My article related to Working with SQL Server Browser Service is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Overview of the SQL Server Browser service.

Insert value in identity column when identity column is only column in table

I came across a question that how to insert value in identity column when identity column is only column in the table. And to set identity insert on is not allowed. It is interesting scenario. So answer is to use DEFAULT VALUES. Consider following code for demo that how to insert value in identity column when identity column is only column in table.

USE AdventureWorks
GO

CREATE TABLE #TestInsert(onlyColum TINYINT IDENTITY (1,1))
GO

INSERT INTO #TestInsert DEFAULT VALUES
GO

SELECT * FROM #TestInsert
GO

And you would get inserted value from this temporary table.

SQL Hour meeting held today

According to plan today we had IsbSSUers group meeting. Surprisingly meeting broke decided time and lot more concepts were discussed and covered in this meeting. Following SQL Server topics were covered

• Extended properties for SQL Server objects
• Go through basic cursor operations
  
• Using cursors where they may be efficient
• Avoiding cursors in certain conditions
• Create basic SQL Server maintenance plans through

During maintenance plan creation demo on development machine, we got error while saving maintenance plan. Error statement was
No description found
while trying to save the maintenance plan.
After some googling, the reason found was to install the original release version of Microsoft Office Visio 2003 after installation of SQL Server 2005. It is mentioned by Microsoft technical support that you may also experience this problem when you run the SQL Server Import and Export Wizard.This problem occurs because the Microsoft Office Visio 2003 Setup program incorrectly sets the registry entries for Microsoft Core XML Services (MSXML). In this case, the wrong version of the Msxml.dll file is being used when you create SSIS packages or maintenance plans.
To resolve this problem, install Microsoft Office Visio 2003 Service Pack 1 from here.
For further work around consult this MS knowledge base article.
Over all this group meeting was fantastic and provided us to learn through coordination and discussion.

Working with READ ONLY databases in SQL Server

If any of database is not meant to change, then it may be considered as good candidate for READ ONLY database. It is an important concept and requires proper analysis of pros and cos. My article related to Working with READ ONLY databases in SQL Server is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Working with READ ONLY databases in SQL Server

SQL Server T-SQL Stored Procedures for beginners

It is important to choose the right choice to achieve an optimal performance. Here I will elaborate some key aspects of Stored procedures in T-SQL fro SQL Server, which would help to choose the right at its place.
A stored procedure is a pre-compiled batch of Transact-SQL statements stored under a name and processed as a unit that you can call from within another Transact-SQL statement or from the client applications. The major difference between batch and stored procedure is a batch is compiled at the time of execution and a stored procedure is already compiled. It can also accept parameters. Parameter name can be up to 30 characters including @. And there can be up to 2100 parameters in a SQL Server 2005 stored procedure. Stored procedures allow many users to use the same code
When a T-SQL statement is executed many things happen on server side in relational engine, like

• Parsing
• Syntax checking
• Object resolution
• Compilation
• Query plan determination
  

All this is required for every query. In case of stored procedures all this is carried out once and only. After that whenever a stored procedure is executed, nothing of above mentioned happens. This is real power of stored procedure.
Users can be given execution permission on a stored procedure that modifies data instead of permission to directly modify the table. It is very help full for security implementation.
Following T-SQL command is used to create stored procedure

CREATE PROCEDURE proc_name
AS

/*

SQL statements or batch

*/
RETURN


Created stored procedure will be stored in current database, but it will have access to all other databases. The “create procedure” command can not be combined with any other statement in a single batch. Any T-SQL statement can be included in stored procedure except the following

• create view
• create rule
• create default
• create procedure
• create trigger
• use
  

To execute the stored procedure use following command

EXECUTE proc_name
--OR
EXEC proc_name

Stored procedure can be called from

• Batches
• Other stored procedures
• Triggers
• User defined functions
  

Stored procedures may or may not return a value. Its output flexibility provides a greater edge over user defined functions. There are three ways to return data from a stored procedure.

• Result Sets
• Output variables
• Return statement
  

Following is an overview and comparison of these data return approaches.

Result Sets:
If we use data selection statement inside the stored procedure, then data will be retrieved and shown as in normal select statement, but do not forget that you will have all server and network optimization with you in this case. Following code will just do it through pubs database.

CREATE PROCEDURE testResultSet
AS
SELECT * FROM pubs..authors
GO

Output variables:
If we want to return a limited number of (mostly) calculated/aggregate values then the choice is to use output variables. These parameters will provide to handle and transport the values in any data type. Currently I am not sure the maximum number of out put parameters provided in SQL Server 2005. But in rare case one will use the maximum number. Use following code to create a SP which will return a integer value.

CREATE PROCEDURE testVars
(@firstNameCount INT OUTPUT )
AS
SELECT @FirstNameCount = COUNT(*)
FROM pubs..authors
WHERE au_fname like 'A%'
GO


And now to execute the procedure use following lines

DECLARE @counting INT
EXEC testVars
@firstNameCount = @counting OUTPUT
SELECT counting = @counting


Return Statement:
The most limited way to get data from a stored procedure is through return statement. It returns a single numeric value and is mostly used to return a status result or error code from inside a stored procedure. Use following code to provide a value to SP and simply get it back when executed.

CREATE PROC testReturnValue (@InValue int)
AS
Return @Invalue
GO

DECLARE @ReturnValue INT
EXEC @ReturnValue = TestReturnValue 3
SELECT ReturnValue=@ReturnValue
GO


Recompiling a Stored Procedure
Because the stored procedure execution plan can be outdated, for example when a large amount of data modifications are made to a table referenced by a stored procedure, you may need to recompile the execution plan. SQL Server 2000 automatically recompiles the stored procedure execution plan when one of the following conditions are met:

• Any schema changes of the objects referenced in the stored procedure were made.
• An index used by the execution plan of the stored procedure is dropped.
• A large amount of data modifications are made to a table referenced by a stored procedure.
• The new distribution statistics were generated.
• The execution plan was deleted from memory, because the memory is required for other objects.
• A table has trigger(s) and the number of rows in the inserted or deleted tables grows significantly.
  

There are three ways to cause SQL Server to recompile the stored procedure execution plan, they are:

• Including a WITH RECOMPILE clause in a CREATE PROCEDURE statement. When you include a WITH RECOMPILE clause in a CREATE PROCEDURE statement, SQL Server will not cache a plan for this procedure and the procedure will be recompiled every time it will be run. Because the stored procedure execution plan will never been cached, you should use the RECOMPILE option in a CREATE PROCEDURE statement very carefully.
• Including a WITH RECOMPILE clause in a EXECUTE statement. When you include a WITH RECOMPILE clause in a EXECUTE statement, the stored procedure execution plan will be recompiled when you run this EXECUTE statement. You can use this option if the parameters you are supplying are atypical or if the data has significantly changed.
• Using the sp_recompile system stored procedure to causes stored procedures to be recompiled the next time they are run. To cause stored procedures to be recompiled the next time they are run, you can use the sp_recompile system stored procedure. You can use the sp_recompile system stored procedure when you want your stored procedure reflects inchanges in indexes or data values.
  

Because SQL Server 2000 and SQL Server 2005 can recompile stored procedures and execution plans automatically, in most cases it is not necessary to use the sp_recompile system stored procedure or a WITH RECOMPILE clause, and you can rely on SQL Server decisions to recompile execution plan.
All these qualities make a stored procedure a very power full tool for most of optimized T-SQL processes.

SQL Server T-SQL user defined function (UDF)

User Defined Function (UDF) became available in SQL Server 2000. User Defined Function (UDF) allow you to create routines that execute much like built-in functions, but they perform data manipulations specific to your business needs. For example, you could write a function that calculates the number of business days between two dates. Avoid their confusion with stored procedures and views. There are some thing common between them but all three are used in different scenarios and when used optimally none can take over other.

Query execution plan of User Defined Function (UDF) may be cached and perform well. So optimization and re use is provided.
As you will build and use the User Defined Function (UDF) you will have a good comprehension and will be able to compare these with SP and views, and will be able to choose the best that fit. So lets get in.
In T-SQL User Defined Function (UDF) fall under three categories.

• Scalar
• Inline table-valued
• Multi-statement table-valued
  

In coming lines we will go through brief description and use of these three categories.

SCALAR UDF:
A scalar user-defined function is used to return a single value based on zero or more parameters. Like a SP you may provide it one or more variables and after defined processes, will return a single value. Scalar User Defined Function (UDF) are often used for converting or translating a current value to a new value, or performing other sophisticated lookups based on specific parameters. Scalar functions can be used within search, column, and join expressions.

Consider following piece of code to create a scalar UDF in pubs that will convert whole column of pubs database to upper case (for learning).

CREATE FUNCTION testScalar (@input varchar(50))
RETURNS varchar(50)
as
BEGIN
DECLARE @output varchar(50)
SET @output = UPPER(@input)
RETURN @output
END
GO


Now to test the created function

SELECT dbo.testscalar(au_lname)
FROM pubs..authors
GO


INLINE TABLE-VALUED UDF:
Inline table-valued UDF returns a table data type. Result is based on single select statement that is used to define the result. You do not explicitly define the returned table, but use a single SELECT statement for defining the returned rows and columns.
The plus point is that unlike SP a User Defined Function (UDF) can be referenced in from clause of a query. It may be used to create joins to other tables. And I have to mention that unlike view they can accept parameters.
Consider the following piece of code to create an inline User Defined Function (UDF) that will return all rows from sales table with quantity greater than given parameter.

USE pubs
GO

CREATE FUNCTION
testInLine (@input INT)
RETURNS TABLE
AS
RETURN(SELECT * FROM SALES
WHERE QTY > @input)
GO


Now use the function to simply count the returned rows

SELECT COUNT(*)
FROM testinline(2)
GO


MULTI-STATEMENT TABLE-VALUED:
Like inline table-valued UDF a multi-statement table valued UDF also returns a result set and is referenced in from clause. But the difference is that these are not restricted to use simple select statement for data retrieval within function definition. But these can use multiple T-SQL statements to retrieve the required result.
Consider following piece of code to create and run a multi-statement UDF. The code is from sqlteam.com. I liked this brief example so its better to consider it here.

CREATE FUNCTION dbo.customersbycountry ( @Country VARCHAR(15) )
RETURNS
@CustomersbyCountryTab TABLE (
[CustomerID] [NCHAR] (5), [CompanyName] [NVARCHAR] (40),
[ContactName] [NVARCHAR] (30), [ContactTitle] [NVARCHAR] (30),
[Address] [NVARCHAR] (60), [City] [NVARCHAR] (15),
[PostalCode] [NVARCHAR] (10), [Country] [NVARCHAR] (15),
[Phone] [NVARCHAR] (24), [Fax] [NVARCHAR] (24)
)
AS
BEGIN
INSERT INTO @CustomersByCountryTab
SELECT [CustomerID],
[CompanyName],[ContactName],[ContactTitle],
[Address],[City],[PostalCode],[Country],
[Phone],[Fax]
FROM [Northwind].[dbo].[Customers]
WHERE country = @Country
DECLARE @cnt INT
SELECT @cnt = COUNT(*)
FROM @customersbyCountryTab
IF @cnt = 0
INSERT INTO @CustomersByCountryTab (
[CustomerID],[CompanyName],[ContactName],
[ContactTitle],[Address],[City],
[PostalCode],[Country],[Phone],
[Fax] )
VALUES ('','No Companies Found','','','','','','','','')
RETURN
END
GO

SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')


Further
like any T-SQL object you may alter or drop a UDF. And here is a use full code snippet to find all UDFs in any database.

SELECT * FROM [dbName]..sysobjects
WHERE xtype = 'FN'
GO

Just replace [dbname] with name of your database.

At the end here are some important attributes of UDF.

• User Defined Function (UDF) prohibit Usage of Non-Deterministic Built-in Functions, Functions GETDATE(), RAND() etc can not be used in User Defined Function (UDF).
• User Defined Function (UDF) Returns Only One Result Set or Output Parameter. Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
• User Defined Function (UDF) can not Call Stored Procedure. Only access to Extended Stored Procedure.
• User Defined Function (UDF) can not run dynamic SQL which are dynamically build in User Defined Function (UDF). Temporary Tables can not be used in UDF as well.
• User Defined Function (UDF) can not Return XML. FOR XML is not allowed in UDFUser Defined Function (UDF) does not support SET options. SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
• User Defined Function (UDF) does not Support Error Handling. RAISEERROR or @@ERROR are not allowed.
• You can use a User Defined Function (UDF) directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can't use a stored procedure in a SELECT statement.
• User Defined Function (UDF) cannot be used for DML operations (INSERT/UPDATE/DELETE.
• You can not use print statement inside a scalar User Defined Function (UDF)

Finding Nth highest value in a column through T-SQL

Here is a situation, to find a specific highest value in a column. Most common example may be that of a salary column where one may need 7th highest salary.
To make short of this short problem i have selected pubs..Sales.qty column to work with.
Apply following query and you will get total 11 distinct values arranged

USE Pubs
GO

SELECT DISTINCT qty
FROM sales
ORDER by qty DESC
GO


Now suppose we have to find 7th highest value in qty column. The simple query for this is

SELECT min(qty)
FROM sales
WHERE qty IN
(SELECT distinct TOP 7 qty
FROM sales
ORDER BY qty DESC)
GO


So its done.
It is appropriate to mention here another query that i found on a blog.

SELECT TOP 1 qty
FROM ( SELECT DISTINCT TOP 7 qty
FROM sales
ORDER BY qty DESC) ABC
ORDER BY qty ASC
GO

So in above queries you may find nth highest value where n is always greater than 1.
That is all for finding nth highest value in a column.

Using order by with nulls in T-SQL

Question:
You have a table with EmpName and DateOfLeaving columns.
The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company.
The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group.
Consider the Example:

Table: Employees

EmpName DateOfLeaving

Abc 10 Oct 1999

Bcd 11 Nov 1998

Ccd null

Dcd 10 Aug 2000

Eed null

The solution should be:

EmpName DateOfLeaving

Bcd 11 Nov 1998

Abc 10 Oct 1999

Dcd 10 Aug 2000

Ccd null

Eed null

Which query will produce this output?

Answer:

• Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,’10/10/9999’),EmpName asc
• Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc
  

Explanation:
In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Name System Databases in SQL Server 2005

Question:

Name system databases in SQL Server 2005?

Answer:

• master
• model
• msdb
• tempdb
• Resource
  

Explanation: All of these are system databases in SQL Server 2005.


Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

DAC in SQL Server 2005 Express Edition

Question:

By default does SQL Server 2005 Express allow Dedicated Administrator Connections (DAC)?

Answer: Yes, if started with trace flag 7806

Explanation: SQL Server 2005 Express Edition does not listen on the DAC port by default. If it is started with trace flag 7806, then DAC connections can be made.

Question:

On which port can you make a Dedicated Administrator Connection (DAC)?

Answer: Dynamically assigned

Explanation: The DAC port is dynamically assigned when the instance starts and listed in the SQL Server error log. By default it tries for 1434, but it is possible that this port is already in use. The message will be something like:

Dedicated admin connection support was established for listening locally on port 1977.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

SQLAgent status through T-SQL

Question:

Using Transact-SQL, how can you find out whether SQLAgent is running?

Answer:

• sysprocesses table for program_name like SQLAgent%
• Use xp_servicecontrol querystate SQLSERVERAGENT
  

Explanation: Querying the sysprocesses table will return a row if sql agent is running, else it will not return any rows. The xp_servicecontrol procedure will return the status of the service (running/stopped) on 2005 / 2008 editions except express. On a SQL Server Express instance this command will return an error. So you need to check for errors as well as the status to determine whether the agent is running.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

union for different data types in T-SQL

Question:

What happens with this code?

select 'B'

union

select 4

Answer: The varchar is converted in int (error results)

Explanation: This is an example of an implicit conversion in T-SQL. In this case, an error is returned as the varchar is converted to an int, or an attempt is made, which returns an error. The int is of higher precedence than a varchar, so that is the order of conversions.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Call it ad-hoc or out of box thinking

Although is does not always work but, some times ad-hoc thinking makes the solution very simple. Consider the following scenario for which a very simplified solution is provided for. We may call it out of box thinking as well.

Question:
Is there a condition for the WHERE clause to display the details about the students born in the month numbers of 1[Jan], 3,4,5,7,8? The condition should not use both IN and OR operators.

Answer: Yes

Explanation: We can give the condition as follows:
where datename(m,dob) not like '%e%'
because these month(s) specified do not have the letter 'e', but all the other months have at least one 'e' in month name.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.