How many types of SQL Server Components are there?
How many types of SQL Server Tools are available in 2018?
This component is used to orchestrate the movement of data between SQL Server components and other external systems. Traditionally used for extract, transform and load (ETL) operations. Choose the option below which best describes the description above
How many number of instances of SQL Server Database Engine can you install on a Single server?
How many types of Normalization Forms are there?
This Normalization form eliminates repeating groups in individual tables. In Addition to this, it creates a separate table for each set of related data. Furthermore, It identifies each set of related data by using a primary key
Which Normalization Form is this?
Which of the following statements are true about Unique Identifiers?
Following are the steps when you are creating a schema except one:
Which of the following objects cannot be stored in a schema?
A primary key uniquely identifies each row within a table and candidate key could be used to identify a row. Which of the following is false about Candidate Key?
Keys which are references between tables and can also be self-referenced. Also, multiple type of this keys can exist in one table. Because of these keys rows cannot be deleted or updated without cascading options.
Among the options given below, what type of key is it?
You are partitioning data and using Partition function. Which of the following data types are not allowed in a partition function?
How many types of Data Integrity are there?
This integrity defines the allowed values in columns:
This integrity defines relationships between tables
Integrity in which primary key uniquely identifies each row within a table
While implementing Domain Integrity, Default Constraints plays an important role. Which of the following aspects of Default Constraints are Essential?
Sequences are another way of creating values for insertion into a column as sequential numbers. Select from the options below which best describes about Sequences when implementing Referential and Entity Integrity.
Indexes in SQL Server plays very important role when it comes to performance and memory optimization. What does Index Depth Means? Choose all the correct options
Maximum number of NonClustered Indexes a table can have?
SQL Server automatically creates statistics on Indexes as it needs to have knowledge of the layout of the data in a table or index before it optimizes and executes queries. Among the queries listed below which 2 queries can update statistics on a specific table and on a specific index?
There are many options we can select in Query Store. Among many of the options we have Operation Mode settings. What other options are available we can select? Choose the one which is incorrect:
What type of different workload formats does Database Engine Tuning Advisor does not accept?
There are few type of Plans in SQL Server that is used to show the query performance. Among them one is Estimated Execution Plan and the other is Actual Execution Plan. From the description below choose all which describes both plans. You can also choose Neither, two or All as well.
• Creates a plan but does not execute the Query
• Estimated Number of Rows based on Statistics.
• Includes actual number of rows returned
• If different, statistics are out of date or missing.
Which of the following Statements best describes, why Column Store Indexes Performs well?
Below statements describes characteristics of NonClustered and Clustered ColumnStore Indexes. Choose all the options which are not true.
You have to create a ColumnStore in-memory table. Below is the query through which you can create one:
CREATE TABLE InMemoryAccount
(accountkey int NOT NULL,
accountdescription nvarchar (50),
accounttype nvarchar (50),
unitsold int,
CONSTRAINT [PK_NC_InMemoryAccount] PRIMARY KEY NONCLUSTERED([accountkey] ASC),
INDEX CCI_InMemoryAccount CLUSTERED COLUMNSTORE)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
As the query is created now you want to make sure it works. For the query to work, which of the following is not mandatory, such that, without it the query will not work:
Part1:How many types of views are there in SQL Server?
Part2a: How many types of views are there in SQL Server?
Part2b:How many types of User Defined views can you create in SQL Server?
Part3a:How many types of system views are there in SQL Server?
Part3bViews that provide information about the state of the SQL Server Database Engine are called as?
We can update the data through views, but we must ensure following conditions are met:
A nested view has some advantages as well as disadvantages.
In Part1, below mentioned are advantages of nested views from which you have to choose all which are correct.
In Part2 choose all the answers which you think are incorrect about disadvantages of nested views
Part1: Advantages:
Part2: Disadvantages:
Which of the following is true about Stored Procedures. Mark all options which are correct.
Parameter-Sniffing problem occurs when stored procedure is executed. SQL Server attempts to reuse query execution plans from one execution of a stored procedure to the next. Although this is mostly helpful in some circumstances when stored procedure uses different execution plan for different sets of parameters. But SQL Server provides 4 resolutions to these problems. Which of the following is not a solution for Parameter-Sniffing issue?
How many types of Functions are there in SQL Server?
Some functions are deterministic, some are Built-in functions, and some are nondeterministic. Given below explains what both of these functions are. Choose all which explains the correct definition of each function
Which of the following statements are not correct about Inline Table-Valued Functions?
Part1: Which of the following statements about After Triggers are not true?
Part2:Which of the following statements about Instead of triggers are true?
Choose all the statements that are correct with regards to After Insert Triggers
Triggers are useful in many situations and are sometimes necessary to handle complex logic. However, triggers are sometimes used in situations where alternatives might be preferable. Choose all the alternative approaches below that cannot be used instead of using Triggers:
If you want to convert your tables or any specific table to memory-optimized tables, Memory Optimization Advisor Performs this process in fixed number of steps. How many steps does it take to convert the table into memory-optimized table?
Choose all the statements below which are not true about natively compiled stored procedures.
In order to create a natively compiled stored procedure certain steps are followed, one of the step from those steps are Creating Procedure Statement. Which of the following options you must use in your CREATE PROCEDURE STATEMENT?
Which of the following objects of SQL server cannot be used in SQL Server?
When considering using managed code in SQL server, which of the following factors are important?.
SQL Server Data tools was first introduced inn SQL Server 2012.It is also integrated into Visual Studio. Main purpose of the SSDT is to develop, debug and refactor database code in addition to developing transact-SQL and CLR managed code. SSDT provides various templates for different SQL Server objects, some of them are listed below. Only one of the object is not Which is?
Part 1: SQL Server has a native datatype for storing XML data. Which can be used for variables, columns and parameters in databases. Which of the following is true about XML Data?
Part 2: Which of the following is not true about XML Namespaces?
Part 1: You want to store XML data, and you know that you can enable any XML (typed vs untyped). In Which of the following situations XML typed data should be used?
Part 2: XML indexes are used to improve the performance of XQuery-based queries. There are two types of XML indexes: Primary XML and Secondary XML indexes. You can construct three types of Secondary XML indexes to help answer specific XQuery queries rapidly. The three types of Secondary XML indexes are: Path, Value and Property Indexes. Select all the options which are correct about each type:
The FOR-XML clause in SQL Server is used to enable the Transact-SQL SELECT statement to return data in an XML format. It can be configured to return attributes, elements, and/or schema that are required by client applications.
Part 1: For XML have 4 different modes. Which of the following is/are not the mode of FOR XML clause?
Part2: When using Raw mode queries which of the following is not true?
Part 3: From the options below, one option describes Auto Mode, one of the four modes in FOR XML clause. Select the correct one:
SQL Server supports many measurement systems directly. When we specify a spatial data type in SQL Server, we also specify the measurement system used. It is done by associating a spatial reference ID with the data. Which of the following statements below about Spatial Reference Identifiers are true. Choose all that apply.
Which existing SQL Server data type could you use to store, but not directly process, raster data?
Which of the following statements about Spatial Data Formats are true?
There are some technical challenges when integrating BLOBs with a database. And the way data is handled changes significantly. Among the factors listed below which one is not a technical challenge faced when integrating BLOBs with a database:
When you consider using a FILESTREAM, which of the following statements are true?
Let’s assume you have a full-text index on a table, How many types of searches against the character-based data can you execute?
There are two types of approaches to resolving data conflicts during concurrent operations by two different users. One approach is Pessimistic and the other one is optimistic concurrency. With regards to Optimistic approach, which of the following statements are not correct when choosing optimistic concurrency model for a system?
Part 1a: Which of the following Transaction isolation levels are of lowest level in SQL Server?
Part 1b: Which of the following Transaction isolation levels are highest in SQL Server?
Part1a: Although Row Versioning based isolation levels have some benefits but, it can cause some issues as well. Which of the following statements describes issues with row versioning-based isolation levels?
Part1b: If Transactions can be defined as A logical unit of work, made up of one or more Transact-SQL statements (Atomicity, Consistency, Isolation and Durability. Which one of the following statements describe Atomicity?
Extended Events is the successor to SQL trace and SQL Profiler. Its architecture is based on Extended events engine which provides capabilities such as user defined session and other component of the architecture is a package, which defines the objects available to a session. User defined session performs some activities which includes:
Which of the following statements will enable Live Query Statistics for all sessions in Activity Monitor?
Which of the following statements about Tempdb are not correct/false?
Part1b: If Transactions can be defined as A logical unit of work, made up of one or more Transact-SQL statements (Atomicity, Consistency, Isolation and Durability. Which one of the following statements describe Atomicity?
Learning Style
Learning Style
Difficulty
Course Duration
| Buy Individually |
|---|
|
About Individual Course:
$109.00
/ Each |
| Lab Access | No |
|---|---|
| Learning Style | Self-Paced Learning |
| Learning Type | Practice Exam |
| Difficulty | Beginner |
| Course Duration | 1 Hour |
| Language | English |
Online IT Certification Courses
& Training Programs
Mon-Fri 9am to 6pm CST
Mon-Fri 9am to 6pm CST
Chat