Transparent Data Encryption. The ability to encrypt an entire database. Backup Encryption. Executed at backup time to prevent tampering. External Key Management. Storing Keys separate from the data. Auditing. Monitoring of data access. Data Compression. Fact Table size reduction and improved performance. Resource Governor. Restrict users or groups from consuming high levels or resources. Hot … Read more
Its a default constraint, you need to perform a: ALTER TABLE {TableName} DROP CONSTRAINT ConstraintName If you didn’t specify a name when you created the constraint, then SQL Server created one for you. You can use SQL Server Management Studio to find the constraint name by browsing to the table, opening its tree node, then … Read more
Try to check for existence: IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID) INSERT INTO dbo.Employee(Col1, …, ColN) VALUES(Val1, .., ValN) ELSE UPDATE dbo.Employee SET Col1 = Val1, Col2 = Val2, …., ColN = ValN WHERE ID = @SomeID You could easily wrap this into a stored procedure and just call that … Read more
2627 is unique constraint (includes primary key), 2601 is unique index SELECT * FROM sys.messages WHERE text like ‘%duplicate%’ and text like ‘%key%’ and language_id = 1033
When you start SSMS, it allows you to choose a Server Type and Server Name. In the server type dropdown, choose “Integration Services” and connect to the server. Then you’ll be able to see what packages are in the db.
You can create table variables: DECLARE @result1 TABLE (a INT, b INT, c INT) INSERT INTO @result1 SELECT a, b, c FROM table1 SELECT a AS val FROM @result1 UNION SELECT b AS val FROM @result1 UNION SELECT c AS val FROM @result1 This should be fine for what you need.
Coerce to an accent insensitive collation You’ll also need to ensure both side have the same collation to avoid errors or further coercions if you want to compare against a table variable or temp table varchar column and because the constant value will have the collation of the database Update: only for local variables, not … Read more
Index key columns are part of the b-tree of the index. Included columns are not. Take two indexes: CREATE INDEX index1 ON table1 (col1, col2, col3) CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3) index1 is better suited for this kind of query: SELECT * FROM table1 WHERE col1 = x AND col2 = … Read more
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name=”MyTableIndex” AND object_id = OBJECT_ID(‘tablename’)) BEGIN — Index with this name, on this table does NOT exist END
A quick google points me to what looks like the solution. Open your EDMX in a file editor (or “open with…” in Visual Studio and select XML Editor). At the top you will find the storage model and it has an attribute ProviderManifestToken. This has should have the value 2008. Change that to 2005, recompile … Read more