This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is In-memory Database?
What is Hekaton in SQL Server 2014?
How to create memory optimized table in SQL Server 2014?
SQL Server 2014: Create Memory Optimized File Group and Table
I want to turn the unique record from multiple rows to multiple column values. Is it possible in SQL Server?
Yes, We can do this task using PIVOT.
SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table. It also allows performing aggregations, wherever required, for column values that are expected in the final output. The basic syntax for a PIVOT relational operator looks like this:
Do you know about the UNPIVOT?
UNPIVOT is another relational operator in SQL Server that performs almost the reverse operation of PIVOT, by rotating column values into rows values.
Using UNPIVOT, Do you think that you can get all correct value which are pivoted using PIVOT?
If you refer back, I said UNPIVOT is almost the reverse of the PIVOT operator; this means it might or might not be exactly the same. The reason is, Pivot performs aggregation while rotating row values into column values and might merge possible multiple row values into single column value in the output.
What is the WITH CHECK OPTION clause for a view?
The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.
How you are checking that record is exists in database or not?
Using EXISTS clause:
|
1 2 3 4 5 6 7 8 9 |
IF EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK) WHERE CustId = @CustId) BEGIN PRINT 'Record Exists' END ELSE BEGIN PRINT 'Record doesn''t Exists' END END IF; |
What is the meaning of the WITH SCHEMABINDING clause?
- SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
- The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.
- All referenced objects must be in the same database. Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error.
- Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.
- The implementation of SCHEMABINDING would increase stability and performance of the system, but also increase the amount of developers’ work.
Can we use WITH SCHEMABINDING in Stored Procedures?
No: We cannot use WITH SCHEMABINDING in stored procedures.
What are the main disadvantages of the WITH SCHEMABINDING clause?
It would take more time to make changes in tables’ structure. If I just want to change the size of one column, I will need to modify before and after several views.
The automated test of the application can catch all broken views and functions. This will not increase the amount of developers’ work.
You can not change the collation of a database with schemabound objects.
What do you know about the OFFSET FETCH clause of the SQL Server 2012?
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
OFFSET: Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.
FETCH: Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.
Please write a script or logic of custom paging using OFFSET and FETCH NEXT.
What are the limitations of OFFSET and FETCH clause of the SQL Server 2012?
- ORDER BY is mandatory to use OFFSET and FETCH clause.
- OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
- TOP cannot be combined with OFFSET and FETCH in the same query expression.
- The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
What is the maximum size of a VARCHAR(MAX) variable?
Maximum size of a VARCHAR(MAX) is 2GB.
What is BCP? When is it used?
It is a tool used to duplicate enormous quantity of information from tables and views. It does not facsimile the structures same as foundation to target. BULK INSERT command helps to bring in a data folder into a record, table or view in a user-specific arrangement.
When is the Explicit Cursor Used ?
If the developer needs to perform the row by row operations for the result set containing more than one row, then he unambiguously declares a pointer with a name. They are managed by OPEN, FETCH and CLOSE.
%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN characteristics are used in all types of pointers.
How can a SQL Developer prevent T-SQL code from running on a production SQL Server?
Use IF logic with the @@SERVERNAME function compared against a string with a RETURN command before any other logic.
Can you create foreign key constraints on temporary tables?
No: You cannot create a foreign key constraint on temporary tables.
