sql-server-2008-r2 interview questions
Top sql-server-2008-r2 frequently asked interview questions
I have a table in my SQL Server 2008 R2 database, and would like to add a column called LastUpdated, that will automatically be changed every time the row is updated. That way, I can see when each individual row was last updated.
It seems that SQL Server 2008 R2 doesn't have a data type to handle this like earlier versions did, so I'm not sure of the best way to do it. I wondered about using a trigger, but what would happen when the trigger updated the row? Will that fire the trigger again, etc?
Source: (StackOverflow)
I have installed MS SQL Server 2008 R2 and when I try to update model from database under EDMX file I am facing that error.
Could not load file or assembly Microsoft.SqlServer.management.sdk.sfc
version 11.0.0.0
I have tried to install Microsoft SQL Server 2008 R2 Shared Management Objects from here but no joy.
Source: (StackOverflow)
I am using this query to rename the database:
ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]
But it shows an error when excuting:
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Is anything wrong with my query?
Source: (StackOverflow)
I have many users on my web site (20000-60000 per day), which is a download site for mobile files. I have remote access to my server (windows server 2008-R2). I've received "Server is unavailable" errors before, but am now seeing a connection timeout error. I'm not familiar with this - why does it occur and how can I fix it?
The full error is below:
Server Error in '/' Application. Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding. The statement has been terminated. Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Timeout
expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. The statement has been
terminated.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding. The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection) +404
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj) +1363
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString) +6387741
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async) +6389442
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result) +538
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe) +689
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +327
NovinMedia.Data.DbObject.RunProcedure(String storedProcName,
IDataParameter[] parameters, Int32& rowsAffected) +209
DataLayer.OnlineUsers.Update_SessionEnd_And_Online(Object Session_End,
Boolean Online) +440
NiceFileExplorer.Global.Application_Start(Object sender, EventArgs e)
+163
[HttpException (0x80004005): Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding. The statement has been terminated.]
System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext
context, HttpApplication app) +4052053
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr
appContext, HttpContext context, MethodInfo[] handlers) +191
System.Web.HttpApplication.InitSpecial(HttpApplicationState state,
MethodInfo[] handlers, IntPtr appContext, HttpContext context) +352
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr
appContext, HttpContext context) +407
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr
appContext) +375
[HttpException (0x80004005): Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding. The statement has been terminated.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +11686928
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context)
+141 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest
wr, HttpContext context) +4863749
EDIT AFTER ANSWERS:
my Application_Start in Global.asax is like below:
protected void Application_Start(object sender, EventArgs e)
{
Application["OnlineUsers"] = 0;
OnlineUsers.Update_SessionEnd_And_Online(
DateTime.Now,
false);
AddTask("DoStuff", 10);
}
The stored procedure being called is:
ALTER Procedure [dbo].[sp_OnlineUsers_Update_SessionEnd_And_Online]
@Session_End datetime,
@Online bit
As
Begin
Update OnlineUsers
SET
[Session_End] = @Session_End,
[Online] = @Online
End
I have two methods for getting online users:
- using
Application["OnlineUsers"] = 0;
- the other one using database
So, for method #2 I reset all OnlineUsers at Application_Start. There are over 482,751 records in that table.
Source: (StackOverflow)
I have MS SQL 2008 R2, 500 databases.
What is the most efficient, easiest and 'modern' way to query all databases sizes.
The output should have columns:
- DatabaseName
- DataFilesSize
- LogFilesSize
Source: (StackOverflow)
I am writing a Stored procedure in sql server 2008
I need to check if a table exist in the DB, if it doesnt then i need to create it.
How do I do it?
Thanks
Prady
Source: (StackOverflow)
What is best practice for handling the dropping of a temp table. I have read that you should explicitly handle the drop and also that sql server should handle the drop....what is the correct method? I was always under the impression that you should do your own clean up of the temp tables you create in a sproc, etc. But, then I found other bits that suggest otherwise.
Any insight would be greatly appreciated. I am just concerned I am not following best practice with the temp tables I create.
Thanks,
S
Source: (StackOverflow)
We have developed an assembly for SQL Server 2008 R2.
The assembly has been working for a week. The managed stored proc inside the assembly was working fine for the whole week and then it stops working. We have been seeing this problem couple times. The way to make it work again is to restart the SQL Server.
Msg 10314, Level 16, State 11, Line 4
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'myAssembly, Version=2.0.0.490, Culture=neutral, PublicKeyToken=5963130873dd3a75' or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
I have found different articles on the web.
This KB suggested that I might have restored the database from another SQL Server, which I swear I didn't.
This blog said I might run into this if I installed .NET 3.5 on SQL Server 2005 but mine was SQL Server 2008 R2 and I did not install anything when this problem occurs.
The main point is that it can keep going for a period of time. It just stops working randomly. Then, if we restart the SQL Server, it will start working again. I have thought of my server was really running out of memory but now, I just see the problem again. SQL Server is using 300MB RAM only and my server has 16GB RAM. This sounds impossible that it's because I am running out of memory.
Now, I want to collect more information on this problem. Any log that I can turn on and look at? Any suggestion that help troubleshooting this problem is welcome.
I have run some SQL queries.
SELECT * from sys.dm_clr_properties
=============================================
directory C:\Windows\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state CLR is initialized
.
SELECT * from sys.dm_clr_appdomains
======================================================
0x0000000087160240 3 mydatabase.dbo[runtime].2 2011-08-12 08:44:08.940 10 1 E_APPDOMAIN_SHARED 1 1
.
SELECT * from sys.dm_clr_tasks
======================================================
0x000000008185A080 0x00000000818562C8 0x0000000000000000 E_TASK_ATTACHED_TO_CLR E_ABORT_NONE E_TYPE_ADUNLOAD 0 0
0x00000000818CE080 0x00000000818CA2C8 0x0000000000000000 E_TASK_ATTACHED_TO_CLR E_ABORT_NONE E_TYPE_FINALIZER 0 0
0x0000000081AD4C30 0x000000000400D048 0x0000000000000000 E_TASK_ATTACHED_TO_CLR E_ABORT_NONE E_TYPE_USER 0 0
.
SELECT * from sys.dm_clr_loaded_assemblies
<returns nothing>
* UPDATE *
On my SQL Server, I have created four databases. Each of them with the same assembly attached to it. Now, SQL Server refused to load the assembly and gave me the above error.
SELECT * from sys.dm_clr_appdomains shows me at that point there was only one appdomain loaded and SELECT * from sys.dm_clr_loaded_assemblies showed me there were no assemblies loaded at all.
Then, I ran the same stored proc on the other three databases. It worked and successfully loaded up the assemblies and successfully ran the stored proc. After executing the stored proc. SELECT * from sys.dm_clr_appdomains now shows me there are only four appdomain loaded and SELECT * from sys.dm_clr_loaded_assemblies showed me there are now three assemblies loaded.
This makes sense. Now, I hope if I run the stored proc again in the original database, it should get the assembly loaded as it were. Guess what. No, it doesn't. It still gives me the same error. It looks like this database is completely stuck. The only way to fix it is to reboot the SQL Server. I am hoping there is a flag/lock somewhere in the system table holding up this. I cannot find it. Any idea is welcome.
Now, my SQL Server is in the state that requiring me to reboot to make it work again.
* UPDATE (8/31/2011) *
It sounds like it's related to the database owner of the database. This is kind of complicated. We have two sites and two AD forests. The SQL Server machine is joined to forest A but the database owner is from forest B. The connection between forest A and forest B is not that stable since they are in two different sites physically connected by WAN.
Once I change the database owner to a SQL Login (Non-Windows account), my stored proc is up running for couple weeks so far with no interruption.
I will accept the answer if anybody can explain it.
Source: (StackOverflow)
I have a table that contains a Xml column:
SELECT *
FROM Sqm
A sample of the xml data of a row would be:
<Sqm version="1.2">
<Metrics>
<Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490" average="21490" minValue="73701" maxValue="73701" >73701</Metric>
<Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" unit="µs" count="1" sum="184487" average="184487" minValue="632704" maxValue="632704" >632704</Metric>
<Metric id="Database.CreateConnection_SaveContextUserGUID" type="timer" unit="µs" count="2" sum="7562" average="3781" minValue="12928" maxValue="13006" standardDeviation="16" >12967</Metric>
<Metric id="Global.CurrentUser" type="timer" unit="µs" count="6" sum="4022464" average="670411" minValue="15" maxValue="13794345" standardDeviation="1642047">2299194</Metric>
<Metric id="Global.CurrentUser_FetchIdentityFromDatabase" type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614" >13752614</Metric>
</Metrics>
</Sqm>
In the case of this data, I would want:
SqmId id type unit count sum minValue maxValue standardDeviation Value
===== =================================================== ===== ==== ===== ====== ======== ======== ================= ======
1 TransactionCleanupThread.RecordUsedTransactionShift timer µs 1 21490 73701 73701 NULL 73701
1 TransactionCleanupThread.RefundOldTrans timer µs 1 184487 632704 632704 NULL 632704
1 Database.CreateConnection_SaveContextUserGUID timer µs 2 7562 12928 13006 16 12967
1 Global.CurrentUser timer µs 6 4022464 15 13794345 1642047 2299194
1 Global.CurrentUser_FetchIdentityFromDatabase timer µs 1 4010057 13752614 13752614 NULL 13752614
2 ...
In the end I'll actually be performing SUM(), MIN(), MAX() aggregation. But for now I'm just trying to query an xml column.
In pseudo-code, I would try something like:
SELECT
SqmId,
Data.query('/Sqm/Metrics/Metric/@id') AS id,
Data.query('/Sqm/Metrics/Metric/@type') AS type,
Data.query('/Sqm/Metrics/Metric/@unit') AS unit,
Data.query('/Sqm/Metrics/Metric/@sum') AS sum,
Data.query('/Sqm/Metrics/Metric/@count') AS count,
Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue,
Data.query('/Sqm/Metrics/Metric/@maxValue') AS maxValue,
Data.query('/Sqm/Metrics/Metric/@standardDeviation') AS standardDeviation,
Data.query('/Sqm/Metrics/Metric') AS value
FROM Sqm
But that SQL query doesn't work:
Msg 2396, Level 16, State 1, Line 2
XQuery [Sqm.data.query()]: Attribute may not appear outside of an element
I've hunted, and it's amazing how poorly documented, or exampled, Xml querying is. Most resources rather than querying a table, query a variable; which I'm not doing. Most resources only use xml querying to to filtering and selection, rather than reading values. Most resources read hard-coded child nodes (by index), rather than actual values.
Related resources that I read
Update: .value rather than .query
I tried randomly using .value, in place of .query:
SELECT
Sqm.SqmId,
Data.value('/Sqm/Metrics/Metric/@id', 'varchar(max)') AS id,
Data.value('/Sqm/Metrics/Metric/@type', 'varchar(max)') AS type,
Data.value('/Sqm/Metrics/Metric/@unit', 'varchar(max)') AS unit,
Data.value('/Sqm/Metrics/Metric/@sum', 'varchar(max)') AS sum,
Data.value('/Sqm/Metrics/Metric/@count', 'varchar(max)') AS count,
Data.value('/Sqm/Metrics/Metric/@minValue', 'varchar(max)') AS minValue,
Data.value('/Sqm/Metrics/Metric/@maxValue', 'varchar(max)') AS maxValue,
Data.value('/Sqm/Metrics/Metric/@standardDeviation', 'varchar(max)') AS standardDeviation,
Data.value('/Sqm/Metrics/Metric', 'varchar(max)') AS value
FROM Sqm
But that also doesn't work:
Msg 2389, Level 16, State 1, Line 3 XQuery [Sqm.data.value()]:
'value()' requires a singleton (or empty sequence), found operand of
type 'xdt:untypedAtomic *'
Source: (StackOverflow)
I am using SQL Server 2008 R2 Express.
I first installed SQL Server 2008 R2 Express Management Studio and then I installed SQL Server 2008 R2 Express. I have the instance SQLEXPRESS running and it is set to automatic.
I am trying to connect to it locally using Windows authentication - server name is set to local and the username is grayed out and set to my profile username.
When I try to connect I get the following error:
Have I installed the wrong SQL Server Management Studio?
Source: (StackOverflow)
I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).
Sometimes when I run my build I get this error:
ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.
ALTER DATABASE statement failed.
Cannot drop database "MyDB" because it is currently in use.
I tried this:
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
but I still cannot drop the database. (My guess is that most of the developers have dbo access.)
I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)
Is there a script that can drop my database regardless of who is connected?
Source: (StackOverflow)
Currently, my database is in Single User mode. When I try to expand me database, I get an error:
The database 'my_db' is not accessible.(ObjectExplorer)
Also, when I try to delete the database, I get the error:
Changes to the state or options of database 'my_db' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
How do I exit out of single-user mode? I don't have any user using this database.
When I try to browse my site with IIS, the error I get is:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I feel as though the single-user mode is causing this.
Thank you for all the help! Much appreciated!
Source: (StackOverflow)
In this excellent SO question, differences between CTE and sub-queries were discussed.
I would like to specifically ask: in what circumstance is each of the following more efficient/faster?
- CTE
- Sub-Query
- Temporary Table
Traditionally, I've used lots of temp tables in developing stored procedures - as they seem more readable than lots of intertwined sub-queries.
Non-recursive CTEs encapsulate sets of data very well, and are very readable, but are there specific circumstances where one can say they will always perform better? or is it a case of having to always fiddle around with the different options to find the most efficient solution?
EDIT
I've recently been told that in terms of efficiency, temporary tables are a good first choice as they have an associated histogram.
Source: (StackOverflow)