Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Monday, December 23, 2013

Nth Highest Salary in Employee Table

-- Query to find the Nth Highest Salary in Employee Table

SELECT EMPNO, ENAME, SAL
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS N,
EMPNO, ENAME, SAL FROM EMP
) DT
WHERE N = 3

I used N=3, as I need the third highest salary. We can check the data for various ranks.


Also check how the below window functions return data differently.

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP
SELECT RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP
SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

Thursday, December 5, 2013

Best Practices for Stored Procedure

Stored Procedure: It is considered as one of the most optimized database object in SQL Server database. If we implement few best practices, it can give more efficient results.

Best Practices: 

1. Use "SET NOCOUNT ON" as the first line of the stored procedure body.
2. Do not use SP_ or sys_ prefixes for naming user stored procedures.
3. Always include schema names prefix for stored procedure and other database objects used in stored procedure. It reduces processing time.
4. Avoid 'SELECT *' and specify the columns in SELECT statement.
5. Use explicit transactions by using BEGIN/END TRANSACTION.
6. Use TRY/CATCH for Error Handling.
7. Defined NULL or NOT NULL for Temporary Table Columns.
8. Last but not least implement best practices for the queries used in Stored Procedure.


Wednesday, December 4, 2013

Extract (Export) word document from SQL Server Table to a Windows Folder

In the last post, we have seen how to upload (insert) into a SQL Server Table. Now let's see how to extract it from the SQL Server table back to a folder.

Query:
-- Extracting the Resume from SQL Server Table to a folder location.
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'BCP "SELECT ResumeDoc FROM AdventureWorks2012.dbo.Consultants WHERE ConsultantName = ''Steve''" QueryOut "D:\Steve_Resume2.docx" -T -n -S HPS\SQL'
EXEC MASTER.dbo.xp_CmdShell @SQL

In above query, HPS\SQL is the SQL Server Instance.



Verifying in Folder Location:
SQL Server exported the word document into the specified location.






Note: By default, dbo.xp_CmdShell is disabled. In order to enable the stored procedure functionality, run the below code.

--Code Starts here:---->
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_CmdShell', 1
GO
RECONFIGURE
GO

Conclusion: For every import of word document, there exists an export, Just kidding ;) Hope this helps! Happy TSQLing :)

Tuesday, December 3, 2013

SQL Server THROW - Similar to RAISERROR

As a part of SQL Server 2012 T-SQL Enhancements, here is one new feature under error handling section named "THROW", similar to RAISERROR function in previous SQL Server versions.

Data Validation is a process of checking values in one or more columns. In stored procedures, its very important to implement the data validation process. So, chances of getting data validation are very common.

The below stored procedures illustrates the usage of THROW statement for error handling.




Stored Procedure Creation:
CREATE PROCEDURE dbo.sp_InsertEmployee
@EMPNO NUMERIC(4,0),
@ENAME VARCHAR(50),
@JOB VARCHAR(50),
@MGR NUMERIC(4,0),
@SAL DECIMAL(7,2),
@COMM DECIMAL(7,2),
@DEPTNO NUMERIC(2,0)
AS
BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT * FROM dbo.DEPT WHERE DEPTNO = @DEPTNO)
INSERT INTO dbo.EMP (EMPNO, ENAME, JOB, MGR, SAL, COMM, DEPTNO)
VALUES (@EMPNO, @ENAME, @JOB, @MGR, @SAL, @COMM, @DEPTNO);

ELSE
THROW 50001, 'Invalid DEPTNO Entry!', 1

END

Stored Procedure Execution:
EXECUTE dbo.sp_InsertEmployee 2222, 'SAM', 'MANAGER', 7839, 4500, 500, 60



I have passed an invalid department number 60 and so SQL Server THROWs the customised error defined in the stored procedure. Please check the above snip.



Monday, December 2, 2013

IIF Statement (Alternate for CASE statement)

SQL Server 2012 includes some new T-SQL features. I would like to talk about 
one such feature called "IIF Statement". 

This is a simplied version of CASE statement. Results from both
CASE and IIF would be same, but with a minimized code, mostly in those occasions where we need to write nested CASE statements.

SQL Server Reporting Services Developers might be aware of this function while developing customized color expressions etc., Now lets see how it works
in T-SQL.

IIF Syntax:
IIF(Condition, True, False)

E.g 1:
DECLARE @X INT = 23
DECLARE @Y INT = 45
SELECT IIF(@X < @Y, 'True', 'False') AS IIF_Result;

Result Set:




E.g 2:
Lets see how it works on few records of a table:
Query:
SELECT 
IIF (JOB IN ('SALESMAN', 'CLERK'), 'LOW CADRE', 'HIGH CADRE')
FROM [dbo].[EMP]



Summary: IIF function is surely a good approach to handle the business logics where we need write Nested CASE statements. Hope this is useful! Happy TSQLing :)



Friday, June 1, 2012

UPSERT Statement in T-SQL

The MERGE statement is also referred as the UPSERT statement. It allows us to merge multiple rows from one table into another table. Illustrating MERGE Statement with an example using step by step process and with multiple executions. We shall see SCD Type 1 Implementation here.

Note: If you are looking for MERGE using SCD Type 2 Implemenation, click here.

          MERGE is about comparing a source and destination table and depending upon the changes, new records get inserted and existing get updated.

Basic Syntax:
MERGE [INTO] Target_Table
   USING Source_Table
   ON JOIN_Condition
[WHEN MATCHED [AND Search_Conditions]
   THEN DML_Statement]
[WHEN NOT MATCHED
   THEN DML_Statement]

Implementation:

Source Table: DEPT

CREATE TABLE [dbo].[DEPT](
            [DEPTNO] [numeric](2, 0) NOT NULL,
            [DNAME] [varchar](25) NULL,
            [LOC] [varchar](25) NULL
)

Target Table: DEPT_MERGE

CREATE TABLE [dbo].[DEPT_MERGE](
            [DEPTNO] [numeric](2, 0) NOT NULL,
            [DNAME] [varchar](25) NULL,
            [LOC] [varchar](25) NULL,
            [START_DATE] [datetime] NULL,
            [END_DATE] [datetime] NULL
)

INSERT data into DEPT:

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (10, N'ACCOUNTING', N'NEW YORK')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (20, N'RESEARCH', N'MIAMI')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (30, N'SALES', N'CHICAGO')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (40, N'OPERATIONS', N'BOSTON')


Screen Shot:



MERGE Statement Code:
/************************************* MERGE STATEMENT ***********************************/
MERGE DEPT_MERGE AS D
            USING DEPT AS S
            ON D.DEPTNO = S.DEPTNO
WHEN MATCHED AND (S.DNAME <> D.DNAME OR S.LOC <> D.LOC)
THEN
            UPDATE SET
            D.DNAME = S.DNAME,
            D.LOC = S.LOC,
            D.END_DATE = GETDATE()
WHEN NOT MATCHED
THEN
            INSERT (DEPTNO, DNAME, LOC, [START_DATE])
            VALUES (S.DEPTNO, S.DNAME, S.LOC, GETDATE());

Run-1 Execution: 4 Rows affected.





















Run-1 Results: All rows from source into the destination along with start dates.























Further Observation:
To observe how it behaves for the second execution, lets make some changes in the source data. A new record is inserted and an existing record is updated in the source table as below.


Run-2 Execution:  2 rows affected. (1 new row inserted and 1 row updated).






















Run-2 Results: Observe the data changes.



























Summary: This is identical to Slowly Changing Dimension of SSIS. If you want to handle the data changes in T-SQL then this is most efficient way. Hope this helps. Happy T-SQLing. :)


Tuesday, May 29, 2012

Write Back Data Prototype

This is the first post on Reporting Services on the blog, and as usual this is not a routine concept but a customized requirement from client.


The objective of the SSRS project here is to first generate a SSRS 2008 report, something like below:

1. Report Design:

The below is a report displaying the client information like ID, Name and City. Along with the actual data we have a dummy column which is linked to a URL, which is a GUI to change the data that inherits on the database.

 2. Report on Report Server:
The below is a shot of report which is deployed on to Report Server.

3. Customized URL:

The below is a customized URL to make changes to the data, which will be inherited on the database.
This has to be designed in ASP.Net project which has to be linked with SSRS.



4. Report and URL integration:

The below screen shot demonstrates a new window pop-up rises, when the Link for a row is clicked. The new window will pop-up with the existing data. The user can now make changes in the editable textboxes and click on UPDATE. Once it is clicked that will make changes to the data in the database.


5. Enhancements:

When user makes changes and clicks on UPDATE button, it should do two operations.
v  It should update the data in the database. (It is accomplished already in the present version of code)
v It should refresh the report page so that the changed data appears instantly. (Got to work on this tomorrow with the developer) 


This in one simple example of the integration of ASP.Net and SSRS. Hope this is useful!!






Saturday, February 19, 2011

TempDB is Special!!!

TempDB, one of the system databases of SQL Server Database Engine is very special. Special, because it is re-created every time SQL Server is restarted. This is nothing but a place for temporary user objects and internal objects explicitly created by SQL Server itself.

When we create a new user database in SQL Server, the model database is used as a template for the creation. The same thing applies for TempDB too, it will inherit the database options from model database except the recovery model, because TempDB always uses Simple Recovery Model.

TempDB Objects:

a) User Objects: We can create Local and Global temporary tables that reside in TempDB. # and ## are the prefixes for local and global temporary tables respectively. Other objects that are stored in TempDB are table variables and table-valued functions.

b) Internal Objects: The internal objects in TempDB are not visible using the normal tools. The catalog views also do not store there metadata, because its stored only in memory. The 3 basic types of internal objects are work tables, work files, and sort units.

Work tables are created by SQL Server during these activities:

  • To hold intermediate results during a large query (Spooling)
  • Running DBCC CHECKDB or DBCC CHECKTABLE
  • Working with XML or varchar(MAX) variables
  • Processing SQL Service Broker objects
  • Working with static cursors

When SQL Server processes a query that uses a hash operator (either for joining and aggregating data) then Work files are used.

In addition to a query containing an ORDER BY clause, when a sort operation takes place, the Sort units are created. Certain types of joins might require SQL Server sort the data before performing the join. Sort units are created in TempDB to hold the data as it is being sorted. 

Conclusion:
Internal Objects is that area, which is not exposed to the regular querying side. But, its important to know whats happening inside IT.



Monday, December 27, 2010

Table Size Estimation!!!


Someone says Table is the core object of a database, I would say its the heart of a SQL Server.

Today, I come up with one interesting observation regarding the table size estimation.
The requirement is to design a table to store Customer information for which i have to find the estimated size of the table for 1000000 records.

Table: CustomerInfo

Column Name
Datatype
ID
INT
FIRSTNAME
VARCHAR(30)
LASTNAME
VARCHAR(30)
ADDRESS
VARCHAR(50)
CITY
VARCHAR(15)
STATE
CHAR(2)
ZIP
CHAR(10)

Heres the procedure to estimate the size:

1. Find size of the row :
4+30+30+50+15+2+10 = 141 bytes

2. Divide 8096 by result above:
8096/141 = 57 (# of rows that fit on page)

3. Assuming 1000000 is the record count expected, divide the count with step 2 results:
1000000/57 = 17543.8 ~ 17544 (# of pages)

4. Multiplying # of pages with 8192
17544 * 8192 = 143720448 bytes = 137 MB


And thats how we estimate the size of the table!!!

Tuesday, December 21, 2010

Facts of SQL Server !!!

There are few moments where we face interesting but very rarely asked questions like " How many databases can a SQL Server Instance handle? " and like this too, " How many maximum objects can we create in a SQL Server database? "


For a guy battling all the day with strange requirements (trying to fix with strange solutions ;)) may not find time to understand the platform to the deepest. Understanding the SQL Server Inside face is like looking at the GOD in real!!!


So I want to share this information on web and thought of creating a blog for the same. We may be aware of few but not all, today I took some time to publish all this in a single post. 



  • The technical limit for one SQL Server Instance is 32,767 databases. (Literally that count would never be reached).
  • A SQL Server database which comprises of different types of objects like tables, views, Monster stored procedures etc., has a technical limit of (2 Power 31) - 1 (just more than 2 million objects). I never saw a database more than tens of thousands objects in all those several projects worked in various domains.
  • The size, from 2MB to 524,272 Terabytes (This figure is no where close to the present business database sizes, and by the time they grow, SQL Server will grow too ;)).
  • The SQL Server database, which is enough intelligent to shrink itself automatically or manually, has its own set of system tables to hold the database catalog.
If I have to talk something SQL Server database is NOT:
It is NOT synonymous with an entire SQL Server Instance. Its neither a single SQL Server table nor a specific operating system file.