Limitations of the XML Data Type

Hi all,

I found the limitation of XML Data Type introduced in SQL Server 2005.

Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are:

1.    XML types cannot convert to text or ntext data types.


2.    No data type other than one of the string types can be cast to XML.


3.    XML columns cannot be used in GROUP BY statements.


4.    Distributed partitioned views or materialized views cannot contain XML data types.


5.    Use of the sql_variant instances cannot include XML as a subtype.


6.    XML columns cannot be part of a primary or foreign key.


7.    XML columns cannot be designated as unique.


8.    Collation (COLLATE clause) cannot be used on XML columns.


9.    XML columns cannot participate in rules.


10.    The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types.


11.    Tables can have only 32 XML columns.


12.    Tables with XML columns cannot have a primary key with more than 15 columns.


13.    Tables with XML columns cannot have a timestamp data type as part of their primary key.


14.    Only 128 levels of hierarchy are supported within XML stored in the database.

User Control and Javascript in UpdatePanel

Hi All,

There is general practices where we require some javascript function while useing UpdatePanel; along with the User Control; and the more problamatic is when the User Contorl is dynamic.

Problem:
When we use UserControl which change the state of Ajax postback; sometimes we require some javascript which needs to be embaded after loading the UserControl and with this said I am using UpdatePanel. Means my code is in the UpdatePanel.

Solution:
Generally we are using following code to run any javascript in code behind

Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "key", "alert('hi');", true);

Now if you are using UpdatePanel then replace you above code with following code.

ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "key", "alert('hi');", true);

Now you can see the alert saying 'hi' to you!!

Query operators evaluation

Hi all,

Here is the order in which query operators are evaluated. There are 11 levels.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH{CUBE | ROLLUP}
7. HAVING
8. SELECT
9. DISTINCT
10. HAVING
11. TOP

First the query processor reads all the rows from the FROM the left table and apply the ON condition with the right table provided in JOIN.

If there are more JOINs the same is done for all the JOINs.

Then the WHERE clause is applied to filter rows.

Then GROUP BY is done

There WITH clause get evaluated followed by HAVING.

Then the columns are selected. (This is the reason why you cannot use a column alias in WHERE or GROUP BY)

Then DISTINCT clause applied

Then ORDER BY is processed...(This is the reason why you CAN use a column alias in the ORDER BY clause)

Then TOP clause get evaluated.
Refrence: SQL Server 2005: Query processing basics. An event presented by Vinod Kumar

Here is the screen-shot of the video captured during the event presented by Vinod Kumar in event.

OUTPUT CLAUSE (Transact-SQL)

How to know your INSERT UPDATE or DELETE statement effect how much recoreds? Or what if I need the list of identity values which get generated by INSERT statements?

One way is before insert I should get the MAX ID; after sucessfull insert again I get the ID and select the ID which are in betwwen them, like...

DECLARE @MinId INT 2: DECLARE @MaxId INT
 
SELECT @MinId = MAX(ID) from SearchResult
 
INSERT INTO SearchResult(Keyword, Hits)
SELECT Keyword, Hits FROM TmpTable
 
SELECT @MaxId = MAX(ID) from SearchResult
 
SELECT ID FROM SearchResult WHERE ID > @MinID AND <= @MaxID

But the result will not always true, like in the world of multitasking, what if some other insert also take place??

Now? How can we get all the newly added or created identities?

The other and best way is using OUTPUT CLAUSE. Here we go...

DECLARE @IDTable Table
{
    Id BIGINT
}
 
INSERT INTO SearchResult(Keyword, Hits)
OUTPUT INSERTED.ID INTO @IdTable

SELECT Keyword, Hits FROM TmpTable

The inserted IDs will be in magic table called INSERTED, and by using OUTPUT CLAUSE we can grab it and save it to temp table or temporary variable... from there we can select the newly added IDs, like..

SELECT ID FROM @IDTable

Read more on OUTPUT CLAUSE

Ahmedabad SQL Server User Group

Hello my dear friends,

I recently joined the user a user group, founded by Jacob Sebastian [MVP/Co-founder of Excellence Infonet, Ahmedabad]; the user group Ahmedabad SQL Server User Group is very fast growing and providing the knowledge which we either dont know or we are not willing to know. The group will have monthly event called "SQL Server Satureday", which provides developer/member to gain new knowledged shared by professional[s].

Recently on 19-July-2008, we have a very good and very interactive session, presented by Mr Pinal Dave [I hope you all know him very vell, visit his blogs http://blog.sqlauthority.com/ was mainly on best SQL Server best practices.

Read more

I was actively participed in the discussion/presentation on best; learns lots many things also added few practices.

Here is the group photo.

On my right side you can see the Mr. Pinal Dave and opposite to Mr Pinal, you can see Mr. Jacob Sebastian. And all the group member.

I request you to join the group and be part of such informative and intractive session.
Regards,
Imran

Overloading ToString Method of Enum

Hello All,

The general scenario for using enum is to provide a way to restrict a variable to one of a fixed set of values.

Now at the same time we need the string value of enum, and by default we get what we right as the name of enum, like I created following enum

public enum Operation
{
    ChangePassword,
    EditProfile,
    SearchResult,
    Search
}

Now when I try to call ToString method will show the name of enum.

Operation operation = Operation.ChangePassword;
Console.Write(operation.ToString());

//It will show you
//ChangePassword

Now what if I want to show Change Password insted of ChangePassword?

We can do it by Extension methods which are introduced in C# 3.0, I created following Extension method which operates on my Operation enum. Here is the method.

public static string EnumToString(this Operation operation)
{
    switch (operation)
    {
        case Operation.ChangePassword:
            return "Change Password";
        case Operation.EditProfile:
            return "Edit Profile";
        case Operation.SearchResultL:
            return "Search Result";
        default :
            return operation.ToString();
    }
}

You see the switch case? I write the case for value which needs to show differently then name.

Operation operation = Operation.ChangePassword;
Console.Write(operation.EnumToString());

//this will show you what you needed
//Change Password

LoginView Control provided by .NET 2.0

Its general prectices where we have to show some different layout when user is anonymous and different layout when user is authorized.

The .NET 2.0 provides the inbuild control, asp:LoginView; it will allow you to define the two different tamplets, one for anonymouse and another is for authorized user. Also it provides the the RoleGroups; using this you can also devide your authorized user template to one step down, means we can define the template based on Role too.

Here is the common template of asp:LoginView

<asp:LoginView runat="server" ID="loginView">
    <AnonymousTemplate>
        <table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr>
                <td class="top-menu">
                    <a href="#" title="">Login</a>
                </td>
            </tr>
        </table>
    </AnonymousTemplate>
    <LoggedInTemplate>
        <table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr>
                <td class="top-menu">
                    <a href="#" title="">Home</a>
                    <a href="#" title="">Change Password</a>
                    <a href="#" title="">Logout</a>
                </td>
            </tr>
        </table>
    </LoggedInTemplate>
</asp:LoginView>