1) How to remove duplicate records from a table?
delete from tmp where rowid not in(select max(rowid) from tmp group by tmpno)
2) How will you copy the structure of a table without...
a)Without Data : select * into temp2 from temp where 1=2
b)With Data : select * into temp2 from temp
c) SELECT TOP 0 * INTO Test1 FROM Test
3) RE: how many column maximum we can add in one table a...
The column length is dependent up on the type of statement we are using:
It will be 4,096 for select statement 1024 for insert statement.
The number of foreighn key maximum we can use in a table is 16 and we can link with 253 tables.
4) What is the difference among "dropping a table" and "truncate"
Dropping table the structure of table as will as row of table .
but truncate table delete all the row of table but structure of table remain same we can apply where condition on it . delete work like truncate the table but we can apply where condition on it. Truncate can't roll back delete can.we can have condition with delete but with truncate we cant have condition
5) Difference between a "where" clause and a "having"...
1. Having clause is usually used with Group By clause although it can be used without it too.
2. 'Having' is just an additional filter to 'Where' clause.
3. 'Where' clause applies to the individual rows whereas 'Having' clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.
6) What is DESCRIBE command in SQL Server?What is its...
Suppose if u want to get the structure of table categories
use the following command in sql server
sp_columns categories
This will work.. Like wise if u want to see the list of tables in ur database, use
sp_tables
7) Difference between "VARCHAR" and "VARCHAR2" dataty...
Both are related to varible length but difference that varchar stores only 2000 byte character of data and varchar2 stores 4000 byte charecter of data .
The other difference is that, varchar occupy total length which is define to create database while the varchar2 occupy the total length of the given string.
8) What is #table and ## table in sql server ?
#table in SQL Server :
This # symbol prefixed with the tablename indicates that it is a local temporary table. This table will be existing till the session exists. Once the session expires the table will be dropped automatically. The table that is created with # symbol prefixed is temporary, so we can't give foreign key constraints to that table. Rest all the features are similar to that of the table that is permanent.
create table #Employee(EmpId int,EmpName Varchar(20))
##table in SQL Server:
This ## symbol prefixed with the tablename indicates that it is a Global Temporary table.This particular temporary table can be used by all the connections of SQL server and the temporary table is made available for all the users to access it.
The Temporary table can be mostly used for 'Select into' statements
9) How do you change column names in SQL Server ?
EXEC sp_rename 'Table.[OldColumnName]', NewColumnName, 'COLUMN'
10) Explain Normalization and Denormalization with exa...
Normalization is done to reduce redundancy and Specifies the fields and the primary key Normalization analyzes record structure through four stages
First normal form (1NF) records
Second normal form (2NF) records
Third normal form (3NF) records
Normalization
First normal form
Unnormalized records contain a repeating group
A repeating group refers to a single record that has multiple values in a particular field
Example: multiple product numbers in a single order record
A 1NF record cannot have a repeating group
Normalization
First normal form
To convert an unnormalized record to 1NF, the repeating group must be removed
Expand the primary key to include the primary key of the repeating group
The new primary key is a combination of the original primary key and the key of the repeating group
Instead of a single record with a repeating group, the result is many records, one for each instance of the repeating group
Normalization
Second normal form (2NF)
To be in second normal form, a record must be in 1 NF, and all nonkey fields must be functionally dependent on the entire primary key - not just part of it
Functional dependency means that a value in one field determines a value in another field
If the primary key is a single field, then any record in 1 NF is automatically in 2 NF
In 2NF, all nonkey fields are functionally dependent on the entire primary key
Normalization
Second normal form (2NF)
To convert a 1NF record to 2NF
Create a new record design for each field (or combination of fields) in the primary key
Place remaining fields with the appropriate record
The result will be several records, each with a primary key field (or combination of fields) that determines the values of the other fields in that record
Normalization
Third normal form (3NF)
To be in 3NF, a record must be in 2NF and no nonkey field is functionally dependent on another nonkey field
In 3NF, all nonkey fields are functionally dependent on the primary key, the entire key, and nothing but the key
Normalization
Third normal form (3NF)
To convert a 2NF record to 3NF
Remove all nonkey fields that depend on another nonkey field and place them in a new record that has the determining field as a primary key
11) RE: Are there any Commands, can be used to calculate the performance of a Querry?
set showplan on
set showplan_text on
set statistics io on
set statistics time on
12) what is fill factor, where it can be used?
When we create clustered index the data in the table is stored in the data pages of the database according to the order of the values in the indexed is known as fill factor.
13)What is the use of database index (apart from fast...
Indexes are useful for maintaining unique values in a column, since the database can easily search the index to see if an incoming value already exists. Primary keys are always indexed for this reason.
By default Primary keys will create Clustered Index(where tipical values are stored in Leaf level),
UNIQUE will create Non Clustred Index(where the references of the values are stored)
Since index entries are stored in sorted order, indexes also help when processing ORDER BY clauses.
14) What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.,inshort which follows the coods rule(12 rules of database)
15) What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
16) What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
17) Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
18) What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.19) What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that
19)What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
delete from tmp where rowid not in(select max(rowid) from tmp group by tmpno)
2) How will you copy the structure of a table without...
a)Without Data : select * into temp2 from temp where 1=2
b)With Data : select * into temp2 from temp
c) SELECT TOP 0 * INTO Test1 FROM Test
3) RE: how many column maximum we can add in one table a...
The column length is dependent up on the type of statement we are using:
It will be 4,096 for select statement 1024 for insert statement.
The number of foreighn key maximum we can use in a table is 16 and we can link with 253 tables.
4) What is the difference among "dropping a table" and "truncate"
Dropping table the structure of table as will as row of table .
but truncate table delete all the row of table but structure of table remain same we can apply where condition on it . delete work like truncate the table but we can apply where condition on it. Truncate can't roll back delete can.we can have condition with delete but with truncate we cant have condition
5) Difference between a "where" clause and a "having"...
1. Having clause is usually used with Group By clause although it can be used without it too.
2. 'Having' is just an additional filter to 'Where' clause.
3. 'Where' clause applies to the individual rows whereas 'Having' clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.
6) What is DESCRIBE command in SQL Server?What is its...
Suppose if u want to get the structure of table categories
use the following command in sql server
sp_columns categories
This will work.. Like wise if u want to see the list of tables in ur database, use
sp_tables
7) Difference between "VARCHAR" and "VARCHAR2" dataty...
Both are related to varible length but difference that varchar stores only 2000 byte character of data and varchar2 stores 4000 byte charecter of data .
The other difference is that, varchar occupy total length which is define to create database while the varchar2 occupy the total length of the given string.
8) What is #table and ## table in sql server ?
#table in SQL Server :
This # symbol prefixed with the tablename indicates that it is a local temporary table. This table will be existing till the session exists. Once the session expires the table will be dropped automatically. The table that is created with # symbol prefixed is temporary, so we can't give foreign key constraints to that table. Rest all the features are similar to that of the table that is permanent.
create table #Employee(EmpId int,EmpName Varchar(20))
##table in SQL Server:
This ## symbol prefixed with the tablename indicates that it is a Global Temporary table.This particular temporary table can be used by all the connections of SQL server and the temporary table is made available for all the users to access it.
The Temporary table can be mostly used for 'Select into' statements
9) How do you change column names in SQL Server ?
EXEC sp_rename 'Table.[OldColumnName]', NewColumnName, 'COLUMN'
10) Explain Normalization and Denormalization with exa...
Normalization is done to reduce redundancy and Specifies the fields and the primary key Normalization analyzes record structure through four stages
First normal form (1NF) records
Second normal form (2NF) records
Third normal form (3NF) records
Normalization
First normal form
Unnormalized records contain a repeating group
A repeating group refers to a single record that has multiple values in a particular field
Example: multiple product numbers in a single order record
A 1NF record cannot have a repeating group
Normalization
First normal form
To convert an unnormalized record to 1NF, the repeating group must be removed
Expand the primary key to include the primary key of the repeating group
The new primary key is a combination of the original primary key and the key of the repeating group
Instead of a single record with a repeating group, the result is many records, one for each instance of the repeating group
Normalization
Second normal form (2NF)
To be in second normal form, a record must be in 1 NF, and all nonkey fields must be functionally dependent on the entire primary key - not just part of it
Functional dependency means that a value in one field determines a value in another field
If the primary key is a single field, then any record in 1 NF is automatically in 2 NF
In 2NF, all nonkey fields are functionally dependent on the entire primary key
Normalization
Second normal form (2NF)
To convert a 1NF record to 2NF
Create a new record design for each field (or combination of fields) in the primary key
Place remaining fields with the appropriate record
The result will be several records, each with a primary key field (or combination of fields) that determines the values of the other fields in that record
Normalization
Third normal form (3NF)
To be in 3NF, a record must be in 2NF and no nonkey field is functionally dependent on another nonkey field
In 3NF, all nonkey fields are functionally dependent on the primary key, the entire key, and nothing but the key
Normalization
Third normal form (3NF)
To convert a 2NF record to 3NF
Remove all nonkey fields that depend on another nonkey field and place them in a new record that has the determining field as a primary key
11) RE: Are there any Commands, can be used to calculate the performance of a Querry?
set showplan on
set showplan_text on
set statistics io on
set statistics time on
12) what is fill factor, where it can be used?
When we create clustered index the data in the table is stored in the data pages of the database according to the order of the values in the indexed is known as fill factor.
13)What is the use of database index (apart from fast...
Indexes are useful for maintaining unique values in a column, since the database can easily search the index to see if an incoming value already exists. Primary keys are always indexed for this reason.
By default Primary keys will create Clustered Index(where tipical values are stored in Leaf level),
UNIQUE will create Non Clustred Index(where the references of the values are stored)
Since index entries are stored in sorted order, indexes also help when processing ORDER BY clauses.
14) What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.,inshort which follows the coods rule(12 rules of database)
15) What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
16) What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
17) Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
18) What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.19) What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that
19)What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
Comments
Post a Comment