SqlDataAdapter da; DataTable dt= new DataTable(); int counter = 0;
in pageload event call this bind() method:
bind() { SqlConnection con = new SqlConnection("server=.;database=demo;integrated security=true;"); con.Open(); da = new SqlDataAdapter("select * from emp", con); da.Fill(dt); con.Close(); DataList1.DataSource = dt; DataList1.DataBind(); }
What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
TRUNCATE
• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
• TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.
• TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
• TRUNCATE cannot be rolled back.
• TRUNCATE is DDL Command.
• TRUNCATE Resets identity of the table
DELETE
• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
• DELETE Can be used with or without a WHERE clause
• DELETE Activates Triggers.
• DELETE can be rolled back.
• DELETE is DML Command.
• DELETE does not reset identity of the table.
Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact‐SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
Name 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 < style="font-weight: bold; color: rgb(255, 102, 0);">Difference between stored procedure and function?
1>Procedure can return zero or n values whereas function can return one value which is mandatory.
2>Procedures can have input, output parameters for it whereas functions can have only input parameters.
3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4>Functions can be called from procedure whereas procedures cannot be called from function.
5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6>we can go for transaction management in procedure whereas we can't go in function.
7>Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
8>Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions.
CREATEFUNCTION dbo.Function1
(
/*
@parameter1 datatype = default value,
@parameter2 datatype
*/
)
RETURNS /* datatype */
AS
BEGIN
/* sql statement ... */
RETURN /* value */
END
Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
• 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
Table : "Orders"
Table : "Orders"
1. Average value of the "OrderPrice" fields.
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
2. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Nilsen'
3. The FIRST() function returns the first value of the selected column
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
Workaround if FIRST() function is not supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1
The LAST() function returns the last value of the selected column.
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
Workaround if LAST() function is not supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1
5. The MAX() function returns the largest value of the selected column.
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
6. The MIN() function returns the smallest value of the selected column.
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
7. The SUM() function returns the total sum of a numeric column.
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
GROUP BY Statement
The GROUP BY statement is used in conjunction (Join together) with the aggregate functions to group the result-set by one or more columns. - find the total sum (total order) of each customer.
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
GROUP BY More Than One Column
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY customer,OrderDate
HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. - find if any of the customers have a total order of less than 2000.
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000
Scalar functions
SQL scalar functions return a single value, based on the input value.
• 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
ROUND (): SELECT ROUND(UnitPrice,0) as UnitPrice FROM Products
The ROUND () function is used to round a numeric field to the number of decimals specified.
Convert 12.20 to 12.
NOW (): SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
The NOW() function returns the current system date and time.
Result: Carlsberg || 10.45 || 10/7/2008 11:25:02 AM
FORMAT (): used to format how a field is to be displayed.
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products
TOP Clause: used to specify the number of records to return.
SELECT TOP 2 * FROM Products
Result: Return first two Rows in the Table
TOP PERCENT: Select only 50% of the records in the table above.
SELECT TOP 50 PERCENT * FROM Persons
Result: Return 50% Rows in the Table. If a table contains 100 rows, it will return 50 rows.
LIKE Operator: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
SELECT * FROM Persons WHERE City LIKE 's%' Result: Returns rows where the City Name starts with ‘S’
SELECT * FROM Persons WHERE City LIKE '%s' Result: Returns rows where the City Name ends with ‘S’
SELECT * FROM Persons WHERE City LIKE '%s%' Result: Returns rows where the City Name contains ‘S’
SELECT * FROM Persons WHERE City NOT LIKE '%s%' Result: Returns rows where the City Name Not contains ‘S’
Note: The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
IN Operator: specify multiple values in a WHERE clause.
SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')
BETWEEN Operator: It selects a range of data between two values. The values can be numbers, text, or dates.
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Petersen'
SELECT * FROM Persons WHERE Amount BETWEEN 1000 AND 5000
Note: Won’t consider about last parameter passed(Take Record from 1000 up to 4999)
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Petersen'
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'
Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to "Tove" OR to "Ola":
SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')
ORDER BY Keyword: The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.
SELECT * FROM Persons ORDER BY LastName DESC
INSERT INTO Statement: used to insert a new row in a table.
INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
UPDATE Statement: used to update existing records in a table.
Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem' AND FirstName='Jakob'
DELETE Statement: used to delete rows in a table.
Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
Delete All Rows: It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
Note: Be very careful when deleting records. You cannot undo this statement!
DELETE FROM table_name
JOIN: The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys.
Different SQL JOINs:
• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables
INNER JOIN: return rows when there is at least one match in both tables.
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
LEFT JOIN: The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
RIGHT JOIN: returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
FULL JOIN: list all the persons and their orders, and all the orders with their persons.
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
UNION: The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA
SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. The SELECT INTO statement is most often used to create backup copies of tables.
SELECT * INTO Persons_Backup FROM Persons
Note: We can also use the IN clause to copy the table into another database:
SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons
Note: We can also copy only a few fields into the new table:
SELECT LastName, FirstName INTO Persons_Backup FROM Persons
SELECT INTO - Joined Tables: Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":
SELECT Persons. LastName, Orders. OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id
DROP INDEX, DROP TABLE, and DROP DATABASE
The DROP INDEX statement is used to delete an index in a table:
DROP INDEX table_name.index_name
The DROP TABLE statement is used to delete a table.
DROP TABLE table_name
The DROP DATABASE statement is used to delete a database:
DROP DATABASE database_name
TRUNCATE TABLE: only want to delete the data inside the table, and not the table itself?
TRUNCATE TABLE table_name
ALTER TABLE: is used to add, delete, or modify columns in an existing table.
• ALTER TABLE Persons ADD DateOfBirth date
• ALTER TABLE Persons ALTER COLUMN DateOfBirth year
• ALTER TABLE Persons DROP COLUMN DateOfBirth
Backup And Restore:
BACKUP DATABASE [AdventureWorks] TO DISK=N'\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak'
-- Full File Backup
BACKUP DATABASE AdventureWorks
FILE=’AdventureWorks_Data’
TO DISK = N’C:\Backup\AdventureWorks.bak’
-- Differential File Backup
BACKUP DATABASE AdventureWorks
FILE=’AdventureWorks_Data’
TO DISK = N’C:\Backup\AdventureWorks.bak’
WITH DIFFERENTIAL
RESTORE FILELISTONLY FROM DISK = 'D:BackUpYourBaackUpFile.bak'
Or
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.mdf'
/*If there is no error in statement before database will be in multiuser
mode.If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO
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
Note: The date types are chosen for a column when you create a new table in your database!
SQL NULL Values
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.
Note: It is not possible to compare NULL and 0; they are not equivalent.
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL
What is BIT datatype and what's the information that can be stored inside a bit column? Bit datatype is used to store Boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL
What is De-normalization and when would you go for it? As the name indicates, de-normalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
Define Candidate key, Alternate key, and composite key?
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.
If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
Explain different isolation levels? An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, and Repeatable Read, serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
What's the maximum size of a row? 8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
What's the maximum number of columns you can create in a table?
• 1024 for fixed-length columns in both all-pages-locked (APL) and data-only- locked (DOL) tables
• 254 for variable-length columns in an APL table
• 1024 for variable-length columns in an DOL table
The maximum size of a column depends on:
• Whether the table includes any variable- or fixed-length columns.
• The logical page size of the database. For example, in a database with 2K logical pages, the maximum size of a column in an APL table can be as large as a single row, about 1962 bytes, less the row format overheads. Similarly, for a 4K page, the maximum size of a column in a APL table can be as large as 4010 bytes, less the row format overheads. See Table 0-1 for more information.
• If you attempt to create a table with a fixed-length column that is greater than the limits of the logical page size, create table issues an error message.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What are constraints? Explain different types of constraints? Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach? Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two type- Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and its row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
What is a deadlock and what is a live lock? How will you go about resolving deadlocks? Deadlock is a situation 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. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.
What is database replication? What are the different types of replication you can set up in SQL Server? Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:
See SQL Server books online for in-depth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.
What is the wildcard character in SQL? Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.
What connections does Microsoft SQL Server support? Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and passwords).
Which one is trusted and which one is un-trusted? Windows Authentication is trusted because the username and password are checked with the Active Directory; the SQL Server authentication is un-trusted, since SQL Server is the only verifier participating in the transaction.
Nth Maximum: Table: employee
1 harry 3500.0000
2 jack 2500.0000
3 john 2500.0000
4 xavier 5500.0000
5 steven 7500.0000
6 susana 2400.0000
• Select * From Employee E1 Where (N-1) = (Select Count (Distinct (E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
• select * from Employee a where N=(select distinct(b.empid)from Employee b where a.empid=b.empid)
Second Maximum:
Select min(Salary) from (select top 2 Salary from employee order by Salary desc) M
What are the advantage of User Defined function over store procedure
There are no of benefits of SQL Server User-Defined functions. Some of these are here we can use these functions in so many different places with comparison to SQL Server stored procedure. Two of user define function acts like a table (Inline and Multi-statement functions) helps developers to reduce the code and break complex logic in short code blocks. On the other hand Scalar User-Defined Function have ability so that we use this function anywhere where we need some single value result or some of operation. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.
What are the advantages of using Stored Procedures? Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.
What is a table called, if it has neither Cluster nor Non‐cluster Index? What is it used for? Un-indexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Un-indexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.
What command do we use to rename a db, a table and a column? To rename db sp_renamedb ‘oldname’ , ‘newname’ If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode. E.g. USE master; GO EXEC sp_dboption AdventureWorks, 'Single User', True GO EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New' GO EXEC sp_dboption AdventureWorks, 'Single User', False GO To rename Table We can change the table name using sp_rename as follows, sp_rename ‘oldTableName’ ‘newTableName’ E.g. SP_RENAME ‘Table_First’, ‘Table_Last’ GO To rename Column The script for renaming any column : sp_rename ‘TableName.[OldcolumnName]’, ‘NewColumnName’, ‘Column’ E.g. sp_RENAME ‘Table_First.Name’, ‘NameChange’ , ‘COLUMN’ GO
Define basic functions for master and msdb and tempdb databases in SQL Server (1)master:-It contains system level information for a SQL Server system and also contains login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. (2) tempdb - This database holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQ L Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. (3)mode - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. (4)msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
What is Cursor: Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.
What is Collate in SQL SERVER2000: The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types. The physical storage of character strings in Microsoft� SQL Server� 2000 is controlled by collations. A collation specifies the bit patterns to represent each character and the rules by which characters are sorted and compared with another character.
Exception Handling in SQL Server 2000: Basically, in Microsoft SQL Server 2000, there exists no structured exception handling. We need to depend on @@ERROR for any errors that occur. No TRY CATCH Block is available in 2000.
create procedure dbo.sp_emp_insert ( @empno int, @ename varchar(20), @sal float, @deptno int ) as begin declare @Error int begin transaction insert into emp (empno,ename,sal,deptno) values (empno,@ename,@sal,@deptno) set @Error = @@ERROR if @Error <> 0 --if error is raised begin goto LogError end commit transaction goto ProcEnd LogError: rollback transaction declare @ErrMsg varchar(1000) select @ErrMsg = [description] from master.dbo.sysmessages where error = @Error insert into error_log (LogDate,Source,ErrMsg) values (getdate(),'sp_emp_insert',@ErrMsg) ProcEnd: end GO
To execute the above program, you need to issue the following statement in query analyzer:
exec sp_emp_insert 1003,'ccc',4000,30
Exception Handling in SQL Server 2005 DropProcedure dbo.sp_emp_insert go createprocedure [dbo].[sp_emp_insert] ( @empno int, @ename varchar(20), @sal float, @deptno int ) as begin begintry begin transaction insert into emp (empno,ename,sal,deptno) values (@empno,@ename,@sal,@deptno) commit transaction endtry begincatch rollback transaction insert into error_log (LogDate,Source,ErrMsg) values (getdate(),'sp_emp_insert',error_message()) endcatch end
Execute: exec sp_emp_insert 1003,'ccc',4000,30
What is a Linked Server? Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T‐SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
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 User Defined Functions? What kind of User-Defined Functions can be created? User‐Defined Functions allow defining its own T‐SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Different Kinds of User‐Defined Functions created are: • Scalar User‐Defined Function • A Scalar user‐defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.
These are the type of user‐defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
What is Difference between Function and Stored Procedure? UDF 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 row-set. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Row-set operations.
What is sub-query? Explain properties of sub-query? Sub‐queries are often referred to as sub‐selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub‐query is executed by enclosing it in a set of parentheses. Sub‐queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.
A sub-query is a SELECT statement that is nested within another T‐SQL statement. A sub-query SELECT statement if executed independently of the T‐SQL statement, in which it is nested, will return a result-set. Meaning a sub-query SELECT statement can standalone and is not depended on the statement in which it is nested. A sub-query SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T‐SQL statement. A Sub-query can also be used as a parameter to a function call. Basically a sub-query can be used anywhere an expression can be used.
What are the properties and different Types of Sub-Queries? Properties of Sub‐Query • A sub‐query must be enclosed in the parenthesis. • A sub‐query must be put in the right hand of the comparison operator, and • A sub‐query cannot contain an ORDER‐BY clause. • A query can contain more than one sub‐query. Types of Sub‐query • Single‐row sub‐query, where the sub‐query returns only one row. • Multiple‐row sub‐query, where the sub‐query returns multiple rows,. and • Multiple column sub‐query, where the sub‐query returns multiple columns
What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.
Candidate key: If any unique data column(s) in table is called candidate key. Maybe one or more unique data column (s).We can select any one unique (candidate key column) as a primary key.
Alternate key: If one table contains more than one candidate keys, remaining candidate keys column(s) which is not selected as a primary key is called Alternate Key.
Composite key: One primary key Contains more than one columns is called Composite key.
What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
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 Inline Table-Value User-Defined Function An Inline Table‐Value user‐defined function returns a table data type and is an exceptional alternative to a view as the user‐defined function can pass parameters into a T‐SQL select command and in essence provide us with a parameterized, non‐updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function
A Multi‐Statement Table‐Value user‐defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T‐SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non‐updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user‐defined function, It can be used in the FROM clause of a T‐SQL command unlike the behavior found when using a stored procedure which can also return record sets.
What is Identity? Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
What is Data-Warehousing? 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.
What are 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 non clustered 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 non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
What are the different index configurations a table can have? A table can have one of the following index configurations: o indexes A clustered index A clustered index and many non-clustered indexes A non-clustered index Many non-clustered indexes
What are different types of Collation Sensitivity? Case sensitivity ‐ A and a, B and b, etc. Accent sensitivity ‐ a and á, o and ó, etc. Kana Sensitivity ‐ When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width sensitivity ‐ A single‐byte character (half‐width) and the same character represented as a double‐byte character (full‐width) are treated differently than it is width sensitive.
