Wednesday, October 31, 2012

CASE Statement with Example in SQL SERVER

0 comments Posted by Rahul Kharde at 5:23 AM

The SQL CASE statement has WHEN, THEN, and ELSE clauses along with an END terminator.

Example

DECLARE @Gender VARCHAR(50)
SET @Gender='Male'

SELECT
CASE @Gender
          WHEN 'Male'   THEN 'M'
          WHEN 'Female' THEN 'F'
          ELSE  null
END       as 'Gender'

SQL Server compares this @Gender  to the expression  and when the values match, it returns the THEN clauses ‘M’or ‘F’. If none of the WHEN clauses match then it will return ‘null’ value
Read More »

Thursday, October 25, 2012

Generate GUID in SQL SERVER

0 comments Posted by Rahul Kharde at 9:26 AM

Use NEWID() function to create unique guid.

SELECT NEWID()

Example

If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function.

CREATE TABLE Employees
(
      EID   UNIQUEIDENTIFIER,
      ENAME VARCHAR(100)
)

INSERT INTO Employees VALUES(NEWID(),'ABC')

INSERT INTO Employees VALUES(NEWID(),'PQR')

SELECT * FROM Employees



Read More »

Friday, October 19, 2012

What are constraints? What is different types of constraints?

0 comments Posted by Rahul Kharde at 8:40 AM


Constraints are allows us to enforce rules in our database. SQL Server supports 6 types of constraints. A Constraint is a property that we can assign to column in table. By assigning constraint property on column we can prevent the users to enter inconsistent of data in columns. We can assign these Constraint properties during the time of creation of table (By Using CREATE TABLE statement) or during the time of changing the existing table structure (By Using ALTER TABLE statement)

  1.      Primary Key Constraint
  2.      Unique Key Constraint
  3.      Foreign Key Constraint
  4.      Not Null Constraint
  5.      Check Constraint
  6.      Default Constraint



Read More »

SQL DEFAULT Constraint

0 comments Posted by Rahul Kharde at 8:29 AM

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.


Example
The following SQL query creates a DEFAULT constraint on the "AddedDate" column when the "Emp" table is created

CREATE TABLE Emp
(
            EID INT PRIMARY KEY IDENTITY,
            ENAME VARCHAR(100),
            EAGE INT,
            AddedDate DateTime Default GetDate()
)

Insert Record into the Emp table , here we are not specified any value into the “AddedDate

INSERT INTO Emp(ENAME,EAGE)
VALUES('abc',24)

AddedDate will be insert the default value (means the Current Date) into the table 

Now with Column value is specified

INSERT INTO Emp(ENAME,EAGE,AddedDate)
VALUES('abc',24,getdate())




Read More »

Wednesday, October 17, 2012

Primary Key Constraint

0 comments Posted by Rahul Kharde at 11:30 AM
  • Primary key constraint is used to uniquely identify each record in database table. 
  • It won’t allow repetition or duplication of data
  • Every table should have a primary key constraint to uniquely identify each row.
  • Each table is having only one primary key constraint and it contains only unique values.
  • Primary key constraint doesn’t accept null values

Example

Here is the syntax to define EID attribute as a primary key in a Employee table.

CREATE TABLE Employee
(
            EID INT PRIMARY KEY IDENTITY NOT NULL,
            ENAME VARCHAR(50),
            SALARY DECIMAL(10,2),
            DEPT VARCHAR(50)
)


Read More »

NOT NULL Constraint

0 comments Posted by Rahul Kharde at 11:22 AM

NULL is unknown or missing data in table.


When you create a new NOT NULL constraint on a column, SQL Server checks the column’s current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of a NULL value fail.


Example


CREATE TABLE Employee
(
          EID INT PRIMARY KEY IDENTITY NOT NULL,
          ENAME VARCHAR(50) NOT NULL,
          SALARY DECIMAL(10,2) NOT NULL,
          DEPT VARCHAR(50)
)

INSERT INTO Employee(ENAME,SALARY,DEPT)
VALUES('abc',20000,'HR')


The following SQL enforces the "EID" column and the "ENAME, SALARY " column to not accept NULL values: 
 

INSERT INTO Employee(ENAME,DEPT)
VALUES('pqr','HR')

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'SALARY', table 'TaskTrack.dbo.Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.




Read More »

Friday, October 12, 2012

Refresh the aspx page automatically

0 comments Posted by Rahul Kharde at 7:43 AM
Automatic web page refresh can be implemented in an ASP.NET (.aspx) web-page by adding some HTML code in the header section.

<meta http-equiv="refresh" content="15">

Set the time according to your requirement in content="time in sec".

For adding Aspx server side the Page_Load function to set it, as shown below: 

Response.AppendHeader("Refresh", "15")

Read More »

Tuesday, October 09, 2012

Difference between Connected and disconnected architecture

1 comments Posted by Rahul Kharde at 9:27 AM
ConnectedDisconnected
It is connection oriented.
It is dis_connection oriented.
DatareaderDataSet
Connected methods gives faster performance
Disconnected get low in speed and performance.
connected can hold the data of single tabledisconnected can hold multiple tables of data
connected you need to use a read only forward only data reader
disconnected you cannot
Data Reader can't persist the data
Data Set can persist the data
It is Read only, we can't update the data.
We can update data

Read More »
 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top