MS-SQL Server
Database:
A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format. Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time.
Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records. When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format.
A database typically has two main parts: first, the files holding the physical database and second, the database management system (DBMS) software that applications use to access data. The DBMS is responsible for enforcing the database structure, including: - maintaining relationships between data in the database. Ensuring that data is stored correctly and that the rules defining data relationships are not violated. - recovering all data to a point of known consistency in case of system failures.
Relational Database: Although there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory).
A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title.
Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345. When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.
To Create DB: CREATE DATABASE DatabaseName
What is SQL what its uses and its component
Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language. It allow the user to concentrate on specifying what data is required rather than concentrating on the how to get it.
The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables
What is the difference between SQL and T-SQL? Why would a company use one over the other?
ANSI SQL is the standard Structured Query Language. Most database vendors support SQL. T-SQL is Microsoft's "flavor" of SQL; it is ANSI SQL with Microsoft's extensions. A company could choose to use T-SQL over SQL if they have a database application that communicates with a MS SQL Server database only. Therefore a developer could use the "extras" for improved performance, ease of SQL coding, etc. A company could chose SQL because a front-end may have to communicate with several relational database management systems (RDBMs) therefore a programmer could rely on the fact that most database understand ANSI SQL.
Provide all the built in string function of SQL SERVER:
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM.
How to get number of Maximum connection can be establish to SQL
Select @@MAX_Connections.
Different index configurations table in SQL:
In SQL table can have following index configurations just like: - No indexes, A clustered index, A clustered index and many non-clustered indexes, A non-clustered index, Many non-clustered indexes.
How many objects SQL Server contains: Here is the list of some of the more important database objects database, The transaction log, Assemblies, Tables, Reports, Filegroups, Full-text catalogs, Diagrams, User-defined data types, Views, Roles, Stored procedures, Users, User Defined Functions.
Difference between Extents and Page: Extents: Multiple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.
Why we use SET ROWCOUNT in SQL: This syntax is used in SQL Server to stop processing the query after the specified number of rows is returned.
Why we use Unicode in SQL server: Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. The SQL Server Unicode data types are based on the National Character data types in the SQL-92 standard.
What is a join and List different types of joins: Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins:
• INNER JOIN,
• OUTER JOIN,
• CROSS JOIN.
OUTER JOIN is further classified as
• LEFT OUTER JOINS,
• RIGHT OUTER JOINS and
• FULL OUTER JOINS.
How does .NET and SQL SERVER thread is work
There are two types of threading pre-emptive and Non-preemptive but SQL Server support Non-preemptive and .NET thread model is different. Because SQL have to handle thread in different way for SQLCLR this different thread are known as Tasking of Threads. In this thread there is a switch between SQLCLR and SQL SERVER threads .SQL SERVER uses blocking points for transition to happen between SQLCLR and SQL SERVER threads.
Difference between temp table and table variable
• Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
• Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.
• You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.
• Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
• In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.
How many types of local table in SQL define with syntax
There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.
CREATE TABLE #MyTempTable ( PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,LastName VARCHAR(50) NOT NULL )
What is SQL tuning: SQL tuning is the process of getting that the SQL statements that an application that will issue that's run in the fastest possible time.
What is SQL injection: SQL injection is a security vulnerability that occurs in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.
What is SET operator in SQL SERVER: SET operators mainly used to combine same type of data from two or more tables. And another thing is that columns and their data type should be same as all the queries have. The column names from the first query will appear in the result.
UNION - It produce rows of first query + rows of 2nd query minus duplicate rows
UNION ALL - It produce rows from both the queries including duplicate rows.
select empid from emp union all select empid from emp_att group by empid
MINUS - Rows that are unique for the 1st query will be retrieved
INTERSECT - common rows from both the queries will be retrieved.
Join is used to select columns from two or more tables.
Can you define ROLLUP in SQL SERVER 2005: ROLLUP work with the "Group By" clause its main functioning comes into existence when we use Group by. We can get sub-total of row by using the Rollup function. When result is return by Group By class first row display the grand total or we can say that the main total.
Syntax: - select firstcolumn,secondcolumn,sum(thirdcolumn) from tablename group by firstcolumn,secondcolumn with rollup order by firstcolumn.
How many records can take clustered index in SQL: A clustered index is a special type of index that reorders the way the records in the table are physically stored. Therefore the table can have only one clustered index.
What is Cascade and Restrict when we use DROP table in SQL SERVER
When we are using Drop table in SQL the syntax is simple. Drop table table_name (CASCADE / RESTRICT) we use cascade to drop table although it have some dependencies just like triggers, storedprocedure, primarykey, foreignkey it will delete first. But if we use restrict a error message is shown on using of DROP if the table have relation Trigger, storedprocedure.
Some Tips when Optimizing SQL Server 2005 Query
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
What is SQL Cache Dependency in ASP.NET 2.0: SQL cache dependencies are new technique in ASP.NET 2.0 which can automatically invalidate a cached data object just like a Dataset. When the related data is modified in the database. So for instance if you have a dataset which is tied up to a database tables any changes in the database table will invalidate the cached data object which can be a dataset or a data source. To enable this we need a syntax that is as follows: - aspnet_regSQL -ed -E -d Northwind.
How many types of local tables in SQL SERVER: There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.
Where .NET CLR and SQL SERVER run: All .net application and SQL Server runs in same process or we can say that on same address because there is no issue of speed because if these two process are run in different process then there may be a speed issue created one process goes fast and other slow may create the problem.
What are Checkpoint in SQL Server: When we done operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. Checkpoint are the point which alert SQL Server to save all the data to main database if no Check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take long time because Checkpoint is also fired.
Explain integration between SQL Server 2005 and Visual Studio 2005: This integration provides wider range of development with the help of CLR for database server. Because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net. The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors.
What is the difference between SQL and PL-SQL: We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statements process in a single command line.SQL is structured query language, various queries are used to handle the database in a simplified manner. While PL/SQL is procedural language contains various types of variable, functions and procedures and other major difference is SQL as the name suggest it is just structured query language whereas PLSQL is a combination of Programming language & SQL.
What is the difference between UNION ALL Statement and UNION
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION, the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.
Write some disadvantage of Cursor: Cursor plays there row quite nicely but although there are some disadvantage of Cursor. Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query grenade output and after that cursor goes one by one so roundtrip happen. Another disadvantage of cursor is there are too costly because they require lot of resources and temporary storage so network is quite busy.
What is Log Shipping and its purpose: In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.
