Let us continue our question of the day series with day twenty-six.

What are roles in SQL Server?

A role is essentially a group of users similar to active directory groups used by SQL Server for security purposes.  In other words, you can grant permissions to the role and place users in the roles and they will inherit the permission for as long as they are placed inside of the role.  There are also fixed roles that have pre-defined permissions established.

Enjoy!

Let us continue our question of the day series with day twenty-five.

What is the activity monitor in SQL Server?

Activity monitor is a built-in monitor that provides information about the process, resource waits, data file input/output, and recent expensive queries that involve the current instance of SQL Server.  It also gives you a small graphical representation of the monitoring as shown below.

Enjoy!

Let us continue our question of the day series with day twenty-four.

Who is Codd and why do I care in regards to SQL Server ?

E.F. Codd was an English computer scientist with IBM who invented the relation model for databases.  The relational model is the core of the architecture essential to today’s modern relational database management systems (RDBMS).  Essentially all of the data involved in our lives is stored somewhere in an RDBMS.  Want to read his paper on the relational model, it’s available here.

Enjoy!

Let us continue our question of the day series with day twenty-three.

What is a constraint?

A constraint is simply a rule associated with a column in a table that any data entered must follow.  Some examples are the unique constraint which ensures that no two records possess the same value for the column in question.  Another commonly used constraint is the primary key constraint which provides a unique identifier for each row in a table.  It is similar to the unique constraint but the key difference is that the unique constraint just means that ONE column is unique whereas a primary key can include up to a combination of 16 columns to make it unique.  There is also the NOT NULL constraint which essentially means that the column cannot be left blank.

Enjoy!

Let us continue our question of the day series with day twenty-two.

What are all these abbreviations:  SSIS, SSMS, SSAS, SSRS, and SSMA?

I had this question the other day from a colleague who is a Windows administrator who was thoroughly confused by the alphabet soup.  Thus I will break it down for you.  Most of you know SSMS is the SQL Server Management Studio, the graphical interface for managing SQL Server.  SSMA is Microsoft’s SQL Server Migration Assistant, which is available for download, for various migrating OTHER databases to SQL Server such as Oracle.  That leaves us with the Business Intelligence arena for SQL Server:  SSIS, SSAS and SSRS.  SSAS is the SQL Server Analysis Services which provides data mining and OLAP (online analytical processing) functionality for the BI arena.  SSIS is the SQL Server Integration Services which provides data integration solutions and ties all of the BI pieces together.  SSRS is the SQL Server Reporting Services package allowing full reporting capabilities.

Enjoy!

Let us continue our question of the day series with day twenty-one.

What is an execution plan?

Contrary to popular belief the execution plan is not a swinging rope or a firing squad, although with some developers, nevermind.  The SQL Server query optimizer devises a plan of execution for your query in order to try to return the efficient results in the quickest manner possible.  Keep in mind that the engine does not always choose the absolute best plan, it strives to choose the best plan in quickest amount of time using complex calculations.

Enjoy!

Let us continue our question of the day series with day twenty.

What is the DAC and how do I use it?

The DAC, or Dedicated Administrator Connection, is essentially a troubleshooting backdoor into a running instance of SQL Server.  The DAC is available through SSMS and the SQLCMD utility.  In SQL Server Management Studio choose New Query on the toolbar (assuming of course that you are not currently connected to any other DACs) as opposed to the Connect button in the Object Explorer, which will not work with DAC.  Once you are prompted for the connection enter ADMIN: in front of the Instance name\Server name (such as ADMIN:instance\server).  Then enter a sysadmin level credential.  If you are using the SQLCMD utility, then the command is sqlcmd -Sadmin:instance\server.

Enjoy!

Let us continue our question of the day series with day nineteen.

What is the default TCP/IP port that SQL Server runs on and how can you change it ?

The default port is 1433.  To change that we would use the SQL Server configuration manager as described by MSDN.

Enjoy!

Let us continue our question of the day series with day eighteen.

What is a view (and is it a view to a kill)?

A view is a great way to combine various pieces of data from multiple tables.  It could also be that you want to do this for security reasons where a user does not have access to one of those tables because of sensitive fields but you give them access to the view because the fields contained in it are safe enough for them to view without exposing the rest of the table.  Views also are useful for a heavily referenced query.  Instead of reusing the query, you just reference the view.

Incidentally, it is only a view to a kill if Mr Bond is involved.  Enjoy!

Let us continue our question of the day series with day seventeen.

What is instant file initialization?

Instant file initialization allows for a file to be created or also to grow instantly.  Therefore it does require the process to zero out the space inside of the file.   It is important to point out that this only applies to SQL Server data files.  Enjoy!