Question:* Which of the following aggregate functions are supported by the Transact-SQL language?
Answer: • All of these are supported
Question:* In which context is the ROW_NUMBER() function valid?
Answer: • select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;
Question:* Which of the following storage options can be used to store LOBs and save storage?
Answer: • Both FILESTREAM and Sparse columns
Question:* Which of the following relationships requires a reference table?
Answer: • Many to many
Question:* Which of the following is not a valid string constant or hexadecimal constant?
Answer: • 'New York"
Question:* What is the name of the database that gets created in SQL Server 2012 when you create an "Integration Services Catalog"?
Answer: • SSISDB
Question:* Which statement is allowed inside of a User Defined Function?
Answer: • DECLARE
Question:* Which of the following is NOT an aggregate function in SQL Server?
Answer: • RANK
Question:* Which Transact-SQL statement should you include at the beginning of a stored procedure to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back?
Answer: • SET XACT_ABORT ON
Question:* What does DBCC stand for?
Answer: • Database Consistency Commands
Question:* True or False? Every row in a FileTable represents a file or a directory.
Answer: • True
Question:* To Improve stored procedure performance,which statement below is used to write beginning of SP?
Answer: • SET NOCOUNT ON
Question:* What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?
Answer: • SSIS
Question:* Which transaction isolation level allows dirty reads?
Answer: • READ UNCOMMITED
Question:* Which sql function finds the length of a string field?
Answer: • LEN
Question:* When creating indexes in SQL, what two main factors must be considered?
Answer: • Performance versus disk space
Question:* What is the default isolation level in SQL Server?
Answer: • Read Commited
Question:* What is the "WITH RECOMPILE" statement used for?
Answer: • Causes SQL Server to rebuild the query plan for a stored procedure on every call
Question:* Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function
Answer: • When casting to desired data type failed it will result NULL
Question:* What is a "CROSS APPLY" used for?
Answer: • Performing joins between calculated sets
Question:* Consider the following queries: A: select * from [tbl1] union select * from [tbl2]; B: select * from [tbl1] union all select * from [tbl2]; What is the difference between these two queries?
Answer: • Query A will return both result sets with duplicates removed; Query B will return both full result sets.
Question:* Which of the following statements will remove a UDF?
Answer: • DROP FUNCTION
Question:* You can create index on views.
Answer: • True
Question:* When you are dealing with lots of NULL Values in columns,which datatype is better?
Answer: • Variable Length
Question:* Which of the following allows you to modify (update or delete) all values of the parent table?
Answer: • CASCADE
Question:* Which of the following are not default database schemas?
Answer: • pte
Question:* Which of the following is a benefit of SQL Server replication?
Answer: • All of these
Question:* a FOREIGN KEY constraint can reference a UNIQUE constraint
Answer: • True
Question:* Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?
Answer: • All of these
Question:* The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.
Answer: • SQL_VARIANT
Question:* What is the basic difference between varchar and nvarchar?
Answer: • nvarchar supports unicode data.
Question:* Differential backups do what?
Answer: • Copy only the information that has changed since the last full backup
Question:* What is order of precedence of order by group by?
Answer: • group by then order by
Question:* True or false: A stored procedure can recursively call itself.
Answer: • True
Question:* What are the 3 Transact-SQL statements that are related to authorization?
Answer: • GRANT, DENY, and REVOKE
Question:* To run Schedule Job in SQL Server, which services used?
Answer: • SQL Server Agent Service
Question:* Data Transformation Services do what?
Answer: • They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats
Question:* What clause allows you to filter the result of a column that has been aggregated?
Answer: • HAVING
Question:* True or False? A table can have more than one primary key.
Answer: • False
Question:* What is the role of a processadmin user?
Answer: • Manages processes running in an instance of SQL Server
Question:* In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.
Answer: • True
Question:* What does the "IN" clause do?
Answer: • Compares a specified value against a list of values and is true if a match is found
Question:* What does the Query Analyzer, or SQL Server Management Studio, tool do?
Answer: • All of these
Question:* In this statement, "SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ", the JOIN term is the same as:
Answer: • INNER JOIN
Question:* Which of the following acts as the Database Engine's security system?
Answer: • Both Windows security and SQL Server security
Question:* Which is the slowest?
Answer: • TABLE scan
Question:* What is CTE
Answer: • Common Table Expresssion
Question:* To find all employees whose name ends with 'ith' complete this sql statement: "SELECT * FROM employees WHERE name " ...
Answer: • LIKE "%ith"
Question:* A sysadmin user is allowed to perform what actions?
Answer: • Any action in SQL server
Question:* What is a cursor in SQL Server?
Answer: • A special T-SQL construct used to iterate through the individual records returned by a query
Question:* Which clause would you use to perform conditional filtering on aggregate data in a query that uses "GROUP BY"?
Answer: • HAVING
Question:* What does "UDF" stand for?
Answer: • User Defined Function
Question:* Which of the following statements are related to transactions?
Answer: • All of these
Question:* "Primary Key" can only be created on a column that doesn't accept NULL
Answer: • True
Question:* Which statement adds a row to a table?
Answer: • INSERT INTO
Question:* Which of the following indices does NOT change the physical order of the rows in the table?
Answer: • Nonclustered Indices
Question:* What is the default port of Microsoft SQL Server (for TCP)?
Answer: • 1433
Question:* What is the correct syntax for a CTE?
Answer: • with myCTE ([column]) as (select ...) select [column] from myCTE;
Question:* A sysadmin password requires at least 8 characters and which of the following character types?
Answer: • All of these
Question:* Which of the following best describes the flow for the execution of a CLR stored procedure?
Answer: • Source code > Object code > Executable code > Procedure as database object
Question:* True or False? The EXEC keyword and the EXEC() function are equivalent to each other.
Answer: • False
Question:* Given a table with the following schema: create table table1 (field1 int) The table contains 1000 records. What would the following sql statement result in: update top (100) table1 set field1 = 1
Answer: • Update the field1 column to the value 1 in only 100 records in the table
Question:* "SELECT UNIQUE city FROM customers" will return what data set?
Answer: • An error.
Question:* Which of the following statements prevents users from preforming actions?
Answer: • DENY
Question:* A relationship that identifies that a column contains primary key values from another table is referred as what?
Answer: • Foreign key
Question:* Which of the following stores FileStream data?
Answer: • FileTable
Question:* The system database that holds information on SQL Server Agent Jobs is?
Answer: • msdb
Question:* True or False? You cannot execute dynamic SQL code from a function.
Answer: • True
Question:* "Unique Constraint" can be created on a column that accepts NULL
Answer: • True
Question:* You are querying a table that contains a field with the data type XML and need only rows that have a particular value in the XML data. Which of the following would not result in an error
Answer: • SELECT * FROM #table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE '%smith%'
Question:* All Database Backup/Restore Related information stored in which system database?
Answer: • MSDB
Question:* Which of the following SQL Server built-in functions is nondeterministic?
Answer: • GETDATE()
Question:* Which of the following is used to change tracking?
Answer: • Use triggers or use change data capture (CDC)
Question:* A ________ is an alphanumerical, hexadecimal, or numberic constant.
Answer: • literal value
Question:* Which of the following statements removes one or more previously granted or denied permissions?
Answer: • REVOKE
Question:* GETDATE() is a ....... function?
Answer: • Nondeterministic
Question:* True or false: You can modify data in a table using a User Defined Function.
Answer: • False
Question:* Which versions of SQL Server support table partitioning?
Answer: • Enterprise edition
Question:* The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.
Answer: • SELECT
Question:* Which of the following operators allows the specification of two or more expressions to be used for a query search?
Answer: • IN
Question:* In SQL Server 2012 introduces what new feature?
Answer: • Always On
Question:* True or false? Adding TOP to a select always improves performance.
Answer: • False
Question:* True or false? Intersection tables should have an autoincrement column as the primary index
Answer: • False
Question:* The command DBCC CHECKDB allows you to do what?
Answer: • Check the average page density
Question:* Which of the following is not considered a Boolean Operator?
Answer: • IN
Question:* What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL
Answer: • Header without any data
Question:* What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?
Answer: • CompoundCurve
Question:* When retrieving data, which command may be used to rotate a set of column names from horizontal across the top to vertically, appearing as values among the result set
Answer: • UNPIVOT
Question:* What does the EOMONTH function do?
Answer: • Adds the specified number of months to the input start date and then returns the last day of the month for the resulting date
Question:* SQL Server will create an Unique Non Clustered index, by default, when a Primary Key is created
Answer: • False
Question:* Which of the following is not a valid data type in SQL Server 2012?
Answer: • smalldatetime2
Question:* Can you select the built in Server Roles for SQL Server from the list below?
Answer: • diskadmin
Question:* By default, SQL Server treats NULL as what data type
Answer: • INT
Question:* The SETCERT.EXE utility can be used to do what?
Answer: • Specify the certificate used by the SQL server
Question:* True or false? Foreign keys always point to other tables.
Answer: • False
No comments:
Post a Comment