creative developer(SQL-DOTNET)

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views. 1.                  Super Key Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example :  Primary key, Unique key, Alternate key are subset of Super Keys. 2.                 Candidate Key A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key. Example:  In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key. 3.                 Primary Key Primary key...

SQL SERVER – 2005 Locking Hints and Examples Jan 23, 2013 by Suraj Locking Hints and Examples are as follows. The usage of them is same same but effect is different. ROWLOCK Use row-level locks when reading or modifying data. PAGLOCK Use page-level locks when reading or modifying data. TABLOCK Use a table lock when reading or modifying data. DBLOCK Use a database lock when reading or modifying data. UPDLOCK UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read. XLOCK Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction. HOLDLOCK Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required. NOLOCK This does not lock any object. This is the default for SELECT operations. It does not apply to I...

Data Type Syntax Explanation (if applicable) integer integer smallint smallint numeric numeric(p,s) Where  p  is a precision value;  s  is a scale value. For example, numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after the decimal. decimal decimal(p,s) Where  p  is a precision value;  s  is a scale value. real real Single-precision floating point number double precision double precision Double-precision floating point number float float(p) Where  p  is a precision value. character char(x) Where  x  is the number of characters to store. This data type is space padded to fill the number of characters specified. character varying varchar...

SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server January 4, 2012 by  s uraj A common question – I often get from Oracle/MySQL Professionals: “What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?” Here is a common question I often get from SQL Server Professionals: “What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?” Very simple question but have showed up so frequently that I feel like to write about it. Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine. You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server ( Reference Link ). CURRENT_TIMESTAMP is an ANS...

SQL SERVER – Importance of Database Schemas in SQL Server January 4, 2013 by  s uraj Beginning with SQL Server 2005, Microsoft introduced the concept of database schemas. A schema is now an independent entity- a container of objects distinct from the user who created those objects. Previously, the terms ‘user’ and ‘database object owner’ meant one and the same thing, but now the two are separate. This concept of separation of ‘user’ and ‘object owner’ may be a bit puzzling the first time one encounters it. Perhaps an example may better illustrate the concept: In SQL Server 2000, a schema was owned by, and was inextricably linked to, only one database principal (a principal is any entity or object that has access to SQL Server resources, for example a user, role or a group). This meant that if, say, a user creates a table in the database, that user cannot be deleted without deleting the table or first transferring it to another user. But in SQL Server 2005 one can n...

Database Mirroring in SQL Server 2008 What is Database Mirroring? Database mirroring is the feature in SQL Server 2005 and SQL Server 2008 that provides a high availability solution for Databases. This feature can be enabled and used only on a database with Full recovery models. The database can be mirrored from one SQL Server instance to another SQL Server instance. The source instance is called Principal server; the target instance is called Mirrored server. We could have one more server called Witness server--we will talk about that in later part of this article series. How does database mirroring work? The principle server sends the active transaction log record to the mirrored server. The mirrored server applies the transaction log record one by one in sequence. Modes of Database Mirroring Database mirroring can be configured in two different modes, High-Safety mode also known as synchronous mode and High-Performance mode also known as asynchronously. The term sync...