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.

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).

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.

Click to expand...

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

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).

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.

9)Default instances are identified by the name of the server on which it is installed and name of the instance

Click to expand...

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.

Click to expand...

That makes me want to change my answer to false.

10)sql server should be configured to run under domain accounts only ?

Click to expand...

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.

occurs when the extends belonging to an index are not contigous or logical and physical page orders are different results in expensive index scan

Click to expand...

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.