I'll try, but as mentioned, context might be important.
1)Authorization is implemented in sql server thru? A) user B) windows c) roles d) permissions
I would go for option
C for this question. Permissions define WHAT someone can do. However, these permissions are assigned TO different entities (I.e. users/groups or roles). Windows is a form of authentication, not authorization.
2)--- permission gives access to define a "schemabinding" view on table without giving access to table ? A)references b) alter c) execute as d) grant
I am not sure I understand the question per se (it's worded in such a way there could be multiple ways to read it), but in order to give someone access to the view, you would use the
grant option.
3) identify data integrity constraints from following , A) function B) varchar C) Foreign key D) check
C. You enforce referential integrity by using foreign keys.This ensures that you cannot, for instance, create an invoice for products you do not have in your products table.
4)identity user database from the following: A) msdb B) resource C) tempdb D) master E) adventure works F) All
Again, I'm not entirely sure I understand the question. There is no user database in SQL Server. Authentication to the server is handles through
logins. Those are stored in the master database, and define who can access the server in some capacity. However, a user is based on a login, and is created on a per-database level. So if you want to grant a user access to a specific database, you need to make sure there is a matching login for that user (Either by creating it on a per-user basis, or by ensuring they are in a Windows group that has been created as a login.
The second step is then to grant the user (or group) access to the database. So depending on the wording of the actual question, the answer could be
D (login info), or
F for all databases.
A third option is that the question is asking which of the above databases is not a standard SQL Server database. SQL Server has a few default databases: master (to store login information, a lists of databases on the instance and other top-level information), msdb (which contains information about jobs, alerts, etc, model (think of model as a template database; any settings you apply to it, and any objects created in it will be inherited by any other databases you create on the server) and tempdb (the equivalent of swap space for SQL Server).
If this is what the question is about, then the answer is
B and
E.
5)-characteristics of inline table value functions are ?
C.
Source:
https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx
Specifically:
Inline user-defined functions follow these rules:
- The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
- There is no function_body delimited by BEGIN and END.
A) Has body and with one table as return value B) has no body with one scalar return value C) has no body with one table as return value D) has body with one vector return value E) NONE
6)SQL Sevrver integration services is responsible for perforing analytical processing thru multidimensional data structures containing de-normalized and aggregated data ,often from diverse data sources?
The wording on this question is confusing, but I'm going to go with
false. Integration services is an ETL (Extract, Transform and Load) tool. It allows you to move data around, and manipulate it as you work with it. This means you can massage, clean and combine data from multiple sources, and deliver it to a multi-dimensional data structure.
However, the analytical processing of that data is handled by SQL Server Analysis Server (SSAS).
7)database engine is a online analytical processing engine? True or false
False. It's an Online Transaction Processing (OLTP) engine. SSAS would qualify as an Online Analytical Processing (OLAP) engine, though. Also see
http://datawarehouse4u.info/OLTP-vs-OLAP.html.
8) identify odd from below ?
A) server plus client access license B) per processor C)none of the options D)per processor device access license E) server plus device client access license
I'm assuming this question intends to ask what licensing mode SQL Server employs. Licensing is something I tend to stay far away from (I'm a technician, and legal mumbo jumbo is not my cup of tea), but to the best of my knowledge, the current licensing model is based on the number of
cores. It used to be a choice between
per-processor (Meaning the more CPUs your machine had, the more you paid) or
per seat (concurrent users). However, with the introduction of multi-core CPUs, the system was changed, and as far as I know, you currently only have the
per core licensing method.
The single exception for this is a licensing option for the
Standard edition of SQL Server. This does allow for a "
Server + CAL" (client access license).
If this is an official Microsoft exam, make sure you know this exception. They are quite fond of wording the question in ways that trick you. (I.e. "Which of the below is a valid license option for Enterprise Edition", which would mean
C would be correct. You could get the exact same question with Standard edition, though, and then the answer would be
E.
More info:
https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing
9)Default instances are identified by the name of the server on which it is installed and name of the instance
Again, very confusingly worded. A default instance normally would be installed as
<hostname>/MSSQLSERVER. If you install SQL Server Express Edition, it used to be
<hostname>/SQLExpress (top of my head, don't crucify me if that's not the case).
So would the
MSSQLSERVER or
SQLEXPRESS be what they consider the name of the instance? I'm inclined to say "yes". However, in
this article, a clear distinction is made, and the
SQLExpress version is referred to as a named instance. It furthermore also states that:
The default instance name is MSSQLSERVER; it does not require a client to specify the name of the instance to make a connection.
That makes me want to change my answer to
false.
10)sql server should be configured to run under domain accounts only ?
It depends. It'd certainly make administration easier. However, it would also negatively impact security. After all, if an attacker would compromise a single server, it'd mean they now have domain access. By default, SQL Server nowadays installs using virtual users. However, if you intend to run a cluster, you need Domain Accounts (as the cluster needs to be able to fail over from one server to another, using a local server account is not possible).
I would say the answer is
False, unless the question involves clusters.
More info:
https://docs.microsoft.com/en-us/sq...gure-windows-service-accounts-and-permissions
occurs when the extends belonging to an index are not contigous or logical and physical page orders are different results in expensive index scan
Internal fragmentation simply means that there are gaps on the pages making up the index. Imagine you have a table, with an index on it. Over time, records are updated, inserted or deleted. The data is still located in physical order, but the pages may contain some gaps.
Some degree of internal fragmentation is actually desirable, as it means you can easily insert new rows without the server having to split pages. It is able to simply store it on the page, which might need some internal updating to accomodate the shifts, but the impact of that is minimal.
External fragmentation is when the physical order of the pages is completely different from the logical order of the data. Let's assume a phonebook, where stuff is (logically) sortedin alphabetical order. So A before B, B before C, etc. On the index level, though, A is stored on page 15, B on 12, C on 13.
External fragmentation is the more evil one of the two, and will have a much greater negative effect on your performance.
Therefore I'd pick answer
D. 