1. A query is written in the SQL language and is a request for information from data in a database.
2. Microsoft SQL Server uses the Transact Structured Query Language (T-SQL)
3. The percent % symbol is the most common wildcard. This symbol represents any number of characters. For example,WHERE Firstname like ‘%N’ would find a name that ends in N regardless of how long the name is. Examples may include Ann, MaryAnn and Dean among others
4. The % sign can even represent zero characters. For example, ‘%A%’ would find Alex and Lisa.
5. The SQL operator LIKE can be used to return a range of names, such as those beginning with a letter ranging from A to M. For example, WHERE Firstname LIKE “[a-m]%’
6. If you want to “exact match” a % symbol, like the name R% per!est and all other names with a percent symbol in them, surround the wildcard with square brackets. For example, LastName LIKE ‘%[%]%’
7．An inner join only returns a result set with perfectly matched values from fields in two or more tables.
8, An inner join is the default join type. If inner is omitted from the join clause of a query, SQL Server will assume it to be an inner join
9. In a left outer join, the table named before the join might have records that appear even if SQL Server finds no matching records in the table listed after the LEFT OUTER JOIN clause.
10. The table listed after the RIGHT OUTER JOIN might have records that appear even if no matching records are found in the table on the left of the ioin.
11. When you alias a table, you use an abbreviation. SQL aliasing usually means using a shorter name than the original identifier.
12. A cross join creates or finds all possible entity combinations. The cross ioin does not need to use an ON clause.
13. Column names for a table must be unique. You can’t have two fields named Hiredate in the same table.
14. A CREATE TABLE statement is a DDL statement. DDL means Data Definition Language. CREATE and DROP are DDL keywords.
15. Data Definition Language (DDL) statements handle the structure or design of database objects (e.g., databases and tables) where as Data Manipulation Language (DML) statements affect the actual data content. SELECT, INSERTUPDATE and DELETE are four key DML keywords
16. Before SQL 2008 you could only insert 1 record with oneinsert statement. New to SQL 2008 is a feature called Row Constructors where you can insert many records at once with one insert statement.
17. SQLCMD is a command-line utility that allows you to run sql scripts or Ad-Hoc SQL queries.
18. BCP stands for Bulk Copy Program. BCP lets you perform data imports and exports using a command-line utility.
19. A transaction is a group of SQL statements treated as a single unit. Transactions ensure data integrity.
20. Transaction statements either all execute together or they don’t at all
21. If one statement can’t run then the transaction is not committed.
22. A failed statement in a transaction means all data in the intermediate state gets discarded and none of the records will be committed.
23. The BEGIN TRANSACTION statement marks the beginníng of a group of SQL statements in a transaction.
24. The COMMIT TRANSACTION marks the end of the transaction and saves all the changes to SQL’s permanent storage.
25. If you want to read dirty data, you can use the READUNCOMMITTED table hint in your query.
26. The NOLOCK and READUNCOMMITTED table hints operate identically. Since NOLOCK is easier to type, it is used more often.
27. The advantage to the NOLOCK or READUNCOMMITTED table hints is your query runs without waiting for another process to release its locks.
28. To log into SQL Server, you need to create a server-level login. There are two types of server level logins (SQL Logins, Windows Logins)
29. Permissions can be manipulated with these DCL statements: GRANT, DENY, and REVOKE.
30. If you DENY a permission, it trumps any other permissions to that object or scope. For example, if you grant control and deny control, the user would have no access to the securable.
31. To remove an existing granted or denied permission, use the REVOKE keyword.