Friday, February 3, 2012

What are the difference between DDL, DML and DCL commands?

DDL


Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
  • CREATE - to create objects in the database
  • ALTER - alters the structure of the database
  • DROP - delete objects from the database
  • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT - add comments to the data dictionary
  • RENAME - rename an object

DML


Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
  • SELECT - retrieve data from the a database
  • INSERT - insert data into a table
  • UPDATE - updates existing data within a table
  • DELETE - deletes all records from a table, the space for the records remain
  • MERGE - UPSERT operation (insert or update)
  • CALL - call a PL/SQL or Java subprogram
  • EXPLAIN PLAN - explain access path to data
  • LOCK TABLE - control concurrency

DCL


Data Control Language (DCL) statements. Some examples:
  • GRANT - gives user's access privileges to database
  • REVOKE - withdraw access privileges given with the GRANT command

TCL


Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
  • COMMIT - save work done
  • SAVEPOINT - identify a point in a transaction to which you can later roll back
  • ROLLBACK - restore database to original since the last COMMIT
  • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Thursday, January 19, 2012

Genereate Serial Number in Sql Server 2000 or 2005 query

In Sql Server 2005 and above version you can generate serial number as:

select row_number() over(order by empid) as serial_Number, empid,empname from emp

In Sql server 2000, you can generate serial number as:

select (select sum(1) from emp a where a.empid<=obj.empid) as serial_number,* from emp obj

Thursday, December 8, 2011

DBCC RESEED Table Identity Value – Reset Table Identity

During application development, we often input dummy data into our database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the truncate command. This will delete data from table and also reset the identity column value to 0.

truncate table [table_name] -- for example truncate table product

But the truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

The other way is...

In this case, first you need to delete data from the child and the master table.

After deleting data, fire this command and it will reset your identity column to 0.

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]) DBCC CHECKIDENT('product', RESEED, 0)

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

DBCC CHECKIDENT (yourtable, reseed, 34)


Friday, November 18, 2011

How to use Web Service in Web Application


Wcf Service:
There is new baby of microsoft in the world. called WCF service. But there is huge Question how to call it in webapplication. thoug it is as easy as we are doing with webservice.
there is a huge differance in the plateform on which webservice is built and on which WCF is service in built.
Differance:
The Main differance bet'n this two services (WebService/WCF) is With WCF Service SOAP messages can be transmitted over a variety of supported protocols including IPC (named pipes), TCP, HTTP and MSMQ. Like any distributed messaging platform,on the other hand in WebService SOAP message can only transmitted via HTTP.
How To Create WCF Service:
To Create WCF service Create
1).Create blank website
2).Right-Click > Add Item >WCF Service
3). Add referance to
System.ServiceModel.
Three files will be created in solution .
A) App_Code/IService.cs
Content:
using System.ServiceModel;// NOTE: If you change the interface name "IService" here, you must also update the reference to "IService" in Web.config.
[ServiceContract]
public interface IService
{
[OperationContract]
string DoWork();

}

B) App_Code/Service.cs
Content:
using System;
using System.ServiceModel.Activation;
// NOTE: If you change the class name "Service" here, you must also update the reference to "Service" in Web.config.
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class Service : IService
{
public string DoWork()
{

return DateTime.Now.ToString();
}
}
C) Service.svc
Content:
<%@ ServiceHost Language="C#" Debug="true" Service="Service" CodeBehind="~/App_Code/Service.cs" %>

And Following lines of Configuration will automatically added to Web.Config file of your web site.
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="ServiceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
behavior>
serviceBehaviors>
behaviors>
<services>
<service behaviorConfiguration="ServiceBehavior" name="Service">
<endpoint address="" binding="wsHttpBinding" contract="IService">
<identity>
<dns value="localhost" />
identity>
endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
service>
services>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
system.serviceModel>
This are the steps to create WCF service.To call WCF service in our application we have to Add bindings in web.Config file & also have to create a class which acts as proxy of WCF service in our application.
How to Consume WCF service in WebApplication:
to call WCF service we have to add following service contract in our web.config file. please copy and paste below configuration tags between <system.serviceModel> system.serviceModel> elements.
Service binding configuration:
<bindings>
<wsHttpBinding>
<binding name="WSHttpBinding_IService" closeTimeout="00:01:00">
binding>
wsHttpBinding>
bindings>
<client>
<endpoint address="http://localhost:54464/SVCTest/Service.svc"
binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IService"
contract="IService" name="WSHttpBinding_IService">
<identity>
<dns value="localhost" />
identity>
endpoint>
client>
Now we have done with configuration of WCF service configuration.now we have to create ClassLibrary which contains proxy class to call WCF service methods.
Steps to create proxy is:
1) on solution Right-Click >Add New Project >Class Library.
2) Add Class and name it ServiceCaller.cs.
3) Add Referance to System.ServiceModel in Class Library project.
4 ) Add following code in that class file.
namespace WCFServices
{
[System.ServiceModel.ServiceContractAttribute(ConfigurationName = "IService")]
public interface IService
{

[System.ServiceModel.OperationContractAttribute(Action = "http://tempuri.org/IService/DoWork", ReplyAction = "http://tempuri.org/IService/DoWorkResponse")] //This line is must
string DoWork();
}
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
public partial class ServiceClient : System.ServiceModel.ClientBase<IService>, IService
{

public ServiceClient(){}
public string DoWork()
{

return base.Channel.DoWork();
}
}
}
Finally now we are done with creating WCF service caller class.I have given sample code for service proxy and configuration elements.But you can also create configuration Using Command line Called svcutil.exe Automatically.
Steps to create configuration and proxy class using Command-line(svcutil.exe):
1) Open Visual Studio 2008 Command Prompt.
Service.cs and output.config will be automaticall create at
Program Files (x86)\Microsoft Visual Studio 9.0\VC.
you can use this Service.cs as ServiceCaller and elements inside output.config as configuration elements for web.config file.
To call WCF service in our application is very easy now.
Just Create object of ServiceClient Class. and you will be able to call methos in WCF service.
Eg.
protected void Page_Load(object sender, EventArgs e)
{

WCFServices.ServiceClient sc = new WCFServices.ServiceClient();
string currentTime = sc.DoWork();
Response.Write(currentTime);
sc.Close();
//Response.Write(DateTime.Now.ToString());

}
Here we GO.
you can find more details of each and every class and terms and Namespace used here in following links.also can find more about making WCF service more secure from the referances given here.

Tuesday, September 6, 2011

Trigger

Create table test (Id int,Name varchar(50))
Create table tblTest (Id int,testvalue varchar(50),Name varchar(50))
Go
Create TRIGGER tritest /* change to ALTER when you edit this trigger */
ON tbltest
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS
BEGIN
UPDATE tblTest SET tbltest.testvalue=tbltest.name FROM INSERTED WHERE inserted.id=tbltest.id
END
Go
insert into tblTest values(2,'TEST','MURLI')

Sunday, September 4, 2011

SQL SERVER – Introduction to SERVERPROPERTY and example

SERVERPROPERTY is very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.

Run following script to see all the properties of server.
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

Thursday, August 11, 2011

Using MERGE in SQL Server to insert, update and delete at the same time

Beginning with SQL Server 2008, now you can use MERGE SQL command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle; it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

SYntex :-
MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE] ON <search_condition>
[WHEN MATCHED THEN <merge_matched>]
[WHEN NOT MATCHED [BY TARGET] THEN <merge_not_matched>]
[WHEN NOT MATCHED BY SOURCE THEN <merge_ matched=""> ]

Example:-

--Create a target table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO


--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO