| How to SELECT TOP N records from mytable without using TOP keyword? |
SET ROWCOUNT N
select * from mytable
SET ROWCOUNT 0
To turn this option off (so that all rows are returned), specify SET ROWCOUNT 0. |
|
|
|
|
|
| What are the pros and cons of creating extended stored procedures? |
| The pros are that you can implement additional functionality and access data from DLLs from within SQL Server. If you need to do something that can be done only in C or C++, or if you have data that can be accessed only outside of SQL Server, you can still provide a link to it. The biggest con to extended stored procedures is that they run in the same process space as SQL Server. So an errant DLL could overwrite memory and cause SQL Server to crash or even corrupt data. The biggest safeguard against these problems is thorough testing of the procedure |
|
|
|
|
|
| Which function is used to find the largest integer less than or equal to a specific value? |
| Floor or trunc both gives same results |
|
|
|
|
|
| What are stand-alone procedures? |
| Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution. |
|
|
|
|
|
| What is the advantage of SET NOCOUNT ON? |
| When we use SELECT and DML statement in SQL. SQL SERVER return a message which specify the number of rows affected by these statements. This information helps developer when they are debugging the code other-wise this is not useful. We can disable this by typing SET NOCOUNT ON. Setting SET NOCOUNT ON for stored procedure contains lots of statements, loops increases the performance and boost network traffic. |
|
|
|
|
|
How to use EXISTS operator OR
How to use subqueries with the EXISTS operators? |
A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. Example -
Select custID, CompanyName from Customers cu where EXISTS (Select OrderID from Orders o where o.custID = cu.custID) |
| Note:- When you use EXISTS, you don't really return data - instead, you return a simple TRUE/FLASE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operating against. |
|
|
|
|
| What is the difference between CAST and CONVERT in SQL? |
| Both CAST and CONVERT perform datatye conversions. In most respects, they both do the same thing, with the exception that CONVERT also does some date formatting conversions that CAST doesn't offer. |
| Note:- If CONVERT does everything that CAST does, and CONVERT also does date conversions, then why would ever use CAST? Simple answer for that - ANSI compliance. CAST is ASNI-compliant, and CONVERT isn't. |
|
|
|
|