Run trace for one database at a time.
The events and data columns used by the Index Tuning Wizard are
SQL:BatchCompleted and the
RPC:Completed events in the TSQL category, and the
EventClass and Text data columns.
Save as trace file or table. NOTE: ITW won't run on a read-only database.
Try running these examples.
PRINT 'Int to Hex' SELECT CONVERT(VARBINARY(8), 15987699) PRINT 'Hex to Int' SELECT CONVERT(INT, 0xF3F3F3)
Fill factor = 100 fills the pages completely.
Fill factor = 0 leaves space in the index non-leaf pages .
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\MSSQL\Reports\JobSchedules.xls;',
'SELECT JobName, ScheduleName, next_run_date FROM [Sheet1$]')
select j.name JobName, s.name ScheduleName,
next_run_date
from msdb..sysjobs j
join msdb..sysjobschedules s
on j.job_id = s.job_id
where j.enabled = 1
and s.enabled = 1
ORDER BY
j.name
GO
— Note:
— Requires E:\MSSQL\Reports\JobSchedules.xls to already exist with the correct columns.
This topic explains how to define the CPU idle condition for your server. The CPU idle definition influences how Microsoft SQL Server Agent responds to events. For example, suppose that you define the CPU idle condition as when the average CPU usage falls below 10 percent and remains at this level for 10 minutes. Then if you have defined jobs to execute whenever the server CPU reaches an idle condition, the job will start when the CPU usage falls below 10 percent and remains at that level for 10 minutes. If this is a job that significantly impacts the performance of your server, how you define the CPU idle condition is important.
To set CPU idle time and duration
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Right-click SQL Server Agent, click Properties, and select the Advanced page.
Under Idle CPU condition, do the following:
http://msdn.microsoft.com/en-us/library/ms189065.aspx
Here's one way, as inspired by Anatoly Lubarsky.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fn_IsLeapYear (@dtDate DATETIME)
RETURNS BIT
AS
BEGIN
DECLARE @dtFeb28 DATETIME,
@nMonthDay tinyint
--
-- Add a day to the 28th of February and see if it is the 1st or the 29th.
SELECT @dtFeb28 = CONVERT(VARCHAR(4), YEAR(@dtDate)) + '0228',
@nMonthDay = DATEPART(DAY, DATEADD(DAY, 1, @dtFeb28))
IF (@nMonthDay = 29)
RETURN 1
RETURN 0
-- Usage:
-- SELECT dbo.fn_IsLeapYear (GETDATE())
-- SELECT dbo.fn_IsLeapYear (dateadd(year,1,GETDATE()))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Use Query Analyzer to script out the table in tempdb.
--
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyEXCELSheet]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyEXCELSheet]
GO
This is the best explanation I've seen so far on this topic. Explains there is really no difference and that we should use CONSTRAINTs when data integrity is the objective.
SQL Server 2005 Books Online (September 2007)
Creating Unique Indexes
Creating a unique index guarantees that any attempt to duplicate key values fails. There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE constraint on the column when data integrity is the objective. This makes the objective of the index clear.