--Compound Operators
DECLARE @NoOfIterations int
SET @NoOfIterations = 0
- - Do something
SET @NoOfIterations += 1
-------------------------------
--DECLARE Statement
DECLARE @NoOfIterations int = 0
-------------------------------
--GROUPING SETS
use AdventureWorks2008
-- Using UNION ALL
SELECT CustomerID, NULL AS Year, SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
UNION ALL
SELECT NULL AS CustomerID, YEAR(OrderDate), SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
-- Using GROUPING SETS
SELECT CustomerID, YEAR(OrderDate), (SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS ((CustomerID), (YEAR(OrderDate)))
------------------------------------
--Row Constructors
INSERT INTO Person.CountryRegion
VALUES ('UK', 'United Kingdom', SYSDATETIME()),
('GB', 'Great Britain', SYSDATETIME())
-------------------------------------
--datetime2 new data type
• Key features: larger and more accurate than datetime. Contains a new
SYSUTCDATETIME function that returns the Coordinated Universal Time
(UTC) date and time, as opposed to the system date and time.
• Range: from 0001/01/01 00:00:00:0000000 to 9999/31/12 23:59:59:9999999.
• Accuracy: 100 nanoseconds.
DECLARE @newDateTime datetime2
SET @newDateTime = SYSDATETIME()
PRINT @newDateTime
-- OUTPUT
-- 2008-02-24 20:50:46.7892464
--------------------------------------
--date new data type
• Key features: consists of the date portion of datetime2.
• Range: from 0001/01/01 to 9999/31/12.
• Accuracy: one day.
The following code example uses the date data type.
DECLARE @newDate date
SET @newDate = SYSDATETIME()
PRINT @newDate
-- OUTPUT
-- 2008-02-24
---------------------------------------
time new data type
• Key features: consists of the time portion of datetime2.
• Range: from 00:00:00:0000000 to 23:59:59:9999999.
• Precision: variable precision, defined when you declare the variable.
• Accuracy: 100 nanoseconds.
The following code example uses the time data type.
DECLARE @newTime time(3)
SET @newTime = SYSDATETIME()
PRINT @newTime
-- OUTPUT
-- 20:50:46.789
---------------------------------------
datetimeoffset new data type
• Key features: similar to datetime2, but includes a time-zone offset. Time-zone
comparisons are performed against UTC and you use the SWITCHOFFSET
function to modify the time-zone offset and preserve the actual time.
The following code example uses the datetimeoffset data type.
DECLARE @newDateTime datetimeoffset
SET @newDateTime = SYSDATETIMEOFFSET()
PRINT @newDateTime
-- OUTPUT
-- 2008-01-24 20:50:46.7892464 -08:00
----------------------------------------
The User-Defined Table Data Type
CREATE TYPE ProductionHistory AS TABLE
(TransactionID int,
-- Remaining column definitions here
)
CREATE PROCEDURE usp_ArchiveHistory
@PH ProductionHistory ReadOnly
AS -– Remaining SP definition here
DECLARE @PH AS ProductionHistory
INSERT INTO @PH –- Populate table here
EXEC usp_ArchiveHistory @PH;
GO
-------------------------------------------
The hierarchyid Data Type
CREATE TABLE OrgTable
(
OrgNode hierarchyid, EmpID int, EmpName varchar(50)
)
INSERT INTO OrgTable
VALUES (
hierarchyid::GetRoot(), 0, 'Darren')
DECLARE @NodeLevel hierarchyid
SELECT @NodeLevel = OrgNode FROM OrgTable WHERE EmpName = 'Darren'
DECLARE @Manager hierarchyid
SELECT @Manager = max(OrgNode) FROM OrgTable
WHERE OrgNode.GetAncestor(1) = @NodeLevel
INSERT INTO OrgTable
VALUES (@NodeLevel.GetDescendant(@Manager, null), 1,
'Dominic')
SELECT OrgNode.ToString(), OrgNode, EmpID, EmpName
FROM OrgTable
----------------------------------------------
FILESTREAM Data
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
• 0 disables FILESTREAM support for this instance.
• 1 enables FILESTREAM for Transact-SQL access.
• 2 enables FILESTREAM for Transact-SQL and Win32 streaming access.
CREATE TABLE ResumeData
(
ApplicantID int,
Resume varbinary(max) FILESTREAM,
ResumeID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
)
INSERT INTO ResumeData
VALUES
(
1, CAST('Resume Text' AS varbinary(max)), newid()
)
Thursday, April 16, 2009
Sunday, April 5, 2009
SQL 2008 : Evaluate and apply policies to a server group
1. Create and test a policy on one server as described in the preceding topics.
2. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click the policy that you want to use, and then click Export Policy.
3. In the Export Policy dialog box, navigate to an appropriate location for the file, and then click Save.
4. In the Registered Servers window, expand Central Management Servers, expand the Central Management Server that controls your group, right-click your group, and then click Evaluate Policies.
5. In the Evaluate Policies dialog box, next to the Source box, click the ellipsis.
6. In the Select Source dialog box, next to the Files box, click the ellipsis.
7. Navigate to the location of your exported policy file, click the file, and then click Open.
8. In the Select Source dialog box, click OK.
9. In the Evaluate Policies dialog box, click Evaluate.
10. If any servers are not compliant and you want to enforce the policy, in the Target details list, select the server check boxes for the noncompliant server, and then click Apply.
11. In the Policy Evaluation Warning message box, click Yes. 12. In the Evaluate Policies dialog box, click Close.
2. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click the policy that you want to use, and then click Export Policy.
3. In the Export Policy dialog box, navigate to an appropriate location for the file, and then click Save.
4. In the Registered Servers window, expand Central Management Servers, expand the Central Management Server that controls your group, right-click your group, and then click Evaluate Policies.
5. In the Evaluate Policies dialog box, next to the Source box, click the ellipsis.
6. In the Select Source dialog box, next to the Files box, click the ellipsis.
7. Navigate to the location of your exported policy file, click the file, and then click Open.
8. In the Select Source dialog box, click OK.
9. In the Evaluate Policies dialog box, click Evaluate.
10. If any servers are not compliant and you want to enforce the policy, in the Target details list, select the server check boxes for the noncompliant server, and then click Apply.
11. In the Policy Evaluation Warning message box, click Yes. 12. In the Evaluate Policies dialog box, click Close.
SQL 2008 : Policy-Based Management
You can use policies to help you to manage one or more instances of SQL Server. You define a policy that you want to apply to objects on an instance of SQL Server, and then you can enforce that policy on one or more instances of SQL Server. Policies can range from disabling SQL Mail, through enforcing naming conventions, through restricting file growth for large data files.
Examples of Policies
The following list describes some business scenarios that can be solved by using policies:
- Many companies like to use standard naming conventions for the objects in their databases. You can create policies that define what the naming convention should be for each particular object; for example, the name of a stored procedure should begin with usp_.
- By default, the local administrators group on a server is a member of the sysadmin SQL Server role. However, in a large enterprise, it is unlikely that system administrators are responsible for database administration, so they should really be removed from the role. This task is simple, but it is equally simple for someone else to add them back into the role at a later stage. To avoid this, you can create a policy that denies access to the BUILTIN\Administrators group, evaluate the policy against your SQL Server instances, and then force any noncompliant servers to deny access to the group. You can also configure the policy to roll back any future attempts to provide access for the group.
- A new corporate policy may define that all SQL Server instances should be using Windows authentication. To manually check the authentication mode of every SQL Server instance and then possibly change it to adhere to the new policy would be a tedious process. Instead, you can create a policy that defines that LoginMode should be set to Integrated, evaluate the policy against the servers, and then enforce the policy on any noncompliant servers.
You can use the Policy Management section of SQL Server Management Studio to create conditions and policies. You must create a condition before you create the policy that uses the condition.
Note: Users must belong to the PolicyAdministratorRole role in the msdb database to create conditions and policies.
Create a policy condition
1. In Object Explorer, expand Management, expand Policy Management, right-click Conditions, and then click New Condition.
2. In the Create New Condition dialog box, on the General page, in the Name box, type a name for the condition.
3. In the Facet box, click the facet that you want your condition to use.
4. In the Expression pane, select the attribute and value for which you want your condition to test, and then click OK.
Create a policy that belongs to a new category
1. In Object Explorer, right-click the Policies folder, and then click New Policy.
2. In the Create New Policy dialog box, on the General page, in the Name box, type a name for the policy.
3. In the Check condition box, click the condition that you previously created.
4. In the Evaluation Mode box, click an evaluation mode for the policy.
5. If you are using the On Schedule evaluation mode, configure a schedule and ensure that the SQL Server Agent service is running.
6. If you are using an automated evaluation mode, select the Enabled check box.
7. In the Create New Policy dialog box, click the Description page.
8. Next to the Category box, click New.
9. In the Create new category dialog box, type a name for the new category, and then click OK.
10. In the Create New Policy dialog box, click OK.
Set the Mandate Database Subscriptions option
1. In Object Explorer, right-click the Policy Management folder, and then click Manage Categories.
2. In the Manage Policy Categories dialog box, in the Mandate Database Subscriptions column, select the check box for the relevant category, and then click OK.
Subscribe an object to a policy category
1. In Object Explorer, right-click the object that you want to subscribe, point to Policies, and then click Categories.
2. In the Categories dialog box, expand the category to which you want to subscribe.
3. Select the Subscribed check box for the category, and then click OK.
Evaluate a policy from a policy
1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.
2. On the Evaluate Results page, examine the results of the policy evaluation.
Evaluate a policy from an object
1. In Object Explorer, right-click a target (for example, a server instance, a database, or a database object), point to Policies, and then click Evaluate.
2. In the Evaluate Policies dialog box, select one or more policies, and then click Evaluate to run the policy in evaluation mode.
3. On the Evaluate Results page, examine the results of the policy evaluation.
View evaluation history
• In Object Explorer, right-click a policy, and then click View History.
Apply a policy
1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.
2. When the policy evaluation is complete, select any noncompliant target, and then click Apply.
3. In the Policy Evaluation Warning message box, click Yes.
4. In the Evaluate Policies dialog box, click Close.
Examples of Policies
The following list describes some business scenarios that can be solved by using policies:
- Many companies like to use standard naming conventions for the objects in their databases. You can create policies that define what the naming convention should be for each particular object; for example, the name of a stored procedure should begin with usp_.
- By default, the local administrators group on a server is a member of the sysadmin SQL Server role. However, in a large enterprise, it is unlikely that system administrators are responsible for database administration, so they should really be removed from the role. This task is simple, but it is equally simple for someone else to add them back into the role at a later stage. To avoid this, you can create a policy that denies access to the BUILTIN\Administrators group, evaluate the policy against your SQL Server instances, and then force any noncompliant servers to deny access to the group. You can also configure the policy to roll back any future attempts to provide access for the group.
- A new corporate policy may define that all SQL Server instances should be using Windows authentication. To manually check the authentication mode of every SQL Server instance and then possibly change it to adhere to the new policy would be a tedious process. Instead, you can create a policy that defines that LoginMode should be set to Integrated, evaluate the policy against the servers, and then enforce the policy on any noncompliant servers.
You can use the Policy Management section of SQL Server Management Studio to create conditions and policies. You must create a condition before you create the policy that uses the condition.
Note: Users must belong to the PolicyAdministratorRole role in the msdb database to create conditions and policies.
Create a policy condition
1. In Object Explorer, expand Management, expand Policy Management, right-click Conditions, and then click New Condition.
2. In the Create New Condition dialog box, on the General page, in the Name box, type a name for the condition.
3. In the Facet box, click the facet that you want your condition to use.
4. In the Expression pane, select the attribute and value for which you want your condition to test, and then click OK.
Create a policy that belongs to a new category
1. In Object Explorer, right-click the Policies folder, and then click New Policy.
2. In the Create New Policy dialog box, on the General page, in the Name box, type a name for the policy.
3. In the Check condition box, click the condition that you previously created.
4. In the Evaluation Mode box, click an evaluation mode for the policy.
5. If you are using the On Schedule evaluation mode, configure a schedule and ensure that the SQL Server Agent service is running.
6. If you are using an automated evaluation mode, select the Enabled check box.
7. In the Create New Policy dialog box, click the Description page.
8. Next to the Category box, click New.
9. In the Create new category dialog box, type a name for the new category, and then click OK.
10. In the Create New Policy dialog box, click OK.
Set the Mandate Database Subscriptions option
1. In Object Explorer, right-click the Policy Management folder, and then click Manage Categories.
2. In the Manage Policy Categories dialog box, in the Mandate Database Subscriptions column, select the check box for the relevant category, and then click OK.
Subscribe an object to a policy category
1. In Object Explorer, right-click the object that you want to subscribe, point to Policies, and then click Categories.
2. In the Categories dialog box, expand the category to which you want to subscribe.
3. Select the Subscribed check box for the category, and then click OK.
Evaluate a policy from a policy
1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.
2. On the Evaluate Results page, examine the results of the policy evaluation.
Evaluate a policy from an object
1. In Object Explorer, right-click a target (for example, a server instance, a database, or a database object), point to Policies, and then click Evaluate.
2. In the Evaluate Policies dialog box, select one or more policies, and then click Evaluate to run the policy in evaluation mode.
3. On the Evaluate Results page, examine the results of the policy evaluation.
View evaluation history
• In Object Explorer, right-click a policy, and then click View History.
Apply a policy
1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.
2. When the policy evaluation is complete, select any noncompliant target, and then click Apply.
3. In the Policy Evaluation Warning message box, click Yes.
4. In the Evaluate Policies dialog box, click Close.
SQL Server 2008 : Use Central Management Servers
1. Open SQL Server Management Studio and connect to the MIAMI database engine instance.
2. Open the Registered Servers window and register a new Central Management Server by using the MIAMI instance of SQL Server.
3. Expand Central Management Servers, right-click MIAMI, and then create a new server group named Miami Management.
4. Add MIAMI\SQLINSTANCE2 and MIAMI\SQLINSTANCE3 to the Miami Management group.
5. Right-click Miami Management, and then click New Query.
6. Note that the status bar shows that you are connected to the two instances.
7. In the Query Editor, type the following code, and then click Execute.
SELECT * FROM sysusers
8. Note that the query returns users from both MIAMI\SQLINSTANCE2 and MIAMI\SQLINSTANCE3.
2. Open the Registered Servers window and register a new Central Management Server by using the MIAMI instance of SQL Server.
3. Expand Central Management Servers, right-click MIAMI, and then create a new server group named Miami Management.
4. Add MIAMI\SQLINSTANCE2 and MIAMI\SQLINSTANCE3 to the Miami Management group.
5. Right-click Miami Management, and then click New Query.
6. Note that the status bar shows that you are connected to the two instances.
7. In the Query Editor, type the following code, and then click Execute.
SELECT * FROM sysusers
8. Note that the query returns users from both MIAMI\SQLINSTANCE2 and MIAMI\SQLINSTANCE3.
Subscribe to:
Posts (Atom)