SQL server CPU utilization history

I find this from a blog and save for my memory

-- Get CPU Utilization History (SQL 2005 Only)
DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%') AS x
) AS y
ORDER BY record_id DESC;

first and last date of Month,Quarter,Week and year.

you can get a lot more by changing parameters a little

--- first & last day of month
select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()),0)
select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1 ,-1)

-- first & last day of week
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()),0)
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1 ,-1)

-- first & last day of year
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()),0)
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1 ,-1)

-- first & last day of Querter
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()),0)
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1 ,-1)

Returning month name from Date

One of my colleage asked about getting month name and month value for date time value.

Here is the example to do that.

For Month,Year and Day value use following datetime functions.
Select DAY(GETDAET()); -- returns day part of date
Select MONTH(GETDAET()); -- returns year part of date
Select YAR(GETDAET()); -- returns year part of date

there is another function to do this
Select DATEPART(mm,GETDAET());

use
'mm', 'm' for month
'dd' for day
'y','yyyy' for year


For Month name value use following datetime functions.
Select DATENAME(mm,GETDAET()); -- returns month part of date like July

using custome code we can achive this using case statement

Select
case month(getdate())
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
End

How to remove time postion from date in SQL server 2005

we can remove time portion in different ways.


1. select DATEADD(DD, DATEDIFF(DD, 0, GETDATE()),0)
2. select convert(datetime,cast(GETDATE() as varchar(12)) , 103)
3. select convert(datetime,cast(getdate() as int) , 103) - 1

if you know any other please mention.

SQL Join Tactics

Join conditions can be specified in either the FROM or WHERE clauses. Specifying them in the FROM clause is recommended. From Microsoft MSDN. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.

Joins can be categorized as:

1. Inner joins: (the typical join operation, which uses some comparison operator like =). Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. This includes equi-join or natural join. For example, retrieving all rows where the employee number is the same in both the employee and working-hour tables.

2. Outer joins: Outer joins can be a left, a right, or full outer join.
LEFT JOIN or LEFT OUTER JOIN The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN a right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

3. FULL JOIN or FULL OUTER JOIN A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

4. CROSS JOINS Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

For more detail please read. http://msdn.microsoft.com/en-us/library/ms191472.aspx

How to shrink database log file size

There are 2 ways to shrink db file size using SQL Server

Way one: Using SQL Server job or Maintainess plan and by setting log shrink to 10%.

but some time we face problem that our log file do not shrink even if job runs successfully. This is because this shrink operation shinks free and unused log file update 10%. if we want to shrink db file at our desired percentage then we have to use DBCC command and manually enter remaing size of log file. like follows

Second Way:
DBCC SHRINKDATABASE ('MYDATABASENAME',10);

This command shrinks database log file size to 10Mb.

Caustion: before using this please take a backup first.

you can take backup using this command

backup log 'dbname' with truncate_only

Returning required rows when there is no primary key.

One of my friend ask solution for this problem

Problem : If a table has 11 records, how can we get records from record number 5 to record number 10 and there is no primary key in the table.

Solution: see the example in SQL Server in following example

Declare @Table Table(name varchar(50),DateCreated datetime)

Insert into @Table values ('Shamas',getdate())
Insert into @Table values ('Qamar',getdate())
Insert into @Table values ('Atif',getdate())
Insert into @Table values ('Kashif',getdate())
Insert into @Table values ('Maria',getdate())
Insert into @Table values ('Usman',getdate())
Insert into @Table values ('Wasif',getdate())
Insert into @Table values ('Narimaan',getdate())
Insert into @Table values ('Hina',getdate())
Insert into @Table values ('Saqib',getdate())
Insert into @Table values ('Farhan',getdate())

Select * from
(
Select row_number() over (order by name ) as rowid,
name,DateCreated
from @Table
) t
where rowid >= 5 and rowid < = 10

Summary: This query will assing row number to each record with name as order column. Then outer query will return record number as what you want.

We can do this in many other ways in SQL Server 2000 and 2008

SQL Server 2008 Katmail new features (Declaring and initializing variables)

This is my first post for SQL Server 2008 update features. We are going to start complete list of new features according to my following link

We declare and assign value to a variable in 2 different statements before SQL Server 2008 update. Lets see example

-- declare variables
DECLARE @date DATETIME
DECLARE @i INT

-- initialize
SELECT @date = GETDATE(), @i = 10

Now with SQL server 2008 we can declare an initialize variables in a single TSQL statement. see this example:

-- sql server 2008 - Declare and initialize in a single statement
DECLARE @date DATETIME = GETDATE(), @i INT = 5;

This is a great feature in a way to easy the code and assigning it value. Another interesting feature is the support for Compound Assignment as we read in C++ and VB 6. If we have a little idea then it is a appreciable change. See following example

DECLARE @i INT
SELECT @i = 10

SELECT @i = @i + 1 -- i = 11 now
SELECT @i = @i - 2 -- i = 9 now
SELECT @i = @i * 3 -- i = 27 now

SELECT @i AS newValue

----- Here is output-----
----------
newValue
-----------
27

There is another very good enhancement in Katmai. We can insert multiple records in a single statement that we do in different insert statements in sql server 2005. See below example of sql server 2005

Declare @table Table(a int, b int)
Insert into @table values (1,2)
Insert into @table values (2,2)
Insert into @table values (3,3)
Insert into @table values (3,4)

Now how can we do that in sql server 2008 is defined in following example

DECLARE @table TABLE ( a INT, b INT )
INSERT INTO @table VALUES (1,1), (2,2), (2,3),(3,3),(3,4)

I will discuss more updates in more posts

To see complete list of update for related posts visit
http://sqlservercoollinks.blogspot.com/2009/07/new-features-in-sql-server-2008-katmai.html

Returning random result from a dataset

Problem: Many of the developers write complex and huge code to return random images or news on every page refresh on its website or project.

Solution: Sql server provides easy way to return random result on each call.

Query:

Select * from [table1] order by newid()

Result:
if you have 2 records in your table you will get different result each time.

How to avoid dynamic query using procedures

We all know string query or dynamic query utalize so much resources while execution. Major flow back of dynamic query is string conversion of all types of parameters and writing bad code through SQL injection.

Here is a solution to avoid that type of query.

Case:
There is a column "Param" and it is not required every time while query execution.
the table joins with master table as left outer join.

Problem:
When we provide parameter we can use case to handle it without writing dynamic query. But when we do not provide parameter value then only child table records return what can i do to return whole dataset.

Solution:
here is the solution

DECLARE @PARAM INT
SET @PARAM = 0

SELECT * FROM Master LEFT OUTER JOIN Child ON Master.id = Child.id
WHERE (CASE WHEN @PARAM = 0 THEN master.id ELSE child.id END )
IN (CASE WHEN @PARAM = 0 THEN master.id ELSE
CASE WHEN @PARAM > 0 THEN @PARAM ELSE child.id END
END)

when we provide paramter then only required records returns else all records returns from master table and we do not need any dynamic query.

New Features in SQL Server 2008 Katmai

Microsoft SQL Server 2008 introduces several important new Transact‑SQL programmability features and enhances some existing ones. You can find details in SQL Server Books Online.

Following are highlighted features

1. Declaring and initializing variables : http://sqlservercoollinks.blogspot.com/2009/07/1-declaring-and-initializing-variables.html
2. Compound assignment operators : http://sqlservercoollinks.blogspot.com/2009/07/1-declaring-and-initializing-variables.html
3. Table value constructor support through the VALUES clause
4. Enhancements to the CONVERT function
5. New date and time data types and functions
6. Large UDTs
7. The HIERARCHYID data type
8. Table types and table-valued parameters
9. The MERGE statement, grouping sets enhancements
10.DDL trigger enhancements
11.Sparse columns
12.Filtered indexes
13.Large CLR user-defined aggregates
14.Multi-input CLR user-defined aggregates
15.The ORDER option for CLR table-valued functions
16.Object dependencies
17.Change data capture
18.Collation alignment with Microsoft® Windows®
19.Deprecation

I have picked this list from official website of Microsoft TECHNET.I will define it in detail one by one.You can also get detail from this attached link.

Returning first row of each repeating group values

create table repeatCheck
(id int identity(1,1),
email varchar(50),
[name] varchar(50) );

insert into repeatCheck values ('shamas@isl.com','Shamas');
insert into repeatCheck values ('shamas@hotmail.com','Shamas');
insert into repeatCheck values ('maria@isl.com','Maria');
insert into repeatCheck values ('azher@isl.com','Azher');
insert into repeatCheck values ('azher@hotmail.com.com','Azher');
insert into repeatCheck values ('khawar@isl.com','Khawar');
insert into repeatCheck values ('khalil@isl.com','Khalil');
insert into repeatCheck values ('khalil@hotmail.com.com','Khalil');
insert into repeatCheck values ('maria@hotmail.com.com','Maria');

Problem: Many of my friends ask me how can i get first or second row from table on some critaria which generates group like Category in Questions table.

Solution: In SQL Server 2005 we have many advance options which can do this. I am going to give you an example for that.


select * from repeatCheck;

--- Now return first row of repeating names

select *
from
(
select rank() over(partition by [name] order by email) as rownumber,email
from repeatCheck
) v
where v.rownumber = 1


delete from repeatCheck;
drop table repeatCheck;


You can see Rank function assign rank with the changing group like in the above example row number 1 and then 2 of rank value assigns form "Maria@isl.com" and "Maria@hotmail.com" but due to same name we have two id's of rank value. For next name we have rank value again starts from 1. At the last we can easily return top 1 of each group or any top number.

We can also do this in SQL Server 2000 or older but not Using Rank..

Find repeating values from table

Today i am going to discuss a very common problem that takes too much time of developers to write code and find soution

Problem: How can i find duplicate and return values from table data
Solution: Using SQL Server following is the example to find duplicating values.


create table repeatCheck
(id int identity(1,1),
email varchar(50));

insert into repeatCheck values ('shamas@isl.com');
insert into repeatCheck values ('shamas@isl.com');
insert into repeatCheck values ('maria@isl.com');
insert into repeatCheck values ('azher@isl.com');
insert into repeatCheck values ('khawar@isl.com');
insert into repeatCheck values ('khalil@isl.com');
insert into repeatCheck values ('maria@isl.com');


select * from repeatCheck

--- Now return all repeating emails

select email,count(email) as emailcount
from repeatCheck
group by email
having count(email) > 1


As you can see how simple is to return duplicate values for different email addresses