How to find SQL Server Version,Edition,Server Name?

How to find sql server version,edition,Server Name?

SQL Server provides a System Defined function SERVERPROPERTY(propertyname) .

By using this function you can find a number of things

Property Name	Description	syntax
Edition	Return SQL Server edition installed on machine.	select ServerProperty('edition')
EditionID	return Edition ID	select ServerProperty('editionid')
InstanceName	Return instance name if it is not default.In case of default return Null.	select ServerProperty('InstanceName')
ProductVersion	return Product version	select ServerProperty('ProductVersion')
BuildClrVersion	return version of the .NET framework Common Language Runtime (CLR)	select ServerProperty('BuildClrVersion')
EngineEdition	return 1 = Desktop 2 = Standard 3 = Enterprise 4 = Express 5 = SQL Azure	select ServerProperty('EngineEdition')
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, or an error.	select ServerProperty('IsClustered')
MachineName	Return machine name	select ServerProperty('MachineName')
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated	select ServerProperty('ResourceLastUpdateDateTime')
ProductLevel	Returns Level of the version of SQL Server instance 'RTM' = Original release version 'SPn' = Service pack version 'CTP', = Community Technology Preview version	select ServerProperty('ProductLevel')

When and why you should use 1=1 in WHERE clause?

One Interviewer ask this question during interview of my friend.It is easy to answer but need some specific and to the point answer.So I try to relate it with C#.

 If you don't know  the list of conditions at compile time and it will built at run time, Then you can made a condition with “where 1=1”. and for other conditions that will affect run time, use

and  <condition>.

Example

StringBuilder sb = new StringBuilder();
         sb.Append("SELECT * FROM Products");  // Your query
         sb.Append(" WHERE 1=1"); // always true condition
 
         // append query's where clause
 
         if (catID != 0)
         {
             sb.Append(" AND categoryID= {0}", catID);
         }
         if (minPrice > 0)
         {
             sb.Append(" AND itemPrice >= {0}", minPrice);
         }
 
         SqlCommand cmd = new SqlCommand(sb.ToString(), cnn);
         SqlDataReader dr = cmd.ExecuteReader();
         // your code to read data from dr.

.

.Net Components(CLR) Integration with SQL Server

Microsoft provide a very powerful feature to SQL Server 2005 or later to integrate .Net components with SQL Server that is “CLR Integration”.CLR integration means that you can create database objects like stored procedures, triggers, user-defined types ,functions and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.By using CLR integration,You can make complex tasks easier.

For Example: In SQL Server Express edition,there is no database mail functionality to create mail profile and sent mail.To achive this you can use CLR function.A good demostration of this example is given by Greg Robidoux.

Sending mail from SQL Server Express edition

In simple word if you are familiar with .Net application development ,then it is very easy to understand concept of CLR Integration.To implement this,You need to create a class library(dll) and register with SQL Server.

By default,CLR integration is disable.So you need to enable it.

1. by using this query

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

OR

2.  By you can set by using Surface Area Configuration Tool
   
   

After enabling CLR integration services,You can create and integrate CLR stored procedure,functions,triggers etc.For this, you need to create CLR code in any .Net compatible language.Visual Studio 2008/2010 already provided template for creating CLR database objects.

Steps to Create CLR database objects:

1. Go to File =>New Project
2. select DataBase=> SQL Server
3. Select Visual C# SQL CLR Database Object

4. On ok,New window appears and asking for database reference.Select relevant one or add new reference.
5. Go to Solution explorer and add new item
   
   
6. Select that you want to work with
7. Add functionality to hellofunction.cs
   
   
   
   

4. Build your project
   
   
5. Right click solution explorer=> Deploy your project(it may ask for server credentials)
6. The below error may occured

Beginning deployment of assembly clrproject.dll to server localhost: TestDb

C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error : Could not connect to server localhost TestDb : Login failed for user 'sa'.

11. Provide connection string
12. Rebuilt and deploy
13. If your Project .Net Framework and SQL Server Supporting .Net framework not compatible, the below error occurred

The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.

Deployment script generated to:D:\learning\clrproject\clrproject\bin\Debug\clrproject.sql

Try to deploy by changing Build Framework in Project Proprties.

14. If the error not resolved till now then it may be you mark your assembly as UNSAFE/External and database is TRUSTWORTHY.So change permission level to Safe.

15. Now Deploy your solution.I hope it will deployed successfully.
16. After successful deployment,The CLR database object will appears in Object Explorer of SQL Server Mgmt. Studio.
    
    
17. Now you can use this function as SQL Server User defined functions.

                     

select dbo.hellofunction()

Output:

                         

18. In the same way you can create CLR stored procedure,types,triggers etc.

Advantages of CLR integration:

1. As you know,T-SQL does not support arrays, collections, for-each loops, bit shifting, or classes.It is specifically designed for direct data access and manipulation in the database.But if you are using Managed code then these can be supported. CLR allows these constructs.
2. CLR has a built in RegEx object.
3. you can consume an external Webservice from a SQLCLR method.
4. Potential for improved performance and scalability:

How to check SQL Server instance Target Framework

When you uses .Net components in SQL server.The component build framework should be compatible with SQL server target framework.If it is not the below error occurred

The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server.

So you need to check SQL Server target framework.

select * from sys.dm_clr_properties 

To resolve this error, set target framework of your projects compatible  with this result accordingly.

SQL Server FAQ on COMPUTE,STUFF,REPLACE,ISNULL,COLLEASE and temporary Tables

SQL Server FAQ 2:

1.      What are COMPUTE and COMPUTE BY  in SQL Server

COMPUTE can be used to generate an addition summary column at the end of result set followed by aggregate functions like SUM, AVG, COUNT, MAX, MIN, STDEV, STDEVP, VAR, and VARP.

Syntax:

COMPUTE    { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }  ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]  

     Example:

select a.cust_id,a.amount from dbo.tblPaymentDetails a where cust_id='1751'
compute SUM(a.amount).

    

     COMPUTE BY create groups of result set based on Column in BY clause

select a.cust_id,a.amount from dbo.tblPaymentDetails a order by cust_id  compute SUM(a.amount) by a.cust_id

2.      Where vs. Having clause

Where is used with search condition upon rows whereas having is used with search condition for group or aggregate.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name HAVING searchcondition;

Having can be used without group by but your query should have aggregate functions. You cannot be able to select any column without aggregation function

SELECT aggregate_function(column_name) FROM tablename HAVING  aggregate_function(column_name)=value

Example:

SELECT SUM(qty)FROM dbo.Orders HAVING SUM(salesprice) > 10
SELECT  * FROM dbo.Orders Where salesprice > 10

3.      STUFF vs. REPLACE

Replace function replace all occurrences of a specified string value with another string value.

REPLACE ( string_expression , string_pattern , string_replacement )

Example:

SELECT  REPLACE('queryingsql','q','A')

Result:  AueryingsAl.

q is replaced by A in whole expression for each occurrence. Now if you want to replace a part of string expression. then you need to use STUFF.

STUFF ( character_expression , start , length , replaceWith_expression )

STUFF deletes all character (specified in length parameter) from specified start position and insert replaceWith_expression string.

SELECT STUFF('queryingsql',9, 5,' SQL SERVER')

Result:  querying SQL SERVER

And on other hand it is length and location based so it not replaces all occurrence of specified pattern.

4.      Local Temporary table vs. Global Temporary table

Local Temporary table: It is created with single ‘#’ as prefix of table name and is available for the connection for which it was created. It is automatically dropped when this connection closed or user drop explicitly by using drop query.

CREATE TABLE #temptest
( ID int NOT Null ,Name varchar(250))

Global Temporary table: It is created with ‘##’ as prefix of table name and is available for all connections or any connection created after. These tables dropped when all connections that are referencing the table disconnected from the instance of SQL Server.

CREATE TABLE ##gobaltest
( ID int NOT Null ,Name varchar(250)) 

5.      COLLEASE vs. ISNULL

·         COALESCE and ISNULL   both can be used in NULL handling. But there are some differences in both of them.

·         COALESCE allows multiple parameters while ISNULL allow only two parameters.      

SELECT ISNULL(null,0) –0
 SELECT COALESCE(null,null,0) --0

·         COALESCE returns type of value with high precedence from list of parameters. While ISNULL return first parameter type.

DECLARE @intval INT, @floatval FLOAT
SELECT  @floatval= 8.25, @intval = 10
SELECT COALESCE(@floatval, @intval) AS Value

Result: 8.25

Since float have higher precedence then int.You can see data type precedence here http://msdn.microsoft.com/en-us/library/ms190309.aspx.

·         COALESCE is ANSI SQL Standard but ISNULL is T-SQL function.

Where to use SQL Server Cursor

Where to Use Cursor

Cursor can be used when you need to manipulate data in a set on a row-by-row basis, however you can also use T-SQL WHILE loop, CASE expression and some system defined stored procedure like sp_MSforeachdb, sp_MSforeachdb etc.

IN SQL Server the cursor can be implemented by 6 step process as:

1. Declare cursor

DECLARE ins_cursor CURSOR

FOR

    Select statement…………….

2. Open cursor

OPEN ins_cursor

3. Fetch row from the cursor

FETCH NEXT FROM ins_cursor 

INTO @fileno,……….variable list

4. Process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

….

…..

…

FETCH NEXT FROM ins_cursor 

INTO @fileno@fileno,……….variable list

END

5. Close cursor

CLOSE ins_cursor

6. Deallocate cursor

DEALLOCATE ins_cursor;

I have used cursor first time when I need to insert data from one table of one database into tables of another database. So I am explaining same example here

--declare variables to use in logic

DECLARE @RID INT

DECLARE @FILENO VARCHAR(50),@ISSUEDATE DATETIME,@REQDATE DATETIME,@RCN VARCHAR(50),@FILEREMARKS VARCHAR(200)

--decalare cusrsor

DECLARE INS_CURSOR CURSOR

    FOR

    --select statement

    SELECT FILENO,REQCONTROLNO,CONVERT(DATETIME,REPLACE(REQDATE,'-','/'),103),CONVERT(DATETIME,REPLACE(ISSUEDATE,'-','/'),103) ,REMARKS FROM ISSUE WHERE RECEIVEDDATE IS NULL

    --open cursor

OPEN INS_CURSOR

--Fetch row from the cursor

FETCH NEXT FROM INS_CURSOR

INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

--process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

--insert into first table

INSERT INTO FISS1.DBO.FILEREQUEST(REQDATE,REQSTATUS,RCN,CORDSTATUS,FILEREMARKS)

VALUES (@REQDATE,1,@RCN,1,@FILEREMARKS)

----assigning primary key(identity) value

SELECT @RID=@@IDENTITY

--insert into 2^nd table with foreign key @rid

INSERT INTO FISS1.DBO.ISSUE(REQID,FILENO,PRIORITY,PURPOSE,ISSUEDATE,STAUS)

VALUES(@RID,@FILENO,1,'GENERAL',@ISSUEDATE,1)

FETCH NEXT FROM INS_CURSOR INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

END

--close cursor

CLOSE INS_CURSOR;

DEALLOCATE INS_CURSOR;

Cursor Recommendations:

·         It is better to avoid using cursor because they consume memory for execution, so performance is less.

·         If you using cursor, then you should always close cursor after using it.

Further Reading

http://msdn.microsoft.com/en-us/library/ms181441.aspx

Prev---> Handling Null Values in SQL Server