sql-server-2012 interview questions
Top sql-server-2012 frequently asked interview questions
I recently installed Microsoft SQL Server 2012 on a fresh Windows 7 installation, but whenever I want to run the server, I get the following error:
Error 1069: The service did not start due to a logon failure.
The following user is configured to start the service: NT Service\MSSQL$SQLEXPRESS
How can I fix this problem?
Source: (StackOverflow)
How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how.
I've looked the the properties but can't see an option, I have seen an answer where you go to the Identity specification property and set it to yes and set the Identity increment to 1, but that section is grayed out and I can't change the no to yes.
There must be a simple way to do this but I can't find it.
Source: (StackOverflow)
Which one:
is THE recommended way to store date and time in SQL Server 2008+?
I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only?
Source: (StackOverflow)
I had SQL Server 2008 R2 and Visual Studio 2008 and 2010 on my laptop. I've installed SQL Server 2012 and have finally got SQL Server Data Tools to show up, so that I can create SSRS reports in Visual Studio 2010.
Although I've installed Management tools - many times - on two separate instances of SQL Server 2012, I can't find them in Programs and can't run them. The only SSMS program I can find runs SSMS 2008 R2.
I realise that common advice is to install SQL Server before Visual Studio, but I really don't want to uninstall Visual Studio, as this will bring a host of other problems.
How can I fix this problem?
Source: (StackOverflow)
I just installed SQL Server Express 2012 on my home server. I'm trying to connect to it from Visual Studio 2012 from my desktop PC, and repeatedly getting the well-known error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
What I've done to try to fix this:
- Run SQL Server Configuration Manager on the server and enable SQL Server Browser
- Add a Windows Firewall exception on the server for TCP, ports 1433 and 1434 on the local subnet.
- Verify that I have a login on the SQL Server instance for the user I'm logged in as on the desktop.
- Verify that I'm using Windows Authentication on the SQL Server instance.
- Repeatedly restart SQL Server and the whole dang server.
- Pull all my hair out.
How can I get SQL Server 2012 Express to allow remote connections!?
Source: (StackOverflow)
This question already has an answer here:
I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly.
Say I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:
1,
2,
3,
4,
5,
1004,
1005
This is happening on a random number of tables at very random times, can not replicate it to find any trends.
How is this happening?
Is there a way to make it stop?
Source: (StackOverflow)
I'm trying to do the Code First Walkthrough of the entity framework ( http://blogs.msdn.com/b/adonet/archive/2011/09/28/ef-4-2-code-first-walkthrough.aspx ).
I have the latest SQL Server Express and when I check my versions available via command line (sqllocaldb info): I see localdbApp1 and v11.0. When I try to run the walkthrough with a few minor tweaks, I get a can't connect error.
My app.config looks like this:
<parameter value="Server=(LocalDB)\v11.0; Integrated Security=True; MultipleActiveResultSets=True" />
I wrote a simple connection test like below and the code returns the same SQL Connection error ((provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)).
new System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\v11.0; Integrated Security=True; MultipleActiveResultSets=True").Open();
I've tried replacing "Data Source=..." with "Server=..." but to no avail there.
Any ideas what the connection string should be?
Source: (StackOverflow)
I have recently upgraded to SQL2012 and i am using the management studio. One of my columns in the database has a Char(13) + Char(10) stored in it.
When i was using SQL Server 2008 this would copy and paste completely fine into Excel. But now copying pasting the same data create a new line/ carriage return on the data i have in excel.
Is there a setting i have missed in SQL2012 that will resolve this issue. I don't want to simply REPLACE(CHAR(13)+CHAR(10)) on every single database selection, As i would have to go from using SELECT * to defining each individual column.
Source: (StackOverflow)
In one of my tables "Fee" in column "ReceiptNo" in Sql Server 2012 Database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.
if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps to 1207306 and if it is 1207314, it jumps to 1208306. What I want to make you note is that the last three digits remain constant i.e 306 whenever the jumping occurs as shown in the following picture.
this problem occurs when I restart my computer
Source: (StackOverflow)
SQL Server 2012 introduced Sequence as a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?
Source: (StackOverflow)
The following code implements an UDT which derives from a generic (SortedDictionary):
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]
public class udtMassSpectra : SortedDictionary<float, float>, INullable, IBinarySerialize, ICloneable, IDisposable
{
...
}
Creating the type (T-SQL):
CREATE TYPE dbo.udtMassSpectra EXTERNAL NAME MassSpectra.udtMassSpectra;
throws an exception:
Msg 10331, Level 16, State 1, Line 1 Type 'udtMassSpectra' in assembly
'MassSpectra' derives from a generic type which is not supported for a
CLR Type.
What is the reason? Is there any workaround other than hiding the base class within a private member? This code works fine on a SQL-Server 2005.
Source: (StackOverflow)
So, I've got a function that returns a number of records that I want to implement paging for on my website. It was suggested to me that I use the Offset/Fetch Next in SQL Server 2012 to accomplish this. On our website, we have an area that lists total number of records and what page you're on at the time.
Before, I was getting the entire record set and was able to build the paging on that programatically. But using the SQL way with FETCH NEXT X ROWS ONLY, I am only given back X rows, so I don't know what my total record set is and how to calculate my min and max pages. The only way I can tell of doing this is calling the function twice and doing a count of rows on the first, then running the second with FETCH NEXT. Is there a better way that won't have me running the query twice? I am trying to speed up performance, not slow it down.
Source: (StackOverflow)
This question already has an answer here:
I am using Sql server 2012(Denali). I just wonder why all identity column values starts from 1001 and so on. At the begining Identity column starts from 1,2 and so on and adding identity smoothly, but suddenly it jumps to 1001,1002 and onwards for all the table in the database containing identity column. What could be the reason. Please assist.
Source: (StackOverflow)
The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET
or FOR XML is also specified.
I am getting the above said error while trying to execute the following query. Can anyone please have a look and tell me what am I doing wrong here?
SELECT
*
FROM (
SELECT
Stockmain.VRNOA,
item.description as item_description,
party.name as party_name,
stockmain.vrdate,
stockdetail.qty,
stockdetail.rate,
stockdetail.amount,
ROW_NUMBER() OVER (ORDER BY VRDATE) AS RowNum
FROM StockMain
INNER JOIN StockDetail
ON StockMain.stid = StockDetail.stid
INNER JOIN party
ON party.party_id = stockmain.party_id
INNER JOIN item
ON item.item_id = stockdetail.item_id
WHERE stockmain.etype='purchase'
ORDER BY VRDATE DESC
) AS MyDerivedTable
WHERE
MyDerivedTable.RowNum BETWEEN 1 and 5
Source: (StackOverflow)