The real multi-lingual site

We can find hungarian, english and german languages in one form.

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?

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.

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

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…

!!!FAIL!!!

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

!!!FAIL!!!

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