Random Number Generation

Objective: Random number generation from 1 to any number using ROWID


//SQL SERVER 2005 - MAKE SURE THE COLUMN NAME MUST BE UNIQUE

SELECT ROW_NUMBER() OVER(ORDER BY DATABASENAME) AS SERIALNUMBER,databasename from DBMONITOR.dbo.T_SHRINKLOGHISTORY

GO

IN SQL SERVER 2000

WE CAN USE THE FOLLOWING METHOD:

DROP TABLE #A
SELECT IDENTITY(INT, 0,1) AS Rank ,databasename INTO
#A from DBMONITOR.dbo.T_SHRINKLOGHISTORY
SELECT * FROM #A

//SQL SERVER 2005

Declare @table table(id int identity(1,1),databasename varchar(20))
insert into @table
select databasename from T_SHRINKLOGHISTORY
select * from @table


Also refer http://www.sqlteam.com/article/returning-a-row-number-in-a-query

Filegroups and Files

Objective:
To add, remove filegroups and files - To find objects in filegroup
Solution:
//**Listing 1: T-SQL to Display objects and filegroups not on Primary OR Any other Filegroup**//

select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
and o.type in ('S ','U ') --system or user table
and i.groupid = s.groupid
AND s.groupname <> 'PRIMARY'
go

select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
and o.type in ('S ','U ') --system or user table
and i.groupid = s.groupid
AND s.groupname = 'PRIMARY'
GO
select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
--and o.type in ('S ','U ') --system or user table
--and i.groupid = s.groupid
AND s.groupname = 'PRIMARY'
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = 2 --* New FileGroup*
GO
CREATE UNIQUE CLUSTERED
INDEX PK_TblWithClustIDXOnly ON dbo.TblWithClustIDXOnly (NonClustCol)
WITH
DROP_EXISTING
ON PRIMARY
GO
USE DBMONITOR
GO
EXEC sp_helpfilegroup
GO
EXEC sp_helpfilegroup @filegroupname= 'PRIMARY'
GO
USE DBMONITOR
GO
EXEC sp_helpfile
GO
EXEC sp_helpfile @filename= 'dbmonitor'
GO
//**ADDING A FILE **//
ALTER DATABASE Test1
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) on filegroupname
GO

//***Remove a file from a database
This example removes one of the files added to the Test1 database.
***//
USE master
GO
ALTER DATABASE Test1 REMOVE FILE test1dat4
GO
//***Modify a file
This example increases the size of one of the files added to the Test1 database.
**//
USE master
GO
ALTER DATABASE Test1 MODIFY FILE (NAME = TEST1DATA3,SIZE = 20MB)
GO
ALTER DATABASE MSDB MODIFY FILE ( NAME = N'MSDBData', FILEGROWTH = 5%)
go
ALTER DATABASE [Logging] MODIFY FILE ( NAME = N'Logging', FILEGROWTH = 9%)

REMOVE FILEGROUPS

ALTER DATABASE MyDBName
REMOVE FILEGROUP MyFileGroupName


Actually sp_help calls for this resultset a nondocumented stored procedure:

sp_objectfilegroup OBJECT_ID('')

In order to display the table names and the filegroups in which they are allocated, you can execute:

select o.name, s.groupname
from sysobjects o
join sysindexes i on o.id = i.id
join sysfilegroups s on i.groupid = s.groupid
where o.type in ('U', 'S')
and i.indid < 2

Refer: http://www.mssqltips.com/tip.asp?tip=1112

Recovery for SQL Server repairs corrupted SQL Server databases, backups and logs (.mdf, .ndf, .bak, .ldf)

Objective:
To recover the SQL Server Database if any damage or corrupt to the files
Solution from Different Vendor:
http://www.recoverytoolbox.com/sql.html
http://www.officerecovery.com/mssql/index.htm