|
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.
|
|
|
|
|
|
|
|
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 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
|
|
|
|
|
|
|
|
Why does inline or embedded SQL run faster than my stored procedures?
|
Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:
SELECT * FROM dbo.mytable
This technique helps you reuse plans and prevent cache misses.
|
|
|
|
|
|
|
|
What are stored-procedures? And what are the advantages of using them.
|
|
Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.
|
|
|
|
|
|
|
|
How to convert timestamp data to date data (datetime datatype) in SQL Server?
|
|
The name timestamp is a little misleading. Timestamp data has nothing to do with dates and times and can not be converted to date data. A timestamp is a unique number within the database and is equivalent to a binary(8)/varbinary(8) datatype. A table can have only one timestamp column. Timestamp value of a row changes with every update of the row. To avoid the confusion, SQL Server 2000 introduced a synonym to timestamp, called rowversion.
|
|
|
|
|
|
|
|
What is the limit of a row n SQL Server?
|
|
A row can be up to 8KB.
|
|
|
|
|
|
|
|
What are some SQL aggregates and built-in functions?
|
|
The common aggregate and built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT
|
|
|
|
|
|
|
|
What is the DEFAULT constraint in SQL Server?
|
|
A DEFAULT constraint, like all constraints, becomes an integral part of the table definition. It defines what to do when new row is inserted that does not include data for the column on which you have defined the DEFAULT constraint. You can either define it as a literal value(like default salary to zero or UNKNOWN for a string column) or as one of several values such as GETDATE(). The main things to understand about a DEFAULT constraint are - 1. DEFAULTs are only used in INSERT statements- They are ignored for UPDATE and DELETE statements.
2. If any value is supplied in the INSERT then the default is not used.
3. If no values supplied the default will always be used.
|
|
|
|
|
|
|
|
What is DTS (Data Transformation Services) in SQL Server?
|
|
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations.
|
|
|
|
|
|
|
|
What is the use of CASCADE CONSTRAINTS?
|
|
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
|
|
|
|
|
|
|
|
What is the FOREIGN KEY in SQL Server?
|
|
FOREIGN KEYs are both a method of ensuring data integrity and a manifestation of the relationships between tables. When you add a FOREIGN KEY to a table, you create a dependency between the table for which you define the FOREIGN KEY (the referencing table) and the table your FOREIGN KEY references (the referred table). After adding a FOREIGN KEY, any record you insert into the referencing table must have a matching record in the referenced column(s) of the referenced table, or the value of the FOREIGN KEY column(s) must be set to NULL.
|
|
|
|
|
|
|
|
What is the CASCADE action in SQL Server?
|
|
Bu default, you can not delete a record or update the referenced column in a referenced table if that record is referenced from the dependent table. If you want to be able to delete or update such records, then you need to set up a CASCADE action for the delete and/or update.
|
|
|
|
|
|
|
|
What is the IDENTITY property in SQL Server?
|
|
The IDENTITY property enables you to use system generated values in your tables column. It is similar to the auto number datatype in MS ACCESS. You are allowed a single column in each table with the IDENTITY property. Typically, IDENTITY column generates system assigned keys. To enforce entity integrity, you must uniquely identify each row in a table. If no natural column or set of column does this, you might went to create an IDENTITY column. You can use the IDENTITY property if the column to which it is being assigned is an integer or compatible with an integer. Therefore you can use the following datatypes - tinyint, bigint, smallint, numeric, integer, decimal. You can use numeric and decimal only if they have scale of 0 (zero). It must also not allowed to NULL.
|
|
|
|
|
|
|
|
How to find nth highest salary from Employee table in SQL Server?
|
|
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary
|
|
|
|
|
|
|