Sql-Interview

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Intro Question: Consider the following code: SELECT CustomerID, CustomerName FROM Sales.Customers WHERE BusinessCategory = 'Cafe' UNION SELECT CustomerID, CustomerName FROM Sales.Customers WHERE City = 'Toronto'; A: What would be the difference in results if the query was changed to use a UNION ALL instead of the UNION?

2021-04-22

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Medium Question: When you add a column to an existing SQL Server table, the column is added to the end of the table. The column will end up with the highest column_id. If a developer asks you how a column can be added to the middle of a table instead, what is your advice and how would you approach the request?

2021-04-21

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Medium Question: When you define a column default with code like below: USE tempdb; GO DROP TABLE IF EXISTS dbo.Customers; GO CREATE TABLE dbo.Customers ( CustomerID int IDENTITY(1,1) CONSTRAINT PK_dbo_Customers PRIMARY KEY, TradingName nvarchar(100) NOT NULL, CreatedDate datetime2(3) NOT NULL DEFAULT (SYSDATETIME()) ); GO the system will define the name of the default.

2021-04-15

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Medium Question: Consider the following code: USE tempdb; GO DROP TABLE IF EXISTS dbo.Customers; GO CREATE TABLE dbo.Customers ( CustomerID int IDENTITY(1,1) CONSTRAINT PK_dbo_Customers PRIMARY KEY, TradingName nvarchar(100) NOT NULL, CreatedDate AS SYSDATETIME() ); GO INSERT dbo.

2021-04-13

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Security Level: Intro Question: Many applications connect to SQL Server databases with users that are members of the db_datareader and/or db_datawriter roles? Why should you avoid using those roles? Answer: db_datareader and db_datawriter are fixed database roles that were provided for convenience.

2021-04-09

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Intro Question: Many versions of SQL require every SQL statement to be terminated with a semicolon. Since it was created, T-SQL has listed statement terminators as optional. Are there any situations where they are required, and not optional?

2021-04-08

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Intro Question: Look at the following multi-row INSERT statement: INSERT Sales.CustomerGroups ( CustomerGroupID, CustomerGroupName ) VALUES (1, 'Group A'), (2, NULL), (3, 'Group C'); The column CustomerGroupName is defined as NOT NULL so the second row cannot be inserted.

2021-04-06

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Intro Question: If you create a table using the following code, what would the name of the primary key constraint look like? CREATE TABLE Sales.CustomerGroups ( CustomerGroupID int IDENTITY(1, 1) PRIMARY KEY, CustomerGroupName nvarchar(50) NOT NULL ); How could you specify the name?

2021-04-01

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Development Level: Medium Question: When you are writing an INSERT statement in T-SQL, the column list is usually optional. Why would including it be a good idea? When would it ever be required (i.e. not optional)? Answer:

2021-03-30

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database. Section: Query Performance Level: Medium Question: In SQL Server a column that is defined with a bit data type can only have the values 0, or 1, or it can be NULL if the column allows it. Given such a small number of possible values, are bit columns ever useful in indexes?

2021-03-26