Storing the password in secure form and reading it back

• Create the table which have fields UserName and Passwords and set Password field Varbinary

CREATE TABLE [dbo].[User] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Password] [varbinary] (255) NULL ,) ON [PRIMARY]GO

• Now store UserName and Password by using new method pwdencrypt().

INSERT INTO UserEncrycptTable(UserName,[Password]) VALUES ( 'Firoz',pwdencrypt('PasswordFiroz'));

• When we execute the above query and open the table you will notice that password is not in plain text.
• Now we will retrive this password usin method using the method pwdencrypt().

DECLARE @varPassword varbinary(255)SELECT @varPassword = [Password] FROM UserEncrycptTable where UserName = 'Firoz'

DECLARE @chkPassword varchar(255)

SELECT @chkPassword = 'PasswordFiroz'

PRINT pwdcompare(@chkPassword, @varPassword, 0);

Tips for increase performance of SQL database

1. Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).
2. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.
3. Don’t index anything else (yet).
4. Unless you need a different behaviour, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.
5. Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.
6. Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
7. Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.
8. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.
9. Avoid NOT IN, instead use a left outer join - even though it’s often easier to visualize the NOT IN.
10. If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.
11. Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.
12. Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.
13. Avoid index and join hints.
14. When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.

How To Retain Password Field Data during round trips to server

Preserve Password Field Data

When Use the password text box on the ASP.NET page and want to preserve the value of the password field during round trips to server; Use that line of code in he block of code through which server roun trip happen

txtPassword.Attributes.Add("value", txtPassword.Text.ToString());

Use "App_Offline.htm" feature while updating a web site

Use "App_Offline.htm" feature while updating a web site

"App_Offline.htm" feature provides a super convenient way to bring down an ASP.NET application while you updating a lot of content or making big changes to the site where you want to ensure that no users are accessing the application until all changes are done. The way app_offline.htm works is that you place this file in the root of the application. When ASP.NET sees it, it will shut-down the app-domain for the application and instead send back the contents of the app_offline.htm file in response to all new dynamic requests for the application. When you are done updating the site, just delete the file and it will come back online.