Query Execution

Apa yang sebenarnya membuat SQL Server berbeda dari bahasa pemrograman lain adalah cara SQL Server memproses kodenya. Umumnya, kebanyakan bahasa pemrograman memroses statement dari atas ke bawah. Sebaliknya, SQL Server memroses dalam urutan yang unik yang dikenal sebagai Logical Tahap Pengolahan Query. Fase ini menghasilkan serangkaian tabel-tabel virtual dengan masing-masing tabel virtual ke tahap berikutnya (tabel virtual tidak dapat dilihat). Fase dan perintah yang diberikan adalah sebagai berikut:
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. ORDER BY
6. HAVING
7. CUBE | ROLLUP
8. ON

9. DISTINCT
10. OUTER
11. TOP
SQL Query Analyzer adalah antarmuka utama untuk menjalankan query SQL terhadap database Anda. Anda dapat menggunakan SQL Query Analyzer untuk membuat dan menjalankan script adhoc, atau anda dapat membuat skrip SQL dan menyimpannya untuk digunakan. Pada dasarnya kalau kita bekerja dengan Microsoft SQL Server kita harus masuk atau joint ke Server, baik kalau kita bekerja dengan SQL Enterprise Manager maupun dengan SQL Query Analyzer. Hanya kalau kita masuk melalui SQL Enterprise Manager kita bisa langsung menggunakan fasilitas yang ada seperti membuka database, membuat database baru, membuat dan membuka tabel, dan lain-lain. Tetapi apabila Anda bekerja di lingkungan Query Analyzer Anda harus menggunakan Transact-SQL baik untuk membuat database, tabel, maupun yang lainnya.

Cara Restore Database

 

Klik kanan di database klik Tasks, lalu klik Restore Database.

 

 

Lalu setelah kotak dialog Restore Database muncul, tentukan tujuan database yang akan di restore lalu pilih From device untuk menentukan lokasi file yang mau di restore.

 

 

Lalu akan muncul kotak dialog Specify Backup.

 

 

Setelah itu pilihlah file yang akan di restore, kemudian klik OK.

 

 

Introduction To Sql Server

Microsoft SQL Server adalah Relational Database Management System (RDBMS) yang dirancang untuk dapat berjalan pada platform mulai dari laptop hingga server multiprosesor besar. SQL Server biasanya digunakan sebagai sistem backend untuk website dan CRMS perusahaan dan dapat membantu ribuan pengguna secara bersamaan. SQL Server hadir dengan sejumlah alat untuk membantu administrasi database dan tugas-tugas pemrograman. SQL Server jauh lebih kuat dan terukur daripada sistem manajemen database desktop seperti Microsoft Access. Penggunaan Access sebagai database pada website umumnya menghasilkan error yang lebih banyak dibanding SQL Server. Meskipun SQL Server juga dapat dijalankan sebagai sebuah sistem database desktop, SQL Server paling sering digunakan sebagai sistem database server. Server Database System (Server berbasis sistem database) Server berbasis sistem database merupakan database yang dirancang untuk berjalan pada server pusat, sehingga beberapa pengguna dapat mengakses data yang sama secara bersamaan. Para pengguna biasanya mengakses database melalui aplikasi.
Sebagai contoh, sebuah website dapat menyimpan semua konten di database. Setiap kali sebuah artikel dibaca pengunjung, mereka mengambil data dari database. Seperti yang Anda ketahui, situs web biasanya tidak terbatas hanya pada satu pengguna. Jadi, pada suatu saat tertentu, sebuah website bisa melayani sampai ratusan, atau bahkan ribuan artikel untuk pengunjung website. Pada saat yang sama, pengguna lain dapat memperbarui profil pribadi mereka pada menu anggota, atau berlangganan newsletter, atau apapun yang pengguna situs web lakukan.

Cara Detach Database

 

Klik kanan di database yang akan di detach, setelah itu klik Task, lalu klik Detach.

 

 

 

Lalu klik OK.

 

 

 

Tips7 Tags: Detach Database, cara Detach Database

add log shipping plan database

sp_add_log_shipping_plan_database

Adds a new database to an existing log shipping plan.

Syntax

sp_add_log_shipping_plan_database
    { [ @plan_id = ] plan_id | [ @plan_name = ] 'plan_name' }
    { , [ @source_database = ] 'source_database' }
    { , [ @destination_database = ] 'destination_database' }
    [ , [ @load_delay = ] load_delay ]
    [ , [ @load_all = ] load_all ]
    [ , [ @copy_enabled = ] copy_enabled ]
    [ , [ @load_enabled = ] load_enabled ]

Arguments

[@plan_id =] plan_id

Is the plan identification number to which the database will be added. plan_id is uniqueidentifier, with a default of NULL.

[@plan_name =] 'plan_name'

Is the name of the plan to which the database will be added. plan_name is sysname, with a default of NULL.

Note  Either the plan_id or the plan_name must be specified. Both cannot be specified at the same time.

[@source_database =] 'source_database'

Is the name of the database on the source server. source_database is sysname, with no default.

[@destination_database =] 'destination_database'

Is the name of the destination database. destination_database is sysname, with no default. The destination database must be unique in the log_shipping_plan_database table.

[@load_delay =] load_delay

Is the length of time in minutes to wait before loading the transaction log. load_delay is int, with a default of zero (0).

[@load_all =] load_all

Specifies that all newly copied transaction logs should be loaded when the job is run. If the value is set to zero (0), only one transaction log will be loaded when the job is run. If the value is one (1), all copied transaction logs will be loaded. load_all is bit, with a default of one (1).

[@copy_enabled =] copy_enabled

Specifies whether a copy for this database will be executed. copy_enabled is bit. The value of one (1) means a copy should be performed; zero (0) means no copy is made.

[@load_enabled =] load_enabled

Specifies whether a load of the transaction logs for this database should be performed. load_enabled is bit. The value of one (1) means a load should be performed; zero (0) means no load is performed.

Return Code Values

0 (success) or 1 failure

Permissions

Only members of the sysadmin fixed server role can execute sp_add_log_shipping_plan_database.

Examples

Note this example assumes that the 'Pubs database backup' plan already exists.

EXECUTE   msdb.dbo.sp_add_log_shipping_plan_database
   @plan_name = N'Pubs database backup',
   @source_database = N'Pubs',
   @destination_database = N'pubs_standby',
   @load_delay = 60  –– wait an hour before loading the transaction logs

sp_add_category

Adds the specified category of jobs, alerts, or operators to the server.

Syntax

sp_add_category [ [ @class = ] 'class', ]
    [ [ @type = ] 'type', ]
    { [ @name = ] 'name' }

Arguments

[ @class = ] 'class'

Is the class of the category to be added. class is varchar(8) with a default value of JOB, and can be one of these values.

Value	Description
JOB	Adds a job category.
ALERT	Adds an alert category.
OPERATOR	Adds an operator category.

[ @type = ] 'type'

Is the type of category to be added. type is varchar(12), with a default value of LOCAL, and can be one of these values.

Value	Description
LOCAL	A local job category.
MULTI -SERVER	A multiserver job category.
NONE	A category for a class other than JOB.

[ @name = ] 'name'

Is the name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_add_category must be executed in the msdb database.

Permissions

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_add_category.

Examples

This example creates a local job category named AdminJobs.

USE msdb
EXEC sp_add_category 'JOB', 'LOCAL', 'AdminJobs'

addumpdevice

sp_addumpdevice

Adds a backup device to Microsoft® SQL Server™.

Syntax

sp_addumpdevice [ @devtype = ] 'device_type' ,
    [ @logicalname = ] 'logical_name' ,
    [ @physicalname = ] 'physical_name'
    [ , { [ @cntrltype = ] controller_type
            | [ @devstatus = ] 'device_status'
        }
    ]

Arguments

[@devtype =] 'device_type',

Is the type of backup device. device_type is varchar(20), with no default, and can be one of these values.

Value	Description
disk	Hard disk file as a backup device.
pipe	Named pipe.
tape	Any tape devices supported by Microsoft Windows NT®. If device is tape, noskip is the default.

[@logicalname =] 'logical_name'

Is the logical name of the backup device used in the BACKUP and RESTORE statements. logical_name is sysname, with no default, and cannot be NULL.

[@physicalname =] 'physical_name'

Is the physical name of the backup device. Physical names must follow the rules for operating-system file names or universal naming conventions for network devices, and must include a full path. physical_name is nvarchar(260), with no default value, and cannot be NULL.

When creating a backup device on a remote network location, be sure that the name under which SQL Server was started has appropriate write capabilities on the remote computer.

If you are adding a tape device, this parameter must be the physical name assigned to the local tape device by Windows NT®, for example, \\.\TAPE0 for the first tape device on the computer. The tape device must be attached to the server computer; it cannot be used remotely. Enclose names containing nonalphanumeric characters in quotation marks.

[@cntrltype =] controller_type

Is not required when creating backup devices. It is acceptable to supply this parameter for scripts, but SQL Server ignores it. controller_type is smallint, with a default of NULL, and can be one of these values.

Value	Description
2	Use when device_type is disk.
5	Use when device_type is tape.
6	Use when device_type is pipe.

[@devstatus =] 'device_status'

Is whether ANSI tape labels are read (noskip) or ignored (skip). device_status is varchar(40), with a default value of noskip.

Note  Either specify controller_type or device_status, but not both.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_addumpdevice adds a backup device to the master.dbo.sysdevices table. It can then be referred to logically in BACKUP and RESTORE statements.
Ownership and permissions problems can interfere with the use of disk or file backup devices. Make sure that appropriate file permissions are given to the account under which SQL Server was started.
SQL Server supports tape backups to tape devices that are supported by Windows NT. For more information about Windows NT-supported tape devices, see the hardware compatibility list for Windows NT. To view the tape devices available on the computer, use SQL Server Enterprise Manager.
Use only the recommended tapes for the specific tape drive (as suggested by the drive manufacturer). If you are using DAT drives, use computer-grade DAT tapes (Digital Data Storage-DDS).
sp_addumpdevice cannot be executed inside a transaction.

Permissions

Only members of the sysadmin and diskadmin fixed server roles can execute this procedure.

Examples

A. Add a disk dump device

This example adds a disk backup device named MYDISKDUMP, with the physical name C:\Dump\Dump1.bak.

USE master
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak'

B. Add a network disk backup device

This example shows a remote disk backup device. The name under which SQL Server was started must have permissions to that remote file.

USE master
EXEC sp_addumpdevice 'disk', 'networkdevice',
   '\\servername\sharename\path\filename.ext'

C. Add a tape backup device

This example adds the TAPEDUMP1 device with the physical name \\.\Tape0.

USE master
EXEC sp_addumpdevice 'tape', 'tapedump1',
   '\\.\tape0'

DBCC USEROPTIONS

Returns the SET options active (set) for the current connection.

Syntax

DBCC USEROPTIONS

Result Sets

DBCC USEROPTIONS returns this result set (values and entries may vary):

Set Option                   Value                                       
---------------------------- ------------------------------------------- 
textsize                     64512
language                     us_english
dateformat                   mdy
datefirst                    7
ansi_null_dflt_on            SET
ansi_warnings                SET
ansi_padding                 SET
ansi_nulls                   SET
concat_null_yields_null      SET

(9 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC USEROPTIONS returns a column for the name of the SET option and a column for the setting of the option.

Permissions

DBCC USEROPTIONS permissions default to any user.

Examples

This example returns the active SET options for the current connection.

DBCC USEROPTIONS

ACOS

Returns the angle, in radians, whose cosine is the given float expression; also called arccosine.

Syntax

ACOS ( float_expression )

Arguments

float_expression

Is an expression of the type float or real, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

Return Types

float

Examples

This example returns the ACOS of the given angle.

SET NOCOUNT OFF
DECLARE @angle float
SET @angle = -1
SELECT 'The ACOS of the angle is: ' + CONVERT(varchar, ACOS(@angle))

Here is the result set:

--------------------------------- 
The ACOS of the angle is: 3.14159                        

(1 row(s) affected)

This example sets @angle to a value outside the valid range.

SET NOCOUNT OFF
DECLARE @angle float
SET @angle = 1.01
SELECT 'The ACOS of the angle is: ' + CONVERT(varchar, ACOS(@angle))

Here is the result set:

-------------------------------------------------------- 
NULL                        

(1 row(s) affected)

A domain error occurred.

ABS

Returns the absolute, positive value of the given numeric expression.

Syntax

ABS ( numeric_expression )

Arguments

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as numeric_expression.

Examples

This example shows the effect of the ABS function on three different numbers.

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)

Here is the result set:

---- ---- ----
1.0  .0   1.0

The ABS function can produce an overflow error, for example:

SELECT ABS(convert(int, -2147483648))

Here is the error message:

Server: Msg 8115, Level 16, State 2
Arithmetic overflow error converting expression to type int.