If you are create trigger for insert in which you are going to insert 10 rows how many time it will fire?
Only once
2
Compare IN and EXISTS?
in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns
3
What is Index Covering?
Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.
4
What is Checkpoint?
The point at which all data pages that have been changed are guaranteed to have been written to the database device.
5
What is direct update and deffered update?
Direct Update:An update operation that takes place in a single step, that is, the log records are written and the data and index pages are changed. Direct updates can be performed in three ways: in-place update, on-page update, and delete/insert direct update.
Deffered Update:An update operation that takes place in two steps. First, the log records for deleting existing entries and inserting new entries are written to the log, but only the delete changes to the data pages and indexes take place. In the second step, the log pages are rescanned, and the insert operations are performed on the data pages and indexes.
6
What is @@spid,@@rowcount,@@error?
returns the no of rows affected by the preceding query
7
I what to see all locks in the server?
sp_lock,syslocks
8
What is sp_help and sp_helpdb?
sp_help use to display the objects in a database,sp_helpdb used to display the databases in your server
9
What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.
Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.
10
Explain Join order for Performance?
The optimal query plan for a join involves picking the best indexes for each table and the most efficient order to process the tables in the joins. The query optimizer checks all the join orders for cost optimization. The minimum I/O time which reduces cost will be selected for processing. If many tables are joined, SQL breaks into possible tables of a group. Examine the tables in the group and query optimizer performs permutation on each table and makes the final four tables, which minimize the cost and I/Os.
11
Explain Index and Performance?
Clustered index not reusing available space on preceding pages which makes pages grow longer and the search takes long time to complete. It is better to use cluster index for data value that is somewhat randomly distributed throughout the table.
In non-cluster point of view, first check the impact on the performance of OLTP environment for addition of non-clustered index. It is better to use non-cluster where low number of duplicates are present. It is good for large number of duplicate values. It is more effective if less than 10 to 20% of data is to be accessed through non clustered. Non-clustered is useful for single row lookups, joins, queries on columns that are highly selective and for queries with small range retrievals
12
what is a ghost record ?
These are records that have been logically deleted but not physically deleted from the leaf level of an index.
The reasons for this are complicated, but basically having ghost records simplfies key-range locking and transaction rollback.
The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits. Once this is done, it is deleted by an asynchronous background proces (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.
Ghost records will be mentioned later in the series when I discuss page compaction.
13
Compare Delete and Truncate?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
14
What is the use of timestamp?
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp is
used typically as a mechanism for version-stamping table rows. The storage
size is 8 bytes.
15
What are the Inbuilt User defined datatypes available in Sybase?
sysnames,timestamps
16
Write a query to select all records that do not have a null value?
17
How the Hotspot can be Resolved?
using heap table
18
How can I select the first 500 rows from a table having million rows & generate a flat file?
set rowcount 500
19
Write a query to find out the Nth max and min?
To find Nth max:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)
To find Nth min:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name
20
What system procedures are used to list all the indexes?
sp_indexes
21
What are the modes of transaction? And how do you change from one mode to another?
unchained mode: The default mode, called unchained or Transact-SQL mode,
requires explicit begin transaction statements paired with commit
transaction or rollback transaction statements to complete the
transaction.
chained mode: The SQL standards-compatible mode, called chained mode,
implicitly begins a transaction before any data retrieval or
modification statement. These statements include: delete, insert,
open, fetch, select, and update. You must still explicitly end the
transaction with commit transaction or rollback transaction.
You can set either mode using the chained option of the set command.
23
How do u run a file with lot of queries on ISQL?
isql -U -P -S -iinput file
24
What is the error code of Deadlock and How will you eliminate Deadlock?
errorcode for dead lock is 1205 and for stored procedure is -3.
25
How do you handle Error in Sybase?
The @@ERROR Function: SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designate error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
SP_ADDMESSAGE:Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:
RAISERROR:You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.
26
How do you handle error in BCP?
-e
27
Write a query to convert the date to dd/mm/yy?
select convert(char,date_exp,101)
28
When a query is sent to the database and an index is not being used, what type of execution is taking
table scan
place?
29
What is an advantage to using a stored procedure as opposed to passing an SQL query from an
faster execution , avoid dead lock,avoid network traffic
application
30
What are defaults? Is there a column to which a default can't be bound?
When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound
31
Where do you think the users names and passwords will be stored in sql server?
syslogins
32
What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
33
What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.
34
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). Untill 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.
35
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
36
What are “GRANT” and “REVOKE’ statements?
37
What are Wildcard operators in sybase?
Selects documents that contain matches to a character string containing variables. The WILDCARD operator lets you define a search string with variables, which can be used to locate related word matches in documents.
38
How will you find second maximum value in a table?
select max(column) from table where column<(select max(column) from table)
39
What is Optimistic locking?
Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
40
What are the types of Direct update not in place?
cheap direct update and Expensive direct update
41
How the Trigger differ from rules and constraints?
42
If I want current database name?
db_name()
43
If I want to check my query syntax what shall I do?
set parseonly on
44
Why we should go for Deallocating the cursors?
45
How do you call the Remote stored Procedures?
exec server_name.db_name.proc_name
46
What is the Drawbacks of Normalization?
Although most successful databases are normalized to some degree, there is one substantial drawback of a normalized database: reduced database performance. The acceptance of reduced performance requires the knowledge that when a query or transaction request is sent to the database, there are factors involved, such as CPU usage, memory usage, and input/output (I/O). To make a long story short, a normalized database requires much more CPU, memory, and I/O to process transactions and database queries than does a denormalized database. A normalized database must locate the requested tables and then join the data from the tables to either get the requested information or to process the desired data. A more in-depth discussion concerning database performance occurs in Hour 18, "Managing Database Users."
47
Explain BCP and Performance?
48
Explain Tempdb and Performance?
49
What is Thresholds and how will you add ?
50
What is the maximum row length limited by size of data page in SQL Server?
2KB
No comments:
Post a Comment