SQL replication

SQL replication is a process for sharing/distributing data between different databases and synchronizing between those databases. You can use SQL replication to distribute data to a variety of network points like other database servers, mobile users, etc. You can perform the replication over many different kinds of networks and this won�t affect the end result.
In every SQL replication there are 2 main players called Publisher and Subscriber. The Publisher is the replication end point that supplies the data and the replication Subscriber is the replication end point that uses the data from the Publisher. Depending on the replication architecture a replication can have one or more Publishers and of course any replication will have one or more Subscribers.
MS SQL Server offers several main replication types. The Transactional replication is usually used when there�s need to integrate data from several different locations, offloading batch processing, and in data warehousing scenarios.
Another replication type is the Snapshot replication. The Snapshot replication is commonly performed when a full database refresh is appropriate or as a starting point for transactional or merge replications.
The third important SQL replication type is the Merge replication. The Merge replication is used whenever there is a possibility for a data conflicts across distributed server applications.

SQL NOT IN

SELECT * FROM Employees
WHERE [Last Name] NOT IN (N'Anderson', N'Shepherd');

SQL String Functions

String Functions

Some of the String Functions comes very handy at times. Let us discuss them one by one.

ASCII()

Returns the ASCII code value of the leftmost character of a character expression.

Syntax

ASCII ( character_expression ) 

SELECT ASCII('A') 

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'The Csharp'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END
SET NOCOUNT OFF

Output:

-----------
65
----------- ----
84          T
----------- ----
104         h
----------- ----
101         e
----------- ----
and so on..... 

SQL DateName

DATENAME()

DATENAME() is very common and most useful function to find out the date name from the datetime value.
Example

 

-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'

Output :

 Today Is Saturday
   Month IsAugust

Parameter Array in asp.net

Parameter Array
Parameter arrays allow a variable number of arguments to be passed into a function member. The definition of the parameter has to include the params modifier, but the use of the parameter has no such keyword. A parameter array has to come at the end of the list of parameters, and must be a single-dimensional array. When using the function member, any number of parameters (including none) may appear in the invocation, so long as the parameters are each compatible with the type of the parameter array. Alternatively, a single array may be passed, in which case the parameter acts just as a normal value parameter. For example:
void ShowNumbers (params int[] numbers)
{
foreach (int x in numbers)
{
Console.Write (x+" ");
}
Console.WriteLine();
}
...

int[] x = {1, 2, 3};
ShowNumbers (x);
ShowNumbers (4, 5);
Output:
1 2 3
4 5

private string Concatenate(string separator, params object[] parts)
{
System.Text.StringBuilder buffer = new System.Text.StringBuilder();
string sepValue = "";
foreach (object o in parts)
{
buffer.AppendFormat("{0}{1}", sepValue, o);
sepValue = separator;
}
return buffer.ToString();
}

Output parametersLike reference parameters, output parameters don't create a new storage location, but use the storage location of the variable specified on the invocation. Output parameters need the out modifier as part of both the declaration and the invocation - that means it's always clear when you're passing something as an output parameter.
Output parameters are very similar to reference parameters
Example :
void Foo (out int x)
{
// Can't read x here - it's considered unassigned
// Assignment - this must happen before the method can complete normally
x = 10;
// The value of x can now be read:
int a = x;
}

// Declare a variable but don't assign a value to it
int y;

// Pass it in as an output parameter, even though its value is unassigned
Foo (out y);
// It's now assigned a value, so we can write it out:
Console.WriteLine (y);
Output:
10

Update Trigger

Create TRIGGER Tgr_Update  ON  LeaveDetails


AFTER UPDATE
AS
BEGIN
Select  Top 1 @Empid = Empid from LeaveDetails 


  IF( @Empid > 0 AND @Empid IS NOT NULL)
 
   BEGIN
          update dbo.MasterLeave set TotalLeaveWithoutPay = 0, RemainingLeave = 1
          where Empid =@Empid
  END
END

SQl Dateadd

select dateadd(dd, 30, getdate())

SQl First day of the Month

------------------Take First day of the Month ---------------

DECLARE @pInputDate          DATETIME
DECLARE @vOutputDate        DATETIME


set @pInputDate='4/25/2011' April Month


    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +   CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
    SET @vOutputDate = DATEADD(DD,0, DATEADD(MM, 0, @vOutputDate))
o/p= 1 April 2011

Sql ISDate

select isdate(getdate())

How Take First day of the Month in SQL

------------------Take First day of the Month ---------------

DECLARE @pInputDate          DATETIME
DECLARE @vOutputDate        DATETIME


set @pInputDate='4/25/2011' April Month


    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +   CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
    SET @vOutputDate = DATEADD(DD,0, DATEADD(MM, 0, @vOutputDate))
o/p= 1 April 2011

Copy one SQL data dump to another table

Dump the all data into another table

insert into dbo.LeaveDetails(EmpId,LeaveTypeId,FromDate,ToDate)

select EmpId,1,WorkedDate,TakenDate from dbo.CompensatoryDetails

SQL Date Duration between two Dates

Declare @Fromdate datetime
Declare @Todate datetime
Declare @TopID int
Declare @Duration int


Select top 1 @TopID= LeaveId from dbo.LeaveDetails order by LeaveId desc


select @Fromdate= convert(varchar,FromDate,103) from dbo.LeaveDetails  where LeaveId=@TopID


select @Todate = convert(varchar,ToDate,103) from dbo.LeaveDetails  where LeaveId=@TopID


SELECT @Duration=DATEDIFF(dd, @Fromdate,@Todate)

SQl Date Difference Between two Dates

Declare @Fromdate datetime
Declare @Todate datetime
Declare @TopID int
Declare @Duration int

Select top 1 @TopID= LeaveId from dbo.LeaveDetails order by LeaveId desc

select @Fromdate= convert(varchar,FromDate,103) from dbo.LeaveDetails  where LeaveId=@TopID

select @Todate = convert(varchar,ToDate,103) from dbo.LeaveDetails  where LeaveId=@TopID

SELECT @Duration=DATEDIFF(dd, @Fromdate,@Todate)

SQL Delete

DELETE FROM "table_name"

WHERE {condition}

SQl Insert

INSERT INTO "table_name" ("column1", "column2", ...)

VALUES ("value1", "value2", ...)

SQl UPDATE

UPDATE "table_name"

SET "column_1" = [new value]

WHERE {condition}

SQL TRUNCATE TABLE

TRUNCATE TABLE  Table Name

SQL foreign key

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
 
Table CUSTOMER

column name	characteristic
SID	Primary Key
Last_Name
First_Name

Table ORDERS

column name	characteristic
Order_ID	Primary Key
Order_Date
Customer_SID	Foreign Key
Amount

Script :
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

if you alter the reference:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

SQL INDEX

Indexes help us retrieve data from tables quicker

Syntax:
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

Example:
CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)

If we want to create an index on both City and Country, we would type in,

CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)

GridView Row Colour Change

protected void Gvattachment_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            // Display the company name in italics.
            e.Row.Cells[1].Text = "<i>" + e.Row.Cells[1].Text + "</i>";
            //Display In colour
            e.Row.Cells[2].BackColor = System.Drawing.Color.Aqua;
        }

    }

SQl Unique

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,
SELECT UNIQUE Sales FROM Store_Information

SQl SubString

SUBSTR(str,pos): Select all characters from <str> starting with position <pos>. Note that this syntax is not supported in SQL Server.
SUBSTR(str,pos,len): Starting with the <pos>th character in string <str> and select the next <len> characters.
Assume we have the following table:
Table Geography

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

 
Example 1:
SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';
Result:
's Angeles'
 
Example 2:
SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';
Result:
'an D'

Sql Aggregate Functions

� AVG: Average of the column.
� COUNT: Number of records.
� MAX: Maximum of the column.
� MIN: Minimum of the column.
� SUM: Sum of the column.

SQL Median

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);
Result:

Median

20

SQl Rank

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

How Specify a Rank in Sql table of Data

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

SQL IFNull

Table Sales_Data

store_name	Sales
Store A	300
Store B	NULL

 
The following SQL,
 
SELECT SUM(IFNULL(Sales,100)) FROM Sales_Data;
returns 400. This is because NULL has been replaced by 100 via the IFNULL function.

SQL ISNULL

Table Sales_Data

store_name	Sales
Store A	300
Store B	NULL

 
The following SQL,
SELECT SUM(ISNULL(Sales,100)) FROM Sales_Data;

Table Sales_Data

store_name	Sales
Store A	300
Store B	100

returns 400. This is because NULL has been replaced by 100 via the ISNULL function.

SQl NVL

Table Sales_Data

store_name	Sales
Store A	300
Store B	NULL
Store C	150

 
The following SQL,
SELECT SUM(NVL(Sales,100)) FROM Sales_Data;
returns 550. This is because NULL has been replaced by 100 via the ISNULL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

SQL SubQuery

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

 
Table Geography

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

and we want to use a subquery to find the sales of all stores in the West region. To do so, we use the following SQL statement:

SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')

Result:

SUM(Sales)

2050

SQL LIMIT

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
LIMIT 2;

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
Boston	$700	Jan-08-1999

SQL INTERSECT

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

 
Table Internet_Sales

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

and we want to find out all the dates where there are both store sales and internet sales. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
Result:

Date

Jan-07-1999

SQL CASE

SELECT
  FirstName, LastName,
  Salary, DOB,
  CASE Gender
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
  END
FROM Employees

---------------------------------------------------------------------------------

SELECT
  CASE 
    WHEN GROUPING(Department) = 1 THEN 'Company Average'
    ELSE Department
  END AS Department,
  AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP

SQl Functions

The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name [parameters] 

RETURN return_datatype;  

IS  

Declaration_section  

BEGIN  

Execution_section 

Return return_variable;  

EXCEPTION  

exception section  

Return return_variable;  

END; 

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.

Example :

CREATE OR REPLACE FUNCTION employer_details_func

RETURN VARCHAR(20);

IS 

emp_name VARCHAR(20); 

BEGIN 

SELECT first_name INTO emp_name

FROM emp_tbl WHERE empID = '100';

RETURN emp_name;

END;

SQl SUM() Function

The SUM() FunctionThe SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name

SQL SUM() ExampleWe have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find the sum of all "OrderPrice" fields".

We use the following SQL statement:

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

The result-set will look like this:

OrderTotal

5700

SQL Server DATEDIFF

SQL Server DATEDIFF() Function
Definition and UsageThe DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF(datepart,startdate,enddate)

Where startdate and enddate are valid date expressions and datepart can be one of the following:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

ExampleNow we want to get the number of days between two dates.
We use the following SELECT statement:

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

Result:

DiffDate

61

ExampleNow we want to get the number of days between two dates (notice that the second date is "earlier" than the first date, and will result in a negative number).
We use the following SELECT statement:

SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate

Result:

DiffDate

-61

SQL Server DATEADD

SQL Server DATEADD() Function
Definition and Usage
The DATEADD() function is adds or subtracts a specified time interval from a date.

Syntax

DATEADD(datepart,number,date)

Where date is a valid date expression and number is the number of interval you want to add. The number can either be positive, for dates in the future, or negative, for dates in the past.

datepart can be one of the following:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

ExampleAssume we have the following "Orders" table:

OrderId	ProductName	OrderDate
1	Jarlsberg Cheese	2008-11-11 13:23:44.657

Now we want to add 45 days to the "OrderDate", to find the payment date.
We use the following SELECT statement:

SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate FROM Orders

Result:

OrderId	OrderPayDate
1	2008-12-26 13:23:44.657

SQL DATEPART

Syntax :DATEPART(datepart,date)

Where date is a valid date expression and datepart can be one of the following:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Example:
Assume we have the following "Orders" table:

OrderId	ProductName	OrderDate
1	Jarlsberg Cheese	2008-11-11 13:23:44.657

The following SELECT statement:

SELECT DATEPART(yyyy,OrderDate) AS OrderYear, DATEPART(mm,OrderDate) AS OrderMonth, DATEPART(dd,OrderDate) AS OrderDay, FROM Orders WHERE OrderId=1

will result in this:

OrderYear	OrderMonth	OrderDay
2008	11	11

SQL Date Data Types

• SQL Server comes with the following data types for storing a date or a date/time value in the database:
  
  • DATE - format YYYY-MM-DD
  • DATETIME - format: YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - format: a unique number

SQL Server GETDATE()

SQL Server GETDATE() Function

It Returns the current date and time

---

SELECT GETDATE() AS CurrentDateTime

SQl FORMAT Function

SQL AND & OR Operators

The AND & OR Operators

The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.

---

SELECT * FROM Persons WHERE FirstName='Tove'  AND LastName='Svendson'


SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola'

SQL Combining AND & OR

SELECT * FROM Persons WHERE

LastName='Svendson'

AND (FirstName='Tove' OR FirstName='Ola')

Row Filter in asp.net

 DataView dv = ((DataTable)ViewState["AppliedLeave"]).DefaultView;
            dv.RowFilter = "StatusId=1";
            gvAppliedLeave.DataSource = dv;
            gvAppliedLeave.DataBind();

Data View Row Filter

 DataView dv = ((DataTable)ViewState["AppliedLeave"]).DefaultView;
            dv.RowFilter = "StatusId=1";
            gvAppliedLeave.DataSource = dv;
            gvAppliedLeave.DataBind();

How Take Month from table of data

SELECT EmpId,month( DOJ), year(doj),day(doj) from dbo.Employee

Take Year of the table records

SELECT EmpId,month( DOJ), year(doj),day(doj) from dbo.Employee

Insert Trigger in SQL

CREATE TRIGGER Trigger1 ON job -- basetable
FOR INSERT
AS
INSERT INTO ClientInstruction (ClientOrganizationID,Summary,DateInstructed,ClientInstructionNo,jobid)
SELECT  ClientOrganizationID,InstructionComments, DateInstructed ,ClientInstructionNo,jobid
FROM INSERTED -- fetch from base table (data)

Can you change a Master Page dynamically at runtime? How?

Yes. To change a master page, set the MasterPageFile property to point to the .master page during the PreInit page event.

Where is View State information stored?

In HTML Hidden Fields.

Different 3 ways to get an accurate count of the number of records in a table?

SELECT *  FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

What is data integrity

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?

SELECT *FROM table1


SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2SELECT COUNT(*) FROM table1

What is SQL server agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is the difference between a local and a global variable?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

Difference between Function and Stored Procedure?

User Defined Function  can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF�s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What�s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined.

But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn�t allow NULLs,

but unique key allows one NULL only.

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What is the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table.

4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

All attributes must be directly dependent on the primary keyBCNF:

What is normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

How to get @@error and @@rowcount at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.

And if @@Recordcount is checked before the error-checking statement then @@Error would get reset.

To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is DataWarehousing?

• Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;

• Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;

• Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;

• Integrated, meaning that the database contains data from most or all of an organization�s operational applications, and that this data is made consistent.

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

---

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL Data Types

SQL Aggregate Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

SQL Constraints

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

Datatable Row Delete

Datatable dtAttach=new Datatable()
dtAttach.Rows[0].Delete();
or
dtAttach.Rows[Gvattachment.SelectedIndex].Delete();
dtAttach.AcceptChanges();
Gv.DataSource = dtAttach;
Gv.DataBind();

How Delete a Row in datatable

Datatable dtAttach=new Datatable()
dtAttach.Rows[0].Delete();
or
dtAttach.Rows[Gvattachment.SelectedIndex].Delete();
dtAttach.AcceptChanges();
Gv.DataSource = dtAttach;
Gv.DataBind();

How Datatable Row Delete in asp.net

Datatable dtAttach=new Datatable()
dtAttach.Rows[0].Delete();
or
dtAttach.Rows[Gvattachment.SelectedIndex].Delete();
dtAttach.AcceptChanges();
Gv.DataSource = dtAttach;
Gv.DataBind();

SELECT DISTINCT:

SELECT DISTINCT: It is used to select unique records from a table. Some tables may have duplicate values for a column. Distinct will eliminate these duplications.

Syntax:
SELECT DISTINCT column_name(s) FROM table_name

Example:
SELECT DISTINCT mobile_num FROM Employees

---

SQL TOP clause

TOP clause is used to specify the number of records to return. Usually used for large tables.

Syntax:SELECT TOP number|percent column_name(s) FROM table_name

Example:SELECT TOP 2 * from employee

---

SQL HAVING

HAVING clause is used to specify some condition along with aggregate functions.

Syntax:SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Example:SELECT emp_id,SUM(salary) FROM employee
WHERE employee_name='tom�
GROUP BY emp_id
HAVING SUM(salary)>1500

---

SQL LIKE

LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character.

Syntax:SELECT * FROM table_name WHERE column_name like 'pattern%';

Example:SELECT * FROM employee WHERE emp_name like 'ma%';

---

SQL GROUP BY

GROUP BY: It is used with aggregate functions to group the result-set by one or more columns
.
Syntax:SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

Example:SELECT Emp_id,joiningdt,SUM(salary) FROM employee GROUP BY emp_id,joiningdt

SQL CREATE VIEW

SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Syntax: CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Example:CREATE VIEW [sample] AS
SELECT employeeID,employeeName
FROM employee
WHERE salary > 10000

---

SQL SUBSTRING:

SQL SUBSTRING: The Substring function in SQL is used to capture a portion of the stored data

Syntax:SUBSTR(str,pos,len): Starting with the th character in string and select the next characters.

Example:SELECT SUBSTR(company_name, 3) FROM Company WHERE company_name = �tata�;

---

SQL BETWEEN

SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.

Example

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

---

SQL INTERSECT

SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Syntax:Select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from tables;

Example:Select salary from employee INTERSECT select salary from manager;

---

SQL IN

The IN operator allows you to specify multiple values in a WHERE clause.

Syntax:SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Example:

SELECT * FROM employee
WHERE emp_LastName IN ('james','jones')

---

Sql Order By

ORDER BY: It is used to sort columns of a result set. It sorts columns in ascending order by default. DESC can be used to sort in a descending order.

Syntax:SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

Example:SELECT balance FROM Account ORDER BY balance DESC

---

SQL CONCATENATE

SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields.
Syntax:CONCAT(str1, str2, str3, ...):

Example:SELECT CONCAT(first_name, last_name) FROM employee WHERE salary > 1000

---

SQL MINUS

SQL MINUS returns all rows in the first query that are not returned in the second query. Each statement must have the same number of fields in the result sets with similar data types.

Syntax:Select field1, field2, . field_n from tables MINUS select field1, field2, . field_n from tables;

Example:
Select salary from employee MINUS select salary from manager

---

IF Exists in Sql Server

if exists (select * from appraisal where employee_id=employee_id);
begin
        Update the data into the Table
End
Else
    begin
         Insert the data into the table
   End

SQL EXISTS

SQL EXISTS: If the subquery returns at least one row the EXISTS condition is considered "to be met".

Syntax:SELECT columns FROM tables WHERE EXISTS ( subquery );

Example:

SELECT * FROM employee WHERE EXISTS (select * from appraisal where employee.employee_id = appraisal.employee_id);

What is Replication?

Replication is the process of distributing data from one database to another on the same server or servers connected through LAN or the internet.

Replication is used to synchronize data between databases that may be at remote location.
Using this, you can maintain copies of database at more than one locations.

Maintaining copies of data at different location provide a standby server.

It can be used for load balancing of server by maintaining area specific data independently and replicate later to have single version of data.

SQL query to retrieve all tables of the database.

Write SQL query to retrieve all tables of the database. 

Select name from sysObjects where xtype=�u� 

Define sub-query.

Sub-query is a query within a Query.

Example of sub-query:

Select CustId, Custname From Customer Where Cust_Id IN (Select Doct_Id from Doctor) 

Define COLLATION.

Define COLLATION. 

Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary 

What is a deadlock and what is a live lock?

When two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.          

A livelock is one, where a  request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely  

Difference between DELETE and TRUNCATE commands in SQL Server.

DELETE TABLE is a logged operation, it is a slow process.   Delete can be roll back operation
     

TRUNCATE TABLE deletes all the rows, but it won't log the deletion, conquently it is fast process.          

TRUNCATE TABLE  can't be roll back operation

What is transact-SQL? Describe its types?

1.Data Definition Language (DDL)
2.Data Control Language (DCL)
3.Data Manipulation Language (DML)

Data Definition Language (DDL)
It allows creating, altering and dropping database objects.

Data Control Language (DCL)

It is used to control access to data in the database.
It controls permission on the database objects using grant, revoke or deny statement.

Data Manipulation Language (DML)

It involves retrieval, insertion, deletion, modification in the database.
It includes select, insert, update, and delete command.

What is the difference between UNION and UNION ALL?

UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.

----------------------------Union------------------------------------

SELECT column_names FROM table_name1
UNION
SELECT column_names FROM table_name2

-----------------------------Union All--------------------------------
SELECT column_names FROM table_name1
UNION ALL
SELECT column_names FROM table_name2

Explain GO Command.

Explain GO Command.

GO command indicates the end of the SQL statements. It is used when there are multiple statements to be executed but sent as a batch.
 
Example:
 
SELECT * FROM table1 INNER JOIN table2 ON table1.c1 = table2.c1 WHERE table1.c1 > 10 ORDER BY table1.c1
GO

Explain the Authentication modes of SQL Server

Windows Authentication and Mixed Mode Authentication

1.Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the SQL server is controlled by Windows NT/2000 account or group used when logged in. this means that Windows username and password are used to access the database server.

2.Mixed mode authentication: - Mixed mode allows use of Windows credentials and the local SQL server account.

What are the Authentication Modes in SQL Server?

SQL Server supports two security (authentication) modes:

Windows Authentication and Mixed mode

1. Windows Authentication mode connects to an instance of SQL Server through a Windows NT 4.0 or Windows 2000 user account.

2. Mixed mode (Windows Authentication and SQL Server Authentication) connect to an instance of SQL Server by using either Windows Authentication or SQL Server Authentication.

What is the purpose of SQL Profiler in SQL server?

SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem.

The different purposes of using SQL Profiler are:

It is used to find the cause of the problem by stepping through problem queries.
It is very useful to analyze the cause of slow running queries.
It can be used to tune workload of the SQL server.
It also stores security-related actions that can be reviewed by a security administrator.
SQL Profiler also supports auditing the actions performed on instances of SQL Server.

Different Types of Integrity in Sql Server

Data Integrity,
Entity Integrity,
Domain Integrity,
 Referential Integrity,
 User-Defined Integrity

Entity Integrity

Entity Integrity

Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints

Domain Integrity

Domain Integrity

Domain integrity validates data for a column of the table.
It can be enforced using:

Foreign key constraints,
Check constraints,
Default definitions
NOT NULL.

Referential Integrity

Referential Integrity

FOREIGN KEY and CHECK constraints are used to enforce Referential Integrity.

User-Defined Integrity

User-Defined Integrity

It enables you to create business logic which is not possible to develop using system constraints. You can use stored procedure, trigger and functions to create user-defined integrity.

what is Data Integrity

Data Integrity

Data Integrity validates the data before getting stored in the columns of the table.
SQL Server supports four type of data integrity:

What is user defined datatypes in SQL Server

What is user defined datatypes and when you should go for them?

User defined data types are based on system data types. They should be used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.

Parameters for user defined datatype:

Name
System data type on which user defined data type is based upon.
Nullability

What is Catalog Views? Its purposes

Catalog views return information used by the SQL Server. They are the most general interface to the catalog metadata and provide efficient way to obtain, transform and present custom forms of this information. They however, do not contain information about replication, backup or maintenance plans etc.

Purpose to use catalog views:

• Access to metadata of databases
• Most efficient way to access server metadata
• Catalog view names and column names are descriptive, which enables a user to query what is expected without having extreme knowledge corresponding to metadata.

SQL Server Overview

SQL Server Overview

• SQL Server is a RDBMS, a complete database.
• SQL Server is compatible with structured query language and has rich support for XML.
• SQL Server includes number of features that support ease of installation, deployment, scalability, data warehousing and system integration with other server software.

SQL Server Components

• Database engine-It provides support for data access interfaces like ADO, OLEDB and ODBC. Replication-It enables to maintain multiple copies of data on different computers and also keep data well synchronized.
• DTS-It is for importing and transferring data from multiple heterogeneous sources. It is used to build data ware housing and data marts by the process of extracting data from multiple OLTP systems.
• Analysis services-It organizes data from data warehouse cubes to provide rapid answers to complex queries. Analysis the data stored in data warehouse and data marts.
• Meta data services-It is a set of services that helps to manage metadata.

• Metadata describes meaning and description of applications and processes.

SQL Server 2000 Architecture

• SQL Server has Logical Architecture and Physical Architecture.
• The data is organized into logical design that is visible to user.
• Logical components include objects, users, roles, collations, logins and groups.
• The database itself maintains data as files on disk.
• Logical components Objects include table, data type, view, stored procedure, function, index, constraint, rule, default and trigger.
• Collations Control how character strings are stored physically and the rules by which character are stored and compared.
• Logins-Each database user should have login id to allow database to identify.
• Users are who can connect to database.
• Role is a single unit of users having same permission.
• Physical Architecture-Page and extents describes physical database organisation.
• Page is the fundamental unit of data storage. Page is divided into data rows which contain all data.
• Extents is the place where tables and indexes are stored.

Transact SQL

• Allow us to create and manage all objects.
• Client application communicates with SQL server by transact-SQL statement.
• SQL Server supports 3 types of transact-SQL statement namely DDL, DLL and DML

What is SQL Server

SQL Server Overview

• SQL Server is a RDBMS, a complete database.
• SQL Server is compatible with structured query language and has rich support for XML.
• SQL Server includes number of features that support ease of installation, deployment, scalability, data warehousing and system integration with other server software.

SQL Server Components

• Database engine-It provides support for data access interfaces like ADO, OLEDB and ODBC. Replication-It enables to maintain multiple copies of data on different computers and also keep data well synchronized.
• DTS-It is for importing and transferring data from multiple heterogeneous sources. It is used to build data ware housing and data marts by the process of extracting data from multiple OLTP systems.
• Analysis services-It organizes data from data warehouse cubes to provide rapid answers to complex queries. Analysis the data stored in data warehouse and data marts.
• Meta data services-It is a set of services that helps to manage metadata.

• Metadata describes meaning and description of applications and processes.

SQL Server 2000 Architecture

• SQL Server has Logical Architecture and Physical Architecture.
• The data is organized into logical design that is visible to user.
• Logical components include objects, users, roles, collations, logins and groups.
• The database itself maintains data as files on disk.
• Logical components Objects include table, data type, view, stored procedure, function, index, constraint, rule, default and trigger.
• Collations Control how character strings are stored physically and the rules by which character are stored and compared.
• Logins-Each database user should have login id to allow database to identify.
• Users are who can connect to database.
• Role is a single unit of users having same permission.
• Physical Architecture-Page and extents describes physical database organisation.
• Page is the fundamental unit of data storage. Page is divided into data rows which contain all data.
• Extents is the place where tables and indexes are stored.

Transact SQL

• Allow us to create and manage all objects.
• Client application communicates with SQL server by transact-SQL statement.
• SQL Server supports 3 types of transact-SQL statement namely DDL, DLL and DML