What do Clustered and Non clustered index actually mean?

Couldn't resist sharing - http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

A clustered index means you are telling the database to store close values actually close to one another on the disk. This has the benefit of rapid scan / retrieval of records falling into some range of clustered index values.
For example, you have two tables, Customer and Order:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the "CustomerID" column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.
P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to "uniquify" the index or let the database handle that for you but that's another story[If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier].
Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.

Clustered Index

• Only one per table
• Faster to read than non clustered as data is physically stored in index order

Non Clustered Index

• Can be used many times per table
• Quicker for insert and update operations than a clustered index

Error while connecting to Remote SQLEXPRESS

Today i installed SQLEXPRESS in a VM, and tried accessing it thru SQL Studio that was installed on different machine. It throws me one of those connection error. So I did two things

Go to SQL Server Configuration Manager

1. Start 'SQL Server Browser' service, if not already started.
2. Enable TCP/IP for SQLEXPRESS('Protocols for SQLEXPRESS).

Restart the SQLEXPRESS service. It worked for me:)

Roles in SQL Server

Just for my reference

http://www.techrepublic.com/article/understanding-roles-in-sql-server-security/1061781
http://msdn.microsoft.com/en-in/library/ms189121.aspx
http://msdn.microsoft.com/en-us/library/ms188659.aspx

An example on how to create Login/User in SQL Server and adding roles using t-sql below

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
BEGIN
    DROP USER myuser
    DROP LOGIN myuser
END;
GO

CREATE LOGIN myuser WITH PASSWORD = '1234!@#$$#@!'
GO

Use [Database];
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
BEGIN
    CREATE USER [myuser] FOR LOGIN [myuser]
    EXEC sp_addsrvrolemember @loginame = N'myuser', @rolename = N'sysadmin'
    EXEC sp_addrolemember N'db_owner', N'myuser'
    EXEC sp_addrolemember 'db_ddladmin', N'myuser' -- this contains create table permission
    EXEC sp_addsrvrolemember @loginame = N'myuser', @rolename = N'dbcreator'
END;
GO


In-case of domain account, we need to have below

CREATE LOGIN [domain\user] FROM WINDOWS
GO

To find out assigned permissions for an user, below t-sql can be used. It will display permissions for the logged-in user

"SELECT permission_name FROM fn_my_permissions(NULL, 'SERVER')";
"SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE')";

SQL - Parse array of guid's Xml

drop PROCEDURE ParseList
go

CREATE PROCEDURE ParseList @list xml AS
   BEGIN
        DECLARE @Ids TABLE(Id uniqueidentifier)
        INSERT INTO @Ids
        SELECT T.Ids.value('.', 'uniqueidentifier') FROM @list.nodes('/ArrayOfGuid/guid') AS T(Ids)
        SELECT * FROM @Ids
    END
GO

EXEC ParseList
N'
        699f9527-1f9d-4a00-937b-a7637b0a8c03
        f766e5fb-27a7-4171-bf0c-c0e093baed27
        c22b2aa6-541e-495f-b315-233c0ed0e7a7
        ccf83d1e-5809-4214-a5a9-416ce22e62ca
      '
go