SQL Interview Q & A (part2)

What is the Difference between a HAVING clause and a WHERE clause?

 1.The WHERE clause specifies the criteria which individual records must meet to be 

selected by a 

query. It can be used without the GROUP BY clause. The HAVING clause cannot be used 

without 

the GROUP BY clause.

2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after 

grouping.

3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain 

aggregate functions.

What is Connection Pooling and  why it is used?

A connection pool is a group of database connections (with same connection properties) maintained in the application server so that these connections can be reused when future requests to the database are required.Creating a connection to the database servers is a  time consuming process.To establish a connection to the database server , a physical  channel such as socket or named pipe must be established , the connection string information to be parsed, it should be authenticated by the server and so on.Connection pool helps to reuse the established connection to serve multiple database request and hence improve the response time. In a practical world, most of the application use only one or a few different  connection configuration.During the application execution, many identical connections will be opened and closed. To minimize the cost of opening connections each time,ADO.NET uses the technique called Connection Pooling. Many people has the misunderstanding that, connection pool is managed in the database server.

What are the properties and Different Types of Sub-Queries?

Properties of Sub-Query

1. A sub-query must be enclosed in the parenthesis.
2. A sub-query must be put in the right hand of the comparison operator, and
3. A sub-query cannot contain an ORDER-BY clause.
4. A query can contain more than one sub-query.

Types of Sub-Query

1. Single-row sub-query, where the sub-query returns only one row.
2. Multiple-row sub-query, where the sub-query returns multiple rows,. and
3. Multiple column sub-query, where the sub-query returns multiple columns.

What are the Authentication modes Modes in SQL Server? How can it be changed?

Windows mode and Mixed mode Authentication(windows & SQL )

To change security authentication mode

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

Which Command using Query Analyzer will give you the Version of SQL and Operating System?

select  @@version

          OR

exec xp_msver

What is an SQL Server Agent?

SQL Server Agent allows you to automate a variety of administrative tasks

To start SQL server agent:Open up Microsoft SQL Server Configuration Manager and locate the SQL Server Agent service. If the status of that service is "RUNNING", you do not need to do anything. Otherwise, right-click on the SQL Server Agent service and select Start from the pop-up menu.

Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. 

You can nest stored procedures and managed code references up to 32 levels

What is Log Shipping?

Log Shipping is one of the disaster recovery  solution introduced in SQL 2000, in case of failover, primary server database is replaced with the stand by server if primary server goes down with unexpected situations like hardware failure, software failure, man made or natural made disasters.

Name 3 ways to get an Accurate Count of the Number of Records in a Table?

1. select COUNT(1) from Table
2. select COUNT(*) from Table
3. select * from Table

What is the difference between UNION and UNION ALL?

1. UNION removes duplicate rows, whereas UNION ALL doesn’t.
2. UNION have to perform distinct sort to remove duplicates, which makes it less faster than UNION ALL.

What is B-Tree?

A B-tree is a method of placing and locating files (called records or keys) in a  database.

What is Scheduled job or scheduled Task?

 A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers.

What are the Advantages of Using Stored Procedure?

1.Stored Procedures can reduce traffic because queries executed as batch
2. Improving the performance because the SPs are Pre-compiled
3.Stored procedures provides better security to your data
4. Easily modify the SPs when business rules change

What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?

Unindexed table or heap used for inserting and updating records. there is no or exceptional use of select data from that table.

can sql servers linked to other servers like oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provide for oracle that Microsoft to add it as linked server  to SQL server group.

what is bcp when is it used?
The Bulk Copy Program (BCP) is a command-line utility that ships with Microsoft SQL Server. With BCP, you can import and export large amounts of data in and out of SQL Server databases quickly and easily

BCP 101

You access the BCP utility from the command prompt. Here's the simple syntax:

bcp {dbtable | query} {in | out | queryout | format} datafile

[-n native type] [-c character type] [-S server name] [-U username]

[-P password] [-T trusted connection]

What Command do we Use to Rename a db, a Table and a Column?

rename db

sp_renamedb 'old_database_name' , 'new_database_name'

 rename Table

sp_rename 'old_TableName'  , 'new_TableName'

rename Column

sp_rename 'TableName.[Old_columnName]', 'New_ColumnName', 'Column'

What are sp_configure Commands and SET Commands?

Use sp_configure to display or change server-level 
settings. To change database-level settings, use ALTER 
DATABASE. To change settings that affect only the current 
user session, use the SET statement 

What is Difference between Commit and Rollback when Used in Transactions?

COMMIT is used to save the changes permanently on the server whereas ROLLBACK is used to undo the changes and restore previous state.

What is the difference between CHAR and VARCHAR Datatypes?

CHAR Data Type is a Fixed Length Data Type. For example, if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.

On the other hand, VARCHAR is a variable length Data Type. For example, if you declare a variable/column ofVARCHAR (10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.

What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?

VARCHAR(n)

If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then we can use this data type.For example First Name, Last Name etc, columns value can’t cross the max 8000 characters limit, in such scenario’s it is better to use this data type.

VARCHAR(MAX)

If we know that the data to be stored in the column or variable can cross a 8KB Data page, then we can use this data type.

what is the difference between varchar and nvarchar data types in sql server

 nvarchar stores Unicode data (which stores Multilingual data) while varchar stores single-byte character data.

what is sql injection? how to protect against sql injection attack?

SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations.

SQL injection is a attack where malicious users can inject SQL commands into an SQL statement, via web page input.

• Encrypt sensitive data.
• Access the database using an account with the least privileges necessary.
• Install the database using an account with the least privileges necessary.
• Ensure that data is valid.
• Do a code review to check for the possibility of second-order attacks.
• Use parameterised queries.
• Use stored procedures.
• Re-validate data in stored procedures.
• Ensure that error messages give nothing away about the internal architecture of the application or the database.

How to Find Out the List Schema Name and Table Name for the Database?

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'AS SchemaTableFROM sys.tables