Development SQL Server Databases

The flashcards below were created by user mateotete on FreezingBlue Flashcards.

  1. What versions of SQL Server have you worked with?
    2005, 2008, 2008(R), 2010, and 2012, 2014, and 2016.
  2. What's the difference between pessimistic and optimistic locking?
    As the name suggests “optimistic” it assumes that multiple transaction will work without affecting each other. In other words NO LOCKS are enforced while doing optimistic locking. The transaction just verifies that no other transaction has modified the data. In case of modification the transaction is rolled back. 

    In pessimistic locking, we make sure to place a FULL LOCK onto the transaction meaning no one else can modify the underlying tables (user's have READ ONLY permissions) until the transaction is either committed or rolled back. 

    For example, by default, SQL uses optimistic locking whereas an ATM would use pessimistic locking.
  3. What are the names of the various system databases in SQL Server? Explain primary function of each.
    • MASTER (Contains all the system level information for SQL such as logins, servers, configuration settings);
    • MODEL (This is a template DB wherein all other new databases will model, guaranteeing items like rules, SP, users, and more without having to re-create these each time);
    • MSDB (Primary database to manage the SQL Server Agent, configurations, Mail, Backup History, DTS packages, Agent jobs/alerts, SSIS packages);
    • TEMPDB (Temporary database to store temporary tables, variables, cursors, and more and to rebuild indexes sorted in TempDB each time the query analyzer is used. Each time the SQL Server instance is restarted all objects in this database are destroyed since the workhorse DB is essentially a scratch-pad);
    • RESOURCES (a hidden system database where system objects are stored for DB manipulation ONLY)
  4. Give a list of 10 common SQL functions.
    Min(), Max(), Sum(), UCase(), Len(), Mid(), Now(), Round(), Count(), Last()
  5. What is a primary key?
    The PRIMARY KEY constraint uniquely identifies each record in a database table, it must contain a unique value AND can not contain NULL values, and each table can ONLY have one primary key.
  6. What is a foreign key used for?
    Maintaining referential integrity, that is, making sure that any value in one table matches another value in a different table.
  7. What does it mean to normalize a database?
    Normalization is the process of efficiently organizing data so that (a) eliminate redundancy and (b) ensuring data dependencies exist between tables.

    Normalization typically creates more tables and relates those tables using relationships. For example, think of a single Excel spreadsheet containing customer information along with amounts and dates a purchase was made...a normalization would result in two tables being created (Customers, Orders) along with a relationship between them.
  8. Normalize the following table, using the column names as a guide: Customer, Date, Item, Price
    Result would include two tables (Customers, Orders) and placing Customer in one table with other items in the Orders table.
  9. What are the benefits or normalization?
    • (a) eliminates reduncancy,
    • (b) improved performance,
    • (c) increases query optimization,
    • (d) less columns returned during search,
  10. What is the format of Date()? DateTime()?
    YYYY-MM-DD and YYYY-MM-DD HH:MM:SS, respectively
  11. What's a clustered index?
    A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.
  12. A table that has a clustered index is referred to as a XXX. A table that has no clustered index is referred to as a XXX.
    Clustered table; A "Heap" refers to a table with a non-clustered index, that is, having un-ordered indexes much like a book index.
  13. What's a non-clustered index?
    Unlike a clustered indexed, the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data. In addition, data in a table is NOT sorted if it is non-clustered.
  14. What is an index?
    • Index optimization is the most crucial improvement to a database table outside of normalization which is essentially placing a "key" on a column for quick lookups. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. An index can be placed on one or more columns in a table.
    • Two types of index exist: Clustered (value) and Non-Clustered (reference).
  15. What's the difference between a primary key, foreign key, and an index?
    Primary key is a unique key that is not null; foreign key is a key whose primary key is in another table; an index is like a lookup key, which can be placed on one or more columns within a table for faster searches.
  16. What's the difference between the DELETE and TRUNCATE command?
    TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas. DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary.
  17. Compare an aggregate function to a scalar function.
    A scalar function works with a single value and returns a single value (ie, ucase(), now(), ltrim(), repeat()) whereas an aggregate function returns a single value but can and often does contain multiple records (ie., avg(), sum(), count()).
  18. What is a user defined function?
    Two types exist: in-line functions which return multiple records or a scalar function which returns a single value.
  19. What is a trigger?
    A special kind of stored procedure that executes in response to a certain action (ie, insertion or deletion of a record).
  20. What are some of the benefits of using a stored procedure?
    (a) code reuse, (b) code tested for errors prior to be saved, compiled, (c) compiled, so runs faster, (d) security dur to encryption, (e) data encapsulation since only the interface is exposed in the form of parameters.
  21. Give several examples of constraints.
    Constraints are limitations set forth on a column, that the constraint must be satisfied in order for a record to exist in that table. They include primary key, foreign key, unique key, not null, check, and default value.
  22. What's the difference between a view and a stored procedure?
    Both objects are stored in the database, are compiled, and result in a query plan being created and stored in cache. However, the view can't contain parameters, error handling, transactions, complex statements (ie, if/then/case),modifications to tables or variables, and fact, it simply uses a SELECT statement to generate a virtual table. Nice thing about a view however, is it contain indexes making them VERY quick, when done this way, the result set is placed into cache for faster processing.
  23. What is a view?
    Views are virtual tables compiled at run time and the data associated with the view is not physically stored in the view (unless it has indexes at which point it is), rather only the definition of the view is stored in the database, that is, the metadata for the view is stored for later use as well as the query plan. Views can contain data from various tables that we would like to query over and over again. Once created, you would treat a view like a table. For security purposes, usually a view is provided rather than a table since you can abstract which columns you want the client to see.
  24. Name some common SQL Data Types.
  25. What can a bit hold?
    0, 1, or null
  26. What is a check?
    A constraint type that makes sure a value is one that falls within the constraint, For example, that values be either 1,2, or 3 would be a check constraint.
  27. Write a query to update ID = 47 with the name 'Peter'.
    UPDATE Leads SET firstname = 'Peter' WHERE id = 47;
  28. Write a query to insert an item into the database.
    INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
  29. What's the difference between CHAR, VARCHAR, NVARCHAR, and TEXT. Explain.
    Char(n) hold a fixed length character, VarChar(n) is dynamic ASCII (American) and can hold up to n maximum (n can't exceed 8k characters), NVARCHAR is also dynamic but UNICODE (Any language) and holds 4k characters, Text can hold up to 1 billion characters.
  30. Name the different types of JOINS.
    INNER, LEFT, RIGHT, CROSS, and FULL OUTER. The Left and Right Joins are considered outer joins, and the inner join is the default join, meaning a query that just uses the join keyword is using the inner join.
  31. What occurs if you return a LEFT JOIN that doesn't match the contents in the right table?
    All records from left side of the table is returned and columns in the result set from the right side would contain NULL values.
  32. Write a simple inner join.
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
  33. What's the difference between ISNULL() function and using IS NULL in a statement?
    • When comparing ANY column that contains NULL values...the result set will not return these values using comparison operators (ie, =, <=, <>), rather, you can only return these by using the IS NULL or IS NOT NULL statements. 
    • The ISNULL() function is a simple function that takes two arguments and returns the first if not null, otherwise returns the second (incidently, it always returns the data type of the first element).
  34. What's the difference between Unique and Distinct keywords?
    Unique is used for the creation of a table column to show that all items are unique, whereas Distinct keyword is used in the querying of data to return only distinct items.
  35. What is the difference between a UNIQUE and PRIMARY key?
    Both must be unique in their column, but only one primary can exist on a table whereas a single table may contain numerous unique keys.
  36. What is the COALESCE function used for in TSQL?
    The function returns the first non-null value in the list of two or more elements and uses the data type of the highest precedence item. For example: SELECT COALESCE(@x, @y, @z, @sum);
  37. Use COALESCE in a query.
    SELECT Id, COALESCE(FirstName,MiddleName,LastName) AS Name FROM tblEmployee
  38. Write a query that checks for several items in the state column using the IN keyword.
    SELECT * FROM States WHERE State IN ('CA', 'NY', 'GA');
  39. Write a query to return values between two different dates.
    SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
  40. Write a simple script to create a Lead table with a primary key and several common columns.
    CREATE TABLE Persons( ID int NOT NULL PRIMARY KEY, Name VARCHAR(255) NOT NULL, Address VARCHAR(255), City VARCHAR(255), State CHAR(2), Zip CHAR(5), Phone INT, DOB DATE, Sex BIT)
  41. Write a simple query that uses CASE WHEN statement.
    SELECT CASE WHEN MIN(value) <= 0 THEN 0 WHEN MAX(1/value) >= 100 THEN 1 END FROM Data;
  42. Write code to do a simple transaction.
    BEGIN @MyTransaction INSERT INTO #Table1 values (3); END IF @@ERROR <> 0 ROLLBACK @MyTransaction ELSE COMMIT TRANSACTION @MyTransaction
  43. How do you return a successful or failed response to the user?
  44. Define an Inner Join
    The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.
  45. Define a LEFT JOIN:
    Return all rows from the left table, and the matched rows from the right table; if no match exists in the right table, then null is used in that column of the result set.
  46. Define a RIGHT JOIN:
    Return all rows from the right table, and the matched rows from the left table; if no match exists in the left table, then null is used in that column of the result set.
  47. Define a FULL OUTER JOIN:
    The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
  48. Can a LEFT JOIN query contain more record results than exist in the left table?
    Yes, in fact, they often do. For example, a Customer table containing just two customers could be queried against a Orders table containing hundreds of orders, the LEFT JOIN on such a table could easily contain numerous records since each customer is likely to have numerous orders each.
  49. Could a LEFT JOIN query contain less record results than exist in the left table?
    No, because at the VERY least, it would contain the same number of records as that of the left table...and that would only happen if no matches were found in the right table.
  50. What's the criteria for a UNION query? Do names have to match?
    • Both tables must have same column (a) names, (b) data types, and (c) order
    • Names do not have to match but the column names returned in the result set originate from the first query.
  51. What's the difference between a UNION and UNION ALL SQL Statement?
    Union returns distinct records whereas UNION ALL returns duplicates.
  52. Write a query to put the contents of all California Leads into a new table called CalLeads.
    SELECT INTO CalLeads FROM Leads WHERE State = 'CA';
  53. What is a SELECT INTO query used for?
    The SELECT INTO statement copies data from one table and inserts it into a new table.
  54. What is the INSERT INTO SELECT statement used for? Give an example.
    The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. Example: INSERT INTO table2 SELECT * FROM table1;
  55. Write a simple query to determine the number of days between your birthday and today.
    SELECT DATADIFF(birthday, NOW());
  56. How can we test for null values in a column?
    We can't use comparison operators such as >, <, >=, <=, or =...for example, we can't say, SELECT * FROM Leads WHERE Address <> NULL; Instead, we can ONLY check for null values using the IS NULL or IS NOT NULL expression.
  57. Why was the HAVING clause created?
    HAVING clause is used in GROUP BY queries to refer to grouped items.
  58. Write a simple query to determine if any Customers have more than 10 Orders in the Customers, Orders tables.
    SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN EmployeesON Orders.EmployeeID = Employees.EmployeeID GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;
  59. What are the trade-offs with having indexes?
    (a) Faster selects, slower updates. (b) Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index
  60. Write a simple create stored procedure script called usp_GetCaliforniaLeads and retrieve the first 10 records.
    CREATE PROCEDURE sp_myStoredProcedure @number int, @state char(2) AS SELECT TOP @number * FROM Leads WHERE State = @state; Go
  61. Write a simple create view script called udv_GetCaliforniaLeads.
    CREATE VIEW udv_GetCaliforniaLeads AS SELECT * FROM Leads WHERE State = 'CA';
  62. Write a simple create Leads table script containing a primary key, index, a non-null column(s), a default timestamp, and.
    CREATE TABLE Leads ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), address VARCHAR(100), city VARCHAR(50), state CHAR(2), zipcode CHAR(10), phone INT NOT NULL, dob DATE NOT NULL, created DATETIME DEFAULT NOW() );
  63. What is an execution plan?
    • SQL Server Management Studio provides the Database Administrator or programmer the ability to manipulate DB objects (ie, tables, views, constraints, and more) and use the query analyzer to run queries against those objects. Rather than simply seeing the results of the query, the user may request to "Show Execution Plan" which will show how the SQL Query Optimizer will run the query and provide a graphical representation of that execution plan and estimated times and processing statistics.
    • An experience SQL Server used with highlight two queries and compare their respective execution plan to determine which one is faster.
Card Set
Development SQL Server Databases
Software Development SQL Server Databases
Show Answers