State Management in .Net

Web form pages are HTTP-Based, they are stateless, which means they don’t know whether the requests are all from the same client, and pages are destroyed and recreated with each round trip to the server, therefore information will be lost, therefore state management is really an issue in developing web applications

We could easily solve these problems in ASP with cookie, query string, application, session and so on. Now in ASP.NET, we still can use these functions, but they are richer and more powerful, so let’s dive into it.

Mainly there are two different ways to manage web page’s state:
1) Client-side
2) Server-side

1.Client-side state management :
--> Cookies
You need to store small amounts of information on the client and security is not an issue.

--> View state
You need to store small amounts of information for a page that will post back to itself. Use of the ViewState property does supply semi-secure functionality.

--> Hidden fields
You need to store small amounts of information for a page that will post back to itself or another page, and security is not an issue.

--> Query string
You are transferring small amounts of information from one page to another and security is not an issue.

2. Server-side state management:

--> Application state object
You are storing infrequently changed, application-scope information that is used by many users, and security is not an issue. Do not store large quantities of information in an application state object.

--> Session state object
You are storing short-lived information that is specific to an individual session, and security is an issue. Do not store large quantities of information in a session state object. Be aware that a session state object will be created and maintained for the lifetime of every session in your application. In applications hosting many users, this can occupy significant server resources and affect scalability.

--> Database support
You are storing large amounts of information, managing transactions, or the information must survive application and session restarts. Data mining is a concern, and security is an issue.

Keep watching for information around state Management...

Lock SQL Server ...3

--> Update
Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a resource, then the update lock is escalated to an exclusive lock, otherwise it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. Using update locks prevents multiple connections from having a shared lock that wishes to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks, but are not compatible with Update locks. Update locks are also used for inserts into a table with a clustered key.
--> Exclusive
Exclusive locks (X) completely lock the resource from any type of access including reads. They are issued when data is being modified through INSERT, UPDATE and DELETE statements. You should try to minimize the time during which any resource is locked exclusively by making your data modifications as short as possible. Exclusive locks are usually the cause of blocking in the applications.
--> Schema
Schema modification locks (Sch-M) are acquired when data definition language (DDL) statements are being executed. This includes modifying tables or views through adding columns, dropping columns, adding or dropping constraints, etc. Schema stability locks (Sch-S) are acquired when queries are being compiled; these will not block any other types of locks, including exclusive locks. Therefore, schema locks will not cause any blocking. However, when queries are compiled, the tables affected by the queries cannot be altered through DDL statements.
--> Bulk Update
Bulk update locks (BU) are used when performing a bulk-copy of data into a table with the TABLOCK hint. These locks improve performance while bulk copying data into a table; however, they reduce concurrency by effectively disabling any other connections to read or modify data in the table. Another way of acquiring BU locks is by specifying the "table lock on bulk load" option with the system stored procedure sp_tableoption.

ROW_NUMBER

ROW_NUMBER Allows you to provide sequential integer values to the result rows of a query. The below code snippet is an example that shows how the ROW_NUMBER() function can provide an ascending number for each row returned when inspecting the Employee view. The ROW_NUMBER() function is nondeterministic.

SELECT ROW_NUMBER() OVER(ORDER BY LastName) AS RowNum,
FirstName + ' ' + LastName
FROM HumanResources.Employee
WHERE JobTitle = 'DatabaseDesign'
ORDER BY LastName

SMS to Cell Phone in VB.Net

I have got couple of mails on building an application in sending out an SMS to cell phones from VB.NET. I thought of writing one but, do i need to!!!! i have google and happen to see the very simple & straight forward code to do it.

Check out the link
http://www.codeproject.com/KB/IP/SendTxtMsg.aspx

WCF

Windows Communication Foundation (formerly code-named "Indigo") is a set of .NET technologies for building and running connected systems. It is a new breed of communications infrastructure built around the Web services architecture. Advanced Web services support in Windows Communication Foundation provides secure, reliable, and transacted messaging along with interoperability. The service-oriented programming model of Windows Communication Foundation is built on the Microsoft .NET Framework and simplifies development of connected systems. Windows Communication Foundation unifies a broad array of distributed systems capabilities in a composable and extensible architecture, spanning transports, security systems, messaging patterns, encodings, network topologies, and hosting models. Windows Communication Foundation is available for Windows Vista™ as well as for Windows XP and Windows Server 2003

More Info http://msdn2.microsoft.com/en-us/library/ms731082.aspx

Lock SQL Server ...2

The following table lists the types of locks available with SQL Server:

Intent --> The intent lock shows the future intention of the lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. Intent locks come in three flavors: intent shared (IS), intent exclusive (IX) and shared with intent exclusive (SIX). IS locks indicate that the transaction will read some (but not all) resources in the table or page by placing shared locks. IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks. SIX locks indicate that the transaction will read all resources, and modify some of them (but not all). This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time, therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource.

Shared --> Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released. Shared locks are normally released as soon as the data is read. However, there are ways to override this default behavior through query hints and transaction isolation levels.

Keep watching for more information...

Lock SQL Server ...1

In order to become a good T-SQL programmer, you need to know how SQL Server manages resources to guarantee data integrity. There are several different kinds of locks SQL Server can use. Certain locks are compatible with others, for instance, the shared locks, acquired with the SELECT statement are compatible with other shared locks - multiple readers can read the data at the same time. Some other types of locks restrict the rest of the users from reading and / or modifying the data.

Locking with SQL Server represents a trade off between concurrency (and thereby, performance) and data integrity. If you are not concerned about the quality of data being read by your users, then you can allow data reads and modifications at the same time. On the other hand, if you need to have highly consistent results when reading your data, then SQL Server will allow for only one user making modifications at one time, therefore reducing the number of users simultaneously accessing data.

Keep watching for more information...

Lock SQL Server ...1

In order to become a good T-SQL programmer, you need to know how SQL Server manages resources to guarantee data integrity. There are several different kinds of locks SQL Server can use. Certain locks are compatible with others, for instance, the shared locks, acquired with the SELECT statement are compatible with other shared locks - multiple readers can read the data at the same time. Some other types of locks restrict the rest of the users from reading and / or modifying the data.

Locking with SQL Server represents a trade off between concurrency (and thereby, performance) and data integrity. If you are not concerned about the quality of data being read by your users, then you can allow data reads and modifications at the same time. On the other hand, if you need to have highly consistent results when reading your data, then SQL Server will allow for only one user making modifications at one time, therefore reducing the number of users simultaneously accessing data.

Keep watch out for next post on the locks...

TOP in SQL Server

TOP keyword is a one of those keywords that has been added by Microsoft to the ANSI SQL. Often developers who are used to T-SQL and are in the process of adapting themselves to PL-SQL find this key word suddenly missing. The TOP keyword is often used to reduce the size of the result set returned by a SQL query. Hence the TOP keyword is the keyword that is applied the last in the sequence of operations performed by the SQL engine. Also the TOP keyword is used to get a certain fixed number or percentage of rows from a table or view.

The TOP keyword is usually followed by an expression which would tell the SQL engine the number of rows of the result set that have to be returned.

SELECT TOP (5) * FROM Northwind.dbo.EMPLOYEE

Duplicate Records

I was looking at the forums and my mail box, the big question that every bods asks me is how to find out the duplicate records.

this is the answer for this query.

SELECT

FROM
tablename
GROUP BY

HAVING
count(*) > 1

JSON

JSON (JavaScript Object Notation) is a lightweight computer data interchange format. It is a text-based, human-readable format for representing simple data structures and associative arrays (called objects). The JSON format is specified in RFC 4627 by Douglas Crockford. The official Internet media type for JSON is application/json.

The JSON format is often used for transmitting structured data over a network connection in a process called serialization. Its main application is in Ajax web application programming, where it serves as an alternative to the traditional use of the XML format.

Although JSON was based on a subset of the JavaScript programming language and is commonly used with that language, it is considered to be a language-independent data format. Code for parsing and generating JSON data is readily available for a large variety of programming languages. The json.org website provides a comprehensive listing of existing JSON bindings, organized by language.

More and more Ajax developers are choosing JSON for their data
transfer instead of XML. Although XML has been the pillar of Ajax (X
is for XML), JSON has become the choice of most developers because it
was particularly built for JavaScript. Compared to XML, JSON is
lighter and will work faster compared to XML. This lightweight
function enables Ajax based websites and applications to run faster.

The reason why JSON is very efficient in data transmission is its
ability to function in itself. By this function we mean that JSON has
the ability to "EXECUTE" without the aid of other functions. XML is
only a format which will be used to transmit data and files, but JSON
is very effective in terms self execution.

More Information : http://www.json.org/

SQL Server Database Recovery Model Part 2

Deciding on the right Recovery model
Now that we know the differences between the three types, it’s essential to know which one to choose. Below are a few guidelines on selecting the most appropriate Recovery Model for your database:

Simple
Select the Simple Recovery model if:
• Your data is not critical and you don’t mind losing some data from the log.
• Space is limited to log transactions.
• Data is derived from other data sources and can be easily recreated.

Bulk-Logged
Select the Bulk - Logged Recovery model if:
• Data is critical, but logging large data loads slows down the system.
• Database is subject to periodic bulk operations and most of it is done during off hours.

Full
Select the Full Recovery model if:
• You need the ability to do a point-in-time recovery.
• Data is critical and no data can be lost.
• You are willing to incur the administrative costs of transaction log backups.

SQL Server Database Recovery Model Part 1

Ever wondered what you’d be facing if one of your Crucial Databases was accidentally dropped off??? Trust me; it would be a disastrous situation!
But the situation would have been deal able if a little bit of attention was taken before hand on selecting the apt recovery model for your database.

So what is the Recovery Model?
A Recovery model is a database property that controls how the transaction log is managed. The model determines how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.

Let’s explore the types of Recovery models available.
There are three of them namely: Full Recovery, Bulk-Logged Recovery and the Simple Recovery Model.
The main difference between all the three models is:
How the transaction log backup is maintained and If there’s a possibility of data loss.

Coming to the basics of each of Model:

The Full recovery Model
This model creates a complete backup of the database as well as an incremental back up of all the changes that have occurred since the last full back up. The best thing about this model is that it’s possible to recover the database to a particular point in time. Selecting this mode would require you to have enough space available for the transaction log to store all the transactions that occur between each backup because the space in the transaction log is only reclaimed when a backup of the transaction log is made.

The Bulk-Logged recovery Model
Bulk-logged recovery is quiet similar to the Full Recovery model but the Bulk copy operations are only minimally logged in this recovery Model thereby giving you better performance, i.e. the SQL Server only logs the minimum necessary to recover the data if a backup is needed and due to this sole reason if a bulk copy operation occurs, point-in-time recovery is not possible.

The Simple recovery Model
The simple model as the name suggests, is the easiest one to manage. It allows only full back ups and the drawback in this case is that there’s no way you can back up only the changes made since the last back up. But considering the fact that the transaction log would hardly become full because of the transactions occurring between the full backups, this model would be beneficial. When using this Recovery Model, the space in the log is reclaimed whenever the database performs a checkpoint.

Union and Union All

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.

Global Temporary Table & Local Temporary Table

A local temporary table has a single hash ('#') at the start of its name. A local temporary table is visible only to the user who created it and is destroyed automatically when that user disconnects.

A global temporary table is denoted by a name starting with two hashes (i.e. '##'). A global temporary table is visible to all users and is deleted automatically when the last user who has referenced the table disconnects.

Microsoft Ajax Repeater Control

Apart from being used in complicated RIAs with colorful interfaces,
Ajax is also used for a more practical use such as data control and
interaction. There are functions that any developer could use to
display data in an Ajax way which does not only look good but has
efficient functions which could not be seen in HTML based sites.

It is relatively easy to build a repeater control. A repeater control
is a binding of XML, JavaScript and HTML codes that could get really
messy if you do not do it step by step. As we have previously pointed
out, Ajax repeater control is quite easy since you just have to create
an XML file and wrap it up with JS and HTML. But XML takes a lot of
line that when you integrate the whole webpage with the repeater in
it, you will never get to understand the whole webpage yourself.

Read More...
http://www.ajaxwith.com/Microsoft-Ajax-Repeater-Control.html

Visual Source Safe

Visual SourceSafe is the ideal version control system for any development team using Microsoft Visual Studio .NET.A file stored in a VSS server is not available via the standard file system, instead, it must be accessed through the VSS client tools - the VSS windows client, the VSS command-line tool, or else some application which integrates with or emulates these client tools.
VSS Database
A VSS Database is a single instance of a VSS server - it's the big black box into which files get placed. All commands to VSS are directed towards a particular VSS Database, and wach database maintains a SRCSAFE.INI file which contains configuration information.

VSS Project
A VSS Database is organised as a tree structure, with each of the nodes of the tree being a VSS Project. Each database contains a single root project, which can branch (to a depth of 15 nodes) into sub-projects.

Working Folder
Because the files stored in VSS are not directly exposed as files, development work on these files takes place on local copies, which are first checked out of VSS, changed, then checked in again. While a file is checked out of VSS, it is can be locked to other developers, preventing file overwriting. VSS also retains historical information about a file's changes, so it is possible to extract and use old versions of a file, or roll back unsuccessful changes. The folder in which a user manipulates his local copy of VSS project files is known as his 'working folder'. Each user will often have a distinct, local working folder for each VSS project, the details of which are held by the VSS client in its SS.INI file. Each working folder also gets populated with a VSSVER.SCC file, which contains version information about the files in that folder. This allows VSS quickly to determine whether or not local files are synchronised with those stored in the VSS database.

Check out
when you 'Check out' a file / project , a writable copy is placed into your working folder. By default, this locks the file to other users, so that while they may 'Get' a copy of the file they cannot themselves check it out. Notice that when you check out (and check in), you have the option to add a comment. This is to help developers keep track of the file changes.

Checking In
When you've finished making changes to a checked-out file / project, then you can check it in again, which writes the file changes to the VSS database. This procedure is also effected in the client tools using a right-click menu. By checking project files out and in using VSS, developers can avoid the situation in which one overwrites the changes made by another.

Hierarchies in SQL

I have recieved couple of mails on how to write hierarchial queries in SQL Server. before i start writing something aorund this i looked at the google and found the following information.

Hierarchies are sometimes difficult to store in SQL tables...things like trees, threaded forums, org charts and the like...and it's usually even harder to retrieve the hierarchy once you do store it. Here's a method that's easy to understand and maintain, and gives you the full hierarchy (or any piece of it) very quickly and easily.

While XML handles hierarchical data quite well, relational SQL doesn't. There are several different ways to model a hierarchical structure. The most common and familiar is known as the adjacency model.

Check out more information on this:
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Isolation Levels in SQL Server

An isolation level determines the degree of isolation of data between concurrent transactions.
4 levels
1.Read committed(default)
Any shared locks you create will be automatically released as soon as the statement that created on them is complete.But with this isolation level unrepeatable reads and phanthoms can still occur.
2.Read uncommitted
Setting isolation level to read uncommitted tells the sql server not to set any locks and not to honour any locks but it gives highest performance interns of speed.
3.Repeatable read
It escalatesyour isolation level and provides an extra level of concurrency ptotection by preventing not only dirty reads but also preventing unrepeatable reads.
4.Serialazable
It prevents all forms of concurrency issues except for a lost update.Even Phanthoms are also prevented.

Full Text Search in SQL Server

Full text search refers to a technique for searching a computer-stored document or database. In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user. Full-text searching techniques became common in online bibliographic databases in the 1970s. Most Web sites and application programs (such as word processing software) provide full text search capabilities. Some Web search engines, such as AltaVista employ full text search techniques, while others index only a portion of the Web pages examined by its indexing system

Full-text Index
* Regular SQL indexes stored under control of the database which they are defined. But full-text indexes stored in the file-system, but administered through the database.
* Only one full-text index can be defined per table.
* Regular SQL indexes, will be updated whenever the new rows has been added, modified, or deleted. But, Full-text index is based on population. Because, full-text service will use more system resources.

Full-text Catalogue
A full-text Catalogue contains full-text indexes in a database. Each catalogue can serve the indexing needs to one or more tables in within a database. A maximum of 256 full-text catalogs can be created on each server.

Full-text enabled Database
A database must be enabled for the full-text service. More than one full-text catalogues can be created and dropped in an enabled full-text database.


Full-text enabled Table
A table should be enabled for full-text support. While creating full-text index, the table will be associated with full-text catalogue. After the table is enabled, you can populate it with the data in columns enabled for the full-text support.

SQL Server limits

The maximum number of columns per base table is 1024
The maximum number of columns per index is 16
The maximum number of columns per SELECT statement is 4096
The maximum number of foreign key table references per table is 253
The maximum number of parameters per stored procedure is 1024
The maximum number of characters allowed for a table name is 128
The maximum number of characters allowed for a column name 128
The maximum number of columns in the view is 250

SQL Server limits

The maximum number of columns per base table is 1024
The maximum number of columns per index is 16
The maximum number of columns per SELECT statement is 4096
The maximum number of foreign key table references per table is 253
The maximum number of parameters per stored procedure is 1024
The maximum number of characters allowed for a table name is 128
The maximum number of characters allowed for a column name 128
The maximum number of columns in the view is 250

Log4et in .Net

log4net is a tool to help the programmer output log statements to a variety of output targets. In case of problems with an application, it is helpful to enable logging so that the problem can be located. With log4net it is possible to enable logging at runtime without modifying the application binary. The log4net package is designed so that log statements can remain in shipped code without incurring a high performance cost. It follows that the speed of logging (or rather not logging) is crucial.

At the same time, log output can be so voluminous that it quickly becomes overwhelming. One of the distinctive features of log4net is the notion of hierarchical loggers. Using these loggers it is possible to selectively control which log statements are output at arbitrary granularity.

log4net is designed with two distinct goals in mind: speed and flexibility


For more information check out the below link
http://logging.apache.org/log4net/release/features.html

Table Difference

TableDiff.exe is a table comparison tool that comes with the sql server.

It's installed in the following location:

"D:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe"if your SQL Server is installed in the program files on D: drive.
I normally install everything on D drive.

This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences:



"D:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"
-sourceserver MyServer1
-sourcedatabase MyDatabase1
-sourcetable MyTable1
-destinationserver MyServer1
-destinationdatabase MyDatabase1
-destinationtable MyTable2
-et DiffsTable


For more information check out the below link
http://msdn2.microsoft.com/en-us/library/ms162843.aspx

NUnit

NUnit is an open source unit testing framework for Microsoft .NET. It serves the same purpose as JUnit does in the Java world, and is one of many in the xUnit family.

NUnit.Forms is an expansion to the core NUnit framework and is also open source. It specifically looks at expanding NUnit to be able to handle testing user interface elements in Windows Forms.

NUnit.ASP is an expansion to the core NUnit framework and is also open source. It specifically looks at expanding NUnit to be able to handle testing user interface elements in ASP.NET.

Example of an NUnit test fixture:

using NUnit.Framework;
using NUnit.Framework.SyntaxHelpers;

[TestFixture]
public class TestOfNUnit
{
[Test]
public void TestMultiplication()
{
Assert.AreEqual(4, 2*2, "Multiplication");

// Equivalently, NUnit offers a new (in v2.4)
// and more intuitive constraint-based assertion syntax:
Assert.That(4, Is.EqualTo(2*2), "Multiplication constraint-based");
}
}

For more information check out the below link
http://www.nunit.com/index.php

Databases in MS SQL Server 2000

-->PUBS
The pubs database is a sample database used extensively by much of the SQL Server documentation. You can safely delete it if you like, but it consumes only 2 MB of space, so unless you’re scrounging for a few more megabytes of disk space. This database is admittedly fairly simple, but that’s a feature, not a drawback. The pubs database provides good examples without a lot of peripheral issues to obscure the central points. Another nice feature of pubs is that it’s available to everyone in the SQL Server community, which makes it easy to use to illustrate examples without requiring the audience to understand the underlying tables or install some new database to try out your examples. You can completely rebuild the pubs database from scratch by running a script in the \Install subdirectory (located right under the SQL Server installation directory). In SQL Query Analyzer, open the file named Instpubs.sql and execute it. You do need to make sure that there are no current connections to pubs, because the current pubs database is dropped before the new one is created.


-->NORTHWIND
The Northwind database is a sample database that was originally developed for use with Microsoft Access. Much of the documentation dealing with APIs uses Northwind, as do some of the newer examples in the SQL Server documentation. It’s a bit more complex than pubs, and at almost 4 MB, slightly larger. As with pubs, you can safely delete Northwind if you like, although the disk space it takes up is extremely small compared to what you’ll be using for your real data. It is recommend leaving Northwind there. The Northwind database can be rebuilt just like the pubs database, by running a script located in the \Install subdirectory. The file is called Instnwnd.sql.


-->MSDB
The msdb database is used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks. In general, other than performing backups and maintenance on this database, you should ignore msdb. All the information in msdb is accessible from the SQL Server Enterprise Manager tools, so you usually don’t need to access these tables directly. Think of the msdb tables as another form of system tables: just as you should never directly modify system tables, you shouldn’t directly add data to or delete data from tables in msdb unless you really know what you’re doing or are instructed to do so by a Microsoft SQL Server technical support engineer.

Databases in MS SQL Server 2000

A Microsoft SQL Server database is a collection of objects that hold and manipulate data. A typical SQL Server installation has only a handful of databases, but it’s not unusual for a single installation to contain several dozen databases. Theoretically, one SQL Server installation can have as many as 32,767 databases. But practically speaking, this limit would never be reached. SQL Server has 6 Built In databases that provide Template Databases, Hold System Information and Backup Information etc. The details of each database is given below.

--> MASTER
The master database is composed of system tables that keep track of the server installation as a whole and all other databases that are subsequently created. Although every database has a set of system catalogs that maintain information about objects it contains, the master database has system catalogs that keep information about disk space, file allocations, usage, systemwide configuration settings, login accounts, the existence of other databases, and the existence of other SQL servers (for distributed operations). The master database is absolutely critical to your system, so be sure to always keep a current backup copy of it. Operations such as creating another database, changing configuration values, and modifying login accounts all make modifications to master, so after performing such activities, you should back up master.

-->MODEL
The model database is 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. If you’d like every new database to start out with certain objects or permissions, you can put them in model, and all new databases will inherit them.

-->TEMPDB
The temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique among all other databases because it’s re-created—not recovered—every time SQL Server is restarted. It’s used for temporary tables explicitly created by users, for worktables to hold intermediate results created internally by SQL Server during query processing and sorting, and for the materialization of static cursors and the keys of keyset cursors. Operations within tempdb are logged so that transactions on temporary tables can be rolled back, but the records in the log contain only enough information to roll back a transaction, not to recover (or redo) it. No recovery information is needed because every time SQL Server is started, tempdb is completely re-created; any previous user-created temporary objects (that is, all your tables and data) will be gone. Logging only enough information for rolling back transactions in tempdb was a new feature in SQL Server 7 and can potentially increase the performance of INSERT statements to make them up to four times faster than inserts in other (fully logged) databases.
All users have the privileges to create and use private and global temporary tables that reside in tempdb. However, by default, users don’t have the privileges to USE tempdb and then create a table there (unless the table name is prefaced with # or ##). But you can easily add such privileges to model, from which tempdb is copied every time SQL Server is restarted, or you can grant the privileges in an autostart procedure that runs each time SQL Server is restarted. If you choose to add those privileges to the model database, you must remember to revoke them on any other new databases that you subsequently create if you don’t want them to appear there as well.

Undocumented Encryption Function

There are some hidden functions in SQL server through which we can encrypt any string and store the same in the table. This will be very helpful in encrypting the user password and other sensitive user data. Encryption supported by SQL server is one way hash. One way hash is nothing but the string encrypted cannot be decrypted. The only way is to compare values with encrypted string.

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'test'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

In the above example @EncryptedPIN will store the cipher Text. The data in this string is not the encrypted string instead it will return the hash code of the supplied plain string.

Undocumented SQL Server Encryption functions

There are some hidden functions in SQL server through which we can encrypt any string and store the same in the table. This will be very helpful in encrypting the user password and other sensitive user data. Encryption supported by SQL server is one way hash. One way hash is nothing but the string encrypted cannot be decrypted. The only way is to compare values with encrypted string.

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'test'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)


In the above example @EncryptedPIN will store the cipher Text. The data in this string is not the encrypted string instead it will return the hash code of the supplied plain string.