Every product has some bug or issues, there is no software in this world without any bug or scope of improvement. Knowing the fact that software development is a never ending process.
Q: Tell at least 5 known issues in SQL Server that you have faced during your journey with SQL Server?
It’s often asked by interviewers, not to confuse the candidate but to gauge the candidate’s hands on experience level with SQL Server. The bug list is pretty much volatile, Microsoft does update to every bug reported by users on Microsoft Connect, a kind of bug tracker maintained by Microsoft. So, interviewer does not expect you to tell them all the bugs and resolution that were addressed recently but they definitely expect you to tell few known or common issues that you have faced in SQL Server. I will list down 10 common issues here that I have faced during my journey so far with SQL Server.
- An extra comma at the end of the TABLE create statement does not give any error.
CREATE TABLE #TempTable ( ID int , Name varchar(100) , -- An extra comma at the end does not give any error )
- Identity skip to 1000 for every int column and by 10000 for every bigint column when an SQL Server instance is unexpectedly restarted.
- The backend version is not supported to design database diagrams or tables. If you have an upper version instance connected from a lower version of SSMS then you cannot design a table or database diagram. Though this is not a bug but many finds it strange since there are many feature stays common between them. At least enabling lower version supported features when you are connected to an upper version instance would be great.
- The query editor in 2008 or lower versions of SSMS, does not support many ASCII characters like if you copy some string with enter or tab ASCII eg: (CHAR(13) + CHAR(10)) from a column to query editor then the string will come as a single straight line rather having enter in the string.
- Sometime “String data, right truncation” error in BCP. Copy SQL table from one server to another using BCP
- Sometime strange Microsoft.ACE.OLEDB driver error occurs while reading data from excel data source to SQL Server.
- Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) ~ This is not a bug but I found this error message little undescriptive because this error comes when you do not have mixed mode authentication set and you are trying to login with sql authentication.
- Theoretically you cannot call or execute a stored procedure inside a function, but there are few system automation stored procedures which can be directly called inside a function. Eg: File system operations in SQL Server using OLE Automation . I will not say this is a bug bug but often it creates some confusion between interviewer and interviewee.
- Problems with TURKISH_CI_AS collation in SQL Server.
- Problem with Copy Database wizard in SSMS, many prefers a backup restore method than copy wizard for coping a database to other server over network because of some weird errors and a lot of dependencies. There are still many questions unanswered on StackOverflow regarding to this topic.
Note: This post is just related to the interview question and answer.