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

2)Create New Policy

3)Enable Policy

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

 

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

 

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

 

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