OVER clause is Enhanced in SQL Server 2012

In SQL Server 2012, one of the additions to OVER clause is, ROWS Clause.
In general, when we apply aggregate functions in over clause, it will apply to all rows in that group/partition.

Suppose, if we wants to restrict the number of rows, it involves some complexity. Assume, to calculate Next 5yrs total revenue for a company, the number of rows needs to be considered are always that row and next 4 rows.

Now in SQL Server 2012, Over Clause is enhanced with ROWS Clause, which allows specifying number of rows to consider while applying aggregates


DECLARE @Companies_Revenue TABLE(CompanyName varchar(10),[Year] int,Amount int)


SELECT COMPANYNAME,
YEAR,
AMOUNT,
SUM(AMOUNT) OVER (PARTITION BY CompanyName ORDER BY YEAR ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) [NEXT 3 YEARS REVNUE],
SUM(AMOUNT) OVER(PARTITION BY CompanyName ORDER BY YEAR ROWS UNBOUNDED PRECEDING)
[RANGE CUMMILATEIVE AMOUNT],
SUM(AMOUNT) OVER(PARTITION BY CompanyNameORDER BY YEAR ROWS UNBOUNDED PRECEDING) [CUMMILATEIVE AMOUNT],
SUM(AMOUNT) OVER (PARTITION BY CompanyNameORDER BY YEARROWS BETWEEN 1 PRECEDING AND CURRENT ROW)[Last 3 Years Revenue]
FROM @COMPANIES_REVENUEINSERT INTO @Companies_Revenue (CompanyName,[Year],[Amount])VALUES ('ABC',2000,100000),('ABC',2001,200000),('ABC',2002,35000),('DEF',2000,50000),('DEF',2001,75000),('DEF',2002,35000)　 

Case Sensitive Search on a Case Insensitive SQL Server

Most SQL Server installations are installed with the default collation which is case insensitive.  This means that SQL Server ignores the case of the characters and treats the string 'ram' equal to the string 'RAM'.  If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?

SolutionOne option is to specify the collation for the query to use a case sensitive configuration.  Let's show an example of a case sensitive search on a case insensitive SQL Server

SELECT * FROM (

SELECT 'RAM' a

UNION ALL SELECT 'Ram' a

UNION ALL SELECT 'ram' a

)RAMWHERE

SELECT * FROM (

                  SELECT 'RAM' a

                  UNION ALL

                  SELECT 'Ram' a

                  UNION ALL

                  SELECT 'ram' a

)RAM WHERE

a LIKE '%Ram%' Collate SQL_Latin1_General_CP1_CS_AS

Run a SQL command on all SQL Server Databnases at a time with out using cursors

There are times to run a SQL command against each database on one of my SQL Server instances. There is a stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb



Query Information From All Databases On A SQL Instance

----------------------------------------

--This query will return a listing of all tables in all databases on a SQL instance:

EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'


---------------------------------------

--This query will return a listing of all files in all databases on a SQL instance:

EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'



--Remove the USE ? clause and you end up executing the query repetitively within the context of the current database:

EXEC sp_MSforeachdb 'SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'



--------------------------------------

CURSOR:--

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)

DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor


Considering the behavior is similar I'd rather type and execute a single line of T-SQL code versus more lines of cursor code....



Regards

Ram

SQL Server 2012 Functions - Lead and Lag

These functions access data from a subsequent row (lead) and previous row (lag) in the same result set without the use of a self-join.

The syntax for the Lead and Lag functions is:

LAG|LEAD (scalar_expression [,offset] [,default]) 
    OVER ( [ partition_by_clause ] order_by_clause ) 

CREATE TABLE [dbo].[Test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[Department] [nchar](10) NOT NULL,
[Code] [int] NOT NULL,
CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Insert some test data
insert into Test_table values('A',111)
insert into Test_table values('B',29)
insert into Test_table values('C',258)
insert into Test_table values('D',333)
insert into Test_table values('E',15)
insert into Test_table values('F',449)
insert into Test_table values('G',419)
insert into Test_table values('H',555)
insert into Test_table values('I',524)
insert into Test_table values('J',698)
insert into Test_table values('K',715)
insert into Test_table values('L',799)
insert into Test_table values('M',139)
insert into Test_table values('N',219)
insert into Test_table values('O',869)

Our table data will look like this:

Now the query for lead value and lag value will be:

SELECT id,department,Code,
LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,
LAG(Code,1) OVER (ORDER BY Code ) LagValue
FROM test_table

In the above example, for the first row the Lead value is the value of the next row because the offset is set to 1. The Lag value is NULL because there were no previous rows.

Now if we change the Lead offset to 2 and Lag offset to 3 the output will be as follows:

One thing to note is that NULL values appear, because there are not values for the Lag or Lead.  To replace NULL values with zero add 0 in Lead\Lag function as shown below. 

SELECT id,department,Code,
LEAD(Code,2,0) OVER (ORDER BY Code ) LeadValue,
LAG(Code,3,0) OVER (ORDER BY Code ) LagValue
FFROM test_table

 

 

 

Ram

 

Policy Management in SQL SERVER 2008

Policy-Based Management is indeed a new feature in SQL Server 2008.  It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.  Originally this feature was called the Declarative Management Framework but has since been renamed.  There are a number of terms that we need to define in order to begin to understand Policy-Based Management:

• Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
• Facet - a predefined set of properties that can be managed
• Condition - a property expression that evaluates to True or False; i.e. the state of a Facet
• Policy - a condition to be checked and/or enforced

Policy-Based Management is configured in SQL Server Management Studio (SSMS).  Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes:

Expand the Facet node to see the list of facets:

As you can see there is a rather comprehensive collection of facets predefined in SQL Server 2008, allowing you to manage just about every aspect of SQL Server.  Double click on a facet to see the actual list of properties in the facet; e.g. double click the Database facet:

These facet properties are used to specify a condition; e.g. AutoShrink = False means that you do not want to automatically shrink database files.  A policy specifies an expression that evaluates to True or False.  The expression can be made up of one or more conditions logically joined by And / Or.
In this tip we are going to gain an understanding of Policy-Based Management by walking through the following demonstration:

• Create a Condition
• Create a Policy
• Evaluate a Policy

The demo steps below were only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 
Create a Condition
The starting point in Policy-Based Management is to create a Condition.  Right click on Conditions in the SSMS Object Explorer (under the Management | Policy Management node) then select New Condition from the menu.  Fill in the dialog as follows:

You select a single Facet for a Condition, then enter an Expression.  The Expression evaluates to either True or False.  This is the essence of Policy-Based Management which will test whether the Condition is True.
Create a Policy
Right click Policies in the SSMS Object Explorer (under the Management | Policy Management node) then select New Policy from the menu.  Fill in the dialog as follows:

The Check Condition drop down will include the list of conditions that you have defined.  You can check Every Database in the Against targets list, or you can click the glyph (between Every and Database) and define a condition.   Execution Mode can have one of the following values:

• On Demand (this is the default)
• On Schedule
• On Change - Log Only
• On Change - Prevent

The On Demand option only evaluates the policy when a user right clicks on the policy in the SSMS Object Explorer and selects Evaluate from the menu. 
The On Schedule option takes advantage of SQL Agent to execute a job on a particular schedule to check the policy.  After selecting On Schedule from the Execution Mode drop down list, you can click either the Pick or New button.
To pick an existing schedule, make a selection from the available options:

To create a new schedule, fill in the familiar schedule dialog:

When policy evaluation is scheduled, any violations are logged to the Windows Event Log.
The On Change - Log Only option evaluates the policy whenever the property in the facet is changed and any violation is logged to the Windows Event Log.  The On Change - Prevent option evaluates the policy whenever the property in the facet is changed and actually prevents the change; this option uses DDL triggers to enforce the policy.  Not all changes can be detected and rolled back by DDL triggers; the Execution Mode drop down list will include the On Change - Prevent option only when it is available.
One final note on the policy setup concerns the Enabled check box.  When the Execution Mode is On Demand, the Enabled check box must be unchecked; for all other options you must check the Enabled check box in order for the policy to be evaluated.
Evaluate a Policy
To evaluate a policy on demand, right click on the policy in the SSMS Object Explorer and select Evaluate from the menu.  The following is a partial screen shot of the output from evaluating a policy on demand:
 

The green check icon signifies that the policy evaluated to True for the databases shown.  Not shown above is a Configure button that allows the user to automatically fix a target where the policy evaluates to False.
Right click on a database in the SSMS Object Explorer and select Properties from the menu.  Click the Options page and change the AutoShrink property to True.  Evaluate the policy again and you will see the following output:

Note the red icon with the X indicating that policy evaluation failed for a particular database.  Not shown above is the Configure button which you can click to automatically change the AutoShrink property to comply with the policy.
Edit the policy and change the Execution Mode to On Change - Log Only.  Select a database and change the AutoShrink property to True.  Open the Windows Event Viewer, click on Application and you will see an event that was written when the policy evaluation detected the violation:

To test the On Change - Prevent Execution Mode for a policy, create a new condition and a new policy.  Create a new condition as follows:

Now create a new policy as follows:

This policy will prevent a table from being created if the table name does not begin with 'tbl_'.  Open a New Query window in SSMS and enter a create table script.  When you execute the CREATE TABLE script you will get the following error message and the table will not be created:

CREATE TABLE sample ( message varchar(256) ) Policy 'Table Prefix Must Be tbl_' has been violated by '/Server/(local)/Database/demo/Table/dbo.sample'.This transaction will be rolled back. Policy description: ''Additional help: '' : ''. Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 50 The transaction ended in the trigger. The batch has been aborted.

Table Valued Parameters in SQL SERVER

Table-Valued Parameters: is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

1.      Create a table type and define the table structure

2.      Declare a stored procedure that has a parameter of table type.

3.      Declare a table type variable and reference the table type.

4.      Using the INSERT statement and occupy the variable.

5.      We can now pass the variable to the procedure.

--Create a Table Named 

CREATE TABLE EMPLOYEE(SALARY INT)

--Insert data ..

INSERT dbo. EMPLOYEE(SALARY) VALUES (1000),(2000),(3000),(4000)

--Create a SQL SERVER TYPE AS TABLE --

CREATE TABLE EMP_DETAILS(SALARY INT)

--Create a ProcedureCREATE PROC NEW_EMPLOYEE(@NEW_EMP AS EMPLOYEE READONLY)

AS INSERT INTO EMP_DETAILS SELECT * FROM @NEW_EMP;

--Execution of table valued parameters

 DECLARE @NEW_EMP AS EMPLOYEE

 INSERT INTO @NEW_EMP(SALARY) SELECT SALARY FROM EMPLOYEE

 EXEC NEW_EMPLOYEE @NEW_EMP;

--Now verify the data...

SELECT * FROM EMP_DETAILS

Regards
Ram

_________________________________________________________________________________________