Removing Leading Zeros From Column in Table

USE tempdb

GO

-- Create sample table

CREATE TABLE Table1 (Col1 VARCHAR(100))

INSERT INTO Table1 (Col1)

SELECT '0001'

UNION ALL

SELECT '000100'

UNION ALL

SELECT '100100'

UNION ALL

SELECT '000 0001'

UNION ALL

SELECT '00.001'

UNION ALL

SELECT '01.001'

GO

-- Original data

SELECT *

FROM Table1

GO

-- Remove leading zeros

SELECT

SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))

FROM Table1

GO

-- Clean up

DROP TABLE Table1

GO

GO

How to check to table locking in SQL Server 2008 R2.

We used to use SP_LOCK and SP_LOCK2, but have since learned that the recommend method to determine locks is the code below as the two procs are to be deprecated in future releases.

Microsoft says the better way is to use the sys.dm_tran_locks dynamic management views.



The following code will select from the sys.dm_tran_locks and sys.partitions system tables.

More information on Locks can be found at the following Microsoft library pages:

Lock Modes

Locking Granularity

Key Range Locking





---------------------------------------------------------------
--  Code to find out what table is locked and the lock reason
---------------------------------------------------------------
select
    object_name(P.object_id) as TableName
    , resource_type
    , resource_description
    , request_mode
    , CASE REQUEST_MODE
                    WHEN 'S'        THEN 'Shared'
                    WHEN 'U'        THEN 'Update'
                    WHEN 'X'        THEN 'Exclusive'
                    WHEN 'IS'       THEN 'Intent Shared'
                    WHEN 'IU'       THEN 'Intent Update'
                    WHEN 'IX'       THEN 'Intent Exclusive'
                    WHEN 'SIU'      THEN 'Shared Intent Update'
                    WHEN 'SIX'      THEN 'Shared Intent Exclusive'
                    WHEN 'UIX'      THEN 'Update Intent Exclusive'
                    WHEN 'BU'       THEN 'Bulk Update'
                    WHEN 'RangeS_S' THEN 'Shared Range S'
                    WHEN 'RangeS_U' THEN 'Shared Range U'
                    WHEN 'RangeI_N' THEN 'Insert Range'
                    WHEN 'RangeI_S' THEN 'Insert Range S'
                    WHEN 'RangeI_U' THEN 'Insert Range U'
                    WHEN 'RangeI_X' THEN 'Insert Range X'
                    WHEN 'RangeX_S' THEN 'Exclusive range S'
                    WHEN 'RangeX_U' THEN 'Exclusive range U'
                    WHEN 'RangeX_X' THEN 'Exclusive range X'
                    WHEN 'SCH-M'    THEN 'Schema-Modification'
                    WHEN 'SCH-S'    THEN 'Schema-Stability'

        ELSE NULL
        END AS REQUEST_LOCK_MODE

FROM   sys.dm_tran_locks   AS L
       join sys.partitions AS P
        on L.resource_associated_entity_id = p.hobt_id

How to Replace Null values with "Unknown" in Select Statement in SQL Server

Scenario:

You are working as SQL Server developer, you need to query dbo.Customer table and replace Null values in Name column to  "Unknown Name". Which SQL functions will you use to replace Null value with "Unknown Name"?

Solution:

You can use ISNULL or COALESCE functions to replace Null with value we like. Let's create dbo.Customer table and insert sample records by using below statements.

--Create dbo.Customer table
Create table dbo.Customer(
Id int,
Name VARCHAR(10),
Address VARCHAR(100))

--Insert records in dbo.Customer table
Insert into dbo.Customer
Values(1,'Aamir','XyZ Address')
,(2,'Raza',Null)
,(3,null,'abc address')

Let's use ISNULL or COALESCE functions to replace Null with "Unknown Name".

Select Id,ISNULL(Name,'Unknown Name') AS NameByISNull,
COALESCE(Name,'Unknown Name') NamebyCoalesce,
Address From dbo.Customer

How to replace Null with Values in SQL Server - TSQL Tutorial

As you can see that we used ISNULL and replaced the Null with "Unknown Name" but it returned "Unknown Na", it did not returned complete "Unknown Name". The reason, the data type of an ISNULL expression is the data type of the first input ( Name column) that is varchar(10), that means it will always return us 10 characters.

If you have situations like this, you better use COALESCE.The data type of a COALESCE expression is the data type of the input argument with the highest data type precedence. In our case "Unknow Name" is 12 characters and it chose to use that instead of Name column data type which is varchar(10).

Filtering by OFFSET-FETCH Options in Select query

Scenario :

You are working as SQL Server developer with front end development team.The front end team needs to implement pagination.  Confuse about Pagination? No problem. Think about viewing your bank statement or credit card statement. Where applications shows only 10 or 20 records per page  and you have to click next to see next records.That is called pagination.

Now you understand pagination, the front end development needs SQL query from you that can be used to returned required results and they should be able to pass page number to return records.

Solution:

There are multiple ways to write Pagination queries, One of them is by using OFFSET FETCH clause. You have to sort the records if you want to use OFFSET FETCH.

Let's create dbo.TotalSale table and insert some sample records. I have only inserted 11 records.

CREATE TABLE [dbo].[TotalSale]
    (
      [id] [int] NOT NULL ,
      [SalePersonFName] [varchar](100) NULL ,
      [SalePersonLName] [varchar](100) NULL ,
      [ProductName] [varchar](100) NULL ,
      [ItemsSold] [int] NULL ,
      [SoldPrice] [float] NULL ,
      [SoldDate] [date] NULL ,
      [City] [varchar](100) NULL ,
      [State] [varchar](100) NULL ,
      [Country] [varchar](100) NULL ,
      [Region] [varchar](100) NULL
    )

INSERT  [dbo].[TotalSale]
        ( [id], [SalePersonFName], [SalePersonLName], [ProductName],
          [ItemsSold], [SoldPrice], [SoldDate], [City], [State], [Country],
          [Region] )
VALUES  ( 1, N'Aamir', N'Shahzad', N'TV', 1, 700, CAST(N'2015-07-15' AS DATE),
          N'Charlotte', N'NC', N'USA', N'North America' )
  ,     ( 2, N'M', N'Raza', N'Cell Phone', 2, 800, CAST(N'2015-07-15' AS DATE),
          N'Charlotte', N'NC', N'USA', N'North America' )
  ,     ( 3, N'Christy', N'Ladson', N'TV', 3, 1600,
          CAST(N'2015-04-02' AS DATE), N'High Point', N'NC', N'USA',
          N'North America' )
  ,     ( 4, N'John', N'Rivers', N'Laptop', 5, 2400,
          CAST(N'2014-03-09' AS DATE), N'Jersey City', N'NJ', N'USA',
          N'North America' )
  ,     ( 5, N'Najaf', N'Ali', N'Computer', 1, 300,
          CAST(N'2015-06-20' AS DATE), N'Karachi', N'Sindh', N'Pakistan',
          N'Asia' )
  ,     ( 6, N'Sukhjeet', N'Singh', N'TV', 2, 900, CAST(N'2015-06-21' AS DATE),
          N'ChandiGar', N'Punjab', N'India', N'Asia' )
  ,     ( 7, N'Chirag', N'Patel', N'Cell Phone', 5, 1500,
          CAST(N'2015-06-23' AS DATE), N'AhmadAbad', N'Gujrat', N'India',
          N'Asia' )
  ,     ( 8, N'Aleena', N'Aman', N'Laptop', 2, 800,
          CAST(N'2015-05-25' AS DATE), N'Lahore', N'Punjab', N'Pakistan',
          N'Asia' )
  ,     ( 9, N'Petra', N'Henry', N'TV', 10, 5000, CAST(N'2015-04-08' AS DATE),
          N'Paris', N'Île-de-France', N'France', N'Europe' )
  ,     ( 10, N'Rita', N'Roger', N'Laptop', 7, 2100,
          CAST(N'2015-04-11' AS DATE), N'Paris', N'Île-de-France', N'France',
          N'Europe' )
  ,     ( 11, N'Tamara', N'Tony', N'Cell Phone', 2, 1200,
          CAST(N'2015-03-03' AS DATE), N'Frankfurt', N'Hesse', N'Germany',
          N'Europe' )

1) Let's say if we would like to skip first 5 rows and want to show all rest of the rows we can use below query.

Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
          OFFSET 5 rows

How to use OFFSET FETCH Clause in SQL Server to skip first X rows and show all rest of them - SQL Server Tutorial

2) Now if we would like to show 3 records per page, we can use below query. In this case we are going to show first page

Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
         OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

How to use OFFSET FETCH clause to return records per page in SQL Server - SQL Server Tutorial

Noticed that I have OFFSET 0, that means that I want to show first page and with 3 rows. If I want to show second page records, I will set to OFFSET 1, the Next 3 ROWS ONLY part will stay the same as want to show only 3 rows per page.

We can use variables so we don't have to make changes in the query and by changing the value of variables, we can return our required results. You can create Stored Procedure if you like by using below query.

Declare @PageNumber int
Declare @RowsPerPage int
set @RowsPerPage=3
SET @PageNumber=1

Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
         OFFSET (@PageNumber-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;

If we need to provide Stored Procedure to Front End team, which accepts page number and number of rows they would like to return for each page, you can use below to create Stored Procedure.

 Create procedure dbo.sp_GetSaleRecordsPerPage

 @PageNumber int,
 @RowsPerPage int
AS 
BEGIN
Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
         OFFSET (@PageNumber-1)*@RowsPerPage 
         ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
END

Let's say if we would like to return second page with 4 records, we can use the dbo.sp_GetSaleRecordsPerPage by providing below parameter values.

EXEC dbo.sp_GetSaleRecordsPerPage 2,4