SQL DBA interview questions+ IBM

1.     What are tools that are used for backup and ticketing?

a.     Lite speed, BMC Remedy

2.     How to restore lite speed backup into sqlserver, is it possible to restore directly?

a.     That is not possible, because media type should be same.

3.     What is the difference between User and login?

a.     User is data base level and login is server level.

4.     How to add drive for cluster?

a.     Go to cluadmin

b.    Choose groups from left side

c.     Right click on add

d.    Choose drive which we want to add for existing cluster

5.     What is heart beat?     

6.     What are problems that you face for log shipping?

a.     Establish network tunnel

b.    Reduce transaction backup time from 30 min to 15 min.

7.     What is your backup strategy?

8.     What are the major differences between 2000 and 2005 in terms of security?

a.     Owner = Schema, hard to remove old users at times in 2000

b.    Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates. In 2005

c.     In 2000 there is no concept of synonyms, in 2005 it is introduced

d.    In 2000 there in policy based management, in 2008 it’s there.

9.     What are the steps for restoring data base?

a.     Go through the restore wizard in sql server management studio

10.  How to find deadlocks and how to resolve that?

a.     Just switch on the trace for 1204, 1222, 3605

b.    ex:DBCC Traceon(1204,1222,3505,-1)

11.  How to move logfile location from once drive to another drive?

a.     By attach and detach

12.  My log file is full how to solve it?

a.     By apply the shrink command

b.    Or by applying truncate command

13.  Can you tell some regular use of DBCC Commands?

a.     Dbcc checkdb()

b.    Dbcc showcontig()

c.     Dbcc dbreindex()

d.    Dbcc sqlperf()

e.     Dbcc traceon()

f.     Dbcc traceoff()

g.    Dbcc shrinkdatabase()

h.     Dbcc shrinkfile()

i.      Dbcc help()

j.      Dbcc ind()

k.     Dbcc checkident()

l.      Dbcc showfilestats()

m.   Dbcc inputbuffer()

n.     Dbcc outputbuffer()

o.    Dbcc log()

p.    Dbcc page(‘wowzzy’,1,945894,3)—check page is corrupted or not.

14.  What is importance of dbcc showcontig()?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Security in SQL Server 2005 VS SQL Server 2000

SQL Server 2000

1.Security:Owner = Schema, hard to remove old users at times

2.Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.

3.High Availability:Clustering or Log Shipping require Enterprise Edition. Expensive hardware

4.Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.

 

SQL Server 2005

1.Security:Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.

2.Encryption:Encryption and key management build in.

3.High Availability:Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.

4.Scalability:4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.

2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.

3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000.It was a separate installation for SQL Server 2000.

4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.

 

SQL DBA interview questions+ CSC

CSC:

1.     What is backup strategy?

2.     How to rename SQL Server?

sp_dropserver 

GO

sp_addserver , local

GO

Remote Logins - If the computer has any remote logins, running sp_dropserver might generate an error similar to the following:

 

Copy

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

There are still remote logins for the server 'SERVER1'.

To resolve the error, you must drop remote logins for this server.

To drop remote logins

·         For a default instance, run the following procedure:

Copy

sp_dropremotelogin old_name

GO

·         For a named instance, run the following procedure:

Copy

sp_dropremotelogin 'old_name\instancename'

GO

 

3.     How to trouble shoot temp db?

4.     How to solve connection issue?

5.     How to solve recovery status issue, if my db box has 100 databases, In that only one db has go to suspect mode? How to solve?

6.     IS Full text search service comes as default service?

7.     How to find fragmentation level by using command prompt?

8.     What are maintenance plans that you executed?

9.     What type of alerting system that you configured for your sql server box?

10.  What is your database size?

11.  How to solve Log file growing issue?

12.  Is it possible to take back up from primary server what was participated in log shipping?

13.  What is end point in mirroring?

14.  What are the security concerns that you execute while we configure mirroring?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TempDB Best Practices

What is TempDB responsible for in SQL Server 2005?

Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
DBCC CHECKDB work tables.
Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

Do not change collation from the SQL Server instance collation.
Do not change the database owner from sa.
Do not drop the TempDB database.
Do not drop the guest user from the database.
Do not change the recovery model from SIMPLE.
Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Cursors -Advantages and DisAdvantages

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Advantage:







Row by Row operations can be successfully executed 


Disadvantages :

 Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors.  

Views - Advantages and Disadvantages

Views are used for several different reasons:

1.To hide data complexity. Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to provide a view that runs a commonly requested SQL statement.

2.To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance, customer names, addresses and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:

1. Create a view to allow reading of only certain columns from a table. A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement.
    
2. Create a view to allow reading only certain rows from a table. For instance, you might have a view for department managers. This way, each manager can provide raises only to the employees of his or her department. This is referred to as horizontal partitioning and is accomplished by providing a WHERE clause in the SELECT statement that creates a view. 

 3. Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.
4. Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.
5. Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.


The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

Pros

Indexed views can increase query performance in the following ways:

• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
• Tables can be prejoined and the resulting data set stored.
• Combinations of joins or aggregations can be stored.

Secondary, nonclustered indexes on views can provide additional query performance. Similar to nonclustered indexes on tables, nonclustered indexes on views may provide more options for the query optimizer to choose from during the compilation process.
Applications that benefit from the implementation of indexed views include:

• Decision support workloads.
• Data marts.
• Data warehouses.
• Online analytical processing (OLAP) stores and sources.
• Data mining workloads.

From the query type and pattern point of view, the benefiting applications can be characterized as those containing:

• Joins and aggregations of large tables.
• Repeated patterns of queries.
• Repeated aggregations on the same or overlapping sets of columns.
• Repeated joins of the same tables on the same keys.
• Combinations of the above.

Cons:
Adding indexes to the schema increases the overhead on the database because the indexes will require ongoing maintenance.
Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. In this case, not only are performance gains not realized, but the additional cost of disk space, maintenance, and optimization is incurred. Online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables.
Indexes on tables and indexed views should be designed concurrently to obtain the best results from each construct. Because both indexes and indexed views may be useful for a given query, designing them separately can lead to redundant recommendations that incur high storage and maintenance overhead. While you tune the physical design of a database, tradeoffs must be made between the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design for indexed views is a challenging task, and the Database Tuning Advisor should be used wherever it is possible.
Query optimization cost can increase substantially if there are many indexed views that the query optimizer may consider for a particular query. A query optimizer may consider all indexed views that are defined on any subset of tables in the query. Each view has to be investigated for the potential substitution before it is rejected. This may take some time, especially if there are hundreds of such views for a given query.

Differences between Stored Procedures and Functions

Procedure can return zero or n values whereas function can return one value which is mandatory.

Procedures can have input/output parameters for it whereas functions can have only input parameters.

Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

Functions can be called from procedure whereas procedures cannot be called from function.

Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

We can go for transaction management in procedure whereas we can't go in function.

Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

 

Monitor tempdb In Sql Server 2005

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.

Shrink Log file

use test
GO
DBCC sqlperf(logspace)


Use Test
GO
--'Nul' it is an old DOS trick that behaves as if you are writing a file,
-- but really just dumps the information off, It's not use diskspace
BACKUP LOG test TO DISK = 'Nul'
go
dbcc shrinkfile(2,256)--Shrink file upto 256
GO

--TO know what are the files created for 'Test' database
Use Test
GO
SP_Helpdb 'test'

Create Folder in SQL Server 2005

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF

GO

How do we know which files and file groups are ONLINE

select fg.name as FilegroupName, fg.type_desc as FileGroupType, df.name as FileName, df.physical_name, df.state_desc from sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id

Common Table Expression

Common Table Expression

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

·         Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

·         Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

·         Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE:

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).

Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.

Introduction:
The common table expression is one of the new features in sql server 2005. It can be used instead of temp table or table variables in the stored procedures in the circumstances. Let's see CTE with some example queries.

Background:
Most of the developers while writing the stored procedures they create the temp tables or table variables. They need some table to store the temporary results in order to manipulate the data in the other tables based on this temp result.

The temp variables will be stored on the tempdb and it needs to be deleted in the tempdb database.

The table variable is best when compare with the temp tables. Because the table variable initially will be there in the memory for the certain limit of size and if the size increase then it will be moved to the temp database. However the scope of the table variable is only up to that program. When compare with table variable the CTE is best. It just store the result set like normal view.

CTE (Common Table Expression):
The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope.

The syntax of the CTE is the following.

WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name

Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.

CTE 1: Simple CTE
WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE

Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE.

This result set can be retrieved like table or view.

CTE2:Simple CTE with alias
WITH ProductCTE(ID,Name,Category,Price)AS(  SELECT ProductID,ProductName,CategoryID,UnitPrice
  FROM Products
)SELECT * FROM ProductCTE

Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.

It also accepts like the following as it is in the normal select query.

WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE

CTE 3: CTE joins with normal table
The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.

WITH OrderCustomer
AS(  SELECT DISTINCT CustomerID FROM Orders
)SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROM Customers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID

Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.

CTE 4: Multiple resultsets in the CTE
WITH MyCTE1
AS(  SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
), 

• MyCTE2AS(  SELECT DISTINCT ProductID FROM "Order Details")SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOIN MyCTE2 C2 ON C1.ProductID = C2.ProductID
  
  Here, there are two result sets that will be filtered based on the join condition.
  
  CTE 5: Union statements in the CTE
  WITH PartProdCateSaleAS(SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Condiments')UNION ALL
  SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Seafood')
  )SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" OD INNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductIDGROUP BY OD.ProductID
  
  Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.
  
  CTE 6: CTE with identity column
  WITH MyCustomCTE
     AS   (      SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM
           Customers
     )SELECT * FROM MyCustomCTE

AdvantagesCan be referenced multiple times in a query.

• Can be used to create recursive queries.
• Can be used in place of a view in scenarios when the metadata definition need not be stored.
• Improves readability.
• CTEs help improve readability of the code without compromising performance.
• They help improve maintainability of the code without compromising performance.
• They make writing recursive code in T-SQL significantly easier than the previous SQL Server versions.

Killing all Sql Server Data Bases

Declare @DBName varchar(50), @withmsg bit
Set @withmsg=1
Set @DBName='yourdatabasenmame' ---Change your databasename here
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
IF @withmsg =1
PRINT  CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB '  + @DBName
GO

Note: Change the databasename for @DBName variable

IBM Interview Questions

1.     How many IP’s are required for 2 node cluster?

Ans: 6

2.     Brief about IP’s usage?

Ans: 6 IP (  2 external IP (public), 4 Private IP)
 
3.     Scenario: My SQL server instances are working fine on Node A but instances are not failover from Node B, what could be the reason?

Ans: Network down

4.     What are differences between SQL 2005 and 2008 w.r.t Cluster?

Ans:

5.     What are the differences between SQL 2005 and SQL 2008 installation wise?

Ans:

6.     What are the agents created when we configure Transactional replication?

Ans: snapshot agent, distribution agent

7.     How to trouble shoot if Primary key violation is happened in Transactional replication?

Ans: sp_browsereplcommands

8.     How transactions are move between Publisher to Distributor and Distributor to Subscriber?

Ans:

9.     What is difference between Transactional replication and Merge replication?

Ans:

10.  What is diff between Log shipping and Mirroring?

Ans:

               With Log Shipping:

Data Transfer:    T-Logs are backed up and transferred to secondary server
Transactional Consistency:  All committed and un-committed are transferred
Server Limitation:   Can be applied to multiple stand-by servers
Failover:   Manual
Failover Duration:  Can take more than 30 mins
Role Change:   Role change is manual
Client Re-direction:  Manual changes required

With Database Mirroring:

Data Transfer:    Individual T-Log records are transferred using TCP endpoints
Transactional Consistency:  Only committed transactions are transferred
Server Limitation:   Can be applied to only one mirror server
Failover:   Automatic
Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change:   Role change is fully automatic
Client Re-direction:  Fully automatic as it uses .NET 2.0


11.  I want to give permissions to only one job for creation/deletion of that job only, out of 100’s of jobs, how?

Ans:

12.  Where we find Log files for clustering?

Ans: c:/program files/ windows/cluster.log


13.  How to move Tempdb, Is it require to re-start sqlserver?

Ans: Yes