Tag: SQL Server

Backup SQL Server Database

1. In SQL Server Management Studio, connect to the server as sa

2. Expand Databases

Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio

3. Right-click the database, point to Tasks, and then click Back Up…

Back Up Database
Back Up Database

4. In Back Up Database dialog, verify the database name

5. In the Backup type dropdown, select Full

6. Specify the backup destination

7. Click OK to start backup

Backup Completed
Backup Completed

References

Advertisements

Lesson Learned: GUID as a Primary Key

In RDBMS, it’s very common to use an int value as a Primary Key (PK). In SQL Server, we have Identity column. In Oracle, we have Sequence.

I’ve never thought of using GUID as a PK. Why we need to spend extra 12 bytes for a PK? (int is 4 bytes vs. GUID is 16 bytes)

In my recent project, the system consists of 2 kind of databases. 1) Server-side RDBMS (SQL Server), 2) Mobile-side RDBMS (Android SQLite). Initially, we designed to use int datatype as a PK for tables which need to be synchronized between the 2 database. However, this approach cannot uniquely identify the record across the 2 databases at all time.

We researched and found a solution to change the PK to GUID. GUID = Globally Unique Identifier. The GUID is so random that the probability of the same number being generated randomly twice is negligible.

This solution saved us 1) Extra roundtrip to query the newly generated Identity value from server RDBMS, 2) Extra roundtrip to synchronize the Identity value from server to the mobile.

How to: Using GUID in Database

Database Data Type Generation
SQL Server UNIQUEIDENTIFIER NEWID()
Oracle RAW(16) SYS_GUID()

References:

 

Schedule DBCC CHECKDB in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_DBCC_CHECKDB”

3. Click Steps, then click New

4. Input Step Name, “DBCC CHECKDB”

5. Paste the following Command

DECLARE @pDbName varchar(1000)

DECLARE c CURSOR FOR SELECT name FROM [sys].[databases] WHERE name <> 'tempdb'

OPEN c

FETCH NEXT FROM c INTO @pDbName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('DBCC CHECKDB(' + @pDbName + ');')

    FETCH NEXT FROM c INTO @pDbName
END

CLOSE c

DEALLOCATE c

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Weekly Sunday 00:00”

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Reorganize / Rebuild Indexes in SQL Server

1. Check the fragmentation of an index

SELECT O.name, I.name, S.avg_fragmentation_in_percent
FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
    [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
    [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
WHERE S.avg_fragmentation_in_percent > 5 
ORDER BY S.avg_fragmentation_in_percent DESC;
avg_fragmentation_in_percent value Corrective statement
> 5% and <= 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD

2. Reorganize Index & Update Statistics

Reorganize index will not automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME} REORGANIZE;
UPDATE STATISTICS {OBJECT_NAME} {INDEX_NAME};

3. Rebuild Index

Rebuild index will automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME};

4. Everything in one shot

DECLARE @pSqlText varchar(1000)

DECLARE c CURSOR FOR SELECT SqlGenerator.SqlText FROM 
    (SELECT CASE WHEN S.avg_fragmentation_in_percent > 5 AND S.avg_fragmentation_in_percent <= 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REORGANIZE; UPDATE STATISTICS ' + O.name + ' ' + I.name + ';'
        WHEN S.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REBUILD;' ELSE '' END AS SqlText
    FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
        [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
        [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
    WHERE I.name IS NOT NULL) SqlGenerator
    WHERE SqlGenerator.SqlText <> ''

OPEN c

FETCH NEXT FROM c INTO @pSqlText

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@pSqlText)

    FETCH NEXT FROM c INTO @pSqlText
END

CLOSE c

DEALLOCATE c

References

How to: Query All Tables’ Row Count, Data Space & Index Space

Query all tables’ row count, data space & index space in SQL Server

SET NOCOUNT ON;

DBCC UPDATEUSAGE(0);

CREATE TABLE #t
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
);

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''';

SELECT * FROM #t
ORDER BY [name];
 
DROP TABLE #t ;

SQL Server Database Project

  1. On the File menu, point to New, and then click Project to add SQL Server 2008 Database Project to the existing solution.
  2. On the Data menu, point to Schema Compare, and then click New Schema Comparison.
  3. In the Source Schema panel, select Database, and then click New Connection to connect to the data source.
  4. In the Target Schema panel, select Project, and then select the Database Project.
  5. Click OK to start Schema Comparison.
  6. After the comparison finishes, a table in the Schema Compare window lists the database objects that were compared.
  7. To update the schema to the target Database Project, click Write Updates button in the Schema Compare toolbar.
  8. You can save the created Schema Comparison for later use.