Can you explain about this ?
1st sql script :
SELECT CONVERT(VARCHAR(6),GETDATE(), 112)
------
200705
(1 row(s) affected)
2nd sql script :
SELECT CONVERT(VARCHAR(4),YEAR(GETDATE()), 112) + CONVERT(VARCHAR(2),MONTH(GETDATE()), 112)
------
20075
(1 row(s) affected)
Why 2nd sql script doesn't show the same result with the 1st sql script ?
Still about using UNION, how sql server grouping this result?
SELECT '200701'
UNION
SELECT '200701'
UNION
SELECT '200701'
UNION
SELECT '200701'
UNION
SELECT '198001'
output
------
198001
200701
(2 row(s) affected)
How can SQL Server order this result?
SELECT '200702'
UNION
SELECT '200701'
UNION
SELECT '200703'
UNION
SELECT '200705'
UNION
SELECT '198001'
output
------
198001
200701
200702
200703
200705
(5 row(s) affected)
* Pre-allocate the log to appropriate size
* Minimize auto growths
* Log might NOT show 0 Percent Log Used after backup for example - the % Log Used will be ~6%
* Disk Bound Systems might experience some performance degradation at log backup
* Consider RAID 1+0 instead of RAID 1, RAID 5 generally is not recommended
* Optimize existing file – minimize VLFs
- - -
source : SQL Server Community In touch
* Inherit initial properties from model database
* Change to model will affect ALL databases created AFTER the change made.
Note : TempDb is re-Created on each system restart. Might be a good idea for data types that are needed for temp table/objects
* Define logical file names
* Define physical location
* Define auto growth setting
* Create DataBase [junkDb] on Primary
(Name = N'JunkDbData', FileName = N'Physical FileName',
Size = 10, --kb, mb, gb, tb
FileGrowth = 10%, --kb, mb, gb, tb
MaxSize = 15) --kb, mb, gb, tb
Log on
(Name=N'JunkDbLog',
FileName = N'Physical FileName',
Size = 10, --kb, mb, gb, tb
FileGrowth = 10%, --kb, mb, gb, tb
MaxSize = unlimited)
* Default for filegrowth is 10%
* Default for MAXSIZE is Unlimited
If you need an example of creating a table for application versioning, maybe this script will help :
CREATE TABLE [dbo].[dnn_Version](
[VersionId] [int] IDENTITY(1,1) NOT NULL,
[Major] [int] NOT NULL,
[Minor] [int] NOT NULL,
[Build] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_dnn_Version] PRIMARY KEY CLUSTERED
(
[VersionId] ASC
) ON [PRIMARY],
CONSTRAINT [IX_dnn_Version] UNIQUE NONCLUSTERED
(
[Major] ASC,
[Minor] ASC,
[Build] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Note : This script taken from dotnetnuke database
This is the sql script to get other SP resultset in our store procedure:
CREATE PROC PROC_X
AS
CREATE TABLE #TmpTable( ... )
INSERT INTO #TmpTable EXEC usp_MySP @1, @2
Sql script:
select
convert(varchar,
dateadd(d,
cast(right('2007265',3) as int)-1,
cast(left('2007265',4)+'0101' as datetime)
) ,
112) as the_date
If you want to delete the log file, this the command:
(database eq : SASB)
USE SASB
BACKUP LOG SASB WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE(SASB,0,TRUNCATEONLY)
GO
EXEC sp_addumpdevice 'disk', 'DumpDisk','e:\program_files\data\dumpdisk.bak'
GO
BACKUP DATABASE SASB TO DumpDisk
GO
Notification Services is an environment for developing and deploying applications that generate and send notifications. You can use Notification Services to generate and send timely, personalized messages to thousands or millions of subscribers, and can deliver the messages to a variety of devices.
Service Broker helps developers build scalable, secure database applications. This new Database Engine technology provides a message-based communication platform that enables independent application components to perform as a functioning whole. Service Broker includes infrastructure for asynchronous programming that can be used for applications within a single database or a single instance, and also for distributed applications.
