Programming Solutions

You can able to learn use of joins in real time.To explore the concept Click Here

Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers. Types of DML Triggers After Trigger (using FOR/AFTER CLAUSE) This trigger fires after SQL Server completes the execution of the action successfully that fired it. Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger. Instead of Trigger (using INSTEAD OF CLAUSE) This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/updat...

In MS SQL for copying table data from old table to new table. You have to use the following query mentioned below SELECT * INTO newtablename FROM oldtablename But for copying only the structure of old table to new table without data in it. Then you have to use the following query SELECT * INTO newtablename FROM oldtablename WHERE 1=2 I hope this article will help you.

SQL constraints are used to specify rules for the data in a table. A constraint is usually associated with a table and is created with a ADD CONSTRAINT SQL statement. The Syntax of creating CONSTRAINT in a table is CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... ); In SQL, we have the following constraints: NOT NULL - Indicates that a column cannot have NULL value. UNIQUE - Ensures that each row for a column must have a unique value. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table. CHECK - Ensures that the value in a column meets a specific condition. DEFAULT - Specifies a default value for a column. For finding constraint in MS SQL,You have to use the following query SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA. TABLE_CON...

Collation:- . Collation is about comparison between characters. It defines a set of rules to compare characters of a character set.A SQL Server collation defines how the database engine stores and operates on character and Unicode data. After data has been moved into an application, however, character sorts and comparisons done in the application are controlled by the Windows locale selected on the computer. The physical storage of character strings in Microsoft SQL Server 2000 is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. For checking database collation we can write the following query SELECT CONVERT (varchar, SERVERPROPERTY('collation')) By changing the collation of database we can change the working of functions in database. For example :- we have employee table and in that there are column as employee(empid,empname,salary,department) Now we want...

A Subquery or Inner query or Nested query is the query within query is referred as subquery Subquery are of two types 1. Co-related Subquery 2. Non Co-related Subquery Co-related Subquery Correlated Subquery is a subquery that uses values from the outer query. In this case the inner query has to be executed for every row of outer query. For example we have table with it's columns named as Emp (Empid,Empname,Salary,Deptid) Now if we want to display the Employee ID,Employee Name,Salary of all employees whose salary is above average for their departments. Then like as above situations we have to use co-related subquery.Query will as shown below SELECT EmpID,Empname,Salary FROM Emp AS A WHERE Salary > (SELECT AVG(salary) FROM Emp WHERE Deptid= A.Deptid); Here we achieved our target and get required result. Note : In Co-related subquery inner will never executed without outer query. Non Co-related Subquery Non Co-related Subquery is the subquery in ...

All and Any operator are used in Subquery. When a scalar value has to be compared with a single-column set of values, we usually use IN or JOINs. In addition to that, we can extend the comparison by using ANY and ALL operators which have rarely been used. These two operators work differently, understanding them would be beneficial to all of us, avoiding complexity of queries. ANY operator returns true when the scalar value matches with any value in the single-column set of values. ALL operator returns true when the scalar value matches with all value in the single-column set of values. The comparison can be extended with , and operators too. We will understand this by example as mentioned below: Consider two tables as Table1 (tb1Id) Table2 (tb2Id) We have tb1Id value as 1,2,3,4 and 5 in Table1 and tb2Id value as 1,3 and 5 in Table2 Now to understand this concept we will write study some query and their output. ---------------------------------------------------...

Find Numeric value in column Imagine that we want to check numeric value entered in any column of table,then for fetching numeric value from column which contain alphabets and numeric value in it.Then we have to use ISNUMERIC() function. This function will return 0 for non-numeric value and 1 for numeric value. Syntax for above condition as below: SELECT columnname FROM tablename WHERE ISNUMERIC(columnname) = 1; Consider that we have employee table with column Emp_id,Emp_name,Salary,City and Phone. Now we can find out employee name which contain only numeric value in their name by the following query. SELECT Emp_Name FROM employee WHERE ISNUMERIC(Emp_Name) = 1; The above query will display only those employee name which contain numeric value in their name. I hope this article will help you.

Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row. Life Cycle of Cursor 1. Declare Cursor : A cursor is declared by defining the SQL statement that returns a result set. Syntax to Declare Cursor Declare Cursor SQL Command is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below DECLARE cursor_name CURSOR [LOCAL | GLOBAL] --define cursor scope [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward) [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks FOR select_statement --define SQL Select statement FOR UPDATE [col1,col2,...coln] --define columns that need to be upd...

Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like as table.We can make index, trigger on view. In Sql Server we make views for security purpose since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make view. One more advantage of Views is,data abstraction since the end user is not aware of all the data present in database table. Syntax for View CREATE VIEW view_name AS select_statement [] Views are of two types 1. System defined view - System defined Views are predefined Views that already exist in the Master database of Sql Server. These are also used as templa...

Generating new column as serial number For generating new column as serial number in output with other column in table. Syntax for above condition as below: SELECT @a:=@a+1 serial_number,Columnname FROM tablename, (SELECT @a:= 0) AS aliasname; Consider that we have employee table with column Emp_id,Emp_name,Salary,City and Phone. Now we can generate six columns in output in which serial number will be your first column by the following query. SELECT @a:=@a+1 serial_number,Emp_id,Emp_name,Salary,City,Phone FROM employee, (SELECT @a:= 0) AS serial_number; The above query will display generate six columns in output of employee table. I hope this article will help you.

Individually set auto increment value of column in table in MS SQL. For this we have set IDENTITY() property of any column.In that property the first argument refer to the starting position of column which auto increment and second argument refer to the difference between two values being inserted in the column. Syntax for creating table as below: For example:- CREATE TABLE [dbo].[usertype] ( [recid] BIGINT NOT NULL IDENTITY(5, 10), [usertype] VARCHAR (50) NOT NULL, PRIMARY KEY CLUSTERED ([recid] ASC) ); Then when we insert record in it as Syntax for inserting data in table as below: INSERT INTO usertype (usertype) VALUES ('client'),('programmer'); then the table data will be as follow Hence we can set the starting position and difference between the two values of any autoincrement column in table in MSSQL. I hope this article will help you.

Question ==> What is difference between temporary table and views? If the query is "long" and you are accessing the results from multiple queries, then a temporary table is the better choice. A view, in general, is just a short-cut for a select statement. If does not imply that the results are ever run and processed. If you use a view, the results will need to be regenerated each time it is used. Although subsequent runs of the view may be more efficient (say because the pages used by the view query are in cache), a temporary table actually stores the results. In SQL Server, you can also use table variables (declare @t table . . .). Using a temporary table (or table variable) within a single stored procedure would seem to have few implications in terms of security, simplicity, and column names. Security would be handled by access ...

There are Inserted and Deleted logical tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table. Use of logical tables Basically, logical tables are used by triggers for the following purpose: 1. To test data manipulation errors and take suitable actions based on the errors. 2. To find the difference between the state of a table before and after the data modification and take actions based on that difference. Inserted logical Table The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table. Suppose we have Employee table as below. Now We need to create two triggers to see data with in logical tables Inserted and Deleted. CREATE TRIGGER trg_Emp_Insert ON Employee FOR INSERT AS BEGIN SELECT * FROM INS...

SQL Statements SQL is divided in four statements.These statements are used for getting definition of database,for manipulation of database tables data,for giving control over database and for controlling transaction performed in database.these types of statements in database are referred as SQL Statements. SQL Statements are of four types 1. Data Definition Language(DDL) 2. Data Manipulation Language(DML) 3. Data Control Language(DCL) 4. Transaction Control Language(TCL) Data Definition Language(DDL) This statement in database are used for creating,altering or modifying the tables in database. These are as below: 1. CREATE - For creating tables,index,procedure in database. 2. ALTER - For alter or modify tables and their structure. 3. DROP - For removing tables,database or any object in database. Data Manipulation Language(DML) This statement in database are used for manipulation of data in tables.They affect the data in database.They are used for selecting,up...