This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is should be your standard format to store date and time?
ISO standard and format number is 112. (yyyymmdd)
When the error occurred, What kind of data we can retrieved in catch block?
- ERROR_NUMBER() – returns the number of the error.
- ERROR_SEVERITY() – returns the severity.
- ERROR_STATE() – returns the error state number.
- ERROR_PROCEDURE() – returns the name of the stored procedure or trigger where the error occurred.
- ERROR_LINE() – returns the line number inside the routine that caused the error.
- ERROR_MESSAGE() – returns the complete text of the error message.
T-SQL scripts and stored procedures don’t produce their PRINT statements and RAISERROR output in a way that keeps you informed of the code’s progress. You usually have to wait until the procedure is complete before seeing messages. How can I get insight into the code’s progress?
The solution is to use the WITH NOWAIT clause of the RAISERROR statement. It may be surprising but using RAISERROR doesn’t require that there is an error condition. If the severity level given to RAISERROR is 0 through 10 SQL Server treats the RAISERROR as a plain message and not an error at all.
What do synonyms do and when could you make the case for using them?
Think of this as an alias and Synonyms provide a great layer of abstraction, allowing us to use friendly and/or local names for verbosely named or remote tables, views, procedures and functions.
In some enterprise systems, you may have to deal with remote objects over which you have no control. For example, a database that is maintained by another department or team.
Synonyms can help you decouple the name and location of the underlying object from your SQL code. That way you can code against a synonym table even if the table you want is moved to a new server/database or renamed.
How you can execute T-SQL batch multiple times?
Using GO command, we can execute T-SQL batch multiple times. We have to specify GO <integer number for loop>.
What is the use of BITWISE operators?
Bitwise operators can be used to store a complex set of criteria as a single value as opposed to having numerous lookup tables or numerous columns used as a ‘flag’ or condition indicator.
What do you know about view in SQL Server?
A view is a virtual table whose contents are defined by a query and stored SELECT statement that works like a virtual table. Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database.
What is a Indexed view?
An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.
What is Partitioned view?
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.
How many columns a view can contain?
1024 columns.
Can you create a view by using another view?
Yes, you can build a view on another view and nest them up to 32 levels.
What is System view?
System views expose catalog metadata. You can use system views to return information about the instance of SQL Server or the objects defined in the instance, like different Dynamic Management View.
What are the benefits of stored procedures?
- Reduced server/client network traffic
- Stronger security
- Reuse of code
- Easier maintenance
- Improved performance
Do you know, why stored procedure is better than Ad hoc SQL?
Database Design: Use Stored Procedure, Do not use Inline or Ad Hoc SQL
What is the difference between Stored Procedure and Function in SQL Server?
- Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
- Functions can have only input parameters for it whereas Procedures can have input/output parameters .
- Functions can be called from Procedure whereas Procedures cannot be called from Function.
- Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
- Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
- Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
- Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
- We can go for Transaction Management in Procedure whereas we can’t go in Function.
What is the File table concept of SQL Server 2012?
The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.
In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.
How to Convert Varbinary to Numeric & vice versa?
SQL Server: How to Convert Varbinary to Numeric & vice versa?
What do you know about the new logical function IIF of SQL Server 2012?
How to insert different Symbol or Multilingual data in a Table?
SQL Server: Insert different Symbol or Multilingual data in a Table
