Lincoln Burrows

There are four types of file organization system in SQL Server database, you can choose one of them as per your convenience.
1. Heap file organization: when there is a huge number of data needs to load into the database at a time, then this method of file organization is best suited. Best suited for very small files as the fetching of records is faster.

2. Hash file organization: The block address is known by hash function, accessing any record is very faster. It is suitable for online transaction systems like online banking, ticket booking system etc

3. Sequential organization:. When there are large volumes of data, this method is very fast and efficient. This method is helpful when most of the records have to be accessed like calculating the grade of a student, generating the salary slips etc. This method is good in case of report generation or statistical calculations.

4. Clustered organization: This file organization is not best suited for the large number of data because the ordering of records are not based on the primary key.

You can also change the path of an existing database using transact-SQL

• First, detach database:

USE master;
GO
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db @databasename = N'DatabaseName';
GO

• Then copy the detach database file to a new location
• At last attach the database:

USE master;
EXEC sp_attach_db @databasename = N'dbName', 
@filename1 = N'',  --path do .mdf
@filename2 = N'';  --path to .ldf
GO

Some of the basic reasons for SQL Server Database to go into suspect mode are:

• SQL Server Database file or transaction log could have been corrupted
• Unexpected shutdown of SQL Server database
• Database could not be opened due to the lack of disk space

To recover SQL Server database from suspect mode

• At very first change the status of your database



• Then set the SQL Server database in emergency mode



• Check the database for any inconsistency



• Then execute the following query



• Now run the REPAIR_ALLOW_DATA_LOSS



• Change database mode to MULTI USERS



• Refresh your database server and connect to database

Note: take the backup of your database for safety

To open SQL Server Management Studio

Click on the Start menu
Point to All Programs
Point to Microsoft SQL Server 2016
Then click SQL Server Management Studio