Maximum Capacity Specifications for SQL Server

Maximum Capacity Specifications for SQL Server

SQL - Rollback TRUNCATE Command in Transaction

SQL SERVER – Rollback TRUNCATE Command in Transaction

What is the Difference between TRUNCATE and DELETE in SQL Server? – Interview Question of the Week #118

sql- Part 2 SQL query to get organization hierarchy

Part 2 SQL query to get organization hierarchy

1.Self-Join
2.CTE
3.Recursive CTE

Declare @ID int;
Set @ID = 7;

With EmployeeCTE AS
{

     SELECT
        EmployeeID  ,EmployeeName ,ManagerID

     FROM EMPLOYEES

     WHERE EMPLOYEEID = @ID

     UNION ALL

      SELECT 

        EMPLOYEES.EmployeeID  ,EMPLOYEES.EmployeeName ,EMPLOYEES.ManagerID

     FROM EMPLOYEES

     Join EmployeeCTE

     On Employees.EmployeeID=EmployeeCTE.ManagerID;

}

Select * from EmployeeCTE

EmployeeID   EmployeeName  ManagerID

7                      David                  4

4                      Tom                     3

3                       Steve                  8

8                       Ben                     NULL

With EmployeeCTE AS
{

     SELECT
        EmployeeID  ,EmployeeName ,ManagerID

     FROM EMPLOYEES

     WHERE EMPLOYEEID = @ID

     UNION ALL

      SELECT 

        EMPLOYEES.EmployeeID  ,EMPLOYEES.EmployeeName ,EMPLOYEES.ManagerID

     FROM EMPLOYEES

     Join EmployeeCTE

     On Employees.EmployeeID=EmployeeCTE.ManagerID;

}

Select E1.EmployeeName,E2.EmployeeName as ManagerName 

from EmployeeCTE E1

JOIN EmployeeCTE E2

ON E1.MANAGERID =E2.EmployeeID

EmployeeName   ManagerName

David                    Tom

Tom                       Steve

Steve                      Ben

With EmployeeCTE AS
{

     SELECT
        EmployeeID  ,EmployeeName ,ManagerID

     FROM EMPLOYEES

     WHERE EMPLOYEEID = @ID

     UNION ALL

      SELECT 

        EMPLOYEES.EmployeeID  ,EMPLOYEES.EmployeeName ,EMPLOYEES.ManagerID

     FROM EMPLOYEES

     Join EmployeeCTE

     On Employees.EmployeeID=EmployeeCTE.ManagerID;

}

Select E1.EmployeeName,E2.EmployeeName as ManagerName 

from EmployeeCTE E1

Left JOIN EmployeeCTE E2

ON E1.MANAGERID =E2.EmployeeID

EmployeeName   ManagerName

David                    Tom

Tom                       Steve

Steve                      Ben

Ben                         NULL

Introduction to Hierarchical Query - SQL in Sixty Seconds #012

Recursive CTE work.

WITH CTE AS
(
SELECT BOSS.EmpID,BOSS.LastName
FROM Employee as BOSS
WHERE BOSS.ManagerID IS NULL

--ANCHOR query runs first
--EmpID  LastName  ManagerID  lvl
--11          Smith         NULL          1

UNION ALL

SELECT  Emp.EmpID,Emp.LastName,Emp.ManagerID,CTE.lvl+1
FROM Employee as Emp INNER JOIN CTE
ON Emp.ManagerID=CTE.EmpID
WHERE Emp.ManagerID IS NOT NULL)

SELECT * FROM CTE

NonClustered Index how many columns one can Include?

Maximum number of columns per index key in Ms SQL Server 2014

In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Create Indexes with Included Columns. The maximum number of bytes in an index key is 900.

Create Indexes with Included Columns

• Include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 32 key columns and a maximum index key size of 1,700 bytes (16 key columns and 900 bytes prior to SQL Server 2016 (13.x)). The Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

How important is the order of columns in indexes?

 Anytime you're in a query, narrowing down the number of results to deal with before the next step means better performance.

Since the index is also stored this way, there's no backtracking across the index to find the first column when you're querying on it.

In short: No, it's not for show, there are real performance benefits.

SQL Server Indexing Questions You Were Too Shy To Ask

CREATE NONCLUSTERED INDEX ix_orderid

ON dbo.Sales(OrderID)

INCLUDE (OrderDate);

SQL - Joins

SQL Joins Tutorial for Beginners - Inner Join, Left Join, Right Join, Full Outer Join

SQL JOIN

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Inner vs Outer Joins on a Many-To-Many Relationship

Recipes                                recipe_ingredients                    ingredients

Id - int                                  recipe_id - int                           id - int
name-varchar(400)              ingredient_id - int                     item - varchar(4000)
description - text
category_id - int
chef_id-int
created-datetime

categories                             chefs

id - int                                   id-int
name - varchar(255)             name - varchar(255)


"many-to-many":

Each recipe - linked to ingredients (dish)
each ingredient - in many dishes

SQL Server - Handling Errors in SQL Server 2012

Handling Errors in SQL Server 2012

SQL Server 2012, you now have a replacement for RAISERROR, the THROWstatement, which makes it easier than ever to capture the error-related data.

Working with the THROW Statement

ALTER PROCEDURE UpdateSales

  @SalesPersonID INT,

  @SalesAmt MONEY = 0

AS

BEGIN

  BEGIN TRY

    BEGIN TRANSACTION;

      UPDATE LastYearSales

      SET SalesLastYear = SalesLastYear + @SalesAmt

      WHERE SalesPersonID = @SalesPersonID;

    COMMIT TRANSACTION;

  END TRY

  BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION;

 

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    DECLARE @ErrorLine INT = ERROR_LINE();

 

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));

    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

 

    THROW;

  END CATCH

END;

GO

~

USE AdventureWorks2012; GO   IF OBJECT_ID('UpdateSales', 'P') IS NOT NULL DROP PROCEDURE UpdateSales; GO   CREATE PROCEDURE UpdateSales   @SalesPersonID INT,   @SalesAmt MONEY = 0 AS BEGIN   BEGIN TRY     BEGIN TRANSACTION;       UPDATE LastYearSales       SET SalesLastYear = SalesLastYear + @SalesAmt       WHERE SalesPersonID = @SalesPersonID;     COMMIT TRANSACTION;   END TRY   BEGIN CATCH     IF @@TRANCOUNT > 0     ROLLBACK TRANSACTION;       DECLARE @ErrorNumber INT = ERROR_NUMBER();     DECLARE @ErrorLine INT = ERROR_LINE();     DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();     DECLARE @ErrorSeverity INT = ERROR_SEVERITY();     DECLARE @ErrorState INT = ERROR_STATE();       PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));     PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));       RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);   END CATCH END; GO ~ If the UPDATE statement runs successfully, the SalesLastYear value is updated and the operation is completed, in which case, the code in the CATCH block is never executed.  the @@TRANCOUNT function to determine whether any transactions are still open. @@TRANCOUNT is a built-in SQL Server function that returns the number of running transactions in the current session. In this case, there should be only one (if an error occurs), so I roll back that transaction.