SQL Authority with Pinal Dave

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.


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:

(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:

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)