Running sum simplified in SQL 2012

Running sum is a regular requirement in query for developer and many times developer has to run number  of complex  queries or self joins to get running sum
But now since SQL  2012 its very easy and funny and simplified.

Lets see this exciting but simple task
 

In 2012 we can use order by also while using aggregate window function. 

In 2008 we can use only partitioned by clause but now in SQL 2012 has simplified our task 

Lets see this 

  

 CREATE TABLE test

     (

          id    INT,

          value INT,

          statsdate date

     )

 DECLARE @i INT

 SET @i = 1

 WHILE @i <= 10

     BEGIN

         INSERTINTO test

         SELECT@i,

                @i *10,

                getdate()+ @i/3

         SET @i = @I + 1

     END

 SELECT *

 FROM   test

 

Running sum for entire table
 

We want to get running sum for all data in the table
 

 Here is the query 

WITH cte

     AS (SELECT *,

                Sum(value) OVER (ORDER BY id) AS runningsum

         FROM   test)

SELECT *

FROM   cte

Running sum reset on date

Now we want running sum but datewise
So when date changes we want to reset running sum
We have to use partition by clause using statsdate column
 

Here is the query
 

WITH cte

      AS (SELECT *,

                 Sum(value) OVER (ORDER BY id)  AS runningsum,

                 Sum(value) OVER (partition BYstatsdate ORDER BYid) ASrunningsum_partitioned

          FROM   test)

 SELECT *

 FROM   cte

 

Expression indicator SSIS

As we all are aware with expression usage in SSIS.
Lets see an example of using connector  for creating dynamic connection.
We can create expression for connection string and assign it to connections.
So at rutime connection string from this  expressions are evaluated first and based on it connection is made.
How SSIS does not giving any indication either connection has expressions defined not.

But from SQL 2012 we can see icon fro expression near the connection on which we have defined expression

see the example below
Here we defined expression for the connection so that we can see fx symbol near the connection

FileStream Part-3

You can access first two parts from here

Part-1
Part-2

Lets see now  how to use FileStream to access files.

USE FileTest

CREATE TABLE FileTestTable  AS FILETABLE

This will create table with necessary fileds for file properties

Lets see the structure of this table

Next step will be to copy files to the shared location and access it using this table

• We will go to windows share which we configured in part-1 for Server at location \\Server\MSSQLSERVER

• Here we will get Directory which we configured for database in part-2 

• Here we will get directory for the table which we created

• Copy files this location    \\server\MSSQLSERVER\FileTableExampleDir\FileTestTable

   We will copy a file here

Now query the table

SELECT *

FROM   FileTestTable

As we can see we get details for the files from the table

Ok If we delete the record from table what will happen?
It will delete file from that  location also.

So it means we can manage windows files by the table in SQL Server itself 🙂

  

FileTable Part-2

Now we are at part2 of FileTable .

You can go for part1 from here

In Part1 We learn how to configure server to use of FileSteam.

Now we will create a database where we will use FileTable

•  Lets create a database named FileTest
• Go to property of FileTest Database

1.Set FILESTREAM Directory Name to FileTableExampleDir
2.Set access to Full/Read-Only
In this example we are going to set it FULL

Now we have enabled database for filestream access.

• Now we need to add filegroup for filestream access.

       Go to property of Database – > FileGroups

   Now add a file to file group fileFG

       Make This File Group as default

FileTable – Part1

FileTables are used to store this unstrucutred data.
FileTables contains FileStream which is used to store unstructured data in Database.

We can indexes/triggers/constraints on FileTable , But we can not drip constraints as well as system constraints from the Table
File Tables have fixed Schemas.
We can handle files like we are managing Files in Windows
We can copy/delete/insert files like we are doing in Windows from SQL Server

To use FileTables we need to configure server to use it.
Lets see how to do it

1.First we will enable FileStream on SQL Server
Go to SQL Server Configuration Manager
Select SQL Server Service and Right Click it

select Enable Filestream for Transact SQL access
Enable Filestream for I/O access.

Also specify windows share where we want to store files.
Here we specify windows share named MSSQLSERVER

2.Now configure server to access filestream

Sp_configure ‘filestream_access_level’,2

RECONFIGURE WITH override