Category: SQL Server

slowest performing query

In SQL 2005 You CAN USE Management views the running Queries to find SLOW A good script i found a while ago on the WILL help get you started; it lists DATA, with the slowest Performing first. SELECT creation_time , last_execution_time , total_physical_reads , total_logical_reads , total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time […]

Create roles, users, permissions

Create roles, users, permissions / * – Example example has all the permissions of the table jobs created in the pubs database the SELECT privilege role r_test titles table then create a Login l_test to, and then the database pubs Login l_test create a user account u_test the same time The user account u_test Add […]

Restore the data to the specified point-in-time processing example

Restore the data to the specified point-in-time processing example – Create a test database CREATE DATABASE Db GO – Backup of the database BACKUP DATABASE Db TO DISK = ‘ c :/ db.bak ‘ WITH FORMAT GO – Create a test table CREATE TABLE Db.dbo.TB_test (ID int ) – Delay of 1 second before the […]

SQL database recovery model (log)

————————————————– ——————– SQL database recovery model ————————————————– ——————– 1) the full recovery model —————– (1) backup database data files and log files to be backed up (2) restore to restore the database using the database backup copy of the data file and log information. (3) can restore all the data, and the database can be […]

SQL Server role database role

Server role All server roles are “fixed” role, from the outset there – since installing SQL Server from the moment you will have all the server role has existed. Role Feature sysadmin This role can perform any operation on the SQL Server. Essentially, any person with this role memberships are on that server SA. The creation of […]

SQLServer server roles and permissions to database roles Detailed

When several users need to perform a similar action in a particular database (there is no corresponding Windows user groups) to the database, you can add a role (role). Database role specifies a set of database users can access the same database object. Database roles can be divided into the following categories: Windows user group or […]

Table structure information query the index and primary key information

–sql server 2005 — 1. — ======================================================================== — —  2005.08() — ======================================================================== SELECT     TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N” END,     TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N”),     CColumn_id=C.column_id,     CColumnName=C.name,     PrimaryKey=ISNULL(IDX.PrimaryKey,N”),     [IDENTITY]=CASE WHEN C.is_identity=1 THEN N’?’ELSE N” END,     Computed=CASE WHEN C.is_computed=1 THEN N’?’ELSE N” END,     TType=T.name,     Length=C.max_length,     Precision=C.precision,     Scale=C.scale,     NullAble=CASE WHEN C.is_nullable=1 THEN N’?’ELSE N” END,     [Default]=ISNULL(D.definition,N”),     ColumnDesc=ISNULL(PFD.[value],N”),     IndexName=ISNULL(IDX.IndexName,N”),     IndexSort=ISNULL(IDX.Sort,N”),     Create_Date=O.Create_Date,     Modify_Date=O.Modify_date FROM sys.columns C     INNER JOIN sys.objects O         ON C.[object_id]=O.[object_id]             AND O.type=‘U’             AND O.is_ms_shipped=0     INNER JOIN sys.types T         ON C.user_type_id=T.user_type_id     LEFT JOIN sys.default_constraints D         ON C.[object_id]=D.parent_object_id             AND C.column_id=D.parent_column_id             AND C.default_object_id=D.[object_id]     LEFT JOIN sys.extended_properties PFD         ON PFD.class=1             AND C.[object_id]=PFD.major_id             AND C.column_id=PFD.minor_id —             AND PFD.name=‘Caption’  — (name)     LEFT JOIN sys.extended_properties PTB         ON PTB.class=1             AND PTB.minor_id=0             AND C.[object_id]=PTB.major_id —             AND PFD.name=‘Caption’  — (name)     LEFT JOIN                       —     (         SELECT             IDXC.[object_id],             IDXC.column_id,             Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,’IsDescending’)                 WHEN 1 THEN ‘DESC’ WHEN 0 THEN ‘ASC’ ELSE ” END,             PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N’?’ELSE N” END,             IndexName=IDX.Name […]

Shrink SQL Server 2008 Database + Log File Script

Use DatabaseName – DB shrink – Get the database free space, decide whether or not to shrinkDB exec [dbname]. dbo.sp_spaceused The DBCC SHRINKDB is (DBNAME) – Log file shrink Use DatabaseName GO Alter Database DatabaseName Set Recovery Simple GO Alter Database DatabaseName Set Recovery Full GO DBCC SHRINKFILE (‘LogFileName’, 1) GO DBCC SHRINKFILE (‘LogFileName’, 1) […]

MS SQL entry basis: Index view

We have already mentioned in front of the view the view: view the result set returned with the ranks of the structure of the table has the same tabular form, and we can in a SQL statement like reference table that references the view, so we often The view is called the virtual table. The standard […]

Clean up the SQL Server 2008 database log

The first step, before shrinking to check the size of the log: SELECT * FROM sysfiles WHERE name LIKE ‘% LOG%’ GO The second step, the database recovery mode is set to “simple”: ALTER DATABASE database name SET RECOVERY Simple GO The third step is to run the checkpoint command, the dirty page into the database: CHECKPOINT GO The fourth step is to […]