Java Script for Making HTML Control Toogle

<script type="text/javascript" language="javascript">
var prev='texxt';
function Showdata(indexx)
{
var tblprev=document.getElementById(prev);
if(prev !=indexx)
{
tblprev.style.display = 'none';
}
prev=indexx;
var tbl1=document.getElementById(indexx);
if(tbl1.style.display == 'block')
tbl1.style.display = 'none';
else
tbl1.style.display = 'block';
}
</script>

An Introduction to Clustered and Non-Clustered Index Data Structures

When I first started using SQL Server as a novice, I was initially confused as to the differences between clustered and non-clustered indexes. As a developer, and new DBA, I took it upon myself to learn everything I could about these index types, and when they should be used. This article is a result of my learning and experience, and explains the differences between clustered and non-clustered index data structures for the DBA or developer new to SQL Server. If you are new to SQL Server, I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code I have provided in order to more fully understand and appreciate the differences between clustered and non-clustered indexes.

Part I: Non-Clustered Index

Creating a Table
To better explain SQL Server non-clustered indexes; let’s start by creating a new table and populating it with some sample data using the following scripts. I assume you have a database you can use for this. If not, you will want to create one for these examples.

Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)

When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. We can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.

Now let’s add a few records in this table using this script:

Insert Into DummyTable1 Values (4, Replicate ('d',2000))
GO

Insert Into DummyTable1 Values (6, Replicate ('f',2000))
GO

Insert Into DummyTable1 Values (1, Replicate ('a',2000))
GO

Insert Into DummyTable1 Values (3, Replicate ('c',2000))
GO

Now, let’s view the contests of the table by executing the following command in Query Analyzer for our new table.

Select EmpID From DummyTable1
GO

Empid

4
6
1
3

As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.

dbcc ind(dbid, tabid, -1) – This is an undocumented command.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')

DBCC ind(@DBID, @TableID, -1)
GO

This script will display many columns, but we are only interested in three of them, as shown below.

PagePID
IndexID
PageType

26408
0
10

26255
0
1

26409
0
1

Here’s what the information displayed means:

PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.

IndexID is the type of index,

Where:
0 – Datapage
1 – Clustered Index
2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index),
PageType tells you what kind of data is stored in each database,
Where:
10 – IAM (Index Allocation MAP)
1 – Datapage
2 – Index page
Now, let us execute DBCC PAGE command. This is an undocumented command.

DBCC page(dbid, fileno, pageno, option)
Where:
dbid = database id.
Fileno = fileno of the page. Usually it will be 1, unless we use more than one file for a database.
Pageno = we can take the output of the dbcc ind page no.
Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data. You can try yourself for the other options.
Run this script to execute the command:
DBCC TRACEON (3604)
GO
DBCC page(@DBID, 1, 26408, 3)
GO
The output will be page allocation details.
DBCC TRACEON (3604)
GO
dbcc page(@DBID, 1, 26255, 3)
GO

The data will be displayed in the order it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 both display the data page.

I have displayed the data page information for page 26255 only. This is how MS SQL stores the contents in data pages as such column name with its respective value.

Record Type = PRIMARY_RECORD
EmpId = 4

EmpName = ddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

Record Type = PRIMARY_RECORD
EmpId = 6

EmpName = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Record Type = PRIMARY_RECORD
EmpId = 1

EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

This displays the exact data storage in SQL, without any index on table. Now, let’s go and create a unique non-clustered index on the EmpID column.

Creating a Non-Clustered Index
Now, we will create a unique non-clustered index on the empid column to see how it affects the data, and how the data is stored in SQL Server.

CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empid
ON DummyTable1 (empid)
GO

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID
IndexID
PageType

26408
0
10

26255
0
1

26409
0
1

26411
2
10

26410
2
2

Now, we see two more rows than before, which now contains index page details. Page 26408 displays the page allocation details, and pages 26255 and 26409 display the data page details, as before.

In regard to the new pages, page 26411 displays the page allocation details of an index page and page 26410 displays the index page details.

MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.

Let us see what would be the output for page 26411, that is page type = 10



IAM: Single Page Allocations @0x308A608E

-----------------------------------------
Slot 0 = (1:26410)

Let us view page 26410 to see the index page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26410, 3)
GO

SQL populates the index column data in order. The last column (?) is pointed to the row locator.

Here are the results, using two different methods:

Method I FileID
PageID
EMPID
?

1
26410
1
0x8F66000001000200

1
26410
3
0x2967000001000000

1
26410
4
0x8F66000001000000

1
26410
6
0x8F66000001000100

The row location display in one of two ways:

If the table does not have a clustered index, the row locator will be combination of fileno, pageno and the no of rows in a page.
If the table does have clustered index, the row location will be clustered index key value.
Non-clustered indexes are particularly handy when we want to return a single row from a table.

For example, to search for employee ID (empid = 3) in a table that has a non-clustered index on the empid column, SQL Server looks through the index to find an entry that lists the exact page and row in the table where the matching empid can be found, and then goes directly to that page and row. This greatly speeds up accessing the record in question.

Select EmpID, EmpName From DummyTable1 WHERE EMPID = 3

Now, let’s insert some more rows in our table and view the data page storage of our non-clustered index.

Insert Into DummyTable1 Values (10, Replicate ('j',2000))
GO

Insert Into DummyTable1 Values (2, Replicate ('b',2000))
GO

Insert Into DummyTable1 Values (5, Replicate ('e',2000))
GO

Insert Into DummyTable1 Values (8, Replicate ('h',2000))
GO

Insert Into DummyTable1 Values (9, Replicate ('i',2000))
GO

Insert Into DummyTable1 Values (7, Replicate ('g',2000))
GO

Now, let’s view the data in our table.

Proper way to change an Index from UNIQUE CONSTRAINT to PRIMARY KEY

Went form Bad SQL
DROP INDEX [PK_TableName] ON [dbo].[TableName] WITH ( ONLINE = OFF )

To Good SQL
ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_TableName]

Implementing Triggers in SQL Server 2000

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.

There are two different types of triggers in Microsoft SQL Server 2000. They are INSTEAD OF triggers and AFTER triggers. These triggers differ from each other in terms of their purpose and when they are fired. In this article we shall discuss each type of trigger.

First of all, let's create a sample database with some tables and insert some sample data in those tables using the script below:



Create Database KDMNN
GO
USE KDMNN
GO
CREATE TABLE [dbo].[User_Details] (
[UserID] [int] NULL ,
[FName] [varchar] (50) NOT NULL ,
[MName] [varchar] (50) NULL ,
[LName] [varchar] (50) NOT NULL ,
[Email] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_Master] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) NULL ,
[Password] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User_Master] WITH NOCHECK ADD CONSTRAINT [PK_User_Master] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User_Details] ADD CONSTRAINT [FK_User_Details_User_Master] FOREIGN KEY (
[UserID]
) REFERENCES [dbo].[User_Master] ( [UserID] )
GO

INSERT INTO USER_MASTER(USERNAME, PASSWORD)
SELECT 'Navneeth','Navneeth' UNION
SELECT 'Amol','Amol' UNION
SELECT 'Anil','Anil' UNION
SELECT 'Murthy','Murthy'

INSERT INTO USER_DETAILS(USERID, FNAME, LNAME, EMAIL)
SELECT 1,'Navneeth','Naik','navneeth@kdmnn.com' UNION
SELECT 2,'Amol','Kulkarni','amol@kdmnn.com' UNION
SELECT 3,'Anil','Bahirat','anil@kdmnn.com' UNION
SELECT 4,'Murthy','Belluri','murthy@kdmnn.com'

AFTER Triggers
The type of trigger that gets executed automatically after the statement that triggered it completes is called an AFTER trigger. An AFTER trigger is a trigger that gets executed automatically before the transaction is committed or rolled back.

Using the below script, first we shall create a trigger on the table USER_MASTER for the INSERT event of the table.

USE KDMNN
Go

CREATE TRIGGER trgInsert ON User_Master
FOR INSERT AS
Print ('AFTER Trigger [trgInsert] – Trigger executed !!')
GO


BEGIN TRANSACTION
DECLARE @ERR INT
INSERT INTO USER_MASTER(USERNAME, PASSWORD) VALUES('Damerla','Damerla')
SET @ERR = @@Error
IF @ERR > 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END

ELSE
BEGIN COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END

Output
AFTER Trigger [trgInsert] – Trigger executed !!
(1 row(s) affected)
ROLLBACK TRANSACTION

By looking at the output, we can conclude that before the transaction is rolled back or committed, the AFTER trigger gets executed automatically.

A table can have several AFTER triggers for each of the three triggering actions i.e., INSERT, DELETE and UPDATE. Using the below script, we shall create two triggers on the table User_Master for the INSERT triggering action.

CREATE TRIGGER trgInsert2
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert2] – Trigger executed !!')
END
GO

CREATE TRIGGER trgInsert3
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert3] – Trigger executed !!')
END
GO

BEGIN TRANSACTION
DECLARE @ERR INT
INSERT INTO USER_MASTER(USERNAME, PASSWORD)
VALUES('Damerla','Damerla')
SET @ERR = @@Error
IF @ERR > 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END

Output
AFTER Trigger [trgInsert] – Trigger executed !!
AFTER Trigger [trgInsert2] – Trigger executed !!
AFTER Trigger [trgInsert3] – Trigger executed !!
(1 row(s) affected)
ROLLBACK TRANSACTION

From the output we can conclude that when the user tries to insert data in the table USER_MASTER, three triggers are executed automatically. That is, you can write several AFTER triggers on one table for each of the three triggering actions.

Similarly, we can write several AFTER triggers on DELETE and UPDATE triggering actions.

Note: If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder. All the other triggers are in an undefined order which you cannot control.

An AFTER trigger can be created only on tables, not on views.

Using the script below, first we shall create a simple view [vwUserMaster] which will fetch the Username and Password from the table USER_MASTER.


Create View vwUserMaster as SELECT USERNAME, PASSWORD FROM USER_MASTER
GO

CREATE TRIGGER trgOnViewON vwUserMaster
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgOnView] – vwUserMaster !!')
END
GO


Output

Server: Msg 208, Level 16, State 4, Procedure trgOnView, Line 2
Invalid object name 'vwUserMaster'.

From the Output we can conclude that we cannot create an AFTER trigger on views.
Like stored procedures and views, triggers can also be encrypted.

The trigger definition is then stored in an unreadable form. Once encrypted, the definition of the trigger cannot be decrypted and cannot be viewed by anyone, including the owner of the trigger or the system administrator.

CREATE TRIGGER trgEncrypted
ON User_Master WITH ENCRYPTION
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!')
END
GO

SELECT sysobjects.name AS [Trigger Name],
SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
syscomments.encrypted AS [IsEncrpted]

FROM sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
(sysobjects.xtype = 'TR')

Output
Trigger Name Trigger Definition Table Name IsEncrpted

-----------------------------------------------------------------

trgInsert CREATE TRIGGER trgInsert User_Master 0
trgInsert1 CREATE TRIGGER trgInsert1 User_Master 0
trgInsert2 CREATE TRIGGER trgInsert2 User_Master 0
trgEncrypted ??????????????? User_Master 1

Since the trigger trgEncrypted is created with the option WITH ENCRYPTION, the trigger definition is hidden and there is no way that one can easily decrypt the trigger code.

We all know that the DML statements change or modify data. Sometimes it becomes necessary for the triggers to have the access to the changes being caused by the DML statements. SQL Server 2000 provides four different ways to determine the affects of the DML statements. The INSERTED and DELETED tables, popularly known as MAGIC TABLES, and update () and columns_updated() functions can be used to determine the changes being caused by the DML statements.

The below table depicts the contents of the INSERTED and DELETED tables for three different table Events

EVENT
INSERTED
DELETED

Insert
Contains the inserted rows
Empty

Delete
Empty
Contains the rows to be deleted

Update
Contains the rows after update
Contains the rows before update

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.

The update() function is used to find whether a particular column has been updated or not. This function is generally used for data checks.


CREATE TRIGGER trgUddate ON User_Details
FOR UPDATE
AS
If UPDATE(FName)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - First Name has been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(LName)

BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Last Name has been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(MName)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - MName Name has been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(Email)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Email has been updated')
ROLLBACK TRANSACTION
END

GO


UPDATE User_Details
SET MName = 'Diwaker'
WHERE UserID = 1

Output

AFTER Trigger [trgUddate] - Executed - MName Name has been updated

Depending upon the column updated, a message will be displayed. With this feature we can determine which column in the table has been updated, and then proceed with the business rules to be implemented further.

Columns_Update() function returns a varbinary data type representation of the columns updated. This function return a hexadecimal values from which we can determine which columns in the table have been updated.


INSTEAD OF Triggers

A trigger which gets executed automatically in place of triggering actions i.e., INSERT, DELETE and UPDATE is called an INSTEAD OF trigger.

INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed automatically after these constraints are checked.


CREATE TRIGGER trgAfterInsert On User_Details
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgAfterInsert] – Trigger executed !!')
END


INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_User_Details_User_Master'. The conflict occurred in database 'KDMNN', table 'User_Master', column 'UserID'.

The statement has been terminated.

UserID 100 does not exist in the User_Master table, so the Foreign Key constraint has been checked and an error message is displayed. What we can conclude is: AFTER triggers gets executed automatically after the PK and FK constraints.

Create Trigger trgInsteadInsert
On User_Details
INSTEAD OF INSERT
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!')
END

INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')

Output
INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!
(1 row(s) affected)

Even if the UserID 100 does not exists in the User_Master table, the trigger gets gets executed automatically.

DROP Trigger trgInsteadInsert
Unlike AFTER triggers, INSTEAD OF triggers can be created on views.

Create trigger trgOnView
on vwUserMaster
INSTEAD OF INSERT
AS
begin
Print ('INSTEAD OF Trigger [trgOnView] – vwUserMaster !!!')
End

INSERT INTO VWUSERMASTER(USERNAME, PASSWORD)
VALUES('Damerla','Venkat')

Output
INSTEAD OF Trigger [trgOnView] – vwUserMaster !!
(1 row(s) affected)

So whenever a user tries to insert data into the view vwUserMaster, the INSTEAD OF trigger trgOnView will automatically be executed.

In SQL SERVER 2000, views can be used to INSERT/DELETE and UPDATE the data in the multiple tables, and this can be achieved using INSTEAD OF triggers.

CREATE VIEW vwUser
AS
SELECT
[User_Master].[Username],
[User_Master].[Password],
[User_Details].[FName],
[User_Details].[MName],
[User_Details].[LName],
[User_Details].[Email]
FROM
[User_Master], [User_Details]
WHERE
[User_Master].[UserID]=[User_Details].[UserID]

CREATE TRIGGER tgrInsertData
ON vwUser
INSTEAD OF INSERT
AS
BEGIN
Declare @UserName varchar(50)
Declare @Password varchar(50)
Declare @FName varchar(50)
Declare @MName varchar(50)
Declare @LName varchar(50)
Declare @Email varchar(50)

SELECT
@UserName = UserName,
@Password = Password,
@FName = FName,
@MName = MName,
@LName = LName,
@Email = Email
FROM INSERTED

INSERT INTO User_Master(UserName, Password)
VALUES(@UserName, @Password)

INSERT INTO User_Details(UserID,FName,LName,MName,Email) VALUES(@@Identity, @FName, @LName, @MName, @Email)

END

INSERT INTO vwUser(UserName,Password,FName,LName,MName,Email)
VALUES ('Dhananjay','Dhananjay','Dhananjay','Nagesh',NULL,
'Dhananjay@kdmnn.com'

Output

AFTER Trigger [trgInsert] – Trigger executed !!
AFTER Trigger [trgInsert2] – Trigger executed !!
AFTER Trigger [trgInsert3] – Trigger executed !!
AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!
(1 row(s) affected)
AFTER Trigger [trgAfterInsert] – Trigger executed !!
(1 row(s) affected)

Then check the data in the following tables User_Master and User_Details. The new row gets inserted in both the tables.

A view or table can have only one INSTEAD OF trigger for each INSERT, UPDATE and DELETE events.

We have seen that you can create any number of AFTER triggers on the table for the same event, whereas you cannot do that with INSTEAD OF triggers.

CREATE TRIGGER trgInsteadOfTrigger1
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadOfTrigger1] – Trigger executed !!')
END

CREATE TRIGGER trgInsteadOfTrigger2
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadOfTrigger2] – Trigger executed !!')
END

Output
Server: Msg 2111, Level 16, State 1, Procedure trgInsteadOfTrigger2, Line 6

Cannot CREATE trigger 'trgInsteadOfTrigger2' for view 'vwUserMaster' because an INSTEAD OF UPDATE trigger already exists.

From the output, it is clear that you cannot create two INSTEAD OF triggers on the view/ table for the same event.

Note: An important point to be noted is that INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined.

At last, how would you know what are the triggers associated with the table and what type of the trigger it is? Whether AFTER or INSTEAD OF?

The solution for this question is sp_helptrigger. This stored procedure gives all the information about the triggers such as Event on which the trigger gets executed, the type of the trigger, etc.

Sp_helptrigger User_Master

Output
trigger_name trigger_owner isupdate isdelete isinsert isafter isinsteadof
trgInsert dbo 0 0 1 1 0
trgInsert2 dbo 0 0 1 1 0
trgInsert3 dbo 0 0 1 1 0
trgEncrypted dbo 0 0 1 1 0

Triggers can be used in the following scenarios, such as if the database is de-normalized and requires an automated way to update redundant data contained in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical value in another table.

Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce the data integrity using CONSTRAINTS, RULES and DEFAULTS. Triggers cannot be created on the temporary tables.

Creating a new database with Sql Server Managment Studio Express

If you get thhis error then try this

CREATE DATABASE permission denied in database 'master'.

If the current user does not have the admin permission.Please refer to the following suggestion . Open Microsoft SQL Server 2005 -> Management Studio Express Edition , right click Management Studio Express Edition and choose Run as Administrator to connect SQL Server with your administrator,select Security->Logins ,then double-click the user account you want to fix , select Server Roles section and check sysadmin check box, click OK to save it. You can also open Microsoft SQL Server 2005-> Configuration Tools-> SQL Server Surface Area Configuration with your current user, click Add New Administrator ,double-click the Member of SQL Server SysAdmin role on yourinstantname in Available priviledges , click OK to save it.
Hope this helps.

System Tables and Catalog Views

When a new version is released or when existing software is upgraded there is always a learning curve and getting used to new ways of doing things. This article demonstrates how to use SQL Server 2005's catalog views in comparison with using system tables in SQL Server 2000.

Catalog views are a storehouse for static metadata. They contain data about the server, database, objects, logins, permissions, etc.

Catalog views are the general interface to the catalog metadata, and we should start using them rather than accessing the system tables directly.

Find all columns
in a table that are computed columns

SQL SERVER 2000:
select name from syscolumns
where id =object_id('TableName')
and iscomputed=1SQL

SERVER 2005:
select name from sys.computed_columns where object_id =object_id('TableName')

Note: The computed column in SQL Server 2005 may be persisted. To narrow down the result set, you could execute the following query:

select * from sys.computed_columns where is_persisted=0



Find all tables that have columns with an identity property

SQL SERVER 2000:
select object_name(id),name from syscolumns where columnproperty(id,name,'IsIdentity')=1
SQLSERVER 2005:
select object_name(object_id),name from sys.identity_columns

Note: SQL Server 2005 stores the last value of the identity property that was generated.

To query the last value execute the following query.

select name,last_value from sys.identity_columns

Find all database names in a SQL Server instance

SQL SERVER 2000:
select name from master..sysdatabases
SQL SERVER 2005:
select name from sys.databasesNote: Many enhancements were made to the database.

Query all of the columns in sys.databases to understand the new enhancements like snapshot, etc.

Find all Procedures in a Database
SQL SERVER 2000:
select name from sysobjects where type='P'

SQL SERVER 2005:
select name from sys.procedures

Note: You can find whether the stored procedure execution is used in replication or if the stored procedure is a startup procedure.

Execute the following queries:
select name from sys.procedures where is_execution_replicated=1
select name from sys.procedures where is_auto_executed=0Find all tables in a Database

SQL SERVER 2000:
select name from sysobjects where type='U'
SQL SERVER 2005:
select name from sys.tables

Note: In SQL Server 2005, you can find whether a table is replicated.

Execute the following query.
select * from sys.tables where is_replicated =1


Find all views in a Database
SQL SERVER 2000:
select name from sysobjects where type='V'SQL SERVER 2005:
select name from sys.views
Note: In SQL Server 2005, you can find whether a view is replicated.

Execute the following query.
select * from sys.views where is_replicated =1

Find all Triggers in a Database
SQL SERVER 2000:
select name from sysobjects where type='TR'

SQL SERVER 2005:
select name from sys.triggers where parent_class=1

Note: In SQL Server 2005, the triggers can be Assembly trigger (CLR) or a SQL trigger. In addition, we can find whether the trigger is an AFTER trigger or INSTEAD of trigger.

Execute the following query:
select name from sys.triggers where type='TA'
select name from sys.triggers where type='TR'
select name from sys.triggers where is_instead_of_trigger=1

Find all SQL logins in a server
SQL SERVER 2000:
select * from master..syslogins where isntgroup=0 and isntname=0
SQL SERVER 2005:
select * from sys.sql_logins

Find all dependencies of the SQL Object in a Database
SQL SERVER 2000:
select * from sysdepends
SQL SERVER 2005:
select * from sys.sql_dependencies

Find all data types in SQL server

SQL SERVER 2000:
select * from systypes
SQL SERVER 2005:
select * from sys.systypes

Find all error messages in SQL server

SQL SERVER 2000:
select * from master..sysmessages
SQL SERVER 2005:
select * from sys.messages

Find all the database files of the current database

SQL SERVER 2000:
select name,filename from sysfiles

SQL SERVER 2005:
select name, physical_name from sys.database_files

Find the type of index
SQL SERVER 2000:
We have to use indid column to determine the type of index from 0,1 or 255.

SQL SERVER 2005:
select object_name(object_id),name, type_desc from sys.indexes where type_desc ='CLUSTERED'

select object_name(object_id),name, type_desc from sys.indexes where type_desc ='HEAP'

select object_name(object_id),name, type_desc from sys.indexes where type_desc ='NONCLUSTERED'

select object_name(object_id),name, type_desc from sys.indexes where type_desc ='XML'

All of the SQL Statements that are used in SQL Server 2000 can still be used in SQL Server 2005. It is advisable to start using the catalog views since the underlying system tables might change in future releases and catalog views are the general interface to the catalog metadata.

SQL Server consists of six databases by default.

Master


It contains system catalogs that keep information about disk space, file allocations,
usage, system wide configuration settings, login accounts, the existence of other
database, and the existence of other SQL Servers (for distributed operations).

Model

It is a simply a template database. Every time you create a new database, SQL Server
makes a copy of model to form the basis of the new database.


Tempdb
Temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique
among all other databases because it is recreated not recovered every time SQL Server
is started

Pubs
This is a sample database used extensively by much of SQL Server documentation.
It’s available to everyone in the SQL Server community


Northwind
This is a sample database that was originally developed for the use of Microsoft
Access

Msdb
This database is used by the SQL Server Agent Service, which performs scheduled
activities such as backups and replication tasks.


Database Files: A database file is nothing more than an operating system
file. SQL Server 2000 allows the following three types of database files:

·Primary data files (.mdf)
·Secondary data files (.ndf)
·Log files (.ldf)

When we create a new user database, SQL Server copies the model database (includes
19 system tables and 2 system views (for backward compatibility)). A new user database
must be 1MB or greater in size.

We can create a new database using the following command: CREATE DATABASE newdb

Types of Backups:

Full Backup

Differential Backup

Log Backup

Tables: A database can contain multiple tables with the same name as long
as the tables have different owners.

The full name of a table has three parts, in the following form:

Database.Owner.Tablename

SQl Server System Tables And Views

System Tables
-------------
Introduction
When a SQL Server object is created, its properties are called metadata.The metadata is stored in special System Tables. For example, in SQL 2000, when a new column was created, the column name and data type could be found in an internal System Table called syscolumns.
All SQL objects produce metadata. Every time SQL 2000 Enterprise Manager or SQL 2005 SQL Server Management Studio is browsed, the information displayed about database, tables, and all objects, comes from this metadata.
There are many uses for this metadata, including gathering performance statistics, discovering table and column similarities and differences during a database upgrade, and obtaining lock information. In previous versions of SQL Server, these System Tables were exposed and could be queried like any standard table.
However, starting with SQL 2005, System Tables are hidden and they cannot be directly queried. Even with full DBA rights, System Tables are restricted. Although not directly accessible, there are built in views and procedures for extracting metadata. Some of these are new in SQL 2005; others were carried forward from pervious versions.
Most have the advantage of being more readable and self-describing than querying System Tables. If you have legacy scripts directly referencing System Tables, there are many new System Views that will directly take their place.
System Views
System Views are predefined Microsoft created views for extracting SQL Server metadata. There are over 230 various System Views. To display all the views in SQL 2005, launch the SQL Management Studio; expand Databases, System Databases, and select master, Views, System Views.

These System Views will be automatically inserted into any user created database. The System Views are grouped into several different schemas. In SQL 2005, schemas are used as security containers. There can be several different schemas inside a single database. This is a better ANSI implementation of schemas compared to their use in SQL 2000. See Marcin Policht's excellent article; SQL Server 2005 Security, at http://www.databasejournal.com/features/mssql/article.php/3481751 for a detailed explanation of SQL 2005 schemas and security.

Information Schema
The first group of System Views belongs to the Information Schema set. Information Schema is an ANSI specification for obtaining metadata. There are twenty different views for displaying most physical aspects of a database, such as table, column, and view information.

Information Schema views were available in SQL 2000 and should continue to appear in future versions of SQL. They are a few ANSI terms that translate differently in SQL. An ANSI "Catalog" is a SQL "Database"; an ANSI "RowVersion" is a SQL "Timestamp"; and an ANSI "Timestamp" is a SQL "DateTime." Aside from this, Information Schema views are easy to implement.
For an example, we will create a small table with a few columns.
CREATE DATABASE TestGOUSE TestGOCREATE TABLE MyTable(Col1 int,Col2 varchar(10),Col3 datetime)GOTo use Information Schema views, select them like any standard view.

The following TSQL will display column and table information on the new database;
SELECT *FROM INFORMATION_SCHEMA.TABLES
SELECT *FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'MyTable'


Most of the Information Schema view names are self-explanatory. INFORMATION_SCHEMA.TABLES returns a row for each table. INFORMATION_SCHEMA.COLUMNS returns a row for each column. A few though, refer to ANSI names. INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE contains a row for each column created with a user-defined type, and INFORMATION_SCHEMA.DOMAIN lists a row for each user-defined type. INFORMATION_SCHEMA.ROUTINES shows a record for each stored procedure or function. A benefit to Information Schema views is that because they are an ANSI standard, you will find them in many other database packages.

Catalog Views
New in SQL 2005 are Catalog Views. Microsoft recommends them as the most general interface to the catalog metadata. They are efficient and all user available catalog metadata is exposed. The amount of views is impressive. Best of all, many of the columns returned by Catalog Views are self-describing.
Documentation organizes Catalog Views into several different groups:

Partition Function Catalog Views
Server-wide Configuration Catalog Views
Data Spaces and Fulltext Catalog Views
Databases and Files Catalog Views
CLR Assembly Catalog Views
Schemas Catalog View
Scalar Types Catalog Views
Security Catalog Views
Objects Catalog Views
Database Mirroring Catalog Views
Messages (For Errors) Catalog Views
XML Schemas (XML Type System) Catalog Views
Service Broker Catalog Views
Linked Servers Catalog Views
HTTP Endpoints Catalog Views
Extended Properties Catalog Views

The views we need to gather table and column information, like the previous example, are grouped under "Objects Catalog Views". This group includes views on tables, columns, indexes, constraints, and triggers to name a few. Our example requires two views, "sys.tables" and "sys.columns." The columns view will need to be joined on the table view as shown below.SELECT *
FROM sys.tables
SELECT *
FROM sys.columns INNER JOIN sys.tables ON
sys.tables.object_id = sys.columns.object_id
WHERE sys.tables.name = 'MyTable'



Sys All
There are four views in a Sys_All group. These views contain information about the System Views as well as user created objects. The views are sys.all_columns, sys.all_objects, sys.all_parameters, and sys.all_views.

Dynamic Management Views
The last groups of views are called Dynamic Management views, or DM. They are used to gather statistics stored in memory but not persistent on disk such as thread information, memory usage, and connection details. These offer administrators a fast and reliable method for obtaining performance numbers. For example, to show the statistics for cached queries, execute this DM statement:

SELECT *
FROM sys.dm_exec_query_stats



System Stored Procedures
In addition to the System Views, there are many System Stored Procedures that can be used for administrative purposes. These pre-made procedures return results similar to System Views. They are located under each database, Programmability, Stored Procedures, and System Stored Procedures. They belong to sys schema.



To obtain column information using a System Stored Procedure, execute sp_columns with the following script:



EXEC sys.sp_columns 'MyTable'

Conclusion
For obtaining SQL Sever metadata information, SQL 2005 offers a large variety of pre-made views and procedures. They are easy and fast to implement and usually return information that is far less cryptic than the tools provided in previous versions.



This set of tables maintained by SQL Server is sometimes called the system catalog. We can identify a system table by its name or the object_id or type column in sysobjects.
SELECT name from sysobjects where type=’V’ and name like ‘sys%’