1) What is abstract class
2) Inheritance
3) Difference between Interface and inheritance
4) Access specifiers private, protected, Public.
5) Try catch loop
6) Does try catch loop can contain multiple catch blocks
7) Try catch Finally
8) What are stored procedures
9) Joins (inner,outer, equi join, left outer join )
10) Difference between select stmt and stored procedure
Ans:
When I first started using SQL Server as a novice, I was initially confused as to the differences between the SELECT statement, views, and stored procedures. They all seemed to perform more or less the same task (retrieve data), and I wanted to know the pros and cons of using each. Why would SQL Server offer three different options to retrieve data from database? As a developer and new DBA, I took it upon myself to learn everything I could about these options, why they may be required, and when they should be used. This article is a result of my learning and experience, and explains the differences between SELECT statements, views, and stored procedures for the DBA or developer new to SQL Server. I hope you find this article useful.
As you read this article, if you choose, you can cut and paste the code into Query Analyzer I have provided in order to more fully understand and appreciate the differences between the SELECT statement, views, and stored procedures. I have divided this article into three parts to better explain this information.
Starting Notes
To get us started on learning the differences between the SELECT statement, views, and stored procedures, I need to mention the syscacheobjects system table. It is used to store information about compiled objects and their execution plans. The reason for this is because compiled SELECT statements, views, and stored procedures are stored here, and I have used this table to experiment and learn more about how these three different objects are stored and used by SQL Server. If you are not familiar with this system table, you might want to take a peek at it. It is stored in the master database, and can be viewed with Enterprise Manager or Query Analyzer.
If you choose to follow along with the examples in this article, you will want to run the DBCC FREEPROCCACHE command before each run. This command clears the syscacheobjects table of any current cached objects, and allows us to perform more accurate tests.
Now, let’s create a table and input a few rows in the table before we commence at taking a look at the differences between the SELECT statement, views, and stored procedures.
Create Sample Table
I assume you have a database you can use for this. If not, you will want to create one at this time. Now, we need to create a table for our experimentation.
Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)
Now, let’s add a few records in this table using this script:
Insert Into DummyTable1 Values (1, Replicate ('a',20))
GO
Insert Into DummyTable1 Values (2, Replicate ('b',20))
GO
Insert Into DummyTable1 Values (3, Replicate ('c',20))
GO
Insert Into DummyTable1 Values (4, Replicate ('d',20))
GO
Insert Into DummyTable1 Values (5, Replicate ('e',20))
GO
Insert Into DummyTable1 Values (6, Replicate ('f',20))
GO
Insert Into DummyTable1 Values (7, Replicate ('g',20))
GO
Insert Into DummyTable1 Values (8, Replicate ('h',20))
GO
Insert Into DummyTable1 Values (9, Replicate ('i',20))
GO
Insert Into DummyTable1 Values (10, Replicate ('j',20))
GO
DummyTable1 has contains sufficient rows to experiment with the differences between the SELECT statement, views, and stored procedures.
Let us begin with the SELECT statement and see how it is different from views and stored procedures.
SELECT Statement
Now, let’s view the contents of the table by EXECuting the following command in Query Analyzer for our new table.
SELECT EmpId, EmpName FROM DummyTable1
GO
| EmpID | EmpName |
| 1 | aaaaaaaaaaaaaaaaaaaa |
| 2 | bbbbbbbbbbbbbbbbbbbb |
| 3 | cccccccccccccccccccc |
| 4 | dddddddddddddddddddd |
| 5 | eeeeeeeeeeeeeeeeeeee |
| 6 | ffffffffffffffffffff |
| 7 | gggggggggggggggggggg |
| 8 | hhhhhhhhhhhhhhhhhhhh |
| 9 | iiiiiiiiiiiiiiiiiiii |
| 10 | jjjjjjjjjjjjjjjjjjjj |
Now, let’s execute the following commands to clear the cache.
DBCC FREEPROCCACHE
GO
Freeing the procedure cache prevents an ad-hoc SQL statement from being reused, assuming that it is currently in the cache. This means that the next time we run the same ad-hoc statement, that it must be newly recompiled.
11) Difference between truncate and delete table
Ans: The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
The DROP command removes a table from the database. All the tables' rows,
indexes and privileges will also be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.
12) types of triggers
Ans: Before Trigger, After Trigger, before Insert Trigger, After Insert Trigger, Before Update Trigger and After Update trigger.http://en.wikipedia.org/wiki/Database_trigger
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
http://www.aspfree.com/c/a/MS-SQL-Server/Brief-Introduction-to-Triggers-in-SQL-Server-2000/
13) Primary Key, Foreign Key
No comments:
Post a Comment