Get Information of Index of Tables and Indexed Columns

Creating Index in Sql

 

CREATE INDEX employee_first_last_name_idx ON
  employee (first_name, last_name);

Droping Index in Sql

Drop  index employee_first_last_name_idx;

Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure.
USE AdventureWorks;
GO
EXEC sp_helpindex 'Person.Address'
GO

Above SP will return following information.
IndexName – IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
Index_Description – nonclustered, unique located on PRIMARY
Index_Keys – AddressLine1, AddressLine2, City, StateProvinceID, PostalCode

Let me know if you think this kind of small tips are useful to you.

Either we can use DBCC Command also

 

The DBCC SHOWCONTIG command shows you the index information. The example below checks the "indexname" index on the table "tablename" in the database "dbname".

DBCC SHOWCONTIG('dbname.dbo.tablename', 'indexname')

Some example output is shown below. This particular example was from a very fragmented index from a table with around 10 million records. Each day around 1 million records are updated/and or inserted and records 60 days or older are deleted.

DBCC SHOWCONTIG scanning 'tablename' table...
Table: 'tablename' (949578421); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 333557
- Extents Scanned..............................: 41805
- Extent Switches..............................: 262556
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 15.88% [41695:262557]
- Logical Scan Fragmentation ..................: 75.11%
- Extent Scan Fragmentation ...................: 13.04%
- Avg. Bytes Free per Page.....................: 2853.9
- Avg. Page Density (full).....................: 64.74%

A low percentage for scan density is bad. A high percentage for logical scan fragmentation is bad.
To defragment your index you need to use the DBCC DBREINDEX or DBCC INDEXDEFRAG commands. DBCC DBREINDEX allows you to rebuild all indexes at once, but is an offline operation so the tables cannot be used while it is running. DBCC INDEXDEFRAG must be called for each index you want to defragment but you can continue to use the tables.
The syntax for DBCC DBREINDEX is as follows to rebuild the above index:

DBCC DBREINDEX('dbname.dbo.tablename', 'indexname')

or all indexes:

DBCC DBREINDEX('dbname.dbo.tablename')

You can also change the fill factor for the index, which affects how much space is left in each page with a third parameter. To make it e.g. 70, you could do this for all indexes in the table:

DBCC DBREINDEX('dbname.dbo.tablename', '', 70)

After doing the above on the example table and rebuilding the index with a fill factor of 70, running DBCC SHOWCONTIG again gave the following, much better, result:

DBCC SHOWCONTIG scanning 'tablename' table...
Table: 'tablename' (949578421); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 297679
- Extents Scanned..............................: 37210
- Extent Switches..............................: 37209
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [37210:37210]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.16%
- Avg. Bytes Free per Page.....................: 2335.3
- Avg. Page Density (full).....................: 71.15%

Clear Reset All Control in a form in asp.net

Using below function we can clear or reset all the controls available on the existing pages.


#region Clear All Controls

    public static void ResetFields(Control control)

    {

        //Reset All the textBoxes in panel.

        foreach (Control ctrl in control.Controls)

        {

            if (ctrl is TextBox)

            {

                string id = ctrl.ID;

                ((TextBox)ctrl).Text = String.Empty;

                ((TextBox)ctrl).Attributes["value"] = "";

            }

            else if (ctrl is DropDownList)

            {

                ((DropDownList)ctrl).ClearSelection();

                ((DropDownList)ctrl).Enabled = true;

            }

            else if (ctrl is RadioButtonList)

            {

                ((RadioButtonList)ctrl).SelectedIndex = 0;

            }

            else if (ctrl is RadioButton)

            {

                ((RadioButton)ctrl).Checked = false;

            }

            else if (ctrl is CheckBox)

            {

                ((CheckBox)ctrl).Checked = false;

            }

            else if (ctrl is CheckBoxList)

            {

                ((CheckBoxList)ctrl).ClearSelection();

            }

        }

    }

    #endregion

Trim and Replace multiple string in Javascript

Using following Javascript sysntax you can replace forward slashes (‘/’) with backward slashes (‘\’) :

var strReplace = “C:\Temp\Temp1\Temp2\text.jpeg“;

strReplace = strReplace.replace(new RegExp(/\\/g),”/”);

/////////////////

function ltrim(str){
return str.replace(/^\s+/, '');
}
function rtrim(str) {
return str.replace(/\s+$/, '');
}
function alltrim(str) {
return str.replace(/^\s+|\s+$/g, '');
}

SQL SERVER – Delete Backup History – Cleanup Backup History

SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.

USE msdb
GODECLARE @DaysToKeepHistory DATETIMESET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 101)EXEC sp_delete_backuphistory @DaysToKeepHistoryGO

Get All Database with respective sizes from your Server

with fs

as

(

    select database_id, type, size * 8.0 / 1024 size

    from sys.master_files

)

select

    name,

    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,

    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB

from sys.databases db

OR

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SQL Server BACKUP LOG command

Overview
There are only two commands for backup, the primary is BACKUP DATABASE which backs up the entire database and BACKUP LOG which backs up the transaction log.  The following will show different options for doing transaction log backups.

Explanation
 The BACKUP LOG command gives you many options for creating transaction log backups.  Following are different examples.

Create a simple transaction log backup to disk
The command is BACKUP LOG databaseName.  The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified.  The file extension is "TRN".  This helps me know it is a transaction log backup, but it could be any extension you like.  Also, the database has to be in the FULL or Bulk-Logged recovery model and at least one Full backup has to have occurred.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' GO

Create a log backup with a password
This command creates a log backup with a password that will need to be supplied when restoring the database.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' WITH PASSWORD = 'Q!W@E#R$' GO

Create a log backup with progress stats
This command creates a log backup and also displays the progress of the backup.  The default is to show progress after every 10%.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' WITH STATS GO

Here is another option showing stats after every 1%.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' WITH STATS = 1 GO

Create a backup and give it a description
This command uses the description option to give the backup a name.  This can later be used with some of the restore commands to see what is contained with the backup.  The maximum size is 255 characters.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' WITH DESCRIPTION = 'Log backup for AdventureWorks' GO

Create a mirrored backup
This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' MIRROR TO DISK = 'D:\AdventureWorks_mirror.TRN' WITH FORMAT GO

Specifying multiple options
 This example shows how you can use multiple options at the same time.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' MIRROR TO DISK = 'D:\AdventureWorks_mirror.TRN' WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$' GO

---

SQL Server BACKUP DATABASE command

OverviewThere are only two commands for backup, the primary is BACKUP DATABASE.  This allows you to do a complete backup of your database as well as differential, file, etc. backups depending on the options that you use.

ExplanationThe BACKUP DATABASE command gives you many options for creating backups.  Following are different examples.

Create a full backup to diskThe command is BACKUP DATABASE databaseName.  The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' GO

Create a differential backup
This command adds the "WITH DIFFERENTIAL" option.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH DIFFERENTIAL GO

Create a file level backup
This command uses the "WITH FILE" option to specify a file backup.  You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILE = 'TestBackup' TO DISK = 'C:\TestBackup_TestBackup.FIL' GO

Create a filegroup backup
This command uses the "WITH FILEGROUP" option to specify a filegroup backup.  You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' TO DISK = 'C:\TestBackup_ReadOnly.FLG' GO

Create a full backup to multiple disk files
This command uses the "DISK" option multiple times to write the backup to three equally sized smaller files instead of one large file.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks_1.BAK', DISK = 'D:\AdventureWorks_2.BAK', DISK = 'E:\AdventureWorks_3.BAK' GO

Create a full backup with a password
This command creates a backup with a password that will need to be supplied when restoring the database.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH PASSWORD = 'Q!W@E#R$' GO

Create a full backup with progress stats
This command creates a full backup and also displays the progress of the backup.  The default is to show progress after every 10%.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH STATS GO

Here is another option showing stats after every 1%.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH STATS = 1 GO

Create a backup and give it a description
This command uses the description option to give the backup a name.  This can later be used with some of the restore commands to see what is contained with the backup.  The maximum size is 255 characters.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH DESCRIPTION = 'Full backup for AdventureWorks' GO

Create a mirrored backup
This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK' WITH FORMAT GO

Specifying multiple options
This next example shows how you can use multiple options at the same time.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK' WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$' GO

---