1. What is SQL?
SQL stands for 'structured query language'. SQL is used to communicate with database.
SQL stands for 'structured query language'. SQL is used to communicate with database.
2. How to determine the service pack currently installed on SQL Server?
The global variable @@version stores the build number of sqlserver.exe, which is used to determine service pack installed.
The global variable @@version stores the build number of sqlserver.exe, which is used to determine service pack installed.
3. What is a scheduled jobs or what is a Scheduled Tasks?(SQL DBA)
4. How do you load large data to the SQL Server database?
Bulk copy is a tool used to copy the huge amount of data from tables.BULK INSERT command helps to imports a data file into a database table or view or user-specified format.
Bulk copy is a tool used to copy the huge amount of data from tables.BULK INSERT command helps to imports a data file into a database table or view or user-specified format.
5. Can you SQL Servers linked to other servers like Oracle?
Yes.
Yes.
6. What are the OS Services that the SQL Server installation adds?(SQL DBA)
7. What is BCP? When does it used?
Bulk copy program is a tool used to copy huge amount of data from tables and views.BCP does not copy structures same as source to destination.
Bulk copy program is a tool used to copy huge amount of data from tables and views.BCP does not copy structures same as source to destination.
8. What is SQL Profiler?
9. Which TCP/IP port does SQL Server run on? How can it be changed?(SQL DBA)
SQL server runs on port 1433.it can be change from network utility TCP/IP properties--> Number .Both on client and the server.
SQL server runs on port 1433.it can be change from network utility TCP/IP properties--> Number .Both on client and the server.
10.Which command using Query Analyzer will give you the version of SQL Server and operating system?(SQL DBA)
11.What is SQL Server agent?
SQL server agent plays an important role in the day-to-day tasks of DBA(database administrator)
SQL server agent plays an important role in the day-to-day tasks of DBA(database administrator)
12. What are Sparse Columns?
Sparse column is another tool to reduce the amount of physical storage used in database .
Sparse column is another tool to reduce the amount of physical storage used in database .
13. Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
-->Select * from table
-->Select count(*) from table
-->select rows from sysindexes
where id=object_id(table) and indid <2
-->Select * from table
-->Select count(*) from table
-->select rows from sysindexes
where id=object_id(table) and indid <2
14. Can SQL Servers linked to other servers?
Yes.
Yes.
15. How to restart SQL Server in single user mode?(SQL DBA)
16. what is DTC?(SQL DBA)
17. What is DTS?
Data Transformation Services (DTS) is a set of graphical tool and programmable objects that lets you extract,Transform,and consolidate data from disparate sources into single or multiple destinations.
Data Transformation Services (DTS) is a set of graphical tool and programmable objects that lets you extract,Transform,and consolidate data from disparate sources into single or multiple destinations.
18. What is SQL injection?(SQL DBA)
19. What are the methods used to protect against SQL injection attack?(SQL DBA)
20. What is RAID and what are different types of RAID configurations?(SQL DBA)
21. What are the steps you will take, if you are tasked with securing an SQL Server? (SQL DBA)
22. What is the architecture of SQL Servers in terms of its hierarchy?
SERVER
|
DATABASE
|
SCHEMA
|
TABLE
|
COLUMNS
A server has multiple databases and database has multiple schemas and so on......
SERVER
|
DATABASE
|
SCHEMA
|
TABLE
|
COLUMNS
A server has multiple databases and database has multiple schemas and so on......
23. What are the two types of authentication on SQL Server 2008?
Windows:
Map with the current use. In other words,you can get on the server as long as you are on your Windows.Uses the credentials for windows.
SQL Server:
Also known as mixed mode authentication. You need both windows and the sql server authentication so this type of Authentication is more secure.
Windows:
Map with the current use. In other words,you can get on the server as long as you are on your Windows.Uses the credentials for windows.
SQL Server:
Also known as mixed mode authentication. You need both windows and the sql server authentication so this type of Authentication is more secure.
24. What are the authentication modes in SQL Server? How can it be changed?(SQL DBA)
25. What is the difference between a connection and session?
Connection:
It is the number of instance connected to the database.An instance is model zed soon as the application is open again.49 connection maximum are allowed in SQL Server 2008
Session:
A session runs queries.In one connection, SQL Server 2008 Allowed 10 million of session for one connection.
Connection:
It is the number of instance connected to the database.An instance is model zed soon as the application is open again.49 connection maximum are allowed in SQL Server 2008
Session:
A session runs queries.In one connection, SQL Server 2008 Allowed 10 million of session for one connection.
26. What is the row size in SQL Server 2008 R2?
8KB
27. How to copy the Tables, Schema and Views from one SQL Server to another?
Data Transformation Server (DTS) is a set of graphical tool and programmable objects that lets user extract,transform,and consolidate data from disparate sources into single or multiple destinations.
28. What are the basic functions for master, msdb, model, tempdb databases?
The master database holds information for all databases located on the SQL server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups,SQL Agent information,DTS packages,SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.
29. How to rebuild Master Database?(SQL DBA)
30. What is the use of Master Database?
8KB
27. How to copy the Tables, Schema and Views from one SQL Server to another?
Data Transformation Server (DTS) is a set of graphical tool and programmable objects that lets user extract,transform,and consolidate data from disparate sources into single or multiple destinations.
28. What are the basic functions for master, msdb, model, tempdb databases?
The master database holds information for all databases located on the SQL server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups,SQL Agent information,DTS packages,SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.
29. How to rebuild Master Database?(SQL DBA)
30. What is the use of Master Database?
31. What is the use of model db if we did any changes in model db these changes will effect existing databases or not?
32. What is the role of master database?
33. What is the role of tempDB database?
34. What is the role of Model Database?
35. What is the role of Model Database?
36. Where SQL Server user's names and passwords are stored in SQL Server?
37. GUID data size?
128bit
38. How GUID becoming unique across machines?(SQL DBA)
39. What are the default file groups and file extensions?
40. What is Primary Data File (.mdf)?
It contains startup information for the DB and points the other Files in the DB.User data and objects can be stored in this file or in secondary data file .
Every DB must have at least one primary DB file(mandatory).
Recommended file extension (.mdf)
41. What s Second Data File (.ndf)?
These are optional, are user defined and store user data. Secondary DB files are used to spread data across multiple Disk Drives .
Recommended file Extension (.ldf)
42. What is Transaction Log File(.ldf)?
It holds the log information that is used to recover the DB.
There must at least one log file (Mandatory), Recommended file extension is (.ldf)
43. Write syntax to take database backup?(SQL DBA)
44. Database backup file extension?
.bak
45. How many types of database backups, what are they?(SQL DBA)
46. Write syntax to create new file group?
Create database db_name on (name='db_name_dat',filename='c///...db_name_dat.mdf',size in mb,max size =in mb's,filegrouwth=in mb's)
47. How can I add files to a file group?
File is a place where actual data is stored.File is a part of file group so whenever we create a table the transaction data is stored in a file
48. What is the use of file group?
It is used to separate the data.
49. Write syntax to create new transaction log? And what is the file extension?
50. How do you add users to roles and give permissions to roles?(SQL DBA)
51. What is a default FG? How to change it?(SQL DBA)
52. List what actios can be done at FG level - eg marking it read-only---(SQL DBA)
53. What are the differences between setting a DB in " Restricted" Model VS 'Single User Mode"?(SQL DBA)
54. What is a "Removable Database" - Why do we require this? How to use this?(SQL DBA)
55. How is "Attaching /Detaching" a DB? How does this work?(SQL DBA)
56. How to change the owner of a DB?(SQL DBA)
57. How to view statistics for a table/col?(SQL DBA)
58. What are the data types in SQL?
Big int,binary,bit,char,cursor,date time,decimal,float,image,int,money,nchar,ntext,nvarchar,real,small int,small money,text,time stamp,tiny int,var binary,varchar,uniqueidentifier.
59. What is user defined data types and when you should go for them?(SQL DBA)
60. How to convert timestamp data to date data (date time data type)?
By using CAST and CONVERT we can convert timestamp data type date data type
Cast timestamp as date
61. What is bit data type and what is the information that can be stred inside a bit column?(SQL DBA).
62. Difference between char and nvarchar / char and varchar data-type?
Char(n):It is a fixed length data type , it stores string values in non-Unicode manner and it occupy space 1byte for each character.
Varchar(n): It is variable length data type,it is used store non-Unicode characters and it occupies 1 byte of space for each character.
Nvarchar(n): It is a variable length data type , it is used to store Unicode characters and it occupies 2 bytes of space for each character.
63. What is the difference between text and image data type?
Text:It is same as varchar data type , it is a variable length data type,and it storesthe string value in non-Unicode manner ,it takes 1 char per 1 byte.
Image:It is same as varbinary , it is a variable length data type , the maximum length of binary data type is 1 to 8000bytes.
64. What do you mean by XML Data type?
XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
65. What is Timestamp data type?
System date datatype is nothing but time-stamp data type
66. How many system data types are in SQL Server?
27
67. What are the different database objects?
These are total seven database objects(6 permanent databases object +1 temporary database object)
permanent DB objects
table,Views,Stored procedures,user-Defined Functions,Triggers,Indexes
Temporary DB object
Cursors
68. Explain CREATE DATABASE syntax?
Create database database_name
Create database creates a database with the given name.
69. What is TABLE SAMPLE?(SQL DBA)
70. What are three SQL Keywords used do change or set someone's permissions?
GRANT,REVOKE AND DENY.
71. What is Select Statement?
The SELECT statement lets you select a set of values from a table in database.The values selected from from the database table would depend on the various conditions that are specified in the SQL query.
72. Write down the general syntax for a SELECT Statement covering all the optios?
Select select_list
[INTO new_table]
FROM source_table
WHERE [search_condition]
[GROUP BY group_by_exp]
[HAVING [search_condition]
[ORDER BY order_exp[ASC|DESC]]
73. Difference between Set and Select?
Set is a ANSI standard for variable assignment.
Select is a Non-ANSI standard when assigning variables.
SET-We can assign only one variable at a time.
SELECT-We can assign multiple values at a time.
SET-Designed to assign values to local variables.
SELECT-Designed to return data.
74. What is the difference between (Select * from table) and ( Select c1, c2 from table name)?
Select * from table: Used to retrieve the data from a table.
Select c1,c2 from table name: Used to retrieve the specified columns from a table.
75. What is the Insert Statement?
The INSERT statement lets you insert information into a database
76. What is an IDENTITY Column in insert statements?
IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.
77. How to retrieve last inserted identity value?
Use @@IDENTITY function to get the last inserted identity value.
78. How data can be copied from one table to another table?
INSERT INTO SELECT statement selects data from one table and inserts it into an existing table.Any existing rows in the target table are unaffected.
Ex; insert into table2 select * from table1.
The SELECT INTO statement copies data from one table and inserts it into a new table.
Ex; select * into newtable from table1.
79. What is the basic form of a SQL Statement to read data out of a table?
The basic form to read data out of table is "SELECT * FROM TABLE".
80. What is Identity?
Identity is a column that automatically generates numeric values. A start and increment value can be set,but most DBA leave these at 1.
81. What is the use of SCOPE_IDENTITY() function?
It returns the most recently created identity value for the tables in the current execution scope.
82. What is the Scope_Static command?(SQL DBA)
83. How do I reset the identity column?
By using DBCC CHECKIDENT we can reset the identity column.
DBCC CHECKIDENT(table,RESEED,idvalue).
84. What are the different ways of moving data/ databases between servers and databases in SQL Server?
There are lots of options available;you have choose your option depending upon your requirements.
Some of the options are:BACKUP/RESTORE,DTS,BCP,INSERT INTO,SELECT INTO.
85. How do you transfer data from textfile to database (other than DTS)?
Using BCP (Bulk Copy Program) utility.
86. What structure can you implement for the database to speed up table reads?
Creating different types of indexes.
Properly locate different DB objects across different table spaces,files,....
87. What is the use of FOR Clause?
FOR clause is mainly used for XML and browser options.This clause is mainly used to display the query results in XML format or in browser.
88. What is DDL?
Data definition language is used to, CREATE table,DROP table,ALTER table and TRUNCATE table.
89. What is DML?
Data Manipulation language , which is used to INSERT,UPDATE and DELETE data in the objects defined using DDL.
90. How do you delete a record from a database?
By using DELETE statement to remove records or any particular column values from a database.
91. What command do we use to rename a db?
SP_RENAMEDB 'oldname','newname'.
If someone is using DB it will not accept sp_renamedb.In that case first bring db to single user using sp_dboptions.
Use sp_renamedb to rename db,use sp_dboptions to bring db to multi user mode.
92. How will I export database?
Through DTS-Import/Export wizard
Backup-through complete/differential/transaction log.
93. How to export database at a particular time, every week?
Backup-Schedule
DTS-Schedule
Jobs-Create a new job.
94. How to export database at a particular time, every week?
95. Explain precision, Scale and Length?
Precision:It is the no. of digits in a number.
Scale:It is the no. of digits to the right of the decimal point in a number
The default precision is 18 and maximum is 38.
ex:3457.7854 precision is 8 and scale is 4.
Length for a numeric data type is the number of bytes that are used to store the number.
96. What is the difference between DELETE TABLE and TRUNCATE TABLE commands?
TRUNCATE:
It is an DDL command,It clears the data from table and it won't information.
It resets identity field.
We can't apply where clause.
Can't truncate primary key table.
DELETE:
It is DML command, It clears data from table and it can log the information .
It does not reset the identity field.
We can apply where clause.
we can delete primary key table . 97. Which command is used for user defined error messages?
RAISEERROR is the command used to generate and initiates error processing for a given session.
98. How to get the results of a Query sorted in any order?
You can sort the results by using ORDER BY keyword.
99. How could I get distinct entries from a table?
In a table , a columnmay contain many duplicates values and some times you want to list the distinct (different) values.
The DISTINCT keyword can be used to return distinct values. 100. What is a "functional dependency"? How does it relate to database table design?
101. Why can a "group by" or "order by" clause be expensive to process?
Processing of Group By OR Order By clause often requires creation of temporary tables to process the results of the query.Which depending of the result set can be very expensive. 102. How do you delete a record from a database?
Use DELETE statement to remove records or any particular column values from a database.
107. How can i find the total number of records in a table?
You can use COUNT keyword.
EX: SELECT COUNT(*) FROM TABLE
108. What is GROUP BY?
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performedon the one or more columns 109. What is the difference among " dropping a table", " truncating a table" and " deleting all records" from a table?
Dropping:(Table structure +Data are deleted),Invalidates the dependent objects,Drops the indexes.
Truncating:If you truncate table total table records deleted and can't be rollback and it reset identity column, it is faster than delete.
Delete:Data alone deleted, doesn't perform automatic commit and data can be rollback. 110. What does TOP Operator Do?
The TOP operator is used to specify the number of rows to be returned by a query. 111. How can we get count of the number of records in a table?
Following are the queries can be used to get the count of records in a table
--SELECT * FROM TABLE
--SELECT COUNT(*) FROM TABLE
--SELECT ROWS FROM SYSINDEXES
WHERE ID=OBJECT_ID(TABLE) AND INDID<2 112. How to delete duplicate rows in SQL Server?
Duplicate rows can be deleted using CTE and ROW NUMBER 113. Why are my insert, update statements failing with the following error?
114. How to upload images or binary files into SQL Server tables? (top)
115. Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or how to generate output with row number in SQL Server?
116. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement
117. Try to restrict the queries result set by using the Where clause.
118. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
119. Try to restrict the queries result set by returning only the particular coumns from the table, not all table's columns.
120. How to list all the tables in a particular database?
USE DATABASE_NAME
GO
sp_help 121. Give a scenario where two operations, Delete Statement and Truncate Statement, Where the Delete Statement was successful and the truncate statement was failed. - Can you judge why?
122. What is use of shrink database?
MS SQL Server 2000 allows each file within a database to be shrunk to remove unused pages.Both data and transaction log files can be shrunk.
123. What is a pass-through query?
124. How can i randomly sort query results?
To randomly order rows,or to return x number of randomly chosen rows, you can use the RAND function inside the SELECT statement.But the RAND function is resolved only once for the entire query 125. How to find dependents of a table?
Verify dependencies with sp_depends before dropping an object.
126. How can i create a plain-text flat file from SQL Server as input to another application?
127. Different types of Backups?
--A full database backup is a full copy of the database.
--A transaction log backup copies only the transaction log.
--A differential backup copies only the database pages modified after the last full database backup.
--A file or file group restore allows the recovery of just the portion of a database that was on the failed disk.
128. How to get @@ error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset and if @@Recordcount is checked before the error checking statement then @@error would get reset.To get @@error and @@rowcount at same time do both in same statement and store them in local variable.
SELECT @RC=@@ROWCOUNT,@ER=@@ERROR
129. 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.
130. What are synonyms?
131. How can i send a message to user from the SQL Server?
132. If a database is normalized by 3 NF then how many number of tables it should contain in minimum? How many minimum if 2 NF and 1 NF?
133. Difference between sysusers and syslogins?
134. What is the purpose of using COLLATE in a query?
135. After removing a table from database, what other related objects have to be dropped explicitly?
VIEW,STORED Procs.
136. If a user does not have permission on a table, but he has permission to a view created on it, will he be able to view the data in table?
Yes.
137. What a query to convert all the letters in a word to upper case?
138. Write a query to roun up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.?
139. I entered login ID and Password click for login but it is taking so much of time to login how will you tune for performance?
140. Write the query to retrieve the name column reverse alphanbetic?
141. Execute each and every query in ssms and write different types and compare time to execute ?
142. Describe few system tables in SQL Server?
143. How many columns for a table?
1024
144. Can i have DML, DDL, Commands inside the procedure?
Yes
145. Can you explain Fourth Normal Form?
146. Can you explain Firth Normal Form?
147. What is the difference between Fourth and Fifth Normal Form?
148. What is Extent and Page?
32. What is the role of master database?
33. What is the role of tempDB database?
34. What is the role of Model Database?
35. What is the role of Model Database?
36. Where SQL Server user's names and passwords are stored in SQL Server?
37. GUID data size?
128bit
38. How GUID becoming unique across machines?(SQL DBA)
39. What are the default file groups and file extensions?
40. What is Primary Data File (.mdf)?
It contains startup information for the DB and points the other Files in the DB.User data and objects can be stored in this file or in secondary data file .
Every DB must have at least one primary DB file(mandatory).
Recommended file extension (.mdf)
41. What s Second Data File (.ndf)?
These are optional, are user defined and store user data. Secondary DB files are used to spread data across multiple Disk Drives .
Recommended file Extension (.ldf)
42. What is Transaction Log File(.ldf)?
It holds the log information that is used to recover the DB.
There must at least one log file (Mandatory), Recommended file extension is (.ldf)
43. Write syntax to take database backup?(SQL DBA)
44. Database backup file extension?
.bak
45. How many types of database backups, what are they?(SQL DBA)
46. Write syntax to create new file group?
Create database db_name on (name='db_name_dat',filename='c///...db_name_dat.mdf',size in mb,max size =in mb's,filegrouwth=in mb's)
47. How can I add files to a file group?
File is a place where actual data is stored.File is a part of file group so whenever we create a table the transaction data is stored in a file
48. What is the use of file group?
It is used to separate the data.
49. Write syntax to create new transaction log? And what is the file extension?
50. How do you add users to roles and give permissions to roles?(SQL DBA)
51. What is a default FG? How to change it?(SQL DBA)
52. List what actios can be done at FG level - eg marking it read-only---(SQL DBA)
53. What are the differences between setting a DB in " Restricted" Model VS 'Single User Mode"?(SQL DBA)
54. What is a "Removable Database" - Why do we require this? How to use this?(SQL DBA)
55. How is "Attaching /Detaching" a DB? How does this work?(SQL DBA)
56. How to change the owner of a DB?(SQL DBA)
57. How to view statistics for a table/col?(SQL DBA)
58. What are the data types in SQL?
Big int,binary,bit,char,cursor,date time,decimal,float,image,int,money,nchar,ntext,nvarchar,real,small int,small money,text,time stamp,tiny int,var binary,varchar,uniqueidentifier.
59. What is user defined data types and when you should go for them?(SQL DBA)
60. How to convert timestamp data to date data (date time data type)?
By using CAST and CONVERT we can convert timestamp data type date data type
Cast timestamp as date
61. What is bit data type and what is the information that can be stred inside a bit column?(SQL DBA).
62. Difference between char and nvarchar / char and varchar data-type?
Char(n):It is a fixed length data type , it stores string values in non-Unicode manner and it occupy space 1byte for each character.
Varchar(n): It is variable length data type,it is used store non-Unicode characters and it occupies 1 byte of space for each character.
Nvarchar(n): It is a variable length data type , it is used to store Unicode characters and it occupies 2 bytes of space for each character.
63. What is the difference between text and image data type?
Text:It is same as varchar data type , it is a variable length data type,and it storesthe string value in non-Unicode manner ,it takes 1 char per 1 byte.
Image:It is same as varbinary , it is a variable length data type , the maximum length of binary data type is 1 to 8000bytes.
64. What do you mean by XML Data type?
XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
65. What is Timestamp data type?
System date datatype is nothing but time-stamp data type
66. How many system data types are in SQL Server?
27
67. What are the different database objects?
These are total seven database objects(6 permanent databases object +1 temporary database object)
permanent DB objects
table,Views,Stored procedures,user-Defined Functions,Triggers,Indexes
Temporary DB object
Cursors
68. Explain CREATE DATABASE syntax?
Create database database_name
Create database creates a database with the given name.
69. What is TABLE SAMPLE?(SQL DBA)
70. What are three SQL Keywords used do change or set someone's permissions?
GRANT,REVOKE AND DENY.
71. What is Select Statement?
The SELECT statement lets you select a set of values from a table in database.The values selected from from the database table would depend on the various conditions that are specified in the SQL query.
72. Write down the general syntax for a SELECT Statement covering all the optios?
Select select_list
[INTO new_table]
FROM source_table
WHERE [search_condition]
[GROUP BY group_by_exp]
[HAVING [search_condition]
[ORDER BY order_exp[ASC|DESC]]
73. Difference between Set and Select?
Set is a ANSI standard for variable assignment.
Select is a Non-ANSI standard when assigning variables.
SET-We can assign only one variable at a time.
SELECT-We can assign multiple values at a time.
SET-Designed to assign values to local variables.
SELECT-Designed to return data.
74. What is the difference between (Select * from table) and ( Select c1, c2 from table name)?
Select * from table: Used to retrieve the data from a table.
Select c1,c2 from table name: Used to retrieve the specified columns from a table.
75. What is the Insert Statement?
The INSERT statement lets you insert information into a database
76. What is an IDENTITY Column in insert statements?
IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.
77. How to retrieve last inserted identity value?
Use @@IDENTITY function to get the last inserted identity value.
78. How data can be copied from one table to another table?
INSERT INTO SELECT statement selects data from one table and inserts it into an existing table.Any existing rows in the target table are unaffected.
Ex; insert into table2 select * from table1.
The SELECT INTO statement copies data from one table and inserts it into a new table.
Ex; select * into newtable from table1.
79. What is the basic form of a SQL Statement to read data out of a table?
The basic form to read data out of table is "SELECT * FROM TABLE".
80. What is Identity?
Identity is a column that automatically generates numeric values. A start and increment value can be set,but most DBA leave these at 1.
81. What is the use of SCOPE_IDENTITY() function?
It returns the most recently created identity value for the tables in the current execution scope.
82. What is the Scope_Static command?(SQL DBA)
83. How do I reset the identity column?
By using DBCC CHECKIDENT we can reset the identity column.
DBCC CHECKIDENT(table,RESEED,idvalue).
84. What are the different ways of moving data/ databases between servers and databases in SQL Server?
There are lots of options available;you have choose your option depending upon your requirements.
Some of the options are:BACKUP/RESTORE,DTS,BCP,INSERT INTO,SELECT INTO.
85. How do you transfer data from textfile to database (other than DTS)?
Using BCP (Bulk Copy Program) utility.
86. What structure can you implement for the database to speed up table reads?
Creating different types of indexes.
Properly locate different DB objects across different table spaces,files,....
87. What is the use of FOR Clause?
FOR clause is mainly used for XML and browser options.This clause is mainly used to display the query results in XML format or in browser.
88. What is DDL?
Data definition language is used to, CREATE table,DROP table,ALTER table and TRUNCATE table.
89. What is DML?
Data Manipulation language , which is used to INSERT,UPDATE and DELETE data in the objects defined using DDL.
90. How do you delete a record from a database?
By using DELETE statement to remove records or any particular column values from a database.
91. What command do we use to rename a db?
SP_RENAMEDB 'oldname','newname'.
If someone is using DB it will not accept sp_renamedb.In that case first bring db to single user using sp_dboptions.
Use sp_renamedb to rename db,use sp_dboptions to bring db to multi user mode.
92. How will I export database?
Through DTS-Import/Export wizard
Backup-through complete/differential/transaction log.
93. How to export database at a particular time, every week?
Backup-Schedule
DTS-Schedule
Jobs-Create a new job.
94. How to export database at a particular time, every week?
95. Explain precision, Scale and Length?
Precision:It is the no. of digits in a number.
Scale:It is the no. of digits to the right of the decimal point in a number
The default precision is 18 and maximum is 38.
ex:3457.7854 precision is 8 and scale is 4.
Length for a numeric data type is the number of bytes that are used to store the number.
96. What is the difference between DELETE TABLE and TRUNCATE TABLE commands?
TRUNCATE:
It is an DDL command,It clears the data from table and it won't information.
It resets identity field.
We can't apply where clause.
Can't truncate primary key table.
DELETE:
It is DML command, It clears data from table and it can log the information .
It does not reset the identity field.
We can apply where clause.
we can delete primary key table . 97. Which command is used for user defined error messages?
RAISEERROR is the command used to generate and initiates error processing for a given session.
98. How to get the results of a Query sorted in any order?
You can sort the results by using ORDER BY keyword.
99. How could I get distinct entries from a table?
In a table , a columnmay contain many duplicates values and some times you want to list the distinct (different) values.
The DISTINCT keyword can be used to return distinct values. 100. What is a "functional dependency"? How does it relate to database table design?
101. Why can a "group by" or "order by" clause be expensive to process?
Processing of Group By OR Order By clause often requires creation of temporary tables to process the results of the query.Which depending of the result set can be very expensive. 102. How do you delete a record from a database?
Use DELETE statement to remove records or any particular column values from a database.
107. How can i find the total number of records in a table?
You can use COUNT keyword.
EX: SELECT COUNT(*) FROM TABLE
108. What is GROUP BY?
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performedon the one or more columns 109. What is the difference among " dropping a table", " truncating a table" and " deleting all records" from a table?
Dropping:(Table structure +Data are deleted),Invalidates the dependent objects,Drops the indexes.
Truncating:If you truncate table total table records deleted and can't be rollback and it reset identity column, it is faster than delete.
Delete:Data alone deleted, doesn't perform automatic commit and data can be rollback. 110. What does TOP Operator Do?
The TOP operator is used to specify the number of rows to be returned by a query. 111. How can we get count of the number of records in a table?
Following are the queries can be used to get the count of records in a table
--SELECT * FROM TABLE
--SELECT COUNT(*) FROM TABLE
--SELECT ROWS FROM SYSINDEXES
WHERE ID=OBJECT_ID(TABLE) AND INDID<2 112. How to delete duplicate rows in SQL Server?
Duplicate rows can be deleted using CTE and ROW NUMBER 113. Why are my insert, update statements failing with the following error?
114. How to upload images or binary files into SQL Server tables? (top)
115. Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or how to generate output with row number in SQL Server?
116. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement
117. Try to restrict the queries result set by using the Where clause.
118. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
119. Try to restrict the queries result set by returning only the particular coumns from the table, not all table's columns.
120. How to list all the tables in a particular database?
USE DATABASE_NAME
GO
sp_help 121. Give a scenario where two operations, Delete Statement and Truncate Statement, Where the Delete Statement was successful and the truncate statement was failed. - Can you judge why?
122. What is use of shrink database?
MS SQL Server 2000 allows each file within a database to be shrunk to remove unused pages.Both data and transaction log files can be shrunk.
123. What is a pass-through query?
124. How can i randomly sort query results?
To randomly order rows,or to return x number of randomly chosen rows, you can use the RAND function inside the SELECT statement.But the RAND function is resolved only once for the entire query 125. How to find dependents of a table?
Verify dependencies with sp_depends before dropping an object.
126. How can i create a plain-text flat file from SQL Server as input to another application?
127. Different types of Backups?
--A full database backup is a full copy of the database.
--A transaction log backup copies only the transaction log.
--A differential backup copies only the database pages modified after the last full database backup.
--A file or file group restore allows the recovery of just the portion of a database that was on the failed disk.
128. How to get @@ error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset and if @@Recordcount is checked before the error checking statement then @@error would get reset.To get @@error and @@rowcount at same time do both in same statement and store them in local variable.
SELECT @RC=@@ROWCOUNT,@ER=@@ERROR
129. 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.
130. What are synonyms?
131. How can i send a message to user from the SQL Server?
132. If a database is normalized by 3 NF then how many number of tables it should contain in minimum? How many minimum if 2 NF and 1 NF?
133. Difference between sysusers and syslogins?
134. What is the purpose of using COLLATE in a query?
135. After removing a table from database, what other related objects have to be dropped explicitly?
VIEW,STORED Procs.
136. If a user does not have permission on a table, but he has permission to a view created on it, will he be able to view the data in table?
Yes.
137. What a query to convert all the letters in a word to upper case?
138. Write a query to roun up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.?
139. I entered login ID and Password click for login but it is taking so much of time to login how will you tune for performance?
140. Write the query to retrieve the name column reverse alphanbetic?
141. Execute each and every query in ssms and write different types and compare time to execute ?
142. Describe few system tables in SQL Server?
143. How many columns for a table?
1024
144. Can i have DML, DDL, Commands inside the procedure?
Yes
145. Can you explain Fourth Normal Form?
146. Can you explain Firth Normal Form?
147. What is the difference between Fourth and Fifth Normal Form?
148. What is Extent and Page?
No comments:
Post a Comment