10 Things you should know about databases

1. 10 Things You Should Know About Databases
2. 'Enhancements to XML File Management'
3. 'Compression Technology'
4. 'Database Clusters on Virtual Environments'
5. 'Moving Functionality Closer to the Storage Device'
6. 'Automation for Managing Virtualized Databases'
7. 'Column DBMS Models Catching On'
8. 'It's MPP, Not MP3'
9. 'Databases in the Cloud'
10. 'Spatial Data'
11. 'Database Migration Technology'

An A-Z Index of the SQL Server 2005 database

Aggregate - CREATE AGGREGATE

          - DROP AGGREGATE
Application Role - CREATE APPLICATION ROLE
                - ALTER APPLICATION ROLE
                - DROP APPLICATION ROLE
Assembly - CREATE ASSEMBLY
        - ALTER ASSEMBLY
        - DROP ASSEMBLY
ALTER AUTHORIZATION

BACKUP
BACKUP CERTIFICATE
BEGIN [DIALOG [CONVERSATION]]

Certificate - ALTER CERTIFICATE
           - CREATE CERTIFICATE
           - DROP CERTIFICATE
CHECKPOINT
COMMIT
Contract - CREATE CONTRACT
        - DROP CONTRACT
Credential - CREATE CREDENTIAL
          - ALTER CREDENTIAL
          - DROP CREDENTIAL

Database - CREATE DATABASE
        - ALTER DATABASE
        - DROP DATABASE
DBCC CHECKALLOC   - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB    - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname    - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC FREE... CACHE  - Remove items from cache.
DBCC HELP        - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN    - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE   - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE  - Shrink or empty a database data or log file. 
DBCC SQLPERF     - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE...    - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands
DECLARE
Default - CREATE DEFAULT
       - DROP DEFAULT
DELETE
DENY - DENY Object permissions
    - DENY User/Role permissions
Endpoint - CREATE ENDPOINT
        - ALTER ENDPOINT
        - DROP ENDPOINT
Event - CREATE EVENT NOTIFICATION
     - DROP EVENT NOTIFICATION
EXECUTE
EXECUTE AS

Fulltext Catalog - CREATE FULLTEXT CATALOG
                - ALTER FULLTEXT CATALOG
                - DROP FULLTEXT CATALOG
Fulltext Index - CREATE FULLTEXT INDEX
              - ALTER FULLTEXT INDEX
              - DROP FULLTEXT INDEX
Function - CREATE FUNCTION
        - ALTER FUNCTION
        - DROP FUNCTION

GO
GRANT - GRANT Object permissions
     - GRANT User/Role permissions

Index - CREATE INDEX
     - ALTER INDEX
     - DROP INDEX
INSERT
iSQL -U user -P password -i script.sql -o logfile.log

Key - CREATE ASYMMETRIC KEY
   - ALTER ASYMMETRIC KEY
   - DROP ASYMMETRIC KEY
   - CREATE SYMMETRIC KEY
   - OPEN SYMMETRIC KEY
   - CLOSE SYMMETRIC KEY
   - ALTER SYMMETRIC KEY
   - DROP SYMMETRIC KEY
KILL
KILL QUERY NOTIFICATION
KILL STATS JOB

Login - CREATE LOGIN
     - ALTER LOGIN
     - DROP LOGIN

Master Key - CREATE MASTER KEY
          - ALTER MASTER KEY
          - BACKUP MASTER KEY
          - DROP MASTER KEY
          - RESTORE MASTER KEY
          - ALTER SERVICE MASTER KEY
          - BACKUP SERVICE MASTER KEY
          - RESTORE SERVICE MASTER KEY
Message Type - CREATE MESSAGE TYPE
            - ALTER MESSAGE TYPE
            - DROP MESSAGE TYPE

Partition Function - CREATE PARTITION FUNCTION
                  - ALTER PARTITION FUNCTION
                  - DROP PARTITION FUNCTION
Partition Scheme - CREATE PARTITION SCHEME
                - ALTER PARTITION SCHEME
                - DROP PARTITION SCHEME
Procedure - CREATE PROCEDURE
         - ALTER PROCEDURE
         - DROP PROCEDURE

Queue - CREATE QUEUE
     - ALTER QUEUE
     - DROP QUEUE

Remote Service Binding - CREATE REMOTE SERVICE BINDING
                      - ALTER REMOTE SERVICE BINDING
                      - DROP REMOTE SERVICE BINDING

RESTORE - RESTORE DATABASE Complete
         RESTORE DATABASE Partial
         RESTORE DATABASE Files
         RESTORE LOGS
         RESTORE DATABASE_SNAPSHOT
         RESTORE FILELISTONLY - List database and log files
         RESTORE HEADERONLY - List backup header info
         RESTORE LABELONLY  - Media info
         RESTORE REWINDONLY - Rewind and close tape device
         RESTORE VERIFYONLY
REVERT
REVOKE - REVOKE Object permissions
      - REVOKE User/Role permissions
Role - CREATE ROLE
    - ALTER ROLE
    - DROP ROLE
ROLLBACK
Route - CREATE ROUTE
     - ALTER ROUTE
     - DROP ROUTE

Schema - CREATE SCHEMA
      - ALTER SCHEMA
      - DROP SCHEMA
SELECT
SEND
SERVERPROPERTY
Service - CREATE SERVICE
       - ALTER SERVICE
       - DROP SERVICE
SESSION_USER
SESSIONPROPERTY
SET @local_variable
SET
SHUTDOWN
Signature - ADD SIGNATURE
         - DROP SIGNATURE
Statistics - CREATE STATISTICS
          - UPDATE STATISTICS
          - DROP STATISTICS

Synonym - CREATE SYNONYM
       - DROP SYNONYM

Table - CREATE TABLE
     - ALTER TABLE
     - DROP TABLE
     - TRUNCATE TABLE
Transaction - BEGIN DISTRIBUTED TRANSACTION
           - BEGIN TRANSACTION
           - COMMIT TRANSACTION
Trigger - CREATE TRIGGER
       - ALTER TRIGGER
       - ENABLE TRIGGER
       - DISABLE TRIGGER
       - DROP TRIGGER
Type - CREATE TYPE
    - DROP TYPE

UNION
UPDATE
User - CREATE USER
    - ALTER USER
    - DROP USER
USE

View - CREATE VIEW
    - ALTER VIEW
    - DROP VIEW

XML Schema Collection - CREATE XML SCHEMA COLLECTION
                     - ALTER XML SCHEMA COLLECTION
                     - DROP XML SCHEMA COLLECTION 

Temp tables in SQL server 2005

About Temporary Tables in SQL Server 2005

1. Temporary Tables are useful in a way when we want to create a table for temporary processing.
2. We can create temporary table in a same way as normal table except temporary table has a pound sign or hash sign (#) preceding its name.
3. Temporary tables are created in a tempdb
4. Temporary is tables are available only in a batch or procedure that creates it. Once the execution of stored procedure or batch is over they are deleted.
5. If you create a temporary table in a Query analyzer, it will be available until the end of the session.
6. You can also drop temporary table within the batch.
7. Temporary tables are accessible only those connections which creates the Temporary table. In this way the scope for temporary table is very limited.
8. Temporary tables are created in tempdb with a unique name. The unique name is created on assigned table name + connection identifier. Assigned table name is a name given to temporary table while creating it plus connection identifier.
9. The length of the Temporary table name is 116 characters + last 12 characters makes the name unique.

Let’s look at a simple example.

–Temporary Table Example

/*

Please execute as a batch. Since this will be executed in a SQL Query Browser (Analyzer) temporary table will be available till the session exists. So you might get error if you create table with the same name again in the same session.Because the table already exists.

you can also drop temporary table..Follow the below example

*/

–Create Temporary Table
CREATE TABLE #Temp1 (Empno int PRIMARY KEY)

–Inserting row into a temporary table
INSERT INTO #Temp1 VALUES (1001)

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Insert rows from other Table
INSERT INTO #Temp1 SELECT EMPNO from Test

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Using Temporary Table in a stored procedure

CREATE PROCEDURE TempTableTest (@NewEmpNo int)
AS
SET NOCOUNT ON

BEGIN
–Create new Temporary Table
CREATE TABLE #Temp2 (Empno int PRIMARY KEY)

–Insert new Record
INSERT INTO #Temp2 VALUES (@NewEmpNo)

–Display new Inserted Record
SELECT * FROM #Temp2
END

EXEC TempTableTest 2

Result