SQL with Manoj

Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

SQL Query for calculating Running Totals

July 4, 2011 4 comments

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

Let’s see how to get these Running totals by creating a simple SQL queries below:

USE [AdventureWorks]
GO

-- Method 1: (Query Cost 9%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

-- Method 2: (Query Cost 91%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  row, SalesPersonID, FirstName, LastName, OrderDate, TotalDue,
(select SUM(TotalDue) from RunTot b where b.SalesPersonID=a.SalesPersonID and b.row<=a.row) as RunTotal
from RunTot a

-- Output Listed below:

Categories: SQL Tips Tags: RUNNING TOTALS

SQL DBA – SHRINK DATABASE & SHRINK FILE – SQL Server

July 2, 2011 3 comments

While working with huge databases with large tables there are times when you get your database and log files expand ridiculously and eating up the entire disks.
This happens when you are either on an:
– OLTP environment and doing lots of DMLs (INSERTS, UPDATES or DELETES) or
– OLAP environment when you are running huge SQL queries that goes on expanding your tempdb and finally eats up your entire drive space.

There are lot of ways to better configure your disks and databases there to avoid such situations. But these kind of situations come and you have to approach the DBA or become a DBA yourself.

Check the LOG Size and Space used, here:

DBCC SQLPERF (LOGSPACE)

Check if your database has adequate space available to free up:

SELECT 
	name, 
	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Before shrinking the log file, it better to backup your transaction logs, as shown below:

BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(database_name_log, 2)

OR, you can simply change the Database recovery mode to Simple, and run the above mentioned DBCC statement.

tempdb can be freed up by simply restarting SQL Server. When SQL Server shuts down it deletes the tempdb and on restart it automatically creates tempdb database. Thus you get fresh tempdb with minimal space. This is not advisable on production servers and should be handled by experts.

Categories: DBA Stuff Tags: BACKUP LOG, DBCC SHRINKDATABASE, DBCC SHRINKFILE

TSQL Interview Questions – Part 4

July 1, 2011 7 comments

Next part of TSQL Interview Questions, contd. from my previous post.

76. What are Running totals and how would you calculate them? Create a single SQL statement starting with SELECT or ;WITH.
https://sqlwithmanoj.com/2011/07/04/calculating-running-totals/

77. What are the various SSIS logging mechanisms?
– Text file
– SQL Server Profiler
– SQL Server Log Provider, link
– Windows Event Log
– XML File
MS BOL link: http://msdn.microsoft.com/en-us/library/ms140246.aspx

78. On which table the SSIS SQL Server logs are stored?
On 2005 its sysdtslog90 & on 2008 its sysssislog.
More on SSIS logging on: https://sqlwithmanoj.com/2011/06/15/logging-in-ssis-using-sql-server-log-provider/

79. Reverse a String value without using REVERSE() function and WHILE loop. Should be a single SQL statement/query.
https://sqlwithmanoj.com/2011/08/18/reverse-a-string-without-using-tsqls-reverse-function/

80. What is the use of BCP utility in SQL Server and how will you use it?
https://sqlwithmanoj.com/2011/09/09/bcp-in-out-queryout-the-bcp-util/

81. Is there any difference between Excel Source in SSIS 2005 & 2008?
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ea770da2-6f0c-41c4-8093-52b5df73f460/#4267c1f1-74a0-4e2c-b5a4-e2909bf4e5f4

82. Difference between Bookmark/Key lookup & RID lookup?
Lookups happen when an Index does not cover a Query, or SELECTED columns in a Query are not available in an Index, thus it lookup in the Clustered Index or the table for missing information/columns. Bookmark/Key lookup happens when a Clustered Index is present, and RID lookup happen when there is no Clustered Index present.

83. How nested transactions behave?
Here is a scenario:
I have a transaction T and inside this there are 2 transactions T1 AND T2.
If TRANSACTION T2 fails then what happens to transaction T and T1.
There is no concept of Nested Transactions in SQL Server and creating them does not make sense. Any Transaction fails will Rollback all outer Transactions, and thus all inner/nested Transactions will also Rollback, check here: https://sqlwithmanoj.com/2015/05/26/sql-myth-nested-transactions-in-sql-server-and-hidden-secrets/

84. How will you check if a stored procedure with 500 line of code is running very slow? What steps will you take to optimize it?

85. New features in SQL Server 2005 compared to SQL Server 2000 you’ve worked with.
– PIVOT, UNPIVOT, Ranking functions (row_number, rank, dense_rank, ntile), CTEs, Grouping sets (ROLUP, CUBE), Intersect, Except, OUTPUT clause, Merge statement, Try-Catch, BIDS (SSIS, SSRS, SSAS), CLR, SMO.
New datatypes: XML, VARCHAR(max), NVARCHAR(max), VARBINARY(max) deprecating the TEXT, NTEXT AND IMAGE datatypes.
– XML indexes.
– Database (SMTP) mail, SSMS, DMVs, Express Edition, Service Broker, Data Encryption, MARS
For all the new SQL Server 2005 features discussed on this blog check here: https://sqlwithmanoj.com/category/sql-server-versions/sql-server-2005-sql-server-versions/

86. How will you copy unique records from duplicates in source to destination in SSIS?

87. What transformation will you use to concatenate First name and Last name in SSIS?

88. What do you mean by selectivity of a column/table?
The selectivity is what goes with the cardinality concept. The “cardinality” refers to the number of “distinct” values, as in the set theory so, take a column “SEX”. The possible values are “male” and “female” (ignoring for the moment other possible values like “unknown” or even “other”) … so, your cardinality for that column would be 2, no matter how many rows you have in that table.

The selectivity is the “number of rows” / “cardinality”, so if you have 10K customers, and search for all “female”, you have to consider that the search would return 10K/2 = 5K rows, so a very “bad” selectivity.

The column for the primary key on the other side is “unique”, and hence the cardinality is equal to the number of rows, by definition. So, the selectivity for searching a value in that column will be 1, by definition, which is the best selectivity possible.

89. Difference between EXISTS & IN, which one gives good performance?
EXISTS vs IN performance: https://sqlwithmanoj.com/2011/02/15/not-in-not-exists-joins-with-null-values/

90. What output will “SELECT 1/2” statement give?
0, it will give zero.

91. What is Database Partitioning?
This involves 4 steps:
1. Create Database with different file groups
2. Create Partition Function
3. Create Partition Scheme
4. Create Partitioned Table or Index

92. What is the use of NOLOCK option?
https://sqlwithmanoj.com/2013/10/04/difference-between-nolock-and-readpast-table-hints/

93. How many types of recovery models are available for a database?
1. Simple
2. Bulk logged
3. Full

94. How many types of temporary tables are there in SQL Server?
– Local Temp tables (#)
– Global temp tables (##)
– Table variables (@)
Check here to know about all these temporary tables: https://sqlwithmanoj.com/2010/05/15/temporary-tables-vs-table-variables/ | YouTube

95. In how many ways you can get a table’s row count?

--// 1. Using COUNT(*)
Select count(*) from Person.Contact

--// 2. using COUNT(1)
select count(1) from Person.Contact

--// 3. Using SUM() aggregate function
select sum(1) from Person.Contact

--// 4. Using sysindexes view
select object_name(id), rows from sys.sysindexes where object_name(id) = 'Contact' and indid&amp;lt;2

--// 5. Using sp_spaceused system SP
exec sp_spaceused 'Person.Contact' 

--// 6. Using DBCC CHECKTABLE function
DBCC CHECKTABLE('Person.Contact') 

--// Note: Before running 5 &amp;amp; 6 you may need to run this script:
DBCC UPDATEUSAGE ('AdventureWorks','Person.Contact') WITH COUNT_ROWS

Reference

96. In how many ways you can select distinct records from a table?

97. In how many ways you can select top 1 row?

98. What are the new features introduced in SQL Server 2008 R2?
SQL Server 2008 new features: https://sqlwithmanoj.com/category/sql-server-versions/sql-server-2008-sql-server-versions/

99. What are the new features added SQL Server 2012 (Denali)?
SQL Server 2012 (denali) new features: https://sqlwithmanoj.com/denali-2012/

100. What new feature has been added to TRY-CATCH construct?
Check here for all posts related to TRY-CATCH: https://sqlwithmanoj.com/tag/try-catch/

… more questions on next post Part-5.

Categories: Interview Q Tags: SQL Server Interview Questions

Are User Defined Functions (UDF) pre-compiled – MSDN TSQL forum

June 15, 2011 Leave a comment

–> Question:

I have seen several posts saying that a UDF is not pre-compiled like a stored procedure.

But this MSDN Page it says both are.

Could any one let me know which one is correct and what is meant by this?

–> My Answer:

UDF are not pre-compiled, UDFs similar to SPs are only compiled on their first execution. They are just parsed and created in SQL catalog.

Try to execute the DMV query before the SQL query where the function TsqlSplit() is called. It won’t give to you any cache’d record. But after you execute the SQL query and then execute the DMV query then you will see a cache’d record for Function execution.

Check this:

dbcc freeproccache
GO

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE FUNCTION [dbo].[TsqlSplit] (@List As varchar(8000)) 
RETURNS @Items table ( 
  Item varchar(8000) Not Null) 
AS 
  BEGIN 
      DECLARE @Item As varchar(8000), 
              @Pos  As int 

      WHILE DATALENGTH(@List) > 0 
        BEGIN 
            SET @Pos=CHARINDEX(',', @List) 

            IF @Pos = 0 
              SET @Pos=DATALENGTH(@List) + 1 

            SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))) 

            IF @Item <> '' 
              INSERT INTO @Items 
              SELECT @Item 

            SET @List=SUBSTRING(@List, @Pos + DATALENGTH(','), 8000) 
        END 

      RETURN 
  END 
GO 

SELECT objtype, 
       cacheobjtype, 
       usecounts, 
       text 
FROM   sys.dm_exec_cached_plans AS ECP 
       CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
WHERE  EST.text LIKE '%TsqlSplit%' 

GO 

SELECT * 
FROM   [TsqlSplit] ('1,2,3') 
GO 

SELECT objtype, 
       cacheobjtype, 
       usecounts, 
       text 
FROM   sys.dm_exec_cached_plans AS ECP 
       CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
WHERE  EST.text LIKE '%TsqlSplit%'

Ref Link.

Categories: SQL Server Questions Tags: MSDN TSQL forum, UDF, User Defined Functions

Logging in SSIS… using SQL Server Log Provider

June 15, 2011 4 comments

As per MS BOL SSIS provides a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. This help in implementing logging in SSIS packages. With logging you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run.

Following are the different log providers:
1. Text File
2. SQL Server Profiler
3. SQL Server
4. Windows Event
5. XML File

Here we will see how can we use the SQL Server log provider (#3 above), which writes log entries to the sysssislog table in a SQL Server database.
Please note in 2005 the logs are stored in sysdtslog90 table. In 2008 the table name is changed to sysssislog.

Following are the easy steps one can follow to configure logging:

1. Create a SSIS package, as shown below are the 2 Execute SQL tasks. To enable logging goto menu, select SSIS-> Logging…

2. On this new window select the Provider Type as “SSIS log provider for SQL Server”, click Add.

3. The log provider will be added, select by checking the check box. Now provide the connection where the log table should get created.

4. Switch to the Details tab and select the events you want to log and audit. Click OK, save your SSIS package and your logging is enabled.

5. After this run your package and switch to SSMS and check on object explorer that the table sysdtslog90 has been created on the database you added as a connection. Now you can query the sysdtslog90 table where the logs are stored.

select * from FROM [AdventureWorks].[dbo].[sysdtslog90]

The sysdtslog90 table contains following columns listed in the query below. All are self explanatory, one can also check the links at the end of the post to get more info about them.

SELECT [id], [event], [computer], [operator], , --,[sourceid], [executionid],
      [starttime],[endtime],[datacode], [databytes], [message]
FROM [AdventureWorks].[dbo].[sysdtslog90]