Friday 27 April 2012

Sql Server–Row Count for All Tables

 

Tested on Sql Server 2008 and 2008 R2.

Code Snippet
  1. select SCHEMA_NAME(sys.tables.schema_id) as [Schema], sys.tables.name as [Table], sys.sysindexes.rowcnt as [Cnt]
  2. from sys.indexes
  3.     join sys.sysindexes
  4.         on sysindexes.id = sys.indexes.object_id
  5.             and sysindexes.indid = sys.indexes.index_id
  6.     join sys.tables
  7.         on sys.tables.object_id = sys.sysindexes.id
  8. where is_primary_key = 1
  9.     and sys.tables.type_desc = 'USER_TABLE'
  10. order by [Schema], [Table]

It is simple, isn’t it?

Thursday 5 April 2012

Looking for unused indexes in Sql Server 2008

 

In a long running project the database is changing day by day. We create new tables, optimize our queries, and of course, make new indexes. Usually when I finish an optimization process I forget an important step:

I always forget to check how many indexes I have that I don’t use anymore.

Sql server stores index statisctics in a sys.dm_ view named SYS.DM_DB_INDEX_USAGE_STATS. As the other sys.dm_ views, sql server clears statistics on every restart, so we have to check after missing indexes only for our database instance had run for significant time and we had collected enough statistics to decide if we delete an index or not.

Now after we have understood the basic, let’s see the query for showing our index statistics:

DECLARE @table_name varchar(100) = null

-- Uncomment the following line if you want to filter for a given table
--set @table_name = 'table_name'

SELECT
         OBJECT_NAME(I.[OBJECT_ID]) AS [OBJECT NAME],
         I.[object_id],
         I.[NAME] AS [INDEX NAME],
         sum(USER_SEEKS) as USER_SEEKS,
         sum(USER_SCANS) as USER_SCANS,  
         sum(USER_LOOKUPS) as USER_LOOKUPS,
         sum(USER_UPDATES)  as USER_UPDATES
     FROMSYS.INDEXES AS I
         LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
  WHEREOBJECTPROPERTY(I.[OBJECT_ID],'IsUserTable') = 1
    and (@table_name is null or OBJECT_NAME(I.[OBJECT_ID]) = @table_name)

group by OBJECT_NAME(I.[OBJECT_ID]),
         I.[object_id],
         I.[NAME]
    order by [OBJECT NAME], [INDEX NAME]

As we can see on our column names, we access seek, scan, lookup and index update statistics counted since the last start of our sql server instance. Our result can contain null statistics. In that case we don’t have any statistics about the given index since the last restart.

This script contains one parameter (@table_name). If we uncomment the 4th line, we can filter for indexes only on a given table.

Monday 19 March 2012

4000 visitors

4000 visitors! :)

I work on 3 projects during this month and don't have a time to write.
The life continues on April.

Morzel

Thursday 1 March 2012

ProgrammerFail - Else or Continue?

 

if (value < 100)
{
    value = 100 - value;
    continue;   // Why???
}
else
{
    value = value + 100;
}
Double penetration – I really don’t want to run the else clause…

ProgrammerFail

!!!FAIL!!!

Monday 27 February 2012

ProgrammerFail–Using System Functions

 

Nice example that shows what happens if we don’t have enough time to read a book about ‘Teach Yourself SQL Server Programming in 24 hours’ and start programming without knowing the tool that we use.

After we check msdn, we can easily check if two dates are in the same day:


create FUNCTION [dbo].[isSameDay](@DATE1 datetime, @DATE2 datetime)
RETURNS bit
AS
BEGIN
    DECLARE @result bit=0
    if (year(@DATE1)=year(@DATE2))
    and (month(@DATE1)=month(@DATE2))
    and (day(@DATE1)=day(@DATE2))
    set @result=1
    return @result
END

ProgrammerFail

!!!FAIL!!!

Friday 24 February 2012

Sql Server–Query random N elements from a table with identity PK

 

A simple solution how to query random N records from a database table without iteration on a full table:

 


--- because cannot call rand() in scalar valued function
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

go

--- returns a randon number between min and max
CREATE FUNCTION RandNumber(@Min int, @Max int)
RETURNS int
AS
BEGIN
RETURN ceiling(@Min + (select RandNumber from vRandNumber) * (@Max-@Min))
END

go


-- selecting 100 random rows from a table
declare @cnt int

select @cnt = MAX(<pk_column_name, sysname, ID>)
from <table_name, sysname, sample_table>



select top <number_of_rows,, 100> *
from <table_name, sysname, sample_table>
where <pk_column_name, sysname, ID> in (
    SELECT dbo.RandNumber(1, @cnt) as rnd
    from <table_name, sysname, sample_table>
)
option (recompile) -- it selects only one row without recompile

 

That’s all.

 

Morzels

Monday 20 February 2012

ProgrammerFail–Stored Procedures

 

I have found this construction in a living project’s db layer:

ALTER PROCEDURE [dbo].[GetSomething] @param VARCHAR(100)
    AS
    BEGIN
    exec [dbo].[GetSomething2] @param
/*
-- Original Select
select .....

*/
END


And what is in the stored procedure that we call:

ALTER PROCEDURE [dbo].[GetSomething2] (
    @param    varchar(100)
) AS
BEGIN
    --- new select
    select ....
    
END

 

Nice code if we collect stored procedures instead of stamps…

ProgrammerFail

!!!FAIL!!!

Thursday 9 February 2012

ProgrammerFail–The Fail of Naming

 

 

if (!need_to_update)
    update_all_views();

Let's update all the views, when there is no need to update.. As we can see high quality comments describe what this piece of shit does.

 

ProgrammerFail

!!!FAIL!!!

Tuesday 7 February 2012

ProgrammerFail–Advanced techniques using Sql Server in C#

 

[WebMethod]
public static string CreateProfileID(string firstName, string lastName)
{
    string profileID = "";

    using (SqlConnection conn = Common.getConnection())
    {
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = string.Format("select dbo.[create-something] ('{0}', '{1}') as somethingID", firstName, lastName);

            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                profileID = dr["somethingID"] as string;
            }
        }
        catch (Exception e)
        {

        }
    }

    return profileID;
}

Everything in one method: How to handle exceptions.

 

ProgrammerFail

!!!FAIL!!!

Thursday 2 February 2012

ProgrammerFail–Go To The Hell

 

Without any comment, let’s go to the hell:

/// <summary>
/// WriteRaw writes out the given string "unescaped", in other words it better be well formed XML markup.
/// So for the XmlNodeWriter we parse this string and build the resulting tree, so it maps to setting the
/// InnerXml property.  
/// </summary>
/// <param name="data"></param>
public override void WriteRaw(string data)
{
    if (data.IndexOf("<") < 0)
    {
        WriteString(data);
        return;
    }

    switch (state)
    {
        case WriteState.Start:
            goto case WriteState.Content;
        case WriteState.Prolog:
            goto case WriteState.Content;
        case WriteState.Element:
            state = WriteState.Content;
            goto case WriteState.Content;
        case WriteState.Attribute:
            {
                ArrayList saved = new ArrayList();
                if (ca.HasChildNodes)
                {
                    while (ca.FirstChild != null)
                    {
                        saved.Add(ca.FirstChild);
                        ca.RemoveChild(ca.FirstChild);
                    }
                }
                ca.InnerXml = data;
                for (int i = saved.Count - 1; i >= 0; i--)
                {
                    ca.PrependChild((XmlNode)saved[i]);
                }
            }
            break;
        case WriteState.Content:
            {
                ArrayList saved = new ArrayList();
                if (current.HasChildNodes)
                {
                    while (current.FirstChild != null)
                    {
                        saved.Add(current.FirstChild);
                        current.RemoveChild(current.FirstChild);
                    }
                }
                current.InnerXml = data;
                for (int i = saved.Count - 1; i >= 0; i--)
                {
                    current.PrependChild((XmlNode)saved[i]);
                }
                state = WriteState.Content;
            }
            break;
        case WriteState.Closed:
            throw new InvalidOperationException("Writer is closed");
    }

}

Nice combination of using goto and switch statement together. I hope I will never work with the author.

 

ProgrammerFail

!!!FAIL!!!

Thursday 26 January 2012

ProgrammerFail–N Layered Architecture

What is wrong with the following code?

using (SqlDataReader dr = ...)

At the first look, it seems perfect but:

  • N layered architecture
  • Layers are: DAO –> Factory –> BL –>WebApp

 

And this code with the previous row:

protected void ddlUnits_SelectedIndexChanged(object sender, EventArgs e)
        {
            using (SqlDataReader dr = ...)

ProgrammerFail

!!!FAIL!!!

Monday 9 January 2012

Sql Server Management Studio tuning: T-Sql Formatter and SSMS Tools Pack


I think Microsoft Sql Server Management Studio is a great tool for managing Sql Server, writing and optimizing Transact Sql code and for anything with Sql Server but I have always missed some functions:
  • Formatting Sql code
  • Code Snippets (Like in Visual Studio)
  • CRUD Stored Procedure generation
I had to reinstall my working machine last week and started searching for some free Sql Server Management Studio addins for these functions and finally I found a solution:
Poor Man’s T-SQL Formatter for formatting Sql code.
SSMS Tools Pack for the others.

Poor Man’s T-SQL Formatter is a free add in that formats Sql scripts with a single hotkey. As Visual Studio, we can format the whole document or just the selected part of it.
After the installation it puts two entries into the SSMS Tools menu: Format T-SQL Code and T-SQL Formatting Options.
PoorMansTSqlFormatterOptions
These are my personal settings. I have changed the default hotkey to CTRL+K, CTRL+D and unchecked all the extras, because the default settings were uncomfortable for me (This hotkey is the same as the debugging hotkey in SSMS Tools Pack but I disabled this function because I think I wont use it).
It also has a free web service for testing at poorsql.com.
SSMS Tools Pack is also a free add-in created by Mladen Prajdić SQL Server MVP. It has a lot of features but for me the most importants are:
  • Execution Plan Analyzer!!!!!!! (Verry important)
    SSMSToolsPack_QueryPlanAnalyzer
  • SQL Snippets (Fires on Enter – as default – or Tab key)
    SSMSToolsPack_Snippets
  • Search Results in Grid Mode
    SSMSToolsPack_SearchGridResults
  • CRUD (Create, Read, Update, Delete) stored procedure generationSSMSToolsPack_CRUDGeneration

  • And a lot of other good stuff Mosolygó arc.
I also have to mention the Format SQL feature that I think is a Fake Function. It only makes keywords uppercase or lowercase, doesn’t format anything in my code.

Summary: I recommend installing these to everybody who uses SQL Server. SSMS Tools Pack works with every version of SQL Server from SQL Server 2005 (as the install shows), and unfortunately I don’t have any information about Poor Mans T-SQL Formatter but I tried it with SQL Server 2008 and it worked fine for me.
Note: EVERYBODY has to try the Execution Plan Analyzer, it is really-really worth it.

The download links are:

Happy Coding!
Morzel

Tuesday 3 January 2012

Server Side Dynamic TreeView in Asp.Net

 

In most cases loading large TreeView in one step can be a good performance killer. The generated html is really large and our page is loading so slow at the client and the average user don’t want to see the full tree together.

Let see, what needs for loading dynamically an asp.net server control on the server side.

  • We need some data (In that case that’s will the ProductCategory table in the AdventureWorks sample database).
  • We need a method to determine if the given node has childs or not.
  • We need a TreeView Mosolygó arc.
  • First step:
    • We have to load the first level nodes of the TreeView on the first page load.
    • Handle the OnTreeNodePopulate event if the childnode has more childs on the next level.
    • Set the nodes expanded property to false. The TreeNodePopulate event will be fired when we click on a node. The default value is true and the tree is expanded.
  • Repeat the previous step on every TreeNodePopulate event.

 

The ProductCategory table contains the following records: ProductCategoriesTable
The value of ParentProductCategoryID is null at the root elements and the ProductCategoryID of the parent element at the others.

 

We need a method that queries the list of the child nodes for a given node (DAO\CategoryDAO.cs):

public static List<ProductCategory> GetCategoriesByParentID(int? parentID)
{
    using (AdventureWorksLT2008Entities ctx = new AdventureWorksLT2008Entities())
    {
        return ctx.ProductCategory.Where(cat => cat.ParentProductCategoryID == parentID || (null == parentID && cat.ParentProductCategoryID == null)).ToList();
    }
}

 

Add a TreeView for the page (Default.aspx):

<asp:TreeView runat="server" ID="tvCategories"
    OnTreeNodePopulate="tvCategoris_TreeNodePopulate"></asp:TreeView>

 

Make an AddChildNodes method for populate the child nodes of the given node (Default.aspx.cs):

protected void AddChildNodes(TreeNodeCollection nodes, TreeNode node)
{
    List<ProductCategory> categories = null;
    int parentID = -1;
    
    if (null != node && int.TryParse(node.Value, out parentID))
        categories = CategoryDAO.GetCategoriesByParentID(parentID);
    else
        categories = CategoryDAO.GetCategoriesByParentID(null);

    foreach (var cat in categories)
    {
        TreeNode newNode = new TreeNode();
        newNode.Value = cat.ProductCategoryID.ToString();
        newNode.Text = cat.Name;
        newNode.PopulateOnDemand = null == node;
        newNode.Expanded = false;

        nodes.Add(newNode);
    }
}

If the new node is leaf node, we have to set the value of the PopulateOnDemand property to false.

 

Load the root elements on the first page load (Default.aspx.cs):

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        AddChildNodes(tvCategories.Nodes, null);
}

 

Create an event handler for the TreeNodePopulate event (Default.aspx.cs):

protected void tvCategoris_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
    if (e.Node.ChildNodes.Count == 0)
    {
        AddChildNodes(e.Node.ChildNodes, e.Node);
    }
}

 

The full project is downloadable from my Skydrive public folder.

 

Happy and Bug free coding!

Morzel