SQL Server

How would you improve performance of the database in SQL Server?

(1) Re-writing inefficient SQL queries (2) avoiding long join chains in a single statement (3) creating/using indexes (4) trying to identify potential hardware issues that may lead to bottlenecks on manipulating large-scale data.

What is a Cursor?

-A cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, a SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application -Can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

What is the difference between a table-valued function and a scalar function?

-A table-valued function returns a table, and an example of this is a function which modifies all of the cells in the table, and returns the modified table. -On the other hand, a scalar function just returns 1 value. An example would be a function which returns the number of cells in the table.

What is partitioning?

-Makes large tables or indexes more managable -Enables user to manage and access subset of data quickly and efficiently while maintaining the integrity of a data collection

Name some Constraints.

-NOT NULL - Indicates that a column cannot store NULL value -UNIQUE - Ensures that each row for a column must have a unique value -PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly -FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table -CHECK - Ensures that the value in a column meets a specific condition -DEFAULT - Specifies a default value when specified none for this column

What are the two different types of union statements and what do they do?

-UNION will combine all the values in a column from two tables into one table, but no duplicates. -UNION ALL will combine all values from two columns into one column with duplicates. This only is useful if the two columns being "unioned" are of the same category, like cities, or names.

How would you setup an SQL database?

-open SQL Server Management -Right click on the folder name database -Select create new database -Name your database Can also use visual studio

What is the extension of a SQL Server Database Log file?

.ldf

What is a trigger?

A SQL procedure that starts an action, (fires an action - (trigger)) when an event like delete or update occurs. These "triggers" are managed by the DBMS and CANNOT BE CALLED or EXECUTED. Triggers are used to maintain the referential integrity of the database.

What are the four properties a Transaction must exhibit?

1: Atomicity - Each transaction is all or nothing: The entire process needs to complete for any part of it to execute. 2: Consistency - Any transaction will bring the database from one valid state to another, and all data must obey the rules of the database 3: Isolation - Any concurrent actions need to have the same effect as if the actions were executed in order. 4: Durability - After a transaction has been committed, it will always be so, even if there is a power failure, crash, etc. Therefore, the effects of the commit need to be saved to nonvolatile memory.

What are foreign keys?

A column in a table that references a column( most often the primary key) in another table

What is a Cross Join or Cartesian Product?

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table In mathematics, a Cartesian product (or product set) is the direct product of two sets.

What is an Inner Join?

A intersect B; the middle part of the venn diagram. only returns parts of A that join on the field in B AND the parts of B that join on the field in A.

What is a Left Outer Join (Left Join)?

A left outer join of A and B gives the matching results of A union B as well as the non matching results of table A

What is the effect of indexing on SQL Tables?

A non clustered index have a negative effect on insert/update/delete performance.

What is a Right Outer Join (Right Join)?

A right outer join of A and B gives the matching results of A union B as well as all the non matching results of table B

What are stored procedures used for? Advantages?

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. Advantages: Maintainability -Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier Testing -Can be tested independent of the application Speed / Optimization -Stored procedures are cached on the server Security -Provide an "interface" to the underlying data structure so that all implementation and even the data itself is shielded.

Difference between a view and a materialized view.

A view is basically a virtual table. Everytime you look at a view you are running the query to make it. A materialized view is more concrete. It caches the query result into a table that can be updated based on the query definition. Helpful is querying the base tables is expensive. As such, a view is more expensive to run, but a materialized view can be potentially out of date

What is connection pooling?

ADO.NET thing; optimization on multiple open/close operations on a DB connection. In brief, a 'pooler' object is created that maintains the actual connection (ports, authentication, etc) and the pooler manages the connections. conn.Open()/.Close() will actually go to the pooler, not the connection.

How do you connect to the database in a ASP .NET application?

ASP.NET gives you flexibility in how you connect to databases. A simple way is to use data source controls, which allow you to encapsulate data access in a control that you can configure with connection and query information. Alternatively, you can write code to perform data access yourself using ADO.NET classes or LINQ queries.

What are joins?

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

What is a Outer Join?

An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union from A and B.

What are four basic commands for dealing with data in SQL?

CRUD (CREATE, READ, UPDATE, and DELETE)

List and explain a few classes in SQL.

Classes? Assuming the question means clauses, SELECT chooses what data, FROM designates what table, and WHERE specifies what conditions for a query.

What is the SQL function that returns the total number of all entries?

Count(Either a select statement or other function)

What is the SQL feature that links tables?

Craig. Foreign Keys link tables together in one-to-one, one to many, and many to many relationships. Foreign keys make one column in two separate tables linked, so that the row related to one table can be called from the linked table and vice versa.

What is the difference between Delete and Truncate?

Delete DML command -Used to delete Rows in a Table (with or without specified condition) -Able to rollback Truncate -DDL Command -Used to delete all the rows from the table and free the space containing the table. -Unable to rollback

What is an Equi Join versus a Natural Join?

Equi join is a special type of join in which we use only the equality operator Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables

What is your experience with SQL?

I know enough to query database, create and alter tables, views, procedures, and functions.

Have you heard about the option STRICT ON in your query?

I've read up on it and discussed this during training and coursework. MySQL was criticized for being too relaxed on allowing invalid values or inserting truncated values that are out of range. So, two strict modes were created. STRICT_ALL_TABLES - produces errors when data is out of range. STRICT_TRANS_TABLES - produces errors when data is out of range, but only on transactional storage engines like INNO DB.

What would happen if you put your select statements directly in your C# code?

If you put your select statement in the C# code, you risk the chance of getting SQL Injection Attack. SQL Injection: What is it? SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations. It is perhaps one of the most common application layer attack techniques used today. It is the type of attack that takes advantage of improper coding of your web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database. In essence, SQL Injection arises because the fields available for user input allow SQL statements to pass through and query the database directly.

What is the extension of a SQL Server Database file?

In SQL: MDF Primary - containing the schema, data files and the startup info for the database and points to other files in the database

How do you use indexes in a database?

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance. You can create indexes on most columns in a table or a view. As mentioned above, indexes can enhance performance because they can provide a quick way for the query engine to find data. However, you must also take into account whether and how much you're going to be inserting, updating, and deleting data. When you modify data, the indexes must also be modified to reflect the changed data, which can significantly affect performance.

Differentiate between Outer and Inner joins.

Inner :selects all rows from both tables as long as there is a match between the columns in both tables. Think of the overlapping portions of a Venn diagram Outer -Full: Selects from both tables -Left: returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match -Right: returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match

Describe what database normalization is.

Isaac - Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy.

Can you have more than one index on a table?

It depends on what type of index; there can be many non-clustered indexes but only one clustered index.

What are the levels?

Levels First normal form (1NF) The domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. Second normal form (2NF) An entity type is in 2NF when it is in 1NF and when all of its non-key attributes are fully dependent on its primary key. Third normal form (3NF) An entity type is in 3NF when it is in 2NF and when all of its attributes are directly dependent on the primary key.

Have you used SQL Profiler? What is it for?

No, since I have never been a system administrator. A SQL Profiler is a tool designed to help system administrators monitor events, stored procedures, transactions, queries, etc. that occur within a server.

Have you used temporary table variables?

Nope

Which version of SQL have you used?

SQL Server, MySQL

Describe your SQL experiences with SQLServer and Oracle.

SQL Server: yep. Oracle: Nope

What is an alias?

SQL aliases are used to give a database table, or a column in a table, a temporary name. Aliases makes column name more readable

What are Foreign Key Constraints?

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

What are primary keys?

The PRIMARY KEY constraint uniquely identifies each record in a database table.

What are Transactions?

These are the unit of work performed against the database. Now, this work is performed logically either by the user, or automatically by the database.

Where would you use the WHERE and the HAVING clauses.

Use the Where clause towards the end of a query, after Select, From, or Join but before Order By and Having, to specify your search. The Having clause goes after a Where or Order By, for limiting rows using an aggregate function

Why use a table-valued function instead of just a select?

User-defined functions that return a table data type are referred to as table-valued functions. A user-defined functions can contain additional SELECT statements that allow more powerful logic than is possible in views (a view is limited to a single SELECT statement).

How would you implement SQL server databases?

Using SQL Server Management Studio. You can create the database, the tables inside and everything using the query command interface. Using the Designer inside SQL Server Management studio. Using Visual Studio - SQL Server Object Explorer. Using Entity Framework Code first or Model First.

Have you ever used TSQL? PL/SQL?

What?

Are you comfortable with manipulating query in a database?

Yep!

Have you done defined functions in SQL?

Yes I have. These functions can be a Built-In or User-Defined. Now, User-Defined functions allows us to create our own functions using the CREATE FUNCTION statement. The Built-IN function operates in the Transact SQL Reference and CANNOT BE MODIFIED. They can only be referenced in the Transact SQL statement only by syntax from Transact SQL Reference.

How do you optimize queries?

You can use SQL Profiler to locate long running queries In general, keep your DBs normalized Move queries to stored procedures:SPs are compiled and run on the server Limit your searches as much as possible-only grab what you need Don't use joins if you don't have to, they are expensive. Try to join on indexed fields if you can. Add indexes to fields you often filter by, especially if they are an Integer, Boolean, or Number You can limit the number of rows returned, or possibly do a pagination, so you don't have to get a giant amount of rows

How would you [set up the database tables to] handle a many-to-many data relationship?

You would set up an intermediate table, with the columns being two foreign keys, that point to the 2 primary keys in the tables you want to have the many to many relationship.

When would you use the outer join?

You would use an outer join when you want to get more than just the matching records of two table. You would use a left outer to also get all the records from the left table, a right outer for the opposite, and a full outer to return all records matching and not matching. Example: You could do an outer join with a table of placed orders and a table of shipped orders so you can see side-by-side the orders that are shipped and the ones that still need to be shipped.

What is referential integrity in a database?

a DB has referential integrity if the foreign keys (the references) are actually pointing to an entry in a different table (the reference has integrity)

What is star schema?

apparently a warehouse schema. It has two kinds of tables, Fact Tables and Dimension Tables. It looks like a Fact Table holds basic data types and foreign keys to dim. tables while dimensions represent more complex data. Dimension Tables are arranged around and connected to the Fact Table, looking like a star. Generally, the DimTables are small and queries will have few tables to join.

How to pass a parameter into an SQL Stored procedure.

exec stored_proc @param1 = value

How do you return all rows in a table where the Primary Key first name starts with "a"

select * from TableName where PK like 'a%' short answer: where PK like 'a%'

What are and have you ever used temporary tables in SQL Server?

similar to views in SQL. created using the INTO command, e.g. select * into @tempTable from Tables ... used to store intermediate results for immediate use. can be queried like regular tables. content is dropped when out of scope (?) but definition is kept until explicitly dropped. have I ever used them? no.