Friday, February 20, 2009

SQL Server

Insert Data From One Table to Another Table

Type 1:

SELECT Column_name(s)
INTO newtable
FROM sourcetable

Its column and its size created automatically

E.g. 1:

SELECT *,
Row_number() OVER(ORDER BY price DESC) AS rownumber
INTO #temptbl
FROM MASTER

E.g. 2:

SELECT resultset.*
INTO temptable
FROM (SELECT *
FROM SOURCE) AS resultset

Type 2

INSERT INTO @TempRecreationMasterID
SELECT Column_name(s)
FROM sourced

Have to create Table and its column

E.g. : To drop the table if already exists

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END

CREATE TABLE #temp
(
propertyid INT,
propertyname VARCHAR(100),
publish INT,
starrate INT
)

INSERT INTO #temp
SELECT propertyid,
propertyname,
publish
FROM vw_basicsearch


Msg 217, Level 16, State 1, Procedure EditPropertyName, Line 24

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).give select table1.columnNames, table2.columnNames from table, table2Don't give select table1.*, table2.* from table1, table2

Last identity value generated in all tables

Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database. This is how you return all the tables with their last generated identity values in a SQL Server Database

SELECT Ident_current(table_name),
*
FROM information_schema.tables
WHERE table_type = 'base table'
AND Objectproperty(Object_id(table_name), 'TableHasIdentity') = 1



Cannot add rows to sysdepends for the current object because it depends on the missing object 'GetAssociate'. The object will still be created


Delete With Join

DELETE propertyrecreationmanager
FROM propertyrecreationmanager
INNER JOIN propertyrecreation
ON propertyrecreationmanager.recreationid =
propertyrecreation.recreationid
WHERE propertyid = @PropertyID


JOIN
JOIN combines two separate result sets

To get the procedure name in a database

SELECT name AS spname
FROM sysobjects
WHERE ( xtype = 'p' )
AND ( name NOT LIKE 'dt%' )
ORDER BY name,
crdate

Object Type is optional, and possible values it can hold are. (Items in bold are frequently used)

Object type:
AF = Aggregate function
(CLR)C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML triggerIF = SQL inlined table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table


Primary key
Only one Identity column—
Can't be null-

Identity
-- Automatic value
-- Only one Identity column
-- Can't be null
-- Can exist in addition of primary key,
GUID
-auto columns
- Uniqueindentifier
-- Can be multiple
-- Can be null
- Uniqueindentifier
- rowguid
-- Auto generated
-- Only one Identity column
-- Can be null
-- Can exist in addition of primary key,
Identity columns
- Unique Key
-- Can be null
-- Can be multiple

ACID (Atomicity, Consistency, Isolation, Durability)

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures.

For a reliable database all this four attributes should be achieved. Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Data Integrity - no two users should modify the same piece of data at the same time.

Above four rules are very important for any developers dealing with databases.


Aggregate Function "SUM" the group if it contains null in any of the column
SELECT SUM(location)
FROM review
WHERE location IS NOT NULL

SELECT propertyid,
COUNT(propertyid) AS propertyidcount,
SUM(Coalesce(cleanliness, 0)) + SUM(Coalesce(facilities, 0)) +
SUM(Coalesce(SERVICE, 0)) + SUM(Coalesce(location, 0)) + SUM(
Coalesce(pricing, 0)) AS customerratings
FROM review
GROUP BY propertyid

View

If a view is defined with the select * statement and the base table is altered by adding a few columns, the new columns do not get included in the view. The asterisk (*) symbol is interpreted and expanded only when the view is created. In order to access the new columns via the view, it is necessary to drop the view and recreate it.

Temporary Table

When we are creating Temporary Table the order of column name should be same as both in the new table and the oldtable and also if we use select statement
eg:

CREATE TABLE #temp
(
propertyname VARCHAR(100),
propertyid INT,
publish INT,
propertyoverview VARCHAR(5000)
)

INSERT INTO #temp
SELECT propertyid,
propertyname,
publish,
propertyoverview
FROM roomtype

ERROR1:
Conversion failed when converting the varchar value 'PropertyName', to data type VARCHAR.
--Why? Because the column order is different
ERROR2: also this error
Conversion failed when converting the varchar value 'Wrap in div or asp:Panel with width for basic wrapping... Any other solution seems quite silly. Wrap in div or asp:Panel with width for basic wrapping... Any other solution seems quite silly.' to data type int.

Dynamic ORDER BY in SQL Server 2005

Order BY CASE WHEN @SortOrder = 'Hotel A - Z' THEN (rank() OVER (ORDER BY
propertyname ASC)) WHEN @SortOrder = 'Hotel Z - A' THEN (rank() OVER (ORDER BY
propertyname DESC)) WHEN @SortOrder = 'Star Rate 1..5' THEN (rank() OVER (ORDER
BY starrate ASC)) WHEN @SortOrder = 'Star Rate 5..1' THEN (rank() OVER (ORDER BY
starrate DESC)) ELSE propertyname END

Note:-

Case WHEN @Conditions = 'StarRate' THEN starrate ELSE propertyname END

In the above StarRate is INT and PropertyName is VARCHAR We will get an error Conversion failed when converting the varchar value '************' to data type int.
To Over Come this situation we have to case them
Case WHEN @Conditions = 'StarRate' THEN CAST(starrate AS VARCHAR) ELSE
propertyname END

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/

Row_number()

eg:--
SELECT Row_number() OVER (ORDER BY cityid) AS rownumber
FROM cityrank
is assigned to the results of the query. We can't able to give ROW_NUMBER()>10. Because the function is called after the query obtaining the results.

ROW_NUMBER () OVER ([] )
RANK () OVER ([] )
DENSE_RANK () OVER ([] )
NTILE (integer_expression) OVER ([] )

Note: is not optional.

Eg:SELECT ROW_NUMBER() OVER ( ORDER BY (select 1)) AS 'Row Number'

(select 1) To overcome sorting since sorting takes more execution time,

Rank() OVER ( ORDER BY dbo.roomrates.DATE) AS 'Rank', dense_rank() OVER ( ORDER
BY dbo.roomrates.DATE) AS 'Dense Rank', ntile(4) OVER ( ORDER BY
dbo.roomrates.DATE) AS 'Quartile' FROM roomrates

SELECT HAI = 1

DECLARE @PropertyName VARCHAR(100)
DECLARE @CountryID INT
DECLARE @CityID INT

SET @PropertyName = NULL
SET @CountryID = 3
SET @CityID = 0

SELECT *
FROM vw_basicsearch
WHERE CASE
WHEN @CityID = 0
AND @PropertyName = NULL THEN @CountryID = 3
WHEN ( @CityID = 0
AND ! Isnull(@PropertyName, ' ') ) THEN
propertyname = @PropertyName
END

GroupBy
you can't refer to an aliases field in the GROUP BY clause. It is good to filter data in where clause before grouping.

Get all the tables in a DB
select count(column_name) as count,Table_name from information_schema.columns group by Table_name

Last Ran Query - Recently Ran Queryhttp://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

exec sp_who2

Check for blocking. Run sp_who2 to see what process it blocking another one. Look at "blocks, avoiding" in SQL Server BOL

Get all the columns from database
SELECT column_name + ','FROM information_schema.columnsWHERE column_name <> 'HotelAmenitiesID'Fiter by column ==> HotelAmenitiesID

on delete cascade

Resolve Sql Server 2005 "Agent XPs disabled" error

Remove NULL coulum with some text

SELECT offersid,
priority = CASE
WHEN Isnull(priority, ' ') = ' ' THEN 'n/a'
--(or) WHEN Priority IS NULL THEN 'n/a'
ELSE priority
END
FROM hotelieroffers


ISNULL

ISNULL( , ) = Note both datatype should be same. Otherwise is give can't able to convert from to if we give something inside ''if we give ' ' no problem


While declare a parameter in Procedure give the correct size if declare VARCHAR without size it took only one char as its size and trim the value of the parameter to one char

SELECT as joins

SELECT roomids.items AS roomid,
noofrooms.items AS noofrooms
FROM (SELECT *
FROM dbo.Split(@RoomIDs, '±')) AS roomids
INNER JOIN(SELECT *
FROM dbo.Split(@NoOfRooms, '±')) AS noofrooms
ON noofrooms.id = roomids.id

Update With Joins

UPDATE roomrates
SET availability = availability + CAST(roomavailable.noofrooms AS INT)
FROM roomrates AS roomrates
INNER JOIN(SELECT roomids.items AS roomid,
noofrooms.items AS noofrooms
FROM (SELECT *
FROM dbo.Split(@RoomIDs, '±'))AS roomids
INNER JOIN(SELECT *
FROM dbo.Split(@NoOfRooms, '±'))AS
noofrooms
ON noofrooms.id = roomids.id)roomavailable
ON roomrates.roomid = roomavailable.roomid

To Delete All Table and StoredProcedures in a DATABASE
Original Link

CREATE PROCEDURE Usp_dropspfunctionsviews
AS
-- variable to object name
DECLARE @name VARCHAR(1000)
-- variable to hold object type
DECLARE @xtype VARCHAR(20)
-- variable to hold sql string
DECLARE @sqlstring NVARCHAR(4000)
DECLARE spviews_cursor CURSOR FOR
SELECT Quotename(routine_schema) + '.' + Quotename(routine_name) AS name,
routine_type AS xtype
FROM information_schema.routines
UNION
SELECT Quotename(table_schema) + '.' + Quotename(table_name) AS name,
'VIEW' AS xtype
FROM information_schema.VIEWS

OPEN spviews_cursor

FETCH NEXT FROM spviews_cursor INTO @name, @xtype

WHILE @@FETCH_STATUS = 0
BEGIN
-- test object type if it is a stored procedure
IF @xtype = 'PROCEDURE'
BEGIN
SET @sqlstring = 'drop procedure ' + @name

EXEC Sp_executesql @sqlstring

SET @sqlstring = ' '
END

-- test object type if it is a function
IF @xtype = 'FUNCTION'
BEGIN
SET @sqlstring = 'drop FUNCTION ' + @name

EXEC Sp_executesql @sqlstring

SET @sqlstring = ' '
END

-- test object type if it is a view
IF @xtype = 'VIEW'
BEGIN
SET @sqlstring = 'drop view ' + @name

EXEC Sp_executesql @sqlstring

SET @sqlstring = ' '
END

-- get next record
FETCH NEXT FROM spviews_cursor INTO @name, @xtype
END

CLOSE spviews_cursor

DEALLOCATE spviews_cursor

GO



CREATE PROCEDURE Dropspviews
AS
-- variable to object name
DECLARE @name VARCHAR(100)
-- variable to hold object type
DECLARE @xtype CHAR(1)
-- variable to hold sql string
DECLARE @sqlstring NVARCHAR(1000)
DECLARE spviews_cursor CURSOR FOR
SELECT sysobjects.name,
sysobjects.xtype
FROM sysobjects
JOIN sysusers
ON sysobjects.uid = sysusers.uid
WHERE Objectproperty(sysobjects.id, N'IsProcedure') = 1
OR Objectproperty(sysobjects.id, N'IsView') = 1
AND sysusers.name = 'USERNAME'

OPEN spviews_cursor

FETCH NEXT FROM spviews_cursor INTO @name, @xtype

WHILE @@FETCH_STATUS = 0
BEGIN
-- test object type if it is a stored procedure
IF @xtype = 'P'
BEGIN
SET @sqlstring = 'drop procedure ' + @name

EXEC Sp_executesql @sqlstring

SET @sqlstring = ' '
END

-- test object type if it is a view
IF @xtype = 'V'
BEGIN
SET @sqlstring = 'drop view ' + @name

EXEC Sp_executesql @sqlstring

SET @sqlstring = ' '
END

-- get next record
FETCH NEXT FROM spviews_cursor INTO @name, @xtype
END

CLOSE spviews_cursor

DEALLOCATE spviews_cursor

Search for text in the StoredProcedureorHow do I find a stored procedure containing ?
Find Text
SELECT ROUTINE_NAME,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'AND ROUTINE_TYPE='PROCEDURE'

--This is with Schema
SELECT ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%babu%'
AND ROUTINE_TYPE ='PROCEDURE'
AND ROUTINE_SCHEMA = 'Vehicle'
ORDER BY ROUTINE_NAME

--(OR)--

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%foobar%'AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

--(OR)--

SELECT Name
FROM
sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%foobar%'

--(OR)--

SELECT OBJECT_NAME(object_id)FROM
sys.sql_modules
WHERE Definition LIKE '%foobar%'AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

Concatenate strings from multiple rows into one in SQL Server 2000 or 2005

DECLARE @Column VARCHAR(500)
SELECT @Column = COALESCE(@Column + ',', '') + Column1 FROM TABLE1


Date Condition
While checking the date in the condition operator it also check the time. So truncate the time by converting the format.

BETWEEN CONVERT(DATETIME, @CheckInDate, 102) AND CONVERT(DATETIME, DATEADD(DAY, -1, @CheckOutDate), 102)

Drop TempTable if already exists

IF EXISTS(SELECT *
FROM tempdb.sys.tables
WHERE name LIKE '#TempTbl%')
BEGIN
DROP TABLE #temptbl
END

(OR)

IF Object_id('tempdb..#TempTbl') IS NOT NULL
BEGIN
DROP TABLE #temptbl
END

Note: --
Temporary table created in the tempdb database. Delete it at the end of the procedure. Since it occupies some space.

Dynamic Select Field
SELECT propertyid,
propertyname,
CASE
WHEN ( @Conditions = 'StarRate' )
OR ( @Conditions = 'PropertyName' ) THEN starrate
WHEN @Conditions = 'Price' THEN ( SUM(price) )
ELSE starrate
END price,
starrate
FROM vw_basicsearch

--At the end of case statement we are passing all the column names in the Then satement.
--Why,because in the below statement we are ordering columns dynamically.

SELECT *,
Row_number() OVER(ORDER BY(CASE WHEN @Conditions = 'Price' THEN CAST(
price AS
VARCHAR) WHEN @Conditions = 'StarRate' THEN CAST(starrate AS VARCHAR)WHEN
@Conditions = 'PropertyName' THEN propertyname END)ASC)
FROM MASTER

How to implement WHERE and ORDER BY clauses dynamically using CASE statement?
Let’s have a look on table Customers from Northwind database.Customers: CustomerId, CompanyName, ContactName, City, Country, etc.The following stored procedure gets all customers having CompanyName or ContactName equals with a specific value. You need to find a company name or a contact name in database and then to order the results after the field you perform the search.
The following code snippet shows us how to use CASE statement.

CREATE PROCEDURE Getcustomers @field VARCHAR (50),
@name VARCHAR (50)
AS
SELECT customerid,
companyname,
contactname,
city,
country
FROM customers
WHERE CASE @field
WHEN 'CompanyName' THEN companyname
WHEN 'ContactName' THEN contactname
END LIKE 'a%'
ORDER BY CASE @field
WHEN 'CompanyName' THEN companyname
WHEN 'ContactName' THEN contactname
END

And now let’s execute our stored procedure:

EXEC GetCustomers 'CompanyName', 'a'
EXEC GetCustomers 'ContactName', 'a'

ALL Dynamic
DECLARE @ColumnIntValue INT
DECLARE @ColumnValue VARCHAR(100)
AND ( CASE @ColumnName WHEN 'Hotel' THEN PropertyName
END LIKE '%' + @ColumnValue + '%'
OR
CASE @ColumnName
WHEN 'CityID' THEN CityID
WHEN 'CountryID' THEN CountryID
END = @ColumnIntValue)

Here if column dataType is differnet. Declare different dataType local variable and ansign the value to it.Otherwise we wil get
Msg 245, Level 16, State 1, Line 31Conversion failed when converting the varchar value 'Asian Hotel' to data type int.

Dont's

Don't create SELECT column(s) INTO #temp FROM TableHere the SQL Server don't know about the size of each column.It has to analyze and create the size.So create the table before and do the insert.
Note:

SELECT column(s) INTO #temp FROM Table
If our query is like this and if we are calling Execution Plan (CTRL + L), it will show #temp errorBecause it doesnot know the size of the columns.

Start SQLServerAgent
EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'

Checks the current identity value for the all the tables
SELECT 'DBCC CHECKIDENT (' + table_name + ')'
FROM information_schema.tables

SELECT *
FROM sys.objects
INNER JOIN (SELECT *
FROM sys.indexes) AS indexes
ON indexes.object_id = sys.objects.object_id
WHERE sys.objects.TYPE = 'U'



FillFactor: The 'fill factor' option specifies how full SQL Server will make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed), you can set the 'fill factor' option to 100. When the table's data modified very often, you can decrease the 'fill factor' option to 70 percent, for example.

Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT SUM(TotalKM) FROM Table

Change it to

SUM(CASE WHEN TotalKM IS NULL THEN 0 ELSE TotalKM end)

Get the Created and Modified Date of a Stored Procedure

SELECT name,
create_date,
modify_date
FROM sys.objects
WHERE TYPE = 'P'
ORDER BY modify_date DESC


Get all table names from all databases in the server

EXEC sp_MSForEachDb 'select TABLE_CATALOG, TABLE_NAME from [?].INFORMATION_SCHEMA.TABLES'

Get all table names from current database

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

View
Remember that a view using the WITH CHECK OPTION provides some flexibility that can’t be duplicated with a normal CHECK constraint.

Wait for sometime before insertion
WAITFOR DELAY '00:00:00.033'

Round up to 2 decimal places
Convert(DECIMAL(5, 2), round(255.87908765444, 2))

Reseed Identity
DBCC checkident ('PropertyDining', reseed, 0)

sql query works from left to right.

IDENTITY columns in 6.5 suffer a problem if the server is shut down unexpectedlyI usually recommend running DBCC CHECKIDENT on each table when the server starts up.

If we set primark key for a column it automatically creates cluster index for the column

--==========================
-- Created By :
-- Created date:
-- Modified By:
-- Last Modified Date:
-- Module :
-- Screen(s) :
-- Called by :
-- Description:
-- PARAMETERS
-- @ParamName -- Description
-- Return:
--**************************
--** Change History
--**************************
--** PR Date Author Description
--** -- -------- ------- ------------------------------------
--** 1 01/10/2008 Dan added inner join
--********************************

Show Header for GridView For NULL DataSource


We have to desing the gridview header in the Table and put it inside the
EmptyDataTemplate of the gridview as given below.


<asp:GridView ID="GridView2" runat="server" CssClass="th">
<EmptyDataTemplate>
<table>
<tbody>
<tr>
<td>
<b>Name</b></td>
</tr>
</tbody>
</table>
</EmptyDataTemplate>
</asp:GridView>


GridView Create THeader and TBody

protected void Page_Load(object sender, EventArgs e)
{
//Add data to the GridView
GridView1.DataSource = SQLHelper.ExecuteDataset("Data Source=SERVER;Initial
Catalog=DBName; Uid=sa; PWD=12345678; Connect Timeout=360; pooling=true;
Max PoolSize
=200; ", CommandType.Text, "Select * from TblName");
GridView1.DataBind();

MakeAccessible(GridView1);

}

private void MakeAccessible(GridView grid)
{
//HtmlTextWriterStyle style = new HtmlTextWriterStyle();
//TableRow tr = new TableRow();
////tr.Style.Add(HtmlTextWriterStyle
//tr.Style.Add(style ,
if (grid.Rows.Count > 0)
{
//This replaces with and adds the scope attribute
grid.UseAccessibleHeader = true;

//This will add the and elements
grid.HeaderRow.TableSection = TableRowSection.TableHeader;
//grid.HeaderStyle.AddAttributesToRender(


//This adds the element. Remove if you don't have a footer row
grid.FooterRow.TableSection = TableRowSection.TableFooter;
}
}


Another Way/

Form Authentication

Login Through Cookies
Detecting ASP.NET Session Timeouts

            
                
                
            
        

We have to set the value to the attribute path="/" in forms tag. Then only a common authentication works for both the browsers (Internet Explorer and Firefox.)
I.e. we logged in IE and open the page in Firefox it once again ask to logon. To over come this gives the path.

The place the cookies are stored in windows XP.
The default location for Internet Explorer
C:\Documents and Settings\\Cookies

If you set form authentication it give the user name that you gave in Login control other wise it took as windows authentication and gave windows user name.
string webUsername = HttpContext.Current.User.Identity.Name;

    protected void LoginAuthenticate(object sender, AuthenticateEventArgs e)
    {
        //if username and password given by the enduser is available in the
        //database then we can authenticate the user by providing

        e.Authenticated = true;
        AuthenticateUser("UserName");
    }

    protected void AuthenticateUser(string userName)
    {
        /*Providing the FormsAuthenticationTicket "Ticket" to the authenticated
        user which contains version, username, entry datetime and expire date time,
        userdata and cookie path*/
        FormsAuthentication.Initialize();
        FormsAuthenticationTicket Ticket = new FormsAuthenticationTicket
        (1, userName.Trim(), DateTime.Now, DateTime.Now.AddMinutes(90),
        true, userName.Trim() + "," + Page.ClientID,
        FormsAuthentication.FormsCookiePath);

        //encrypt the ticket and assign it into the string variable "hash"
        string hash = FormsAuthentication.Encrypt(Ticket);

        /*FormsAuthentication.FormsCookieName --> This is the name
        that we gave in web.config file*/

        HttpCookie Cookie = new HttpCookie(FormsAuthentication.FormsCookieName, hash);
        Response.Cookies.Clear();

        /*checking whether the cookie is constant,
        If constant then expires the cookie based on the expiration
        time of user specified in ticket
        */
        if (Ticket.IsPersistent)
        {
            Cookie.Expires = Ticket.Expiration;
            Response.Cookies.Add(Cookie);
        }
    }

    //When writing a cookie, use Response but reading may depend on your situation. 
    //Normally, you read from Request but if your application is attempting 
    //to get a cookie that has just been written or updated and the round trip 
    //to the browser has not occured, you may need to read it form Response.

    public void WriteCookie(string name, string value)
    {
        HttpCookie cookie = new HttpCookie(name, value);
        HttpContext.Current.Response.Cookies.Set(cookie);
    }

    public string ReadCookie(string name)
    {
        string[] CookiesKeys = HttpContext.Current.Response.Cookies.AllKeys;
        if (((IList)CookiesKeys).Contains(name))
        {
            HttpCookie cookie = HttpContext.Current.Response.Cookies[name];
            if (cookie != null) return cookie[name];
        }

        CookiesKeys = HttpContext.Current.Request.Cookies.AllKeys;
        if (((IList)CookiesKeys).Contains(name))
        {
            HttpCookie cookie = HttpContext.Current.Request.Cookies[name];
            if (cookie != null) return cookie[name];
        }
        return null;
    }

    //Update Cookie 
    private void UpdateCookie()
    {
        // If the request cookie exists, copy it to the response.
        // Otherwise create a response cookie.
        HttpCookie cookie = Request.Cookies["theCookie"];
        if (cookie == null)
        {
            Response.Cookies.Set(new HttpCookie("theCookie", "SomeValue"));
        }
        else
        {
            Response.Cookies.Set(cookie);
            // Add the expiration date. 
            cookie.Expires = DateTime.Now.AddYears(30);
            // Change the cookie's value 
            cookie.Value = "NewValue";
        }
    }

    private string GetCurrentCookieValue()
    {
        string currentCookieValue = null;
        // Response.Cookies always has the latest values
        if (Request.Browser.Cookies)
        {
            HttpCookie cookie = Request.Cookies["theCookie"];
            if (cookie != null) currentCookieValue = cookie.Value;
        }
        return currentCookieValue;
    }

    protected void ibtnLogedOut_Click(object sender, EventArgs e)
    {
        Session.RemoveAll();
        Session.Clear();
        //Removes the FormsAuthentication ticket from the server
        FormsAuthentication.SignOut();

        #region Removers Cookie When User Log Off
        //Clear cookie when user Logout 
        Response.Cookies.Remove(FormsAuthentication.FormsCookieName);
        #endregion

        ibtnLogin.Visible = true;
        ibtnLogOut.Visible = false;

        Response.Redirect("~/Home.aspx");
    }
Add the below code in Global.asax
    //Code that runs on application startup
    protected void Application_AuthenticateRequest(object sender, EventArgs e)
    {
        if (HttpContext.Current.User != null)
        {
            if (HttpContext.Current.User.Identity.IsAuthenticated)
            {
                if (HttpContext.Current.User.Identity is FormsIdentity)
                {
                    FormsIdentity FID = (FormsIdentity)HttpContext.Current.User.Identity;
                    FormsAuthenticationTicket Ticket = FID.Ticket;

                    //Get the stored user-data, in this case, Page Client ID and UserName
                    string userData = Ticket.UserData;
                    string[] roles = userData.Split(',');
                    HttpContext.Current.User = new System.Security.Principal.GenericPrincipal(FID, roles);
                }
            }
        }
    }
Set the cookie expiration time by giving the session time out
    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
    {
        bool isPersistent = this.Login1.RememberMeSet;
        string username = this.Login1.UserName;
        FormsAuthenticationTicket ticket = new FormsAuthenticationTicket
            (
                1,//Version
                username,
                DateTime.Now,// issueDate
                GetExpirationTime(),// expiration
                isPersistent,// isPersistent
                Guid.NewGuid().ToString("N"), //roles or userData
                FormsAuthentication.FormsCookiePath // cookiePath
            );

        // Encrypt the ticket.
        string encryptedTicket = FormsAuthentication.Encrypt(ticket);

        // Create the cookie.
        this.Response.Cookies.Add(new HttpCookie(FormsAuthentication.FormsCookieName, encryptedTicket));

        Session["UserName"] = username;

        // Redirect back to original URL.
        this.Response.Redirect(FormsAuthentication.GetRedirectUrl(username, isPersistent));
    }

    private DateTime GetExpirationTime()
    {
        double minutes = 0;
        TimeSpan obj;
        minutes = ((System.Web.Configuration.AuthenticationSection)System.Configuration.ConfigurationManager.GetSection("system.web/authentication")).Forms.Timeout.Minutes;
        obj = TimeSpan.FromMinutes(minutes);
        return DateTime.Now.AddMinutes(obj.Minutes);
    }
Note
//Clear cookie when user Logout 
Response.Cookies.Remove(FormsAuthentication.FormsCookieName); 
//Removes the FormsAuthentication ticket from the server 
FormsAuthentication.SignOut(); 
//Note If you set form authentication it give the user name that you gave in Login control. 
//other wise it took as windows authentication and gave windows user name 
//string webUsername = HttpContext.Current.User.Identity.Name; 
//==================================================================== 
//Get Windowns userName 
string str = Request.ServerVariables["LOGON_USER"];
Brute force protect your websites

SQL SERVER Table and Index Architecture

Table and Index Architecture