Useful shortcut key in Sql Server


There are so many handy short key available in sqlserver. Which will improve the productivity of developer. I have made note on few of them. I hope this will help to other developer too.

1. Open a new query window
Click on >> CTRL +N

2. Toggle between the opened tabs
Click on >> CTRL+TAB

3. Show/Hide result panel
If you have to hide the result window then click on >> CTRL + R

4. Execute highlighted query
There are two option to execute the query that is using
a. F5
b. CTRL + E

5. Cancel the query execution
Click on >> ALT+ BREAK

6. Make the selected Text with Uppercase
Click on >> CTRL + SHIFT + U

7. Make the selected Text with Lowercase
Click on >> CTRL + SHIFT + L

8. Display the estimated execution plan
>> Select the query then click on CTRL + L

9. Intelligence list member and complete word
>> CTRL + SPACE, TAB

10. Go to Line no.
>> CTRL + G

11. To Comment the code
>> Ctrl + K & Ctrl + C;

12. To uncomment the code
>> Ctrl + K & Ctrl + U

13. Showing the table definition
>> Select the table name then click ALT + F1

14. Bookmark in Code
CTRL +K; CTRL+K

Summary

We show that some of the handy shortcut key of Sqlserver. If You know more than this, please let me know by your comment.

What is the CTE in SQL Server ?


1. CTE is the common table expression. This feature comes in sql server 2005 onward.
2. It is the temporary result set and generally it will be the result set of complex Sub Query.
3. CTE offer the same functionality as View.
4. It is defined with WITH Satement.
5. It improves the readability and ease in maintenance of complex queries and sub query.

CTE Simple Example

WITH CTE1
AS
 (
 SELECT 1 as Col1, 2 as Col2
 )
 
Select * from CTE1

Other Example

WITH CTEExample(EmpName,EmpAddress,DeptName) --Column Name for CTE
AS
(
 Select E.EmpName, E.EmpAddress,D.DeptName from tblEmp E Join tblDept D On  E.DeptId=D.DeptId
)
Select * from CTEExample where CTEExample.DeptName='CS'

When to use CTE ?

1. To simplifying the complex query to simple one like View in sql
2. To maintain more readable code
3. To create recursive query.
4. if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
5. To hold the values as Temp Table.

Point to remember

1. CTE Life will expire after first select statement.
2. In CTE, We can write only one Select query.

What is the difference between Temp Table and Table Variable?


Temp table and Table Variable both are created in TempDB and not in Memory

Syntax for creating Temp Table is exactly same as creating Physical Table

Create table  #Emp(Id int, EmpName Varchar(150),EmpAdd Varchar(150))

Insert into #Emp(Id,EmpName,EmpAdd) Values(1,'Ram','Bangalore')

Select *from #Emp

The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:

Declare @Emp Table(Id int,EmpName Varchar(150),EmpAdd Varchar(150))

Insert into @Emp (Id,EmpName,EmpAdd) Values(2,'Ram','Bangalore')

Select *from @Emp


• Table variables can not have Non-Clustered Indexes
• You can not create constraints in table variables
• You can not create default values on table variable columns
• Statistics can not be created against table variables

Similarities with temporary tables include:

• Instantiated in tempdb
• Clustered indexes can be created on table variables and temporary tables
• Both are logged in the transaction log
• Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

Table variable lifespan is only for the duration of transaction that it runs in. For executing the Table variable we have to execute the complete block code. Otherwise it will throw the exception.

When to use Table variable?

Table variable is mainly used in the function. We cannot use Temp table in function. For example

CREATE FUNCTION dbo.example1
(
)
RETURNS INT
AS
BEGIN
DECLARE @t1 TABLE (i INT)
INSERT @t1 VALUES(1)
INSERT @t1 VALUES(2)
UPDATE @t1 SET i = i + 5
DELETE @t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM @t1
RETURN @max
END
GO


--Exec example1

--Select dbo.example1()


We cannot create above example using Temp Temple like this

CREATE FUNCTION dbo.example2
(
)
RETURNS INT
AS
BEGIN
CREATE TABLE #t1 (i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)
UPDATE #t1 SET i = i + 5
DELETE #t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM #t1
RETURN @max
END
GO

What is the CTE in sql server ?


1. CTE is the common table expression. This feature comes in sql server 2005 onward.
2. It is the temporary result set and generally it will be the result set of complex Sub Query.
3. CTE offer the same functionality as View.
4. It is defined with WITH Satement.
5. It improves the readability and ease in maintenance of complex queries and sub query.

CTE Simple Example


WITH CTE1
AS
 (
 SELECT 1 as Col1, 2 as Col2
 )
 
Select * from CTE1

Other Example

WITH CTEExample(EmpName,EmpAddress,DeptName) --Column Name for Temporary Table
AS
(
 Select E.EmpName, E.EmpAddress,D.DeptName from tblEmp E Join tblDept D On  E.DeptId=D.DeptId
)
Select * from CTEExample where CTEExample.DeptName='CS'

When to use CTE ?

1. To simplifying the complex query to simple one like View in sql
2. To maintain more readable code
3. To create recursive query.
4. if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
5. To hold the values as Temp Table.

Point to remember

CTE Life will expire after first select statement.

How to create custom paging in Gridview using CTE ?


CustomPaging

Hi

There are so many approaches to do custom paging in Gridview for eaxmple using LINQ, using Temp table, using CTE etc.

Recently i used custom paging in gridview using CTE in store procedure. CTE is commmon table Expression. This feature come in sql server 2005 onward.

We generally use custom paging to increase the performance of application. if we will not use custom paging then every time all data will fetch from database. So performance of application will be very very slow.

We can do custom paging using CTE in very simple ways

Note: Here i have used datalist for displaying paging in footer of Gridview.

Step1: Write the store procedure using CTE like this



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Exec getEmpDetails 1,100,0

ALTER PROCEDURE getEmpDetails
      @StartIndex int,
      @PageSize int,
      @TotalCount int OutPut
as
--Select @TotalCount=count(1) from tblEmp where EmpName like '%';
Select @TotalCount=count(1) from tblEmp;

WITH EmpCTE AS
(
   select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY EmpId) 
   RowNumber,
   EmpId,
   EmpName,
   EmpSal
   from tblEmp 
)
select * from EmpCTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)

GO

Step 2: Create the Entity of table like in Business Entity layer


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CustomPaging
{
    public class EmpBE
    {
        public int StartIndex { get; set; }
        public int PageSize { get; set; }
        public int TotalCount { get; set; }
        public int EmpId { get; set; }
        public string EmpName { get; set; }
        public string EmpSal { get; set; }
    }
}

Step3: Write the Method for fetching data from database in Data acess layer like this


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.ObjectModel;


namespace CustomPaging
{
    public class EmpDAL
    {
        SqlConnection con=new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=EmpDB;Integrated Security=True");

        public Collection<EmpBE> GetEmpsDetails(EmpBE objEmp,out int totalcount) 
        {
            con.Open();
            
            Collection<EmpBE> EmpList = new Collection<EmpBE>();
            using (SqlCommand cmd = new SqlCommand("getEmpDetails", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StartIndex", objEmp.StartIndex);
                cmd.Parameters.AddWithValue("@PageSize", objEmp.PageSize);
                SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
                parTotalCount.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parTotalCount);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        EmpBE objEmp1 = new EmpBE();
                        objEmp1.EmpId = Convert.ToInt32(dr["EmpId"]);
                        objEmp1.EmpName = Convert.ToString(dr["EmpName"]);
                        objEmp1.EmpSal = Convert.ToString(dr["EmpSal"]);
                        EmpList.Add(objEmp1);
                    }
                    con.Close();
                    totalcount = Convert.ToInt32(parTotalCount.Value);
                }
            }
            return EmpList;
        }
    }
}

Step 4: Design the .aspx page like this


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="CustomPaging.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style3
        {
            width: 121px;
        }
        .style4
        {
            width: 98px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" Width="356px" ForeColor="#333333"
            AutoGenerateColumns="False">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="EmpId" HeaderText="EmpId" />
                <asp:BoundField DataField="EmpName" HeaderText="EmpName" />
                <asp:BoundField DataField="EmpSal" HeaderText="EmpSal" />
            </Columns>
            <EditRowStyle BackColor="#7C6F57" />
            <FooterStyle BackColor="#1C5E55" ForeColor="White" Font-Bold="True" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#E3EAEB" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#F8FAFA" />
            <SortedAscendingHeaderStyle BackColor="#246B61" />
            <SortedDescendingCellStyle BackColor="#D4DFE1" />
            <SortedDescendingHeaderStyle BackColor="#15524A" />
        </asp:GridView>
        <asp:DataList CellPadding="2" RepeatDirection="Horizontal" runat="server" ID="dlPager"
            OnItemCommand="dlPager_ItemCommand" BackColor="LightGoldenrodYellow" Width="356px"
            BorderColor="Tan" BorderWidth="1px" ForeColor="Black">
            <AlternatingItemStyle BackColor="PaleGoldenrod" />
            <FooterStyle BackColor="Tan" />
            <HeaderStyle BackColor="Tan" Font-Bold="True" />
            <ItemTemplate>
                <asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>'
                    CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>
            </ItemTemplate>
            <SelectedItemStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
        </asp:DataList>
        <br />
    </div>
    </form>
</body>
</html>

Step 5: write the code in Code behind file like this


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;

namespace CustomPaging
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        int pageSize = 3;
        int totalRowCount = 0;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGridview(1);
            }
        }

       
        /// <summary>
        /// This Method is used to generate the paging.
        /// </summary>
        /// <param name="totalRowCount">It has passed as integer.</param>
        /// <param name="pageSize">It has passed as integer.</param>
        /// <param name="currentPage">It has passed as integer.</param>

        public void generatePager(int totalRowCount, int pageSize, int currentPage)
        {
            int totalLinkInPage = 3;
            int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);

            int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);
            int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);

            if ((startPageLink + totalLinkInPage - 1) > totalPageCount)
            {
                lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);
                startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);
            }

            List<ListItem> pageLinkContainer = new List<ListItem>();

            if (startPageLink != 1)
                pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));
            for (int i = startPageLink; i <= lastPageLink; i++)
            {
                pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));
            }

            if (lastPageLink != totalPageCount)
                pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));

            dlPager.DataSource = pageLinkContainer;
            dlPager.DataBind();
        }
        protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName == "PageNo")
            {
                BindGridview(Convert.ToInt32(e.CommandArgument));
            }
        }

        private void BindGridview(int currentPage) 
        {
            int startRowNumber = ((currentPage - 1) * pageSize) + 1;
            EmpDAL objDAL = new EmpDAL();
            EmpBE objBE = new EmpBE();
            objBE.StartIndex = startRowNumber;
            objBE.PageSize = 3;
            objBE.TotalCount = 0;
            Collection<EmpBE> objEmpList = new Collection<EmpBE>();
            objEmpList = objDAL.GetEmpsDetails(objBE,out totalRowCount);
            GridView1.DataSource = objEmpList;
            GridView1.DataBind();
            generatePager(totalRowCount, pageSize, currentPage);
        }
    }
}

How to change a column of table without dropping that table in SQL Server 2008 ?


Hi

You can change a column of table without dropping that table in sql server 2008 like this

Step 1: go to Tools >> Options. In the little window, click on “Designer”.

Step2: Uncheck “Prevent saving changes that require” like this image

How to fetch data on basis of multiple Id in LINQ or EF ?


Hi
Recently one of my project, there was requirement to filter from one table on basis of multiple Id. There was using EF. So in EF we can write the Syntax like this

using (DemoJobPortalModel.DemoJobPortalEntities objE = new DemoJobPortalModel.DemoJobPortalEntities())
{
List<int> ListCountryId = new List<int> { 3, 5, 7 };

var query = from m in objE.tblCountries
where ListCountryId.Contains(m.Id)
select m;
GridView1.DataSource = query;
GridView1.DataBind();
}

In Sql Server we can do like this

Select *from tblCountry where Id in (3,5,7)