VENKATA RAMANA REDDY-MSBI -SSIS,SSRS,SSAS-BLOG

Thursday, 6 November 2014

Clustered v/s Non Clustered Index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Handling Errors in SQL Server by using TRY...CATCH

Hi All,I found one of the best example to handle errors in SQL Server database.There are different ways to do that. Based on the situation or requirements. These examples can also found in MSDN.

The stored procedure uspLogError logs error information in the ErrorLog table about the error that caused execution to transfer to the CATCH block of a TRY…CATCH construct. For uspLogError to insert error information into the ErrorLog table, the following conditions must exist:

uspLogError is executed within the scope of a CATCH block.

If the current transaction is in an uncommittable state, the transaction is rolled back before executing uspLogError.

The output parameter @ErrorLogID of uspLogError returns the ErrorLogID of the row inserted by uspLogError into the ErrorLog table. The default value of @ErrorLogID is 0. The following example shows the code for uspLogError.

CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorTime] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NOT NULL,
CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Its time to create stored procedure-

CREATE PROCEDURE [dbo].[uspLogError]

@ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        RETURN -1;
    END CATCH
END; 
For Reference -- 
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms179296.aspx

SQL Server Date Formats

Hi,
Here are some date formats which might help you in some situations while working in SQL Server.

Date Format	SQL Queries	Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006
Mon YYYY	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006
Month YYYY	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006
DD Month	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September
Month DD	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11
DD Month YY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72
DD Month YYYY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02
Mon-YYYY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002
DD-Mon-YY	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05
DD-Mon-YYYY	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005

SSRS Expressions

Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.
Here are some of the reporting services expressions:
1.To get Today’s date
=Today
2.The following code will get Today's date -3 days
=DateAdd("d", -3, Today)
3.Get the Name of the day - like sunday
=WeekdayName(DatePart("w", Today))
4.SWITCH statement - An alternative to IIF/CASE
=SWITCH(WeekdayName(Fields!Date.Value) = "Monday","Blue",
WeekdayName(Fields!Date.Value) = "Tuesday","Green",
WeekdayName(Fields!Date.Value) = "Wednesday","Red")

5.Format Numbers as Currency
=FormatCurrency(1000)
6. Convert integer values to string
= CStr(123123)
7.The Iif function returns one of two values depending on whether the expression is true or not.

=IIF(Fields!LineTotal.Value > 100, True, False)

8.Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on the value of PctComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.

=IIF(Fields!PctComplete.Value >= 10, "Green", IIF(Fields!PctComplete.Value >= 1, "Blue", "Red"))

9.Test the value of the ImportantDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:

=IIF(DateDiff("d",Fields!ImportantDate.Value,Now())>7,"Red","Blue")

10.Test the value of the Department field and return either a subreport name or a null (Nothing in Visual Basic). This expression can be used for conditional drillthrough subreports.

=IIF(Fields!Department.Value = "Development", "EmployeeReport", Nothing)

11.The Sum function can total the values in a group or data region. This function can be useful in the header or footer of a group.

=Sum(Fields!LineTotal.Value, "Order")

12.You can also use the Sum function for conditional aggregate calculations.

=Sum(IIF(Fields!State.Value = "Finished", 1, 0))

13.The RowNumber function, when used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. This function can be useful to number rows in a table. It can also be useful for more complex tasks, such as providing page breaks based on number of rows
The scope you specify for RowNumber controls when renumbering begins. The Nothing keyword indicates that the function will start counting at the first row in the outermost data region. To start counting within nested data regions, use the name of the data region. To start counting within a group, use the name of the group.

=RowNumber(Nothing)

14.Color alternate rows in table with a different color - This is very useful when there are a lot of rows with similar data and its hard to differentiate the rows. You have to set the table background color property with the following code.

=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
15. Dynamic DataSource - If you have a requirement for a report to be run against multiple database server you can change the datasource connection string property to be dynamic. You can write your own expression in the connection string option in the data source properties. The following will create a dynamic connection to the server depending on the report parameter (ServerName) selected. Note: You have to make sure that the login has permission on all the servers you would like to connect to.

="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=WSS_Content"
16.Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:

=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

17.Format dates and numbers in a string with the Format function.

=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

18.The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:

=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

19.The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)

20.Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")
21.The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn":

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

SQL_MERGE

USE tempdb;
GO
CREATE TABLE dbo.TargetData(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.SourceData(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.SourceData(EmployeeID, EmployeeName) Values(101, 'Bob');
INSERT dbo.SourceData(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

--BEGIN TRAN
--
MERGE TargetData T
USING SourceData S
ON T.EmployeeID = S.EmployeeID

--Not Exist in Target
WHEN NOT MATCHED /* AND AND S.EmployeeName LIKE 'S%' */ BY Target
THEN INSERT VALUES(S.EmployeeID, S.EmployeeName) -- Inserts to Target
--Not Exist in Target
WHEN NOT MATCHED /* AND AND S.EmployeeName LIKE 'S%' */ BY Source
THEN DELETE -- Inserts to Target
WHEN MATCHED THEN UPDATE SET T.EmployeeName = S.EmployeeName

OUTPUT $action, inserted.*,deleted.*;
--updated.*;

select * from dbo.TargetData
select * from dbo.SourceData
/*
DROP TABLE dbo.TargetData
DROP TABLE dbo.SourceData
*/

--COMMIT TRAN
--ROLLBACK TRAN

SQL

-----------------------User Degine Function---------------

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

-- Type 1 > Scaler Functions : Returns single value.

--Ex: Factorial of Given No.

CREATE FUNCTION UDF_Factorial( @num int)

RETURNS int

BEGIN

DECLARE @fact int

SET @fact=1

WHILE @num != 0

BEGIN

SET @fact=@fact * @num

SET @num=@num-1

END

RETURN @fact

END

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

--Execute User Define Function

SELECT dbo.UDF_Factorial(5) as Factorial

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

--Ex: Function to Find Employee MAX Salary of Given Dept

CREATE FUNCTION UDF_getMaxSal(@Deptid int)

RETURNS NUMERIC(10,2)

BEGIN

DECLARE @MaxSal NUMERIC(10,5)

SET @MaxSal=(SELECT TOP 1 Salary FROM tblEmployee WHERE DeptId=@Deptid ORDER BY Salary DESC)

RETURN @MaxSal

END

--Execution of UDF in SELECT Statment

SELECT EmpId,EmpName,Address,Deptid,Salary FROM tblEmployee WHERE Salary=dbo.UDF_getMaxSal(2)

-- Type 2 > Inline Table Valued Function : Returns Table Object

--Ex: Function to Return Record of Emplyees whose Salary > given salry

CREATE FUNCTION UDF_getEmpRecordsAboveGivenSal(@Salary int)

RETURNS Table

return (SELECT EmpId,EmpName,Address,Gender,Salary FROM tblEmployee WHERE Salary > @Salary)

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

-- Execution

SELECT * FROM dbo.UDF_getEmpRecordsAboveGivenSal(25000)

-- Type 3 > Multi Statement Table Valued Function :

-- Explicitly defines the structure of the table to return.

-- Defines column names and datatypes in the RETURNS clause.

--Ex: Fuction to get Employee Records with Dept Name

CREATE FUNCTION getEmpByDeptName()

RETURNS @EmpWithDept Table

(

EmpId int,

EmpName varchar(50),

DeptName varchar(50)

)

BEGIN

INSERT INTO @EmpWithDept SELECT e.EmpId,e.Empname,d.DeptName FROM tblDepartment d INNER JOIN tblEmployee e ON d.DeptId=e.DeptId

return

END

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

-- Execute Multi Statement Table Valued Function

SELECT * FROM dbo.getEmpByDeptName()

--Modify or Alter UDF like Follows

Alter FUNCTION getEmpByDeptName(@DeptName varchar(50))

RETURNS @EmpWithDept Table

(

EmpId int,

EmpName varchar(50),

DeptName varchar(50)

)

BEGIN

INSERT INTO @EmpWithDept SELECT e.EmpId,e.Empname,d.DeptName FROM tblDepartment d INNER JOIN tblEmployee e ON d.DeptId=e.DeptId WHERE DeptName=@Deptname

UPDATE @EmpWithDept SET DeptName='DOT NET' WHERE DeptName='.NET'

return

END

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

-- Execute Multi Statement Table Valued Function

SELECT * FROM dbo.getEmpByDeptName('.NET')

----------------------------------Triggers-----------------------------------

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

--Defination : A trigger is an action that is performed behind-the-scenes when an event occurs on a table.

-- Types of Trigger: 1) Instead of/Before 2) After/For

-- There are Two Tables with Field and Diffrent name one is tblPersone and another is tblPersonUpdate

SELECT * FROM tblPerson

SELECT * FROM tblPersonUpdate

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

--Inserting Records into tblPerson

INSERT INTO tblPerson VALUES('Vinay','Sayaji,Indore',Convert(Varchar,GETDATE(),114))

INSERT INTO tblPerson VALUES('Rahul','Vijay Nagar,Indore',Convert(Varchar,GETDATE(),114))

INSERT INTO tblPerson VALUES('Hitesh','Khargone',Convert(Varchar,GETDATE(),114))

SELECT * FROM tblPerson

SELECT * FROM tblPersonUpdate

--Creating Trigger on Table tblPerson After Update will Insert Old Record into tblPersonUpdate

CREATE TRIGGER UDT_PersonUpdate

ON tblPerson

After UPDATE

DECLARE @id int;

DECLARE @name varchar(50);

DECLARE @address varchar(50);

DECLARE @time varchar(50);

select @id=U.id from deleted U;

select @name=U.name from deleted U;

select @address=U.address from deleted U;

select @time=U.time from deleted U;

BEGIN

INSERT INTO tblPersonUpdate(id,name,address,time)VALUES(@id,@name,@address,@time)

PRINT 'Record Has been Inserted into tblPersonUpdate'

END

--Now Updating in Table tblPersone

UPDATE tblPerson SET address='Bhopal' WHERE id=3

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

--Now Updated Records in tblPerson and tblPersonUpdate

SELECT * FROM tblPerson

SELECT * FROM tblPersonUpdate