Today I will be discussing Q&A on SQL Server.Q. Difference between "VARCHAR" and "VARCHAR2" datatypes?
Ans. The VARCHAR datatype is actually a subtype of VARCHAR2. Currently the two datatypes are identical, and you're free to use either. However, if you use VARCHAR and the ANSI standard changes, the database will be forced to change the VARCHAR type to conform, and you'll have to change any apps that depend on it. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.
Q. How do you optimize stored procedures in SQL Server?
Ans.
- Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization.
- Select only those fields which really require.
- Joins are expensive in terms of time. Make sure that use all the keys that relate the two tables together and don't join to unused tables, always try to join on indexed fields. The join type is important as well (INNER, OUTER).
Q. What is a View in SQL Server Database?
Ans. As per the theory of database (which includes SQL Server, Oracle) a view can be thought of stored SQL query which result can be accessible as a table. It can be used for retrieving data, as well as updating or deleting rows. But database view does not have physical schema. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
Q. What is a Linked Server?
Ans. A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
Linked servers offer the following advantages:
- Remote server access.
- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
- The ability to address diverse data sources similarly.
With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Q. What is a "trigger" in SQL Server?
Ans. In any database including SQL Server a trigger is a procedure that initiates on INSERT, DELETE or UPDATE actions. Before SQL Server 2000 Triggers are also used to maintain the referential integrity. We can not execute triggers explicitly; the DBMS automatically fires the trigger when data modification events (INSERT, DELETE or UPDATE) happened in the associated table.Triggers are same as stored procedures in term of procedural logic that is stored at the database level. Stored procedures are executed explicitly and triggers are event-driven. Triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
I will covering some more important Q&A on SQL Server in my coming interactions.
Happy Learning!!...
Cheers
Padhaku Einstein
Contact me at einstein.padhaku@gmail.com
No comments:
Post a Comment