Restrictions with View Definition in SQL Server 2008

We do have a few restrictions with the view’s definition in SQL Server. We cannot use each and every T-SQL statement inside a view’s definition. And, of course, this is one of the most commonly asked questions in technical interviews as well.

Here goes a list of restrictions with view’s definition:

1. Every column, basically derived columns must have a column name.
2. The INTO keyword cannot be used inside the SELECT statement.
3. The COMPUTE BY clause cannot be used with the SELECT statement.
4. Normally, the ORDER BY clause cannot be used with the SELECT statement. However, if we wish to use ORDER BY clause, we must use TOP(n) with the SELECT statement.
5. A local or global temporary table (tables with # and ## sign) cannot be used inside a view.                                                                                                                                      
6. AFTER triggers cannot be associated with a view. However, we may associate INSTEAD OF trigger with a view.
7. Rules and Defaults cannot be associated with a view.
8. Full-Text index cannot be defined on a view.
9. A view can have maximum of 1,024 column in its SELECT list.
10. A view can have maximum of 32 levels of view nesting i.e. called nested views. 
11. All columns names must be explicitly specified when using WITH SCHEMABINDING option with a view’s definition. Even, when we are trying to pull all the columns out, we can’t use (*) sign. Instead, we need to write all column names explicitly.

This is all what I can compile that my memory could recall so far. If you have something to add/remove, which I missed out over here, please do put it in Leave a comment/Leave a Reply section below. I would appreciate your valuable contribution to the community.

What is Identifier in SQL Server?

I’ve come across this question many times in life – 

What is “Identifier” in SQL Server?

 So, I thought to write about it today. Here it goes.

In SQL Server we create objects like database, login, tables, views, stored procedures, columns, indexes, etc. We give a name to every object, which is used to refer to it later on. This name is known as “IDENTIFIER” in SQL Server. But, one must be surprised to know that there are objects in SQL Server, which don’t require identifier i.e. a name. For example, Constraints.

Identifiers are created at two levels:

 (i) SQL Serve Instance Level

 (ii) Database Level. 

Identifiers can be logically categorized into two categories:

 (i)  Regular Identifier

(ii) Delimited Identifier.

Regular identifier is one which conforms to the object naming rules in SQL Server while Delimited identifier is one which does not conform to object naming rules for SQL Server. Delimited identifiers are enclosed with either ( ‘  ‘ ) single quote or ( [  ] ) square bracket.

How to Prevent Conflict j query?

  <script type="text/javascript">
        var $j = jQuery.noConflict();
        $j(function () { $j(".slideshow").cycle({ fx: "fadeout", timeout: 4000 }); $j('#slider').slider({}); });
     </script>

New Feature in 2013 VS

1:Create a new Azure website from within VS
2:Open the Resolve menu when typing
3:Live debugging in Azure
4:Live tracing from Azure
5:64 bit Edit and Continue
6:Return value inspection
7:Just my code
8:Peek a definition

how to change emergency mode to normal mode in sql server database?

USE master
GO

SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO

USE master
GO

ALTER DATABASE db_PanCard SET EMERGENCY
GO


DBCC CHECKDB (db_PanCard, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
SELECT state_desc FROM sys.databases WHERE name='db_PanCard'
go
ALTER DATABASE db_PanCard SET SINGLE_USER;
go
DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
go

SELECT state_desc FROM sys.databases WHERE name='db_PanCard'
GO


ALTER DATABASE db_PanCard SET MULTI_USER;

Treeview in Asp.net C# with Checkbox

CREATE TABLE [dbo].[District_mst](

        [state_id] [int] NULL,

        [district_id] [int] NULL,

        [district] [varchar](50) NULL

) ON [PRIMARY]
CREATE TABLE [dbo].[State_mst](

        [state_id] [int] NULL,

        [state] [varchar](50) NULL

) ON [PRIMARY]

=======================

using System;
using System.Collections;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{

    SqlConnection conn = new SqlConnection("Data Source=ADMIN-PC;Initial Catalog=test;  Integrated Security=True");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            PopulateTreeview();
            foreach (TreeNode node in tvTables.Nodes)
            {
                node.Checked = true;
                if (node.ChildNodes.Count > 0)
                    checkChildNode(node);
            }


        }
    }
    private void checkChildNode(TreeNode node)
    {
        foreach (TreeNode chNode in node.ChildNodes)
        {
            chNode.Checked = true;
            if (chNode.ChildNodes.Count > 0)
                checkChildNode(chNode);
        }
    }

    private void PopulateTreeview()
    {
        try
        {
            DataSet ds = new DataSet();
            DataTable dtparent = new DataTable();
            DataTable dtchild = new DataTable();
            dtparent = FillParentTable();
            dtparent.TableName = "A";
            dtchild = FillChildTable();
            dtchild.TableName = "B";

            ds.Tables.Add(dtparent);
            ds.Tables.Add(dtchild);

            ds.Relations.Add("children", dtparent.Columns["state_id"], dtchild.
                                                           Columns["state_id"]);

            if (ds.Tables[0].Rows.Count > 0)
            {
                tvTables.Nodes.Clear();

                foreach (DataRow masterRow in ds.Tables[0].Rows)
                {
                    TreeNode masterNode = new TreeNode((string)masterRow["state"],
                                         Convert.ToString(masterRow["state_id"]));
                    tvTables.Nodes.Add(masterNode);
                    tvTables.CollapseAll();
                    foreach (DataRow childRow in masterRow.GetChildRows("Children"))
                    {
                        TreeNode childNode = new TreeNode((string)childRow["district"],
                                               Convert.ToString(childRow["state_id"]));
                        masterNode.ChildNodes.Add(childNode);
                        childNode.Value = Convert.ToString(childRow["district_id"]);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception("Unable to populate treeview" + ex.Message);
        }
    }

    private DataTable FillParentTable()
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataTable dtnew = new DataTable();
        conn.Open();
        string cmdstr = "Select * from State_mst";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(ds);
        cmd.ExecuteNonQuery();
        dt = ds.Tables[0];
        conn.Close();
        dtnew = dt.Copy();

        return dtnew;
    }

    private DataTable FillChildTable()
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataTable dtnew = new DataTable();
        conn.Open();
        string cmdstr = "Select * from District_mst";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(ds);
        cmd.ExecuteNonQuery();
        dt = ds.Tables[0];
        conn.Close();
        dtnew = dt.Copy();
        return dtnew;
    }
    protected void btn_save_Click(object sender, EventArgs e)
    {
        Response.Write("<b>Seleted values of nodes:</b><br/>");
        foreach (TreeNode item in this.tvTables.CheckedNodes)
        {

            conn.Open();
          string  str = "Insert into Category1 values ('" + item.Text + "')";
            SqlCommand com = new SqlCommand(str, conn);
            com.ExecuteNonQuery();
            conn.Close();
            Response.Write(item.Value + "<br/>");
        }
    }
}

====================

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"

    CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">

    <script type="text/javascript">

        function OnTreeClick(evt) {

            var src = window.event != window.undefined ? window.event.srcElement : evt.target;

            var isChkBoxClick = (src.tagName.toLowerCase() == "input" && src.type == "checkbox");

            if (isChkBoxClick) {

                if (src.checked == true) {

                    var nodeText = getNextSibling(src).innerText || getNextSibling(src).innerHTML;

                    var nodeValue = GetNodeValue(getNextSibling(src));

                    document.getElementById("label").innerHTML += nodeText + ",";

                }

                else {

                    var nodeText = getNextSibling(src).innerText || getNextSibling(src).innerHTML;

                    var nodeValue = GetNodeValue(getNextSibling(src));

                    var val = document.getElementById("label").innerHTML;

                    document.getElementById("label").innerHTML = val.replace(nodeText + ",", "");

                }

                var parentTable = GetParentByTagName("table", src);

                var nxtSibling = parentTable.nextSibling;

                //check if nxt sibling is not null & is an element node

                if (nxtSibling && nxtSibling.nodeType == 1) {

                    //if node has children    

                    if (nxtSibling.tagName.toLowerCase() == "div") {

                        //check or uncheck children at all levels

                        CheckUncheckChildren(parentTable.nextSibling, src.checked);

                    }

                }

                //check or uncheck parents at all levels

                CheckUncheckParents(src, src.checked);

            }

        }

        function CheckUncheckChildren(childContainer, check) {

            var childChkBoxes = childContainer.getElementsByTagName("input");

            var childChkBoxCount = childChkBoxes.length;

            for (var i = 0; i < childChkBoxCount; i++) {

                childChkBoxes[i].checked = check;

            }

        }

        function CheckUncheckParents(srcChild, check) {

            var parentDiv = GetParentByTagName("div", srcChild);

            var parentNodeTable = parentDiv.previousSibling;

            if (parentNodeTable) {

                var checkUncheckSwitch;

                //checkbox checked 

                if (check) {

                    var isAllSiblingsChecked = AreAllSiblingsChecked(srcChild);

                    if (isAllSiblingsChecked)

                        checkUncheckSwitch = true;

                    else

                        return; //do not need to check parent if any(one or more) child not checked

                }

                else //checkbox unchecked

                {

                    checkUncheckSwitch = false;

                }

                var inpElemsInParentTable = parentNodeTable.getElementsByTagName("input");

                if (inpElemsInParentTable.length > 0) {

                    var parentNodeChkBox = inpElemsInParentTable[0];

                    parentNodeChkBox.checked = checkUncheckSwitch;

                    //do the same recursively

                    CheckUncheckParents(parentNodeChkBox, checkUncheckSwitch);

                }

            }

        }

        function AreAllSiblingsChecked(chkBox) {

            var parentDiv = GetParentByTagName("div", chkBox);

            var childCount = parentDiv.childNodes.length;

            for (var i = 0; i < childCount; i++) {

                if (parentDiv.childNodes[i].nodeType == 1) {

                    //check if the child node is an element node

                    if (parentDiv.childNodes[i].tagName.toLowerCase() == "table") {

                        var prevChkBox = parentDiv.childNodes[i].getElementsByTagName("input")[0];

                        //if any of sibling nodes are not checked, return false

                        if (!prevChkBox.checked) {

                            return false;

                        }

                    }

                }

            }

            return true;

        }

        //utility function to get the container of an element by tagname

        function GetParentByTagName(parentTagName, childElementObj) {

            var parent = childElementObj.parentNode;

            while (parent.tagName.toLowerCase() != parentTagName.toLowerCase()) {

                parent = parent.parentNode;

            }

            return parent;

        }

        function getNextSibling(element) {

            var n = element;

            do n = n.nextSibling;

            while (n && n.nodeType != 1);

            return n;

        }

        //returns NodeValue

        function GetNodeValue(node) {

            var nodeValue = "";

            var nodePath = node.href.substring(node.href.indexOf(",") + 2, node.href.length - 2);

            var nodeValues = nodePath.split("\\");

            if (nodeValues.length > 1)

                nodeValue = nodeValues[nodeValues.length - 1];

            else

                nodeValue = nodeValues[0].substr(1);

            return nodeValue;

        }  

    </script>

</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <table width="600px" align="center">

        <tr>

            <td colspan="2" style="background-image: url(Images/header.jpg); height: 70px">

                <img src="Images/font1.png" alt="Dotnet-Fox" height="30px" />

            </td>

        </tr>

        <tr>

            <td>

                <asp:TreeView ID="tvTables" runat="server" ForeColor="Black" Font-Size="13pt" ShowCheckBoxes="All"

                    OnClick="OnTreeClick(event)">

                </asp:TreeView>

                <br />

                <div id="label">

                </div>

            </td>

        </tr>

        <tr>

            <td colspan="2" style="background-image: url(Images/header.jpg); height: 30px">

            </td>

        </tr>

    </table>

</asp:Content>

SQL Server XQuery(XML) Methods

declare @xml xml
set @xml='<Supplier><user  userNo="1" email="gautam.singh@gmail.com"><item no="1" name="Item1"></item></user>
                    <user  userNo="2" email="gautam1.singh@gmail.com"><item no="2" name="Item2"></item></user>
<user  userNo="3" email="gautam2.singh@gmail.com"><item no="6" name="Item3"></item></user>
<user  userNo="3" email="gautam2.singh@gmail.com"><item no="6" name="Item3"></item></user>
         </Supplier>'
 -----Select
 --SELECT @xml.exist('/Supplier/user[@email = "gautam12.singh@gmail.com"]') as Result1
 --SELECT @xml.query('/Supplier/user/item') as Users
 -- --select Distincts
 --SELECT @xml.query('distinct-values(data(/Supplier/user/item/@no))') as Items
 ----select on particular column
--SELECT @xml.value('/Supplier[1]/user[1]/@email', 'VARCHAR(50)') as ResultEmail1

--SELECT x.value('@userNo', 'int') AS UserNo, x.value('@email', 'varchar(50)') AS Email FROM @xml.nodes('/Supplier/user') TempXML (x)

-- --modify
--DECLARE @userNo int =120
--SET @xml.modify ('replace value of (/Supplier/user/@userNo)[3] with sql:variable("@userNo")')
--SELECT @xml;
--SET @xml.modify(' replace value of (/Supplier/user/@userNo)[3] with ( if (count(/Supplier/user[3]/item) > 1) then "3.0" else "1.0" ) ')
--SELECT @xml;
----Delete
--DECLARE @itemNo int=2
--SET @xml.modify(' delete Supplier/user/item[@no=sql:variable("@itemNo")]')
--SELECT @xml;