SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31

Click here to get free chapters (PDF) in the mailbox

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?

Usually, when the name of the table or column is very long or complicated to write, aliases are used to refer them.

e.g.

SELECT VeryLongColumnName col1
FROM VeryLongTableName tab1

In the above example, col1 and tab1 are the column alias and table alias, respectively. They do not affect the performance at all.

What is the difference between CHAR and VARCHAR Datatypes?

VARCHARS are variable length strings with a specified maximum length. If a string is less than the maximum length, then it is stored verbatim without any extra characters, e.g. names and emails. CHARS are fixed-length strings with a specified set length. If a string is less than the set length, then it is padded with extra characters, e.g. phone number and zip codes. For instance, for a column which is declared as VARCHAR(30) and populated with the word ‘SQL Server,’ only 10 bytes will be stored in it. However, if we have declared the column as CHAR(30) and populated with the word ‘SQL Server,’ it will still occupy 30 bytes in database.

What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?

VARCHAR stores variable-length character data whose range varies up to 8000 bytes; varchar(MAX) stores variable-length character data whose range may vary beyond 8000 bytes and till 2 GB. TEXT datatype is going to be deprecated in future versions, and the usage of VARCHAR(MAX) is strongly recommended instead of TEXT datatypes.

What is the Difference between VARCHAR and NVARCHAR datatypes?

In principle, they are the same and are handled in the same way by your application. The only difference is that NVARCHAR can handle unicode characters, allowing you to use multiple languages in the database (Arabian, Chinese, etc.). NVARCHAR takes twice as much space when compared to VARCHAR. Use NVARCHAR only if you are using foreign languages.

Which are the Important Points to Note when Multilanguage Data is Stored in a Table?

There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example,

INSERT INTO table (Hindi_col) values (N’hindi data’)

How to Optimize Stored Procedure Optimization?

There are many tips and tricks for the same. Here are few:

• Include SET NOCOUNT ON statement.
• Use schema name with object name.
• Do not use the prefix “sp_” in the stored procedure name.
• Use IF EXISTS (SELECT 1) instead of (SELECT *).
• Use the sp_executesql stored procedure instead of the EXECUTE statement.
• Try to avoid using SQL Server cursors whenever possible.
• Keep the Transaction as short as possible.
• Use TRY-Catch for error handling.

(Read more here)

What is SQL Injection? How to Protect Against SQL Injection Attack?

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

Here are few methods which can be used to protect again SQL Injection attack:

• Use Type-Safe SQL Parameters
• Use Parameterized Input with Stored Procedures
• Use the Parameters Collection with Dynamic SQL
• Filtering Input parameters
• Use the escape character in LIKE clause
• Wrapping Parameters with QUOTENAME() and REPLACE()

How to Find Out the List Schema Name and Table Name for the Database?

We can use following script:

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable
FROM sys.tables

(Read more here)

What is CHECKPOINT Process in the SQL Server?

CHECKPOINT process writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (https://blog.sqlauthority.com)