What Command do we Use to Rename a db, a Table and a Column?
To Rename db
sp_renamedb ‘oldname’ , ‘newname
If someone is using db it will not accept sp_renmaedb. In that case, first bring db to single user mode using sp_dboptions. Use sp_renamedb to rename the database. Use sp_dboptions to bring the database to multi-user mode.
e.g.
USE MASTER;
GO
EXEC sp_dboption AdventureWorks, 'Single User', True
GO
EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'
GO
EXEC sp_dboption AdventureWorks, 'Single User', False
GO
To Rename Table
We can change the table name using sp_rename as follows:
sp_rename 'oldTableName' 'newTableName'
e.g.
sp_RENAME 'Table_First', 'Table_Last'
GO
To rename Column
The script for renaming any column is as follows:
sp_rename 'TableName.[OldcolumnName]', 'NewColumnName', 'Column'
e.g.
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
GO
What are sp_configure Commands and SET Commands?
Use sp_configure to display or change server-level settings. To change the database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.
e.g.
sp_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO
sp_CONFIGURE
GO
You can run the following command and check the advanced global configuration settings.sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE
GO
How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is Difference between Commit and Rollback when Used in Transactions?
The usual structure of the TRANSACTION is as follows:
BEGIN TRANSACTION
Operations
COMMIT TRANSACTION or ROLLBACK TRANSACTION
When Commit is executed, every statement between BEGIN and COMMIT becomes persistent to database. When Rollback is executed, every statement between BEGIN and ROLLBACK are reverted to the state when BEGIN was executed.
What is an Execution Plan? When would you Use it? How would you View the Execution Plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query, and it is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. Within the Query Analyzer, there is an option called “Show Execution Plan” (in the Query drop-down menu). If this option is turned on, it will display query execution plan in a separate window when the query is ran again.
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.
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
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.
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' )
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
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.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.
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
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
How to Enable/Disable Indexes?
--Disable Index
ALTER INDEX [IndexName] ON TableName DISABLE
GO
--Enable Index
ALTER INDEX [IndexName] ON TableName REBUILD
GO
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. (Read more here)
Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
There is no performance difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table). (Read more here)
What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
INSERT TOP (N) INTO Table is faster than Using Top with INSERT but when we use INSERT TOP (N) INTO Table, the ORDER BY clause is totally ignored. (Read more here)
Does the Order of Columns in UPDATE statements Matter?
No, the order of columns in UPDATE statement does not matter for results updated.
Both the below options produce the same results.
Option 1:
UPDATE TableName
SET Col1 ='Value', Col2 ='Value2'
Option 2:
UPDATE TableName
SET Col2 ='Value2', Col1 ='Value'
No comments:
Post a Comment