RSS

Monthly Archives: May 2009

Using Policy management in SQL Server 2008 to manage table name

 

introduction

Policy-Based Management facet

A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types. Some properties of a facet can only apply to a specific version. For example, the Database Mail property of the Surface Area Configuration facet only applies to SQL Server 2005 and later versions.

Policy-Based Management condition

A Boolean expression that specifies a set of allowed states of a Policy-Based Management managed target with regard to a management facet.

Policy-Based Management policy

A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled.

Example

1) Create New Condition

image

image

2)Create New Policy

image

image

3)Enable Policy

image

Now try to create any table in any database if you start table by tbl creation will work otherwise creation will through exception

 
 

Tags: , ,

Virtual Labs

you can now test SQL Server 2008 new features through virtual labs on line in Business intelligence, Data warehousing, OLTP and Application Deployments

believe me really useful

http://www.microsoft.com/sqlserver/2008/en/us/virtual-labs.aspx

 

Tags: , , , ,

Useful Tools for Assessment and Migration tools for SQL Server 2008

Introduction

You can download useful tools from this Microsoft System Center to help you in migration from Oracle, Sybase, Access and also for IT Assessments to help you to plan to set your production environments

just visit bellow URL

http://www.microsoft.com/sqlserver/2008/en/us/assessment-tools.aspx

 
Leave a comment

Posted by on May 28, 2009 in SQLServer, SQLServer Tools

 

Tags: ,

Convert Result Set to pivot table

Introduction

Sometime you need to change the table view to see it as MATRIX or by other word pivot table let’s start to discuss this example by see bellow table

Id

FullName

QoutaAmount

Year

arter

1

Ahmed

50000

2008

1

2

Ahmed

70000

2008

2

3

Ahmed

90000

2008

3

4

Ahmed

10000

2008

4

5

Ayman

50000

2008

1

6

Ayman

70000

2008

2

7

Ayman

90000

2008

3

8

Ayman

10000

2008

4

9

Mohamed

50000

2008

1

10

Mohamed

70000

2008

2

the above table is regular table you need to change its view to other view like bellow

FullName

2008

2009

Ahmed

220000

220000

Ayman

220000

220000

so how we can made this by SQL Server 2005 and SQL Server 2008

you must first ask yourself 4 Questions

  1. What is the pivot Column which will convert it to Column Header? Ans : Year
  2. What is the non-pivot Column? Ans :FullName
  3. What is the Aggregate Column which will be cross value between pivot column and Non-pivot column?                 Ans : QoutaAmount
  4. What is the aggregation Type? Ans : Sum

Syntax

Select <Non-Pivot Column>,[Pivot Column Value1],[Pivot Column Value2]…

From (Select [Non-Pivot Column],[Pivot Column], [Aggregate Value] From SourceTable) as tb

Pivot( AggregateFunc(AggrgateValue) for [Pivot Column] in ([Pivot Column Value1],[Pivot Column Value2]..))as pvt

Example

 

USE [AdventureWorks]

GO

 

/****** Object:  Table [dbo].[Qouta]    Script Date: 05/27/2009 20:03:55 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[Qouta](

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [FullName] [nvarchar](50) NULL,

      [QoutaAmount] [float] NULL,

      [Year] [int] NULL,

      [Quarter] [nvarchar](50) NULL

)

 

GO

SET IDENTITY_INSERT [Qouta] ON

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (1, N’Ahmed’, 50000, 2008, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (2, N’Ahmed’, 70000, 2008, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (3, N’Ahmed’, 90000, 2008, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (4, N’Ahmed’, 10000, 2008, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (5, N’Ayman’, 50000, 2008, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (6, N’Ayman’, 70000, 2008, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (7, N’Ayman’, 90000, 2008, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (8, N’Ayman’, 10000, 2008, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (9, N’Mohamed’, 50000, 2008, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (10, N’Mohamed’, 70000, 2008, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (11, N’Mohamed’, 90000, 2008, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (12, N’Mohamed’, 10000, 2008, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (13, N’Ahmed’, 50000, 2009, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (14, N’Ahmed’, 70000, 2009, N’2′)

INSERT [Qouta] ([Id],
[FullName], [QoutaAmount], [Year], [Quarter]) VALUES (15, N’Ahmed’, 90000, 2009, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (16, N’Ahmed’, 10000, 2009, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (17, N’Ayman’, 50000, 2009, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (18, N’Ayman’, 70000, 2009, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (19, N’Ayman’, 90000, 2009, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (20, N’Ayman’, 10000, 2009, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (21, N’Mohamed’, 50000, 2009, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (22, N’Mohamed’, 70000, 2009, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (23, N’Mohamed’, 90000, 2009, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (24, N’Mohamed’, 10000, 2009, N’4′)

SET IDENTITY_INSERT [Qouta] OFF

 

 

go

 

 

 

Select FullName,[2008],[2009]

From

(Select Fullname,QoutaAmount,[YEAR] from Qouta)tb

pivot(sum(QoutaAmount) for[YEAR] in ([2008],[2009]) )pvt

 

 

 
Leave a comment

Posted by on May 27, 2009 in SQLServer, T-SQL, Warehouse

 

Tags: , ,

 
%d bloggers like this: