-
Difference between Primary Key and Aggregate Key?
psingla
- Mar 8th, 2010
primary key:is a key on ( 1 or group of columns) used to identify a record uniquely.builds clustered index automaticallyAlternate key:is a key which is a unique key but not a primary key.no...
khurram
- Aug 3rd, 2006
aggregate fact tables have two types of keys-composite key-serrogate key (system generated key)So aggregate-key is a primary key
-
What is the difference among "dropping a table”,” truncating a table" and deleting all records" from a table?
psingla
- Mar 8th, 2010
Truncate:Deletes all the record from the table.keeps no entry in log file.so rollback is not possiblefast in operation than delete.Its a DDL statementReset the identity column value to 1 (specific to ...
vasumathi.sridharan
- May 26th, 2009
Delete is a DML StatementTruncate is a DDL StatementDelete can be rolledbackTruncate cannot be rolledback The memory occupied by the table will be released in Truncate.That is Not the case with DeleteTruncate cannot use a where clauseDelete can use a where clauseTruncate is faster than Delete.
-
Unsort Query
How to write query to unsort while retrieving data?
psingla
- Mar 9th, 2010
No need to do so. the data fetched by the sql query returns the unsorted data unless the order by clause is not used.
-
Secure Data
How to secure data in the SQL Server Database?
-
After trigger
Is there any reason for which we cannot use After trigger with a view?
psingla
- Mar 16th, 2010
A after trigger gets fired whenever there is any insert/update/delete happens in the table on which the trigger is defined.But the view is just a definition rather than an actual table so insert/updat...
pprasad_ktda
- Sep 4th, 2009
In a view if composite primary key is present than "After Trigger" cannot be defined.
-
Why can a "group by" or "order by" clause be expensive to process?
psingla
- Mar 17th, 2010
Group by: It clubs all the similar rows and display only the distinct data so here clubing and displaying distinct data is an overhead.Order by: It sorts the data either in ascending order or in desending order as specified in the query. Sorting the data is an overhead.
Shehzad
- Aug 22nd, 2006
Group by class will have to group all the data returing from the query in our specifed groups... so it is definitly an overhead from execution point of view... imagine that the number of rows returned...
-
How we can protect SQL Server Database?Tell me about Process?
psingla
- Mar 8th, 2010
Solution1:Create a replica of a database on a different machine.Update the replica using log shipping functionality of sqlserver.Advantage:Less space is usedDisadvantage:Complex Solution2:Take the complete backups of the databases.Advantage:SimpleDisadvantage:More space is used
pratap das
- Nov 23rd, 2006
this is also worhs mentioning database backup & recovery
-
What are "HINTS"? What is "index covering" of a query?
psingla
- Mar 17th, 2010
Hints are instructions given by the developer in the query to use a particular type of exectuion plan.Whenever a query is written then sql comipler creates n number of execution plans and opt for the ...
Bobby
- Oct 13th, 2007
Hints are options or strategies specified for enforcement by the SQL Server 2005 query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.
-
a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
b. SELECT * FROM Persons SORT REVERSE 'FirstName'
c. SELECT * FROM Persons ORDER BY -'FirstName'
d. SELECT * FROM Persons ORDER BY FirstName DESC
SELECT * FROM Persons ORDER BY DESC FirstName
3)What is the correct SQL syntax for selecting all the columns where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
a. SELECT * FROM Persons WHERE LastName > 'Hansen', LastName < 'Pettersen'
b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons
c. SELECT * FROM customers WHERE LastName > 'Hansen' AND LastName > 'Pettersen'
d. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
4)Consider the following queries:
1. select * from employee where department LIKE "[^F-M]%";
2. select * from employee where department = "[^F-M]%";
Select the correct option:
a. Query 2 will return an error
b. Both the queries will return the same set of records
c. Query 2 is perfectly correct
d.Query 2 would return one record less than Query 1
5)Which of the following is not a global variable?
a. @@colcount
b. @@error
c. @@rowcount
d. @@version
e. All are valid global variables
Consider the following two tables:
1. customers( customer_id, customer_name)
2. branch ( branch_id, branch_name )
6)What will be the output if the following query is executed:
Select * branch_name from customers,branch
a. It will return the fields customer_id, customer_name, branch_name
b. It will return the fields customer_id, customer_name, branch_id, branch_name
c. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
d. It will return an empty set since the two tables do not have any common field name
e. It will return an error since * is used alone for one table only
7)Which of the following is not a valid Numeric datatypes in SQL Server?
a. INT
b. SMALLINT
c. TINYINT
d. BIGINT
e. MONEY
8)Which of the following datatypes is not supported by SQL-Server?
a. Character
b. Binary
c. Logical
d. Date
e. Numeric
f. All are supported
9)Which of the following are false for batches (batch commands)?
a. Statements in a batch are parsed, compiled and executed as a group
b. None of the statements in the batch is executed if there are any syntax errors in the batch
c. None of the statements in the batch is executed if there are any parsing errors in the batch
d. None of the statements in the batch is executed if there are any fatal errors
in the batch
10) Select the correct option:
a. Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
b. Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer
11) How can you view the structure of a table named "myTable" in SQL Server?
a. desc myTable
b. desc table myTable
c. sp_columns myTable
d. None of the above
e. Using either option a or c
12) Which of the following is not a valid binary datatype in SQL Server?
a. BINARY
b. VARBINARY
c. BIT
d. IMAGE
e. TESTAMP
13) Which of the following is false with regards to sp_help?
a. When a procedure name is passed to sp_help, it shows the parameters
b. When a table name is passed to sp_help, it shows the structure of the table
c. When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
d. All of the above are true
14)Which of the following are false for batches (batch commands)?
a. Statements in a batch are parsed, compiled and executed as a group
b. None of the statements in the batch is executed if there are any syntax errors in the batch
c. None of the statements in the batch is executed if there are any parsing errors in the batch
d. None of the statements in the batch is executed if there are any fatal errors
in the batch
">1)What is the maximum value that can be stored for a datetime field? a. Dec 31, 9999 b. Jun 6, 2079 c. Jan 1, 2753 d. Jan 1, 2100 2)What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"? a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC b. SELECT * FROM Persons SORT REVERSE 'FirstName' c. SELECT * FROM Persons ORDER BY -'FirstName' d. SELECT * FROM Persons ORDER BY FirstName DESC SELECT * FROM Persons ORDER BY DESC FirstName 3)What is the correct SQL syntax for selecting all the columns where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"? a. SELECT * FROM Persons WHERE LastName > 'Hansen', LastName < 'Pettersen' b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons c. SELECT * FROM customers WHERE LastName > 'Hansen' AND LastName > 'Pettersen' d. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' 4)Consider the following queries:1. select * from employee where department LIKE "[^F-M]%";2. select * from employee where department = "[^F-M]%";Select the correct option: a. Query 2 will return an error b. Both the queries will return the same set of records c. Query 2 is perfectly correct d.Query 2 would return one record less than Query 1 5)Which of the following is not a global variable? a. @@colcount b. @@error c. @@rowcount d. @@version e. All are valid global variables Consider the following two tables:1. customers( customer_id, customer_name)2. branch ( branch_id, branch_name )6)What will be the output if the following query is executed: Select * branch_name from customers,branch a. It will return the fields customer_id, customer_name, branch_name b. It will return the fields customer_id, customer_name, branch_id, branch_name c. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name d. It will return an empty set since the two tables do not have any common field name e. It will return an error since * is used alone for one table only 7)Which of the following is not a valid Numeric datatypes in SQL Server? a. INT b. SMALLINT c. TINYINT d. BIGINT e. MONEY 8)Which of the following datatypes is not supported by SQL-Server? a. Character b. Binary c. Logical d. Date e. Numeric f. All are supported 9)Which of the following are false for batches (batch commands)? a. Statements in a batch are parsed, compiled and executed as a group b. None of the statements in the batch is executed if there are any syntax errors in the batch c. None of the statements in the batch is executed if there are any parsing errors in the batch d. None of the statements in the batch is executed if there are any fatal errorsin the batch 10) Select the correct option: a. Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer b. Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer 11) How can you view the structure of a table named "myTable" in SQL Server?a. desc myTable b. desc table myTable c. sp_columns myTable d. None of the above e. Using either option a or c12) Which of the following is not a valid binary datatype in SQL Server?a. BINARY b. VARBINARY c. BIT d. IMAGEe. TESTAMP13) Which of the following is false with regards to sp_help?a. When a procedure name is passed to sp_help, it shows the parameters b. When a table name is passed to sp_help, it shows the structure of the tablec. When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database d. All of the above are true14)Which of the following are false for batches (batch commands)?a. Statements in a batch are parsed, compiled and executed as a group b. None of the statements in the batch is executed if there are any syntax errors in the batch c. None of the statements in the batch is executed if there are any parsing errors in the batchd. None of the statements in the batch is executed if there are any fatal errorsin the batch
psingla
- Mar 16th, 2010
1. a.Dec 31, 9999 2. a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC suggested: SELECT <column_names> FROM Persons WHERE FirstName ORDER BY FirstName DESC 3. d. SELECT * FROM Pe...
Rsubscribe
- Oct 27th, 2009
Following answers are for SQL Server (few verified on SQL 2005)1. ... (a)2. ... (d)3. ... (d)4. ... (a)5. ... (e) In SQL @@Variable = is a global variable @Variable = local 6. ... (c) It returns ...
-
Join same fields
How to join same fields of four tables at SQL Server?
psingla
- Mar 19th, 2010
There are 5 types of join in sqlservercross joininner joinleft outer joinright outer joinfull outer joincross joinselect * from table1cross join table2cross join table3cross join table4inner joinselec...
-
Call Specific Index in Query
If a table has more than one index, how will you call a specific index in query?
-
No Lock
What is 'no lock' term in SQL, like "select * from table a (no lock)". What is its purpose?
psingla
- Mar 19th, 2010
Define: By default select puts exclusive lock. Specifying no lock puts no lock on the table.Advantage: Improve the peformanceDisadvantage: Dirty reads Suggestion: Use in static table data (with fewer DML operations)
sidhuonline
- Jun 30th, 2009
It is useful to improve the performance in executing the query. However there is a disadvantage in using it. The disadvantage is that one may not be sure that they are getting the data which is curren...
-
SQL Server Insert and Delete
1. how to insert five rows in a table using single query(in ms-sql or oracle)2.how to delete duplicate values in a table. for example, the table containing five duplicate values(like name rani). i would like to delete all four rows in a table. but not the one row.
psingla
- Mar 19th, 2010
As suggested by krishna create table test_a(a int)insert into test_a values(1)insert into test_a values(1)insert into test_a values(3)insert into test_a values(4)insert into test_a values(5)inser...
clickfreehit
- Apr 11th, 2009
Inset data:insert into targettableselect top 5 * from sourcetabledelete data:1. select distinct * into targettable from sourcetable2. delete from sourcetable3. insert into sourcetable ??? select * from targettable
-
Sending Mail
How to send mail from SQL Server?
vishalparate
- Mar 30th, 2010
With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However i...
-
Which is the best option used to move database from one server to another serve on same network?a) Import - Exportb)Backup-Restorec)Detach-AttachWhy?
kirandba
- Apr 1st, 2010
Backup and Restore works well and its also faster when compare to copying data and log file. Impoert/Export works well if your dealing with very small databases. If we have few million rows its takes minutes to copy when compared to seconds using backup and restore.
-
Can we send mail by sqlserver without using smtpserver in dot net?
vishalparate
- Mar 30th, 2010
By using xp_sendmail store procedure we can do this but my problem is what parameter needs to be pass to this store procedure if I want to send mail from my local machine to another user, bcos&nb...
-
Stored Procedure Performance Tuning
How can we improve stored procedure performance? Explain the performance tuning techniques.
hiteshvyas
- Apr 21st, 2010
Use the following steps:1) Dont use temporary tables instead use CTE or table Variable2) Use Inner Join instead of Sub Queries.3) Check indexs in tables used4) Instead of Select * from use select colu...
bonagirdipti
- Feb 17th, 2010
To expect improvemnt in stored procedure performance, pl keep following points in mind :-1. Please don't use many subqueries.2. Don't use cursor, instead of it, use temp. table in the looping....
-
Fetching records from attributes
How will you fetch records having underscore ( _ ) in the middle of of strings, from columns having attributes as "amit_6", "t_mit06", "am_06", "m_t", "j_x_t" ?
-
SQL Server Transactions
Why Transactions are needed in SQL Server?
kirangiet
- Aug 17th, 2010
SQL Transactions ensure to follow ACID Properties.A-Atomicity: Using Transactions, either none or all the statements inside the transaction will execute successfully.C-Consistency: Using Transact...
sanjaykp
- Mar 12th, 2010
To guarantee the authenticity and integrity of any change to data in a database(s), be it an insert, update or delete. Transactions are a fundamental tool used by all industry standard Relation Databa...
-
Explain real time situation where you would use clustered and Non-clustered Indexes?
kirangiet
- Aug 17th, 2010
Cluster Index should be created on unique columns. By default it will be created on Primary Key. We can change it to any other column. Note: We can create Clustered Key on Non-Unique column also....
RAGHUKANTH
- Aug 21st, 2007
Yes, coming to your answer clustered index is used and applied only and primary keysFor example if a table student is created consider here in this table student id is the primary key so clustered ind...
SQL Server Interview Questions
Ans