how to create structure of database in sqlserver

Creating other database structure using existing data base using sqlserver is pretty simple compare to other.As you can see the below image ,the existing data base is appeared in sql server.In this example the existing  DB has 15 tables .Now i will place the same structure in to other DB.

To create structure we need to create a script for existing database.For this we have to select the generate script option.Then we can create script as in below

 

 To create a nee DB with this query we need to update the database name with desired DB name.Here i have changed to TestDB1.
                                      

 

Here you can observe the structure of the new database (TestDB1) which is complely similar to the structure of  existing one

how to get duplicate records from a table in sql server

When i insert the bulk amount data from one table into another table i got problem with existing data.Here in the "products" table the "prod_code" column has already some data of import table(sales_import).For this i have checked the count of redundant(already exists) data in existing table.In this post i have given a query to get the existing data(products) in the table compare to import table(sales_import).

select count(*) from products a where exists
(
 select  1 from [sale_Import] b where a.prod_code = b.[Title ID]
)

If you want to get tha data you have to use * instead of count(*)

database is in transition. try the statement later

 This message will get when the data base is set to offline.if we set the this property to particular database ,we can not do any transaction queries on it.

 

so the solution for this one  is to restart  the SQL server or to wait till then up to the transition process completed.The transition process time takes process.,so if you want to do any queries on it immediately ,SQL server restart is better solution as my point of view.

SQL server 2005==>goto SQL server configuration surface area==>restart the server

SQL server2008  ==>go to configuration  tools==>select SQL server==>restart

View with check option in sql server

When a view was created without"with check option" then it will not check the condition specified in the select statement of the  view during insert,update,delete when you want to check the condition specified in  select statement of the view during insert,update&delete you must create the view with check option
Alter view
To alters a view use the alter view command that has the same syntax as create view command

Alter View<View Name>[(col1,.)][with Encryption] as <select statement>[with check option]

exists and not exists in sql server

The predicate EXISTS will return true when the sub query return at least one row and otherwise is returns false
NOT EXISTS will return true when the sub query return no  rows and otherwise is returns false
The below example display the details of emp to when at least one employee is reporting

select *from emp M where EXISTS(select *from emp E where E.no=M.empno)

Display the details of all departments in which there are no emps

select *from dept where not exists(select *from emp e where e.deptno=d.deptno)

login failed for user 'sa' in sql server

This error will arise the below scenarios have done.
1.The user name and pass word is incorrect
2.We don't have an user account with this name

To resolve this error we need to create a new login user using server management studio.Here i will show how to create a user with images
After login in to the server with windows authentication ,we have to collapse the Security menu then right click on login and select New login.We can see the below image how we doing this process.

Give the login name and select the sql server authentication ,then give password for user.After complete this process we can give some roles to this user by selecting server Roles which on top most left side menu item.By default the public role  has assigned to user here.

Finally we can see the created user under login tab as in the below screen.By using created credentials we can login into sql server

how to sort data in sql

When we insert random data into any table, now in order to sort those elements in the table we can use the following query.TO achieve this we create a temporary table using sql statement.

declare @sorttable table(names char(10))

After creating the table we insert the data into table we created above.

insert into @sorttable select 'bhaskar' union all select 'siva' union all select 'krishna' union all select 'anil'

Now to sort the above data we use the following statement

select names  from @sorttable  order by left(names,1)

how to round a number in sql server

Here i will  give a simple query to round  the number in sql.For this we have a sql function  ROUND() is used to convert to near number if the number is decimal number
Query:
select CEILING(21.8) as FirstRounupvalue

Result:22

In the above image i just give an example to round  three number in one statement.you can see the output as in below columns in results.The ceiling is convert the decimal number to nearest maximum number.If you want round to number minmum number ,we have to use FLOOR.
Query:
select FLOOR(21.8) as AfterFloorNumber

Result:21

Find list of primary keys in a database in sql server

Here i will show how to get the all list of existing primary key field's in data base in sqlserver.For this i have write a simple sql query to get constraint in tables.Here i have three table which are emp,Department,city.

Sql query:

USE [Test Krishna]

SELECT pk.TABLE_NAME AS TableName,

pk.CONSTRAINT_NAME AS PrimaryKeyFieldInTable FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk

WHERE pk.CONSTRAINT_TYPE ='PRIMARY KEY' 

Sql Server:How to reverse a string

Here i will show how to reverse a string using sql function.There is a function "REVERSE" is used to done this task in sql.I declared a string "mystring" then pass the value "bhaskar" to it .The output after will like "raksahb

Syntax:

DECLARE @mystring varchar(50)
SET mystring="Bhaskar"
SELECT REVERSE (@mystring) as ReversedString

How to Convert SQL Table Data To XML Format in sqlserver

Here i will show how to get the xml output from sql data table.For this we can use FOR XML AUTO statement.The AUTO is one of mode of FOR XML.This sql statement get the emp table data ,then convert in to xml.This can be see in the black rectangle in below screen shot.

Query: select*from emp FOR XML auto

sql server:how to create a foreign key relationship

Here i will show how to create a foreign  key relation between two tables in server.The parent table must have the primary key field when we will giving the foreign key with child table.For this we have to right click on the child table ,select relationships then  we will get a pop like as in screen.In this pop up we have an option "Table and column specification" ,,then we have to select circled option  which is right to blue color field.

 

We will get an option to select which  tables has to be involved in  foreign key relation as in below screen.Here the emp ,table_3 are table's are used

 The ID is the primary key field in emp table is foreign key relation with id in table_3 table.we can see this process as in below 2 images

After all process has done ,we can see the which table and columns are involved in this relationships

A network-related or instance-specific error

In previous articles i have explained  login failed for user 'sa' in sql server,.This is the common error will occur while establishing a connection to sqlserver. To resolve this problem we need to change network configuration  settings.Here i will explain how to change remote connection settings in configuration area .First we need to check the given data source name is correct or not in connection string.The TCP/IP protocol should be enable to made such type connection.The following steps will describe how to enable TCP/IP protocol in sql server configuration manager
Go To-->Configuration Tools folder which is in MS Sql Server 2008 folder in start menu

2.Select server Network configuration-->click on protocols for SQLEXPRESS.

Then we can see the protocols at Right side panel.By default the TCP/IP status is in disable mode.So we need to change status to enable

indexes in sql server

By using index: it will traverse through the index tree structure to find the required data and extract the data that satisfy the query criteria.

Indexes: indexes in sql server are similar to index in a book. Indexes are used to improve the performance of quires.
Indexes are generally created for following columns.
1). Primary key column
2). Foreign key column: Frequently used in join conditions
3). Column which are frequently used in where clause
4). Columns which are used to retrieve the data in sorting order
Indexes are cannot be created for following columns
The columns which are not frequently used in where clause.
Columns containing the duplicate and NULL values.
Columns containing images, binary information, and text information.
Types of indexes
1). Clustered index
2). Non-clustered index
3). Unique index
4). Composite index
1). Clustered index: Only one clustered index is allowed for a table. The order of values in a table. Order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.
Syntax:
Create clustered index <index_name> an <table_name> (columns)
Create clustered index emp_clindex an emp (empno)
2). Non-clustered index:
It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.
Max no of non – clustered indexes allowed for table is 249.
Syntax:
Create non-clustered index <index_name> on table_name <columns>
Create non-clustered index emp_sal on emp (deptno, sal)

3). Unique index:
An index with unique constraint. It will not allow duplicate values.
Syntax:
Create unique index <index name> on <tablename> (column)
Create unique index dept_index on dept (dname)

How to create comma delimited list from table column || Display multiple row data from database into single row

I have two tables which are advertiser and orders.The advertiser table has multiple order's data.so here my requirement is have  to display the order id's list of advertiser with comma delimited in sql.For this i have used XML path elements in sql.

SELECT DISTINCT Advertiser_Id,
(substring( (SELECT ', ' + cast(Order_Id as nvarchar)FROM Dfp_Main_Data e2 WHERE e2.Advertiser_Id = e1.Advertiser_Id group by Order_Id FOR XML path(''), elements),2,500)) as orders ,
o.Notes FROM Dfp_Main_Data e1,Dfp_Orders o  where e1.Order_Id=o.OrderId and e1.Advertiser_Id='"+adverid+"'";

Granting Privileges to a role in sql server

To grant privileges to a role use the same syntax as granting privileges to a user except that in the place of user name specify the role name.Here i will shown a example to grant various permissions on the tables orders,sales and customers to the role myrole

Grant select,Insert,Update(name,job) on orders to myrole
Grant select,Insert,Update on sales to myrole
Grant select,Insert,Update on customers to myrole

Adding a member to the role:To add a member to the role use the procedure sp_addrolemember

sp_addrolemember 'rolename','username'

if you want to delete the role myrole then the below syntax will be used

sp_droprole 'myrole'

Return values from a sql server stored procedure

If you want to return a value from a procedure ,the parameter which return the value has to be declared using the output class
note:A procedure can return one or n values at a time

procedure test(@x int,@y int output)
                |         |
              input     output
              Parameter  Parameter
Create procedure process(@x int,@y int,@z int output)
As
Begin
SET @z:@x+@y
END
//calling the above procedure
Declare @a int
EXEC Process 100,50,@A output
print @A

Note:
While returning a value from a procedure you don't require to use any return statement .If you assign the values to the output parameter ,procedure only will take the responsibility of returning the values

Procedure with multiple output parameters:
Here i will show a procedure which can be used for inserting the id,name,sales and city into orders table

Create procedure insert_orders(@id int,@name varchar(50),@sales money,@city varchar(50) 
AS
BEGIN
Insert into orders(Id,name,sales,city)values(@id ,@name,@sales,@city)
END
*calling above procedure
EXEC insert_orders 1,'aaa',3000,US

Insert multiple rows using a single INSERT statement

The common thing That all have you done using Insert command is to insert the data into data base table .But the thing here is "can we store the data into multiple tables using single Insert command?".we can say surely "it can possible ". Recently i had a requirement to store the data into different tables using single INSERT query.Here i will show how to insert the data into multiple table using single select statement in sql

Example:

INSERT ALL
  INTO employee(empid, empname) VALUES (1, 'Developer')
  INTO Departments (Deptid, Deptname, category) VALUES (15, 'Information 
Technology', 'Computers')

 SELECT * FROM dual;

The above query will insert one row into the employee table and one row into the
Departments table.

"data not found" error in PLSQL

When we working with PLSQL statements some common errors will raise.In this one of the common Error in PLSQL is " data not found" .It will arise when there is no particular field in table. Here i will shown an example how to prevent the exception when a record is not found in PLSQL code.
Example:

select Id,Name, Address from Details where Address = City.Address;

Solution:
The above example give an error because of the record"Address" is not found.So for this we have to get the count of records then using the condition we will execute the PL/SQL code

select count(1) into testcount from Details where Address=city.Address;

if testcount>0 then 
select Id,Name, Address
from Details
where Address = City.Address;
end if;

R3MQ7XNAWTMR

Intersect and Except in sql server

These are called as Set operators.The set operators are used to perform mathematical set operations on the data available in the tables .To work with set of operations we need to write multiple select statements the data retrieved by each select statement will be treated as one set and then set operator are apply on those sets.Sql Server supports three set operators
INTERSECT: The set operator INTERSECT will written the values that are common in both the sets by eliminating duplicate values.Here i will show an example to find the jobs that are available in both the departments 40 and 30

select Job from emg where deptno=30
INTERSECT
select Job from emg where deptno=40

EXCEPT:
The set operator INTERSECT will written the values that are available in first but not in second set.Here i will show an example to find the jobs that are available in first

select Job from emg where deptno=30
EXCEPT
select Job from emg where deptno=40