New Features in SQL Server 2011 (Denali)

SQL Server 2011 has several major enhancements including a new look for SSMS. SSMS is now similar to Visual Studio with greatly improved Intellisense support.
This article we will focus on the T-SQL Enhancements in SQL Server 2011.
The main new TSQL features in SQL Server 2011 are:
WITH RESULT SETS
OFFSET AND FETCH
THROW in Error handling
SEQUENCE
WITH RESULT SETS

--Create Sequence
CREATE SSEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000

--- Use
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c

-- Alter
ALTER SEQUENCE [Seq]
RESTART WITH 1

-- Clean Up
DROP SEQUENCE [Seq]


CREATE PROCEDURE Denali_WithResultSet
AS
BEGIN
SELECT 1 as No,'Tsql' Type, 'WithResultSet' AS Feature UNION ALL
SELECT 2 as No,'Tsql' Type, 'Throw' AS Feature UNION ALL
SELECT 3 as No,'Tsql' Type, 'Offset' AS Feature UNION ALL
SELECT 4 as No,'Tsql' Type, 'Sequence' AS Feature
END
GO
EXEC Denali_WithResultSet
WITH RESULT SETS
(
( No int,
FeatureType varchar(50),
FeatureName varchar(50)
)
)


--- Importing concept of paging/Batching
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
ORDER BY NAME
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

Dinesh Babu Verma

http://dineshbabuverma.blogspot.com/

Dinesh Babu Verma

Blogger Buzz: Blogger integrates with Amazon Associates

Order of SQL Query execution

Order of SQL Query execution are as follows
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

SQL SERVER – User Defined Functions (UDF) Limitations

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

* UDF has No Access to Structural and Permanent Tables.
o UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
* UDF Accepts Lesser Numbers of Input Parameters.
o UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
* UDF Prohibit Usage of Non-Deterministic Built-in Functions
o Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
* UDF Returns Only One Result Set or Output Parameter
o Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
* UDF can not Call Stored Procedure
o Only access to Extended Stored Procedure.
* UDF can not Execute Dynamic SQL or Temporary Tables
o UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
* UDF can not Return XML
o FOR XML is not allowed in UDF
* UDF does not support SET options
o SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
* UDF does not Support Error Handling
o RAISEERROR or @@ERROR are not allowed in UDFs.
* UDF does not support transation control stmt
o commit,save point,rollback ,begin trans are not supported in sql server