sqlserver material 4

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
FROMFROM 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.

How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?

A non-clustered index and tempdb can be created on a separate disk to improve performance.
(Read more here)

How to Find the List of Fixed Hard Drive and Free Space on Server?

We can use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives.
EXEC master..xp_fixeddrives

Why can there be only one Clustered Index and not more than one?

Cluster Index physically stores data, or arranges data in one order (depends on which column(s) you have defined Clustered index and in which order).
As a fact, we all know that a set of data can be only stored in only one order; that is why only one clustered index is possible.(Read more here)

What is Difference between Line Feed (\n) and Carriage Return (\r)?

Line Feed – LF – \n – 0x0a – 10 (decimal)
Carriage Return – CR – \r – 0x0D – 13 (decimal)
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
(Read more here)

Is It Possible to have Clustered Index on Separate Drive From Original Table Location?

No! It is not possible. (Read more here)

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.(Read more here)
There are three different types of hints. Let us understand the basics of each of them separately.

Join Hint

This hint is used when more than one table is used in a query. Two or more tables can be joined using different types of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements.

Query Hint

This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query as opposed to a part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.

Table Hint

This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE is used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm, and these hints are useful in those scenarios. (Read more here)

How to Delete Duplicate Rows?

We can delete duplicate rows using CTE and ROW_NUMBER () feature of SQL Server 2005 and SQL Server 2008.
e.g.
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount >1
(Read more here)

Why the Trigger Fires Multiple Times in Single Login?

It happens because multiple SQL Server services are running and also as intellisense is turned on. (Read more here)

What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY for filtering query using aggregate values.
The following functions are aggregate functions.
AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP (Read more here )

What is Use of @@ SPID in SQL Server?

A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed. (Read more here)

What is the Difference between Index Seek vs. Index Scan?

An index scan means that SQL Server reads all the rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all the rows of the index are examined instead of the table directly. This is sometimes compared to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.
An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; in terms of performance, this is highly beneficial when a table has a very large number of rows. (Read more here)

What is the Maximum Size per Database for SQL Server Express?

SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if the database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.
(Read more here)

How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?

In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon every time as it is difficult to reach a conclusion when there are many columns and many rows.
It is easy to measure how much data is retrieved from server to client side. The SQL Server Management Studio has feature that can measure client statistics. (Read more here)

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. (Read more here)

How to Create Primary Key with Specific Name while Creating a Table?

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1)NOTNULL,
[FirstName] [varchar](100)NULL,
CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED
([ID] ASC))
GO
(Read more here)

What is T-SQL Script to Take Database Offline – Take Database Online?

-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
(Read more here)

How to Enable/Disable Indexes?

--Disable Index
ALTER INDEX [IndexName] ON TableName DISABLE
GO
--Enable Index
ALTER INDEX [IndexName] ON TableName REBUILD
GO
(Read more here)

Can we Insert Data if Clustered Index is Disabled?

No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).
(Read more here)

How to Recompile Stored Procedure at Run Time?

We can Recompile Stored Procedure in two ways.
Option 1:
CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT*
FROM dbo.tblPerson
WHERE Age <= @MinAge AND Age >= @MaxAge
GO
Option 2:
EXEC dbo.PersonAge65, 70 WITHRECOMPILE
We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.
This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.