Discuss Dotnet Posts

Tuesday, August 28, 2012

SQL Server Indexes - Definition

One of the most important technique to high performance queries in a SQL Server database is the index. Like we have index in books for fast searching, indexes speed up the querying process by providing fast access to rows in the data tables.

Indexes are of two types, clustered and nonclustered indexes.

Clustered Index

A clustered index is an index that stores the actual data. It is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A clustered index stores the actual data rows at the leaf level of the index. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table. A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Non-Clustered Index

A non-clustered index is just a pointer to the data. It is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and data row pointers , rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row pointer depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Friday, July 27, 2012

VisualStudio 2010, Define shortcut keys to Enable/Disable All Breakpoints

In Visual Studio, you can create or delete a single breakpoint by hitting F9, and can delete all breakpoints by Ctrl+Shift+F9 shortcut. It does not provide shortcut keys to disable or enable all breakpoints, but you can define that by yourself.
  • Go to Tools > Options popup.
  • Enter "DisableAllBreakpoints" in the textbox for Show commands containing:.
  • I am using Global option from User new shortcut in: combobox.
  • Set focus on Press shortcut keys textbox and hit your shortcut keys you want to set. (e.g. I am using Alt+1)
  • Press Assign button
  • Now press OK and use your new shortcut keys.(Make sure your newly created shortcut key is selected in Shortcuts for selected command) combobox while hitting OK button.
Similary you can set shortcut key for EnableAllBreakPoints or any other command.

Wednesday, June 27, 2012

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I have installed SQL Server and Management Studio on new machine and facing issues while connecting to SQL Server through remote machines on my local area network. After thorough searching and experimenting I have got resolved the error. Following are the steps(not necessary all) to resolve the issue.

SQL Server should be running

Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node SQL Server 2005 Services, under SQL Server Configuration Manager parent node. SQL Server's state should be Running. SQL Server Browser service should also be Running (you can Start/Stop by right click on service name)

Enable TCP/IP in SQL Server Configuration

Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node Protocols for SQLServer(your server's instance name) , under SQL Server 2005 Network Configuration.
TCP/IP's state should be Enabled. (can be Enabled/Disabled by right click on Protocol Name)

App Exception for SQL Server TCP Port in Windows Firewall

Go to Conctrol Panel > Windows Firewall > Exceptions (tab). Click button Add Port, enter name and port number 1433. Make sure your entered name is displaying in Programs and Services section, and should be checked while pressing OK button on the tab.

App Exception for sqlbrowser.exe in Windows Firewall

In Firewall window > Exceptions tab, click on Add Program. Locate the sqlbrowser.exe by Browse button. Click OK. At my system, it is located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.

App Exception for sqlservr.exe in Windows Firewall

Similarly add sqlservr.exe in Firewall Exceptions. At my system, it is located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe.

Enable Remote Connection

Right click on SQL Server main node in Management Studio, and go to Properties. Select Connections page from Select a Page section. Allow remote connections to this server should be checked.


Tuesday, May 8, 2012

MS sql what is CTE? how to write recusive query in sql?

A common table expression (CTE) can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Pseudocode and semantics

The recursive CTE structure must contain at least

  • one anchor member
  • The first invocation of the recursive CTE consists of one ( or more) CTE_query_definition(s). Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
  • one recursive member.

    The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
The following pseudocode shows the components of a simple recursive CTE that contains a single anchor member and single recursive member.

WITH cte_name ( column_name [,...n] )
CTE_query_definition –- Anchor member is defined.
CTE_query_definition –- Recursive member is defined referencing cte_name.
-- Statement using the CTE
FROM cte_name


Let's demontrate the Category table example, we have table with different categories, sub-categories, sub-sub-categroies etc... to Nth level.

We have the following data in our Category table:

CategoryID Name ParentID
1 Office NULL
2 Computer NULL
3 Books NULL
4 Office Accessories 1
5 Furniture 1
6 Decoration Items 1
7 LCDs 2
8 CPUs 2
9 Keyboards 2
10 Urdu 3
11 Programming 3
12 ASP.Net 11
13 C# 11
14 VB.Net 11
15 Java 11
16 PHP 11
17 Poetry 10
18 Stories 10
19 Novels 10
20 Kids 18
21 Funny 18

We are interested to get the full category names (from parent category to last child category, e.g. Books > Programming > C# ), we acheive the result implementing the same CTE pseudocode.

WITH CategoryList
-- Anchor Query Member
SELECT parent.CategoryID, CONVERT(VARCHAR(50), Parent.Name) as Name, parent.ParentID
FROM Category as parent
WHERE parent.ParentID IS NULL


-- Recursive Query Member
SELECT child.CategoryID, CONVERT(VARCHAR(50), CL.Name + ' > ' + child.Name) as Name, child.ParentID
FROM Category as child
INNER JOIN CategoryList as CL ON child.ParentID = CL.CategoryID -- Can be think of as the termination condition
FROM CategoryList

That's simple, you will get the required output as:

CategoryID Name ParentID
1 Office NULL
2 Computer NULL
3 Books NULL
10 Books > Urdu 3
11 Books > Programming 3
12 Books > Programming > ASP.Net 11
13 Books > Programming > C# 11
14 Books > Programming > VB.Net 11
15 Books > Programming > Java 11
16 Books > Programming > PHP 11
17 Books > Urdu > Poetry 10
18 Books > Urdu > Stories 10
19 Books > Urdu > Novels 10
20 Books > Urdu > Stories > Kids 18
21 Books > Urdu > Stories > Funny 18
7 Computer > LCDs 2
8 Computer > CPUs 2
9 Computer > Keyboards 2
4 Office > Office Accessories 1
5 Office > Furniture 1
6 Office > Decoration Items 1



Saturday, April 28, 2012

Cannot resolve collation conflict for column 1 in SELECT statement.

In MS SQLSERVER, When you try to concatenate, compare, make joins on columns (defined with different collations), you will get the following error message:

Cannot resolve collation conflict for column 1 in SELECT statement.

In MS SQLSERVER, collation can be set at column level. You can resolve the issue in different ways :
  1. Define same collation for each column in the subject query (Right click TableName > Design/Modify > Select Column > set Collation property in TableDesigner section of Column Properties pane).

  2. Place COLLATE DATABASE_DEFAULT after each column name used in the query. e.g.
        FirstName COLLATE DATABASE_DEFAULT + ' ' +
        LastName COLLATE DATABASE_DEFAULT + ' ' +
        Email COLLATE DATABASE_DEFAULT + ' '       
    FROM [User]

  3. Place COLLATE DATABASE_DEFAULT after select clause, but before from clause. e.g.
        FirstName + ' ' +
        LastName + ' ' +
        Email + ' '       
    FROM [User]

Monday, April 23, 2012

How to edit selected rows manually in SQL Server Mangament Studio

Right-click on any specific table in SQL Sever object explorer, gives you the option Edit top 200 rows, and displays the top 200 rows in grid format, where you can edit values manually. But the problem arises when you want to edit rows in this way, but need rows of your choice, not top 200. You need the rows e.g. based on where clause filter expressions.

To obtain this behavior, you need the following steps :
  1. Right-click on any table
  2. Click Edit top 200 rows
  3. Right-click on any cell of the rows (displaying in the result pane)
  4. Goto Pane > SQL
  5. Now you can use where clause with filter expressions and you required and get the results in grid form which you can edit manually.

Saturday, April 21, 2012

How to fix local host port number in visual studio

Recently I have faced an issue, how to fix the localhost port number in visual studio. With visual studio's default behavior, it changes the port number randomly (by checking any available port). But for some testing purpose I need it be retain at fixed number, so that I could refer the site with some static url for a specific timespan.

Following are the steps to set the fixed/static port number:
  • Select the website project node in solution explorer.
  • Right-click > Properties, OR press F4.
  • Set Use dynamic ports to false.
  • Enter any fixed available port number in the field Port number.

Now your localhost website's url remains on static port while you rerun you website.