Section 1
Preview this deck
WHEN TO USE TABLE VAIRABLE
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
7 years ago
Date created
Mar 1, 2020
Cards (101)
Section 1
(50 cards)
WHEN TO USE TABLE VAIRABLE
When you are required to use the current result set in next queries then store result into temp variables. When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.
WHAT IS RECURSIVE CTE
A CTE that references itself is called as recursive CTE. Recursive CTE's can be of great help when displaying hierarchical data.
Limitations Of TABLE Variables
1)you cannot use a table variable as an input or an output parameter. 2)The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement. 3)We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table.
what is CTE?
CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. if a CTE is created on one base table, then it is possible to update the CTE, which will update the underlying base table. IF a CTE is based on more than one table, and if the update affects only one base table, the the UPDTAE is allowed. if a CTE is based on multiple tables, and if the update statement affects more than 1 base table, then the update is not allowed.
What are Temporary tables?
Permanent tables get created in the database you specify, and remain in the database permanently, until you delete (drop) them. On the other hand, temporary tables get created in the TempDB and are automatically deleted, when they are no longer used.
Transaction processing follows these steps:
1. Begin a transaction. 2. Process database commands. 3. Check for errors. If errors occurred, rollback the transaction, else, commit the transaction
the syntax of derived table
Derived tables are available only in the context of the current query. Select DeptName, TotalEmployees from ( Select DeptName, DepartmentId, COUNT(*) as TotalEmployees from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId group by DeptName, DepartmentId ) as EmployeeCount where TotalEmployees >= 2
updated CET
if a CTE is created on one base table, then it is possible to update the CTE, which will update the underlying base table. IF a CTE is based on more than one table, and if the update affects only one base table, the the UPDTAE is allowed. if a CTE is based on multiple tables, and if the update statement affects more than 1 base table, then the update is not allowed.
ACID
A transaction is a group of database commands that are treated as a single unit. A successful transaction must pass the "ACID" test, that is, it must be A - Atomic C - Consistent I - Isolated D - Durable
Isolated
The transaction must affect data without interfering with other concurrent transactions, or being interfered with by them. This prevents transactions from making changes to data based on uncommitted information, for example changes to a record that are subsequently rolled back. Most databases use locking to maintain transaction isolation.
Advantages of using views:
1. Views can reduce the complexity of the database schema. for example, for some Non-IT users, they can use View very easy without knowing joins or subquery .they don't have to write complex joins. 2. Views can improve security. For example, my client want to get the all of employee's info from IT Department. In that case,I can create a view which returns only IT department employees. if I grant him access to the underlying tblEmployees and tblDepartments tables, he will be able to see, every department employees. which is not security at all. 3. Views can be used to present only aggregated data and hide detailed data. For example, I can create a View that returns summarized data, Total number of employees by Department. in that case, I dont have to disclousre all of details.
What is a covering query?
If all the columns that you have requested in the SELECT clause of query, are present in the index, then there is no need to lookup in the table again. The requested columns data can simply be returned from the index.A clustered index, always covers a query, since it contains all of the data in a table. A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes. To a certain extent, a composite index, can cover a query.
Unique index
is used to enforce uniqueness of key values in the index.
the syntax of CTE
With EmployeeCount(DeptName, DepartmentId, TotalEmployees) as ( Select DeptName, DepartmentId, COUNT(*) as TotalEmployees from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId group by DeptName, DepartmentId ) Select DeptName, TotalEmployees from EmployeeCount where TotalEmployees >= 2 WITH cte_name (Column1, Column2, ..) AS ( CTE_query ) SQL query using CTE: With EmployeeCount(DepartmentId, TotalEmployees) as ( Select DepartmentId, COUNT(*) as TotalEmployees from tblEmployee group by DepartmentId ) Select DeptName, TotalEmployees from tblDepartment join EmployeeCount on tblDepartment.DeptId = EmployeeCount.DepartmentId order by TotalEmployees
Why indexes?
Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views. If there is no index to help the query, then the query engine, checks every row in the table from the beginning to the end.
3NF
1. Meets all the conditions of 1NF and 2NF. 2, all non-key attribute(column) must be mutually independence. (意思就是column和column之间不能有关系）
1 NF
1. The data in each column should be atomic. 2. The table does not contain any repeating column groups 3. Identify each record uniquely using primary key.
what is a trigger？
In general, a trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
function vs stored procedure
1，SP could be executed by execute statement, UDF must called from select statement 2，SP could not return any value but UDF must return something 3，SP could have out parameter, UDF could not have out parameter 4，SP could create temporary tables but UDF couldn't 5，SP could include DDL and DML but UDF cannot have DDL and cannot do DML with permanent tables. 6，SP can call UDF but UDF cannot call SP.
what is clustered index
A clustered index determines the physical order of data in a table. for this reason, a table can have only one clustered index.
what is temp table
Temporary tables are stored in TempDB. Local temporary tables are visible only in the current session, and can be shared between nested stored procedure calls. Global temporary tables are visible to other sessions and are destroyed, when the last connection referencing the table is closed.
pivot 是啥
Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table.
structured data VS unstructured data
structured data refers to information with a high degree of organization, such as the data in a relational database which can be readily searchable by simple, straightforward search engine algorithms or other search operations; whereas unstructured data is essentially the opposite. The lack of structure data is very time-consuming and energy consuming.
union vs join vs merge
joins combine data into new columns. Unions combine data into new rows Unions are typically used where you have two results whose rows you want to include in the same result. A use case may be that you have two tables: Teachers and Students. You would like to create a master list of name and birth days sorted by date. Both joins and unions can be used to combine data from one or more tables into a single results. They both go about this is different ways. Whereas a join is used to combine columns from different tables, the union is used to combine rows. Merge needs two tables: 1 source table and target table The merge statement joins the target table to the source table by using a common column in both the tables. Based on how the rows match up as a result of the join, we can then perform insert, update, and delete on the target table.
Difference between Clustered and NonClustered Index:
1. Only one clustered index per table, where as you can have more than one non clustered index 2. Clustered index is faster than a non clustered index, because, the non-clustered index has to refer back to the underlying table 3. Clustered index determines the storage order of rows in the table, and hence doesn't require additional disk space, but where as a Non Clustered index is stored separately from the table, so it requires additional storage space.
what scenarios I should consider using table variable, temp table, or CTE? They all seem to serve the same purpose, which is providing a temporary set of data, correct? What are the advantages and disadvantages of using each?
1.Temp table Can have constraints where CTE cannot, 2.TT Can be referenced by other queries or subprocedures where CTE cannot. 3. A temp table is a table created on disk so you can use it more than once while the CTE only use within specific the Query as its not in the memory.
Consistent
All data touched by the transaction is left in a logically consistent state. For example, if stock available numbers are decremented from tblProductTable, then, there has to be a related entry in tblProductSales table. The inventory can't just disappear.
A standard or Non-indexed view
A standard or Non-indexed view, is just a stored SQL query. When, we try to retrieve data from the view, the data is actually retrieved from the underlying base tables. So, a view is just a virtual table it does not store any data, by default.
syntax of temp table
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees into #TempEmployeeCount from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId group by DeptName, DepartmentId Select DeptName, TotalEmployees From #TempEmployeeCount where TotalEmployees >= 2 Drop Table #TempEmployeeCount
Both primary key and unique key are used to enforce, the uniqueness of a column. So, when do you choose one over the other?
A table can have, only one primary key. If you want to enforce uniqueness on 2 or more columns, then we use unique key constraint.
What is CTE ?common table expressions
A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
indexed view
This means, the view is now, capable of storing data.
What is a View?
A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.A view does not store any data. So, when this query is executed, the database engine actually retrieves data, from the underlying base table.
problems of data redundancy
1 disk space wastage 2 Data Inconsistency 3. DML queries can become slow(insert, update, delete) 举例子：Consider Employees table below. For every employee with in the same department, we are repeating, all the 3 columns (DeptName, DeptHead and DeptLocation). Let's say for example, if there 50 thousand employees in the IT department, we would have unnecessarily repeated all the 3 department columns (DeptName, DeptHead and DeptLocation) data 50 thousand times. The obvious problem with redundant data is the disk space wastage. Another common problem, is that data can become inconsistent. For example, let's say, JOHN has resigned, and we have a new department head (STEVE) for IT department. At present, there are 3 IT department rows in the table, and we need to update all of them. Let's assume I updated only one row and forgot to update the other 2 rows, then obviously, the data becomes inconsistent. Another problem, DML queries (Insert, update and delete), could become slow, as there could many records and columns to process. So, to reduce the data redundancy, we can divide this large badly organised table into two (Employees and Departments), as shown below. Now, we have reduced redundant department data. So, if we have to update department head name, we only have one row to update, even if there are 10 million employees in that department.
what is transaction?
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
two disadvantages of view
(1)When table is dropped or modified, view becomes inactive, it depends on the table objects. 2)Not all the time we can perform DML (insert, update, delete) statements, as normally views are made for complex query and depends on more than one table. So there is more possibilities of violating your database constrains while performing DML statements. 3)it is also database object so it will occupy the space.
syntax of table variable
Declare @tblEmployeeCount table (DeptName nvarchar(20),DepartmentId int, TotalEmployees int) Insert @tblEmployeeCount Select DeptName, DepartmentId, COUNT(*) as TotalEmployees from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId group by DeptName, DepartmentId Select DeptName, TotalEmployees From @tblEmployeeCount where TotalEmployees >= 2
What is the difference between Primary key constraint and Unique key constraint?
1. A table can have only one primary key, but more than one unique key 2. Primary key does not allow nulls, where as unique key allows one null 3 Primary auto generate clustered index, unique key auto generated a non-clustered index.
difference between unique constraint and a unique index.
In fact, when you add a unique constraint, a unique index gets created behind the scenes.
当view中有group by , 那么就不能update view 中的任何column
When modifying data through a view (that is, using INSERT or UPDATE statements) certain limitations exist depending upon the type of view. Views that access multiple tables can only modify one of the tables in the view. Views that use functions, specify DISTINCT, or utilize the GROUP BY clause may not be updated. Additionally, inserting data is prohibited for the following types of views:
what is table variable?
Just like TempTables, a table variable is also created in TempDB. The scope of a table variable is the batch(就是一大坨code必须一起执行 否则会报错）, stored procedure, or statement block.
Non Clustered Index:
A nonclustered index is similar as an index in a textbook. The data is stored in one place, the index in another place. The index will have pointers to the storage location of the data. Since, the nonclustered index is stored separately from the actual data, a table can have more than one non clustered index, just like how a book can have an index by Chapters at the beginning and another index by common terms at the end.
Database normalization
is the process of organizing data to minimize data redundancy (data duplication), which in turn ensures data consistency.
what is function
built-in function, system function, user-defined function. Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can't use function to Insert, Update, Delete records in the database table(s). System Defined Function are defined by Sql Server for different purpose. such as max() min() avg() round() User Defined Function are created by user in system database or in user defined database. We three types of user defined functions. (1)Scalar functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data type. To create a function, we use the following syntax: CREATE FUNCTION Function_Name(@Parameter1 DataType, @Parameter2 DataType,..@Parametern Datatype) RETURNS Return_Datatype AS BEGIN Function Body Return Return_Datatype END 2) an Inline Table Valued function, return a table. Syntax for creating an inline table valued function CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType) RETURNS TABLE AS RETURN (Select_Statement)
what is relation
relations consist of a relation instance and a relation schema. A relation instance is a set of tuples or records. A relation schema consist of the relation's name, the name of each column in the table. the column heading and the domains of the table. a database is a collection of relations.
Different Types of Temporary tables
In SQL Server, there are 2 types of Temporary tables - Local Temporary tables and Global Temporary tables.
What is Atomic
atomic - All statements in the transaction either completed successfully or they were all rolled back. The task that the set of operations represents is either accomplished or not, but in any case not left half-done. For example, in the spUpdateInventory_and_Sell stored procedure, both the UPDATE statements, should succeed. If one UPDATE statement succeeds and the other UPDATE statement fails, the database should undo the change made by the first UPDATE statement, by rolling it back. In short, the transaction should be ATOMIC.
what is stored procedure?
stored procedure is group of T-SQL (Transact SQL) statements. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name
Durable
Once a change is made, it is permanent. If a system error or power failure occurs before a set of commands is complete, those commands are undone and the data is restored to its original state once the system begins running again.
2NF
1)The table meets all the conditions of 1NF 2).All attributes (non-key columns ) dependent on the key .remove non -related data to a separate table 3. Create relationship between these tables using foreign keys.
Section 2
(50 cards)
what is Trigger
A trigger is a procedure that a collection of sql query will automatically excute when some event happened A trigger is enforce the referential integrity.
1NF 2NF 3NF（加上例子）
Table Variable
This acts as a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.
what is lost update problem?
Lost update problem happens when 2 transactions read and update the same data. example: transaction A update salary from 3000 to 5000. (waitfor delay is 10 seconds 让transaction b先执行） then transaction b update salary from 3000 to 1000. 这时候两个transition 读取的data不一致 如果把transaction level设定为 repeatable read. 例子： 存货一共10台电脑 transaction1 update减去2个 transaction2 update减去一个 transaction2会报错，要求你再rerun.系统会确保transaction 1成功process,然后重新跑transction2才会成功。 最后结果显示剩下的电脑存货为7.
有哪些常用的datatype
two types of temp table
The scope of Local temp is the current query window. If you will close the current query window or open a new query window and will try to find above-created temp table, it will give you the error. Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign. Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
Transaction processing follows these steps:
1. Begin a transaction. 2. Process database commands. 3. Check for errors. If errors occurred, rollback the transaction, else, commit the transaction
What is a transaction
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit of work. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
what is index
An index is a physical structure containing pointer to the data Use index to retrieve data very quickly
function vs procedure的区别
what is the side effect of the highest isolation level
Serializable), you will have no concurrency side effects, but the downside is that, this will reduce the number of concurrent transactions that can be executed at the same time if those transactions work with same data.
Some of the common concurrency problems
Dirty Reads Lost Updates Nonrepeatable Reads Phantom Reads
what is phantom read?
A phantom read occurs when, in the course of a transaction , two identical queries are executed, and the collection of rows returned by the second query is different from the first one. this happens when a second transaction inserts a new row. 例子 User A runs the same query twice. In between, User B runs a transaction and commits.
什么时候需要denormalization
Denormalization is a way to store data redundantly to help simplify certain queries. There are three general cases that are solved by denormalization: #1 To enhance query performance. As usual, a normalized database requires joining lots of tables to fetch queries but the more joins, the slower the query. To meet this challenge, you should reduce the number of joins required for a query by adding redundancy to a database (copying values between parent and child tables) 2 To make a database more convenient to manage. A normalized database doesn't contain calculated values that are important for applications. Calculating these values on-the-fly requires time and slows down query execution. Denormalization allows you to add calculated values to a database; this helps downstream programmers easily manage a database without having in-depth knowledge of the app's code (age = current date- DOB)
面试1 什么是function how to use for what with example
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tblInventory' directly or indirectly in database 'SampleDB' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
comiited read vs uncommitted read
default level就是 read committed commited read就是说同时有两个transaction ab. a 在update把工资从3000 改到5000. transction b就是读取工资。 当a在更新失败的时候就会rollback 原返回3000. 但是此时b的读取过程被block直到a完全执行完后才能读取。 如果在transaction b中设置为 uncommited syntax: set transction isolation level read committed a 在update把工资从3000 改到5000. transction b就是读取工资。 当a在更新失败的时候(中间持续10秒假设）就会rollback 原返回3000. 但是此时b的读取过程不会被被block，会立即读取。得到的结果是5000. 但是a的数据被roll back了就恢复成原始数据。这样读的数据不统一 就是dirty read.
how to solve phantom read problem
set the transaction a 的isolation level as serializable it will place a range lock on the rows 1 and 3, which prevents any other transaction from inserting new rows with in that range -- Transaction 1 Begin Transaction Select * from tblEmployees where Id between 1 and 3 -- Do Some work waitfor delay '00:00:10' Select * from tblEmployees where Id between 1 and 3 Commit Transaction -- Transaction 2 Insert into tblEmployees values(2, 'Marcus')
difference between CTE, TEMP TABLE and table variable
Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index-like normal tables. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the Tempdb database but not the memory.
The isolation level that you choose for your transaction, defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Depending on the isolation level you have chosen you get varying degrees of performance and concurrency problems.
read uncommitted transaction isolation level is the only isolation level that has dirty read side effect, this is the least restrictive of all the isolation levels. when this transction isolation level is et, it is possible to read uncommited or dirty data.
什么是engine tuning advisor
what is CTE?
It is a temporary result set and typically it may be a result of complex sub-query. Unlike a temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon. When to use CTE: This is used to store a result of a complex subquery for further use. This is also used to create a recursive query.
Some of the common concurrency problems
Dirty Reads Lost Updates Non-repeatable Reads Phantom Reads
text vs varchar vs char()
When stored in a database, varchar uses only the allocated space. E.g. if you have a varchar(1999) and put 50 bytes in the table, it will use 52 bytes. But when stored in a database, char always uses the maximum length and is blank-padded. E.g. if you have char(1999) and put 50 bytes in the table, it will consume 2000 bytes.
什么是cursor
可以逐条读取 简单来说就是可以停的for loop Cursor is a Database object which allows us to process each row and manipulate its data. A Cursor is always associated with a Select Query and it will process each row returned by the Select Query one by one. Using Cursor we can verify each row data, modify it or perform calculations which are not possible when we get all records at once. A simple example would be a case where you have records of Employees and you need to calculate Salary of each employee after deducting Taxes and Leaves 0 The FETCH statement was successful. -1 The FETCH statement failed or the row was beyond the result set. -2 The row fetched is missing. -9 The cursor is not performing a fetch operation.
Dirty Reads - This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad. Phantom Reads - This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.
why CTE? why bother to divide this into two parts, when you could have done the whole thing with a single query?
you can nearly always use a single complex SQL query to avoid using a CTE; but using a CTE will nearly always make a query easier to understand.
temp table vs CTE
Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index-like normal tables. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the Tempdb database but not the memory.
different types of joins
Join is used to combine rows from two or more table, based on related column between them. Four type Inner join: returns the record that have matching value in both tables Left join: returns all records in left table and matched record from right table Right join: return all records in right table and matched record from left table Full join: return all records when there is a match in either left or right table records
olap VS OLTP
1 OLTP online transaction processing is the current database in use for users, holds only current info or limited historical data 2 OLAP Online analysis processing Separate database holding historical data, used for business decision and trends.
what is the down side of lowest isolation level
If you choose the lowest isolation level (i.e Read Uncommitted), it increases the number of concurrent transactions that can be executed at the same time, but the down side is you have all sorts of concurrency issues.
是不是一定需要key才能join
Natural join does not use any comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain. Natural join acts on those matching attributes where the values of attributes in both the relations are same.
Difference between serializable and snapshot isolation levels
Serializable isolation is implemented by acquiring locks which means the resources are locked for the duration of the current transaction. this isolation doesnt have any concurrency side effects but at the cost of significant reduction in concurrency. snapshot isolation doesn't acquire locks, it maintains versioning in Tempdb. Since, snapshot isolation doesn't lock resources, it can significantly increase the number of concurrent transactions while providing the same level of data consistency as serializable isolation does.
SQL Server provides different transaction isolation levels, to balance concurrency problems and performance depending on our application needs.
Read Uncommitted Read Committed Repeatable Read Snapshot Serializable
cte vs temp table
三种identity的区别 分别有什么效果
how to solve this non-repeatable problem?
set transaction 1 isolation lever repeatable read 这时候当 transaction a 在执行的时候 （读取两边工资为3000，中间还间隔十秒） transaction b 会被block，直到a执行完。b才会执行
what is non repeatable read problem?
User A runs the same query twice.In between, User B runs a transaction and commits.The A row that user A has queried has a different value the second time. 举例子 transaction a select from read id =1 的salary 为3000两次 中间wait 10秒 transaction b 把salary 更新从3000 到5000 这时候 读取transaction a 会有两个不同的结果 第一个是3000 第二个是5000
what is merge
Merge statement introduced in SQL Server 2008 allows us to perform Inserts, Updates and Deletes in one statement. This means we no longer have to use multiple statements for performing Insert, Update and Delete. With merge statement we require 2 tables 1. Source Table - Contains the changes that needs to be applied to the target table 2. Target Table - The table that require changes (Inserts, Updates and Deletes)
之前是否用过temp table
yes,i do. i used sql server, 得到了最近一个月的波士顿地区的拖车的数量，在哪条街上被拖车最多。把这个东西存成一个temp table. 在分析的时候就query 这个temp table,所以节省时间提高效率， 而不用每次再去原始的dataset中得到这个table.（original database)
Disadvantages of Cursor
The major disadvantage of a Cursor is its performance issue. A Cursor can be really slow when performing operations on large number of records and your SQL Query may take minutes to execute and produce results. Thus you must wisely choose and decide on the right scenario where you want to use a Cursor.
the difference between repeatable read vs Serializable
Repeatable read prevents only non-repeatable read. repeatable read isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction, but it doesnt prevent new rows from being inserted by other transactions resulting in phantom read concurrency problem. Serializable prevents both non-repeatable read and phantom read problems. Serizlizable isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction.it also prevents new rows fro being inserted by other transactions
normalization的几种范式
什么是isolation level
Different type of attributes
Simple attribute: atomic values Composite attribute: made of more than one simple attribute (name) Derived attribute: do not exist in the physical database, but values could be derived form other attributes present in database (average salary) Single-value attribute: contain single value Multi-value attribute: contain more than one value
truncate vs delete
what is snapshot isolation
举例子： transaction A is trying to update salary from 3000 to 5000. Transaction B is trying to update salary from 3000 to 8000. when we set the transaction A 's isolation level as snapshot, then execute those two transaction at the same time. situation 1 :if we didnt commit the transaction a, the result of Transaction a will show immediately. then execute transaction2, it will be blocked. thats because that both of the transaction are trying to update the same piece of data. situation 2 : if we commit the transation 1, then the transaction 2 will fail. 系统提示不能同时更新同一个data
what isn Dirty read example
a dirty read happens when one transction is permitted to read data that has been midified by another transition that has not yet been committed. In most case, it would not cause a problem. However, if the first transaction is rolled back after the second reads the data, the second transaction has dirty data that does not exist anymore
Clustered index and non-cluster index
Cluster index is the physical storage order of the data Non-cluster index is the logical order of the index
Section 3
(1 card)