Error when saving a modified table, "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
Solution: Click menu Tools > Options > Designers and uncheck "Prevent saving changes that require table re-creation."
How to get SQL Server to generate a new primary key ID? With the table open, right click on the int field, Properties > Identity Specification > change Is Identity to Yes
What is Identity Seed? The Primary Key number to begin with
How to set the default value of a field? Select the field > Column Properties > Default Value or Binding
How to create a default datetime2 value? Select the field > Column Properties > Default Value or Binding > type getdate()
How to create a default guid value? Set the field's DataType to uniqueidentifier. Select the field > Column Properties > Default Value or Binding > type newid()
How to create a check constraint? Right click on a field. Click Check Constraints. In the Expression field enter an expression like Quantity > 0 AND Quantity < 100. Check constraints are entered for the table, not a specific field.
How to create a unique constraint? Right click on a field. Click Indexes/Keys > Add > Type and change it to Unique Key. Click the ellipse next to the columns field. Add two or more column names.
How to delete a foreign key relationship: View a database diagram of your tables. Or in the Object Explorer right click on the Database Diagrams > New Database Diagram > Select the tables with the primary and foreign key relationship > Add > Close > Right click one of the tables and click Relationships. Click INSERT And UPDATE Specification > Delete Rule > Cascade > Close. Now you if you delete a row with with foreign keys pointing to it, it will delete the rows in the other tables also. This feature is available but NOT recommended.
How to create a computed column: Create a field in a table (e.g. Total). In the Column Properties section click Formula under Computed Column Specification. Type in your formula using field names.
How to import and export data: 1) Run Import and Export Data (32-bit or 64-bit) or 2) In SQL Server Management Studio, Right click your database > Tasks > click Import Data or Export Data. Note: Use the Copy Database Wizard to move or copy databases from a SQL Server 2000 or later.
How to query a table and dynamically make a new column name in the output: Use the AS keyword. For example, SELECT FirstName + ' ' + LastName AS FullName, EmailAddress FROM Customer
How to change the default database in the dropdown list Object Explorer > Security > Logins > Find yourself in the list and right click > Properties > Default Database. This way you don't need the fully qualified database name or need the USE Database Name statement.
LIKE statement characters:
- % — wildcard that matches any characters
- _ — matches a single character
How to retrieve rows with NULL values in a field: Use the IS keyword (i.e. WHERE City IS NULL)
How to retrieve rows without NULL values in a field: Use the IS keyword (i.e. WHERE City IS NOT NULL)
How to query rows in descending order: ORDER BY Price DESC
Query the top 10 items: Begin your query with SELECT TOP 10
Query the top 30% items: Begin your query with SELECT TOP 30 PERCENT
How to get a count all the rows in a table: Begin your query with SELECT COUNT (*) FROM
What are common aggregate functions to calculate values?: MAX, MIN, AVG, SUM, COUNT (count just gives us the number of rows)
Give an example of an aggregate function using the GROUP BY clause?: SELECT COUNT(*), FirstName FROM Customer GROUP BY Zipcode
How to find distinct values in a column?: Begin your query with SELECT DISTINCT FieldName
What is an alternative to a JOIN?: A subquery, also known as an inner query
Which is faster--a JOIN vs subquery?: It depends. Enter and highlight both SELECT statements and click the toolbar button Display Estimated Execution Plan. Next click Include Actual Execution Plan
How do you get unique or distinct results?: Use DISTINCT (e.g. SELECT DISTINCT)
