SQL Server Questions

No - The SQL Server Express Edition does not have a SQL Server Agent Service.

Do all of the SQL Server 2005, 2008, 2008 R2 editions install the SQL Server Agent service by default?

It is a way to convert the original text of the stored procedure into encrypted form. The stored procedure gets obfuscated and the output of this is not visible to CREATE PROCEDURE Abc WITH ENCRYPTION AS << SELECT statement>> GO WITH ENCRYPTION indicates that SQL Server will convert the original text of CREATE PROCEDURE statement to an encrypted format. Users that do not have no access to system tables or database files cannot retrieve the encrypted text. However, the text will be available to privileged users.

Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.

SQL Server (server-side) supports 3 major elements: a. Creation of XML fragments: This is done from the relational data using FOR XML to the select query. b. Ability to shred xml data to be stored in the database. c. Finally, storing the xml data. Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of: • XML Views: providing bidirectional mapping between XML schemas and relational tables. • Creation of XML Templates: allows creation of dynamic sections in XML. SQL server can return XML document using FOR XML clause. XML documents can be added to SQL Server database and you can use the OPENXML clause to display the data from the document as a relational result set. SQL Server 2000 supports XPath queries.

What XML support does the SQL server extend?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query: SELECT * FROM sys.dm_os_wait_stats; Another example is examining current sessions, much like the sp_who2 command: SELECT * FROM sys.dm_exec_sessions;

What are DMVs?

Case sensitivity - A and a, B and b, etc. Accent sensitivity Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

What are different types of Collation Sensitivity?

SQLAgentUserRole - Ability to manage Jobs that they own SQLAgentReaderRole - All of the SQLAgentUserRole rights and the ability to review multi-server jobs, their configurations and history SQLAgentOperatorRole - All of the SQLAgentReaderRole rights and the ability to review operators, proxies and alerts, execute, stop or start all local jobs, delete the job history for any local job as well as enable or disable all local jobs and schedules

What are the new SQL Server Agent Fixed Database Roles and what is the significance of each role?

The Builtin/Administrator account is basically used during some setup to join some machine in the domain. It should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations.

What is Builtin/Administrator?

Failover clustering is mainly used for data availability. Typically, in a failover cluster, there are two machines. • One machine provides the basic services and the second is available to run the service when the primary system fails. • The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called as cluster controller. In an event of failure of primary computer, the failover system takes control.

What is Failover clustering overview?

Lock escalation is used to convert row locks and page locks into table locks thereby "escalating" the smaller or finer locks. This increases the system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory. Hence, escalation is used. Lock escalation from SQL Server 7.0 onwards is dynamically managed by SQL Server. It is the process of converting a lot of low level locks into higher level locks.

What is lock escalation?

It allows you to embed an if-else like clause in the SELECT clause.

What is the SQL CASE statement used for? Explain with an example?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value. A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values. You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What is the difference between a clustered and a nonclustered index?

SQL Server Agent Log is a record of all entries written by the SQL Server Agent service. Performance Monitor and Profiler can be setup to monitor the status of a particular job.

What resources are available to troubleshoot SQL Server Agent?

I'm not asking if they regularly attend, I'm not asking if they speak, I'm not asking if they run for office. At the senior DBA level, I'd just be thankful if they were at least vaguely aware that user groups existed. Huge massive bonus points if they've been involved with the community, and I'd skip the next question.

When and where does the local user group meet?

Windows Task Scheduler. Third party solutions.

If SQL Server Express does not have a job scheduling interface what alternatives are available?

Use a brochure from any third party application (like Microsoft Project Server or Blackberry Enterprise Server) and say the project manager wants to set this up. I want the DBA to ask questions like: How big will the database be? (Leading to questions about whether we can add the database to an existing server) How critical is the database? (Leading to questions about clustering, disaster recovery, high availability) What's the company standard on virtualization? (Can we save money by using a virtual server) If the senior DBA candidate comes back with a shocked look and doesn't know where to begin, then they haven't done a lot of deployments. That might be fine if your shop rarely does new deployments, or if you're hiring a development DBA, but they still should have some basic knowledge about sizing.

A project manager needs a new SQL Server. What do you ask her?

In one of the earlier questions, you probably found an area of weakness for the DBA's knowledge - heck, nobody's perfect. Go after that weakness. Challenge it, make them uncomfortable, and really push their buttons. This is your one chance to see how they handle stress before the brown stuff actually hits the fan. Ideally, a senior DBA is someone who's had their cage rattled more than once, and they're comfortable under the gun. No, don't use an actual gun during the interview - but that reminds me of a plastic slot machine I used to keep on my desk at the office. When you pulled the lever, it shot water straight at you like a squirt gun. Always fun to see how somebody handled that one, but I wouldn't let job candidates pull the lever. That's a little overboard!

Ask stressful questions.

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 provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

Can SQL Servers linked to other servers like Oracle?

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.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

• Buffer Cache: Buffer cache is a memory pool in which data pages are read. The ideal performance of the buffer cache is indicated as: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is need physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server. • Log Caches: Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log cache separately from the buffer cache.

Can you explain about buffer cash and log Cache in SQL Server?

MS SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server. Following are the steps to use Linked Server for any OLE DB. You can refer this to use an MS-Excel workbook. 1. Open SQL Server Management Studio in SQL Server 2. Expand Server Objects in Object Explorer. 3. Right-click on Linked Servers. Click on New Linked Server. 4. Select General page in the left pane and i. Type any name for the linked server in the first text box ii. Select the Other Data Source option. iii. Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list. iv. Type the Excel as the name of the OLE DB data source. v. Type the full path and file name of the Excel file in Data Source box. vi. Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97. vii. To create a linked server click on OK.

Describe how to use Linked Server.

Start with just that open-ended interview question, and if they have trouble getting started, give them a scenario. "Say we've got a table for Orders, and a table for OrderDetails. Someone places an order for two books - Bacon: A Love Story and the hit bestseller Eat What You Want and Die Like A Man. Tell me what happens." After they've answered, ask them when transactions should not be used. I don't want my developers wrapping anything inside a transaction unless it absolutely needs to be. (Unlike bacon, which should be used as often as possible for wrapping purposes.)

Explain when and how transactions should be used.

I like to phrase this interview question this way because I'm not saying the DBA is right - I'm just asking the developer to explain the DBA's point of view. I don't have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question. The candidate gets bonus points if they seem even vaguely aware of the terms "set-based processing" and "row-based processing", but that's purely a bonus. (I wish I could say that these concepts are requirements, but in today's economic market, companies don't always want to pay top dollar to get the best candidates.)

Explain why DBAs don't like cursors

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

How are transactions used?

• Use IF logic with the @@SERVERNAME function compared against a string with a RETURN command before any other logic.

How can a SQL Developer prevent T-SQL code from running on a production SQL Server?

You can create a trigger that will automatically delete elements in the second table when elements from the first table are removed.

How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?

Manually review the failed Jobs in Management Studio. Setup an automated process to query the msdb.dbo.sysjobhistory system table to find the failures.

How many options are available to identify failed jobs?

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 the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

How to get @@ERROR and @@ROWCOUNT at the same time?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

SELECT * FROM table1 SELECT COUNT(*) FROM table1 SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Name 3 ways to get an accurate count of the number of records in a table?

...

Name some commands that can be used to manipulate text in T-SQL code. For example, a command that obtains only a portion of the text or replace a text string, etc.

True

True or False - Can a single Job have multiple Job Schedules?

True

True or False. Besides the MSDB database, SQL Server Agent also has configuration parameter related data stored in the registry.

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples: DBCC CHECKALLOC — Check disk allocation consistency. DBCC OPENTRAN — Display information about recent transactions. DBCC HELP — Display Help for DBCC commands.

What are DBCC commands?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table. CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) ) Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects. Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed. CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) ) Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

What are temp tables? What is the difference between global and local temp tables?

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 non clustered 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 non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the difference between clustered and a non-clustered index?

A table can have one of the following index configurations: No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes

What are the different index configurations a table can have?

SQL Server Agent is a Windows service that accompanies each instance of SQL Server on a machine for most editions of SQL Server. SQL Server Agent is primarily a job scheduler for executing T-SQL, SSIS, DOS, etc. scripts. SQL Server Agent is also responsible for defining Operators and Alerts. Operators can be associated with Jobs or Alerts, so that particular people (email addresses, pagers, NET SEND) are notified or distribution lists are notified if an issue occurs. Alerts can be setup for custom conditions or errors of a particular severity level.

What sorts of functionality does SQL Server Agent provide?

This is almost a bonus question. Most of the time, the candidate doesn't know because it's a function of the data modeler or architect, not the developer. However, I want to see how the candidate reacts to tough questions. Ideally, they say in a relaxed tone of voice, "I'm not sure, but I know who I'd ask." If they don't mention where they'd go, ask them where they go for SQL Server answers. Speaking of which...

What's the difference between a primary key and a clustered index?

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

Which command using Query Analyzer will give you the version of SQL server and operating system?

MSDB

Which database stores the SQL Server Agent objects?

Zoom out to the big picture. Tell me why you're in this chair right now. Is this some sort of career plan? Have they heard good things about the environment from another employee? Are they looking for an employee discount on products? (It doesn't work at wine & spirits companies, just for the record.) Or are they just desperate? Nothing wrong with desperation, but remember that desperate employees are desperate for something, and you'd better find out what it is. If they're desperate for money, it's going to color the judgements they make. I'm not worried about someone stealing data and selling it as much as I'm worried about someone taking the job and then bailing out shortly thereafter for just a little more money from somebody else.

Why are you here?

If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What's an orphan? How do we make sure that we don't end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?

7. Explain referential integrity and where it can be enforced.

SQL Server has a feature for sending mails. Stored procedures can also be used for sending mail on demand. With SQL Server 2005, MAPI client is not needed for sending mails. The following is the process for sending emails from database. • Make sure that the SQL Server Mail account is configured correctly and enable Database Mail. • Write a script to send an e-mail. The following is the script. USE [YourDB] EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected]; [email protected];[email protected]' @body = ' A warm wish for your future endeavor', @subject = 'This mail was sent using Database Mail' ; GO

Explain how to send email from SQL database.

Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.

What are the advantages of using Stored Procedures?

Writing a query with parameters. • Using EXEC. • Using sp_executesql.

What are the three ways that Dynamic SQL can be executed?

A senior DBA should have a basic grasp of the advantages and disadvantages of the major database platforms. They've probably answered this question before, too - if not from a manager, then from a developer who's whining because they think Platform X is better than Platform Y. I also want to see senior DBAs that can clearly explain a very political concept without taking it personally. My ideal DBA knows when his platform isn't the right fit, and has no problem suggesting other ideas.

I'm a manager, and you're my Senior DBA. Explain to me why we shouldn't switch to MySQL or Oracle.

Use the SET ROWCOUNT command. For instance, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0.

If you are a SQL Developer, how can you delete duplicate records in a table with no primary key?

I want to know that they've got at least a couple of people they can call when the going gets rough and the servers catch fire. These other references could be mentors, or could be people they've mentored or just worked with along the way. I expect to get terrified looks, and I'd answer those by saying, "I don't want to raise any red flags by calling people at your current employer, and I'm sure you know people who've left your company and moved on. I just want to talk to people who've worked with you on projects or on problems. I won't ask for your level of technical competency, because these other guys can't judge that. I just want to know you've interacted with them." I know, it's creepy, but here's the problem: there's a lot of fakers out there with all the right answers, but no actual experience. At the senior level, for somebody with five or more years of experience, they have to have met other people who can at least verify they're database administrators.

Can you give me references from other DBAs and developers who aren't at your company?

I want to hear that they do things like load tests or maybe look at execution plans. I'm sometimes comfortable when a senior developer says things like, "I can pretty well tell when something isn't going to scale, because I know the production boxes really well." The key is asking a followup question about times when things didn't scale.

How can you tell if a query will scale for production?

When new versions of database servers come out, how do you prefer to learn how to use 'em? Ideally, I want to hear a DBA say they build their own server under the desk when the beta comes out, and start hammering it and getting their arms around it long before it gets released. Not everybody has that much time, though - they might be stretched to the breaking point at their current job, with barely enough time to get their work done, let alone train on new versions. In that case, I like to level-set them by saying, "You're coming to work here because we're not that kind of shop. I want you to keep your knowledge current. How much time per month do you need to keep current, and how would you do it?" Some DBAs learn best by going to offsite training classes, and can't budget their time well enough or maintain a train of thought to learn inside the office. As a manager, you want to know that before you hire the person, so you can build that training budget into their salary. (You don't want unqualified employees, do you? Yeah, you probably do - I've worked for you, ha ha ho ho.)

How do you learn new things?

sp_help_job This stored procedure returns information about the job. If no parameters are used information is returned for all jobs. If a specific job_id is passed it gives you job information, job step information, schedule information and last run information. sp_help_jobactivity This stored procedure returns information about the status of the job run. If no parameters are used information is returned for all jobs. sp_help_jobcount This stored procedure gives you a count of how many jobs a schedule is tied to. This stored procedure requires either @schedule_id or @schedule_name to be passed as a parameter. sp_help_jobhistory This stored procedure returns all history information for all of the job runs. If no parameters are used information is returned for all jobs. If you also use parameter @mode = N'FULL' this provides additional information about each job step. sp_help_jobs_in_schedule This stored procedure gives you a list of the jobs that are tied to a schedule. This requires either @schedule_id or @schedule_name to be passed as a parameter. sp_help_jobschedule This stored procedure provides jobs schedule information for a particular job. This stored procedure requires either @job_id or @job_name to be passed. sp_help_jobserver This stored procedure provides information about a specific server tied to a job. This stored procedure requires either @job_id or @job_name to be passed. sp_help_jobstep This stored procedure provides information about the job steps for a specific job. This stored procedure requires either @job_id or @job_name to be passed. sp_help_jobsteplog This stored procedure returns information about a specific job step log. This stored procedure requires either @job_id or @job_name to be passed. sp_get_composite_job_info This stored procedure returns combined data for all jobs in the system. If no parameters are used info is returned for all jobs.

How many of the SQL Server Agent system stored procedures can you name with their associated purpose?

sysjobactivity stores data about job activity sysjobhistory stores data for all historical executions of all jobs sysjobs stores data about each job such as the name sysjobschedules stores job schedule information sysjobservers stores server information related to a job sysjobsteps stores specific job step information such as the type of code being issued, the actual code, etc. sysjobstepslogs stores specific job step log information for each run if this is enabled.

How many of the SQL Server Agent system tables can you name with their associated purpose?

There are many ways to find second highest salary of Employees in SQ. You can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery: Select MAX(Salary) from Intellipaat_emplyee WHERE Salary NOT IN ( select MAX(Salary) from Intellipaat_employee.

How to find second highest salary of an Employee

The following is the process to make a remote connection in database: 1. Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database. 2. Click on Surface Area Configuration for Services and Connections. 3. Click on SQLEXPRESS/Database Engine/RemoteConnections 4. Select the radio button: Local and Remote Connections and select 'Using TCP/IP only' under Local and Remote Connections. 5. Click on OK button / Apply button

How to make remote connection in database?

Create a column as type 'blob' in a table. Read the content of the file and save in 'blob' type column in a table. Or Store them in a folder and establish the pointer to link them in the database.

How to store pdf file in SQL Server?

And really pretend that you're a developer. If you're a DBA manager, bring in one of your toughest developers to play bad cop. Challenge them - does it really improve performance or manageability? How do you know? Is it just your opinion, or where's the proof? If they can explain it in clear, easy-to-comprehend terms, that bodes well for their ability to communicate with other teams. Speaking of inter-team communication...

I'm a developer. Explain why I need a unique key on my table.

Synonyms were released with SQL Server 2005. • Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In simple words, the original object that is referenced in the whole code is using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes. • Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle tier code. This could be useful during a re-architecture or upgrade project.

In what version of SQL Server were synonyms released? How do synonyms work and explain its use cases?

Yes, six commands are available to import data directly in the T-SQL language. These commands include: • BCP: The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, bcp is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method. • Bulk Insert: The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with bcp (bulk copy program) via a script. • OpenRowSet: The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one. • OPENDATASOURCE: Provides ad hoc connection information as part of a four-part object name without using a linked server name. • OPENQUERY: Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. • Linked Servers: Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services? If so, what are the commands?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is Log Shipping?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What is NOT NULL Constraint?

Challenge the DBA to do this on a whiteboard with pseudocode. The code doesn't have to be perfect syntax, but we're looking for the ability to solve this problem clearly and quickly. I'd ask this of any type of DBA, production or development. There's countless ways to do this, but I'm almost not as concerned with their technical accuracy as I am with the way they approach the problem itself. Do they get excited? Do they scribble out a few different ideas? Are they stunned at the thought of having to actually write T-SQL? Do they make excuses?

Solve the FizzBuzz problem with SQL code. Here's a quote of the FizzBuzz problem: Write a program that prints the numbers from 1 to 100. But for multiples of three print "Fizz" instead of the number and for the multiples of five print "Buzz". For numbers which are multiples of both three and five print "FizzBuzz".

This is a spin on the classic interview question, "Tell me about a time when you failed." Implemented a user-defined function, trigger, CLR in the database, or something else that made the DBA freak out? I want to hear that the candidate listened to what the DBA had to say, good or bad.

Tell me about a time when a DBA got mad at you.

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

What is OLTP (Online Transaction Processing)?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

What is PRIMARY KEY?

Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

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

Properties of Sub-Query A sub-query must be enclosed in the parenthesis. A sub-query must be put in the right hand of the comparison operator, and A sub-query cannot contain an ORDER-BY clause. A query can contain more than one sub-query. Types of Sub-Query Single-row sub-query, where the sub-query returns only one row. Multiple-row sub-query, where the sub-query returns multiple rows,. and Multiple column sub-query, where the sub-query returns multiple columns

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

The primary problem here is that the object will outlive the session it came from. Lazily loaded properties won't get loaded if needed later. To overcome the problem, perform cache on the object's id and class and then retrieve the object in the current session context.

What are the risks of storing a hibernate-managed object in cache? How do you overcome the problems?

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

What command using Query Analyzer will give you the version of SQL server and operating system?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores. SELECT * FROM table_name (NOLOCK) Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint.

What does the NOLOCK query hint do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.

What does the SQL Server Agent Windows service do?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is BCP? When does it used?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

What is CHECK Constraint?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

What is FOREIGN KEY?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly. Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is SQL Profiler?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.

What is SQL Server Agent?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

What is UNIQUE KEY constraint?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows: WITH ExampleCTE (id, fname, lname) AS ( SELECT id, firstname, lastname FROM table ) SELECT * FROM ExampleCTE A CTE can be used in place of a view in some instances.

What is a CTE?

A SQL Server Agent Proxy is an account that is setup to help secure a particular sub-system, so that if an login\user is trying to access the particular sub-system and does not have rights, the proxy will grant rights. The SQL Server Agent Proxies include: ActiveX Script Operating System (CmdExec) Replication Distributor Replication Merge Replication Queue Reader Replication Snapshot Replication Transaction-Log Reader Analysis Services Command Analysis Services Query SSIS Package Execution Unassigned Proxies The SQL Server Agent Proxies offer a new level of granularity for SQL Server Agent that was not previously available.

What is a SQL Server Agent Proxy? Can you name some of the sub-systems proxies? Why are the proxies of any significance?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a Scheduled Jobs or What is a Scheduled Tasks?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What is a query execution plan?

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 then do bulk of inserts and to restore those indexes after that.

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables. The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a view? What is the WITH CHECK OPTION clause for a view?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

What is an execution plan? When would you use it? How would you view the execution plan?

De-normalization is used to optimize the readability and performance of the database by adding redundant data. It covers the inefficiencies in the relational database software. De-normalization logical data design tend to improve the query responses by creating rules in the database which are called as constraints. Examples include the following: - Materialized views for implementation purpose such as: - Storing the count of "many" objects in one-to-many relationship - Linking attribute of one relation with other relations - To improve the performance and scalability of web applications.

What is de-normalization in SQL database administration? Give examples

TRUNCATE: TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE cannot be rolled back. TRUNCATE is DDL Command. TRUNCATE Resets identity of the table DELETE: DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement. DELETE Can be used with or without a WHERE clause DELETE Activates Triggers. DELETE can be rolled back. DELETE is DML Command. DELETE does not reset identity of the table. Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

What is difference between DELETE and TRUNCATE commands?

Job management paradigm with a master server and one or more target servers. The master server sends and receives jobs from the target servers with all of the job and job step related information stored on the master server. When the jobs complete on the target servers notification is sent to the master server so this server has the updated information. Multi-server administration is used in a enterprise environment where a consistent set of jobs need to run on numerous SQL Servers, this technology helps to consolidate the creation, execution and management of those jobs.

What is multi-server administration and when would you use it?

• Stuff function: - This function is used to replace string from the given start position, passed as 2nd argument with string passed as last argument. In Stuff function, 3rd argument defines the number of characters which are going to be replaced. Syntax:- STUFF ( character_expression , start , length , replaceWith_expression ) For example:- Select Stuff ('Intellipaat', 3, 3, 'abc') This query will return the string "Iabcllipaat". In this example, Stuff function replaces the string "Intellipaat" onwards the 3rd position('nte') with 'abc'. • Replace Function:- Replace function is used to replace all occurrence of a specified with the string passed as last argument. Syntax:- REPLACE ( string_expression , string_pattern , string_replacement ) For example:- Select Replace ('Abcabcabc', 'bc', 'xy') This query will return the string Axyaxyaxy. In this example, Replace function replaces the occurrence of each 'bc' string with 'xy'.

What is the STUFF function and how does it differ from the REPLACE function in SQL?

STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

What is the STUFF function and how does it differ from the REPLACE function?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

What is the default port number for SQL Server?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What is the difference between a Local and a Global temporary table?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What is the difference between truncate and delete?

• The sp_MSforeachdb system stored procedure accepts the @Command parameter which can be exetecuted against all databases. The '?' is used as a placeholder for the database name to execute the same command. • The alternative is to use a cursor to process specific commands against each database.

What is the native system stored procedure to execute a command against all databases?

OPENXML parses the XML data in SQL Server in an efficient manner. It's primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using 'xpath'.

What is the purpose of OPENXML clause SQL server stored procedure?

If somebody's been doing database administration long enough to claim the title Senior DBA, they've built up a little wish list of database management tools they've seen along the way. Tool types might include: Data modeling Change management Backup compression Performance monitoring Alerting If they had a $X tool budget for their workstation, how would they spend it? Forget corporate standards - I want to know what tools they'd use if they could pick on their own. I'm not asking what software they've had experience using, because they might work somewhere so cheap that they're restricted to native tools only. They have to have at least seen some ads for products that looked cool, though. I might follow up with questions about tools we were using in-house already by saying things like, "We're currently using Product X for monitoring. Have you seen it? What'd you think of it?" This does two things: it gives me an outside opinion about other tools out there that my DBAs could be using to do a better job, and it tells me how much the job candidate has seen.

What third party database tools are your favorites?

Both primary key and unique key enforces 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.

What's the difference between a primary key and a unique key?

I'm not looking for the best answers - I'm just looking to hear that they've done some work to performance tune their queries. If they're doing fully logged individual record inserts, one at a time, into a data warehouse-size system, we're going to have problems down the road. (Yes, I've actually worked with a BI developer that did millions of individual inserts per night in full recovery mode and thought the performance was the database's fault.) Bonus points if they link back to the previous interview question and talk about whether or not they should disable constraints or referential integrity during data loads. (I don't care what their final answer is, but I just want them to know the pros and cons.)

What's the fastest way to get a thousand records into the database?

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too often because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

When is the use of UPDATE_STATISTICS command ?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

When is the use of UPDATE_STATISTICS command?

Personally, I like stored procedures because they're easier for us DBAs to test, tune and tweak. On the other hand, the developer community isn't always as fond of stored procs. For their side, see these posts by Jeff Atwood: Who Needs Stored Procedures, Anyway? Stored Procedures vs Ad-Hoc SQL My Database is a Web Service I don't mind what arguments the coder candidate uses, but I want to see 'em put some thought into it. No matter which angle they take, I'll play the devil's advocate and prod them with arguments just to see how they react.

Where do you like business logic - in the app or in the database? Why?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

Which TCP/IP port does SQL Server run on? How can it be changed?