Sharing knowledge does not lessen your store, often it gets you more.
Success doesn't happen overnight and patience is key to living your dream life.
Success is a journey not a destination
Had this issue in using a SQL Server CLR Assembly.
Issue
Normally this issue arises if you have restored the database to a new server.
Solution
Check whether the owner has been assigned for the database.
Drop and Create the assembly and make to put the path of the assembly in plain text rather than scripting it from another environment as it would have been encoded and won't work on this server.
By taking advantage of SQL Server's data compression feature, you can improve SQL Server performance without changing any application code. Compressing data reduces database storage, which leads to fewer I/O reads and writes. However, SQL Server consumes more CPU resources when it compresses and decompresses data. Data compression is currently supported in the Enterprise, Developer, and Evaluation editions of SQL Server 2008 and later.
Row compression doesn’t work on XML, BLOB and MAX data types instead you can create CLR functions to gzip them to reduce the storage space.
I had a scenario where I had to load a csv file which had around 2000 columns. Then I started looking at the wide table feature which came as part of SQL Server 2008. A wide table can have 30,000 columns as opposed to a non-wide table which can have 1024 columns.
Having 30,000 or even 1,024 columns in a table is a lot of columns and design review needs to be done for these type of scenarios. In my scenario it is another system which generates this file and the business wants an ability to query it easily via a single table. More or less just for analysis purposes.
Sparse columns are designed to allow a relational database to store and process relatively unstructured data, where any individual entity may have a modest selection from a very wide set of attributes. This sort of data can be found frequently in scientific, pharmaceutical, and medical applications. EAV (Entity/Attribute/Value) storage techniques were engineered for such data; however, EAV data storage has proved to be somewhat inefficient in such areas as query speed and storage cost.
Sparse columns work well where a high proportion of the data in a column is NULL, because negligible storage space is used for the NULL representation. A table that is wide in theory can be made narrow in practice by implementing sparse columns, and the addition of filtered indexes provides effective searches on the sparse data. Sparse columns support the use of check constraints, as long as the constraints allow NULL values (a sparse column requirement).
Column sets provide a solution to the difficulties of efficient imports and exports of sparse data. The untyped XML representation that is generated for each row interacts directly with each sparse field. The XML field accepts inserts, and its data can be extracted and modified easily. This feature effectively supports certain types of serialized object data from various applications.
CREATE TABLE SPARSETABLE
(
ID INT NOT NULL IDENTITY(1,1)
,FIRSTNAME VARCHAR(100) NOT NULL
,MIDDLENAME VARCHAR(100) SPARSE NULL
,LASTNAME VARCHAR(100) NOT NULL
,SPARSEDATASET XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
INSERT SPARSETABLE(FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES ('John','Daniel','Smith');
GO
INSERT SPARSETABLE(FIRSTNAME, LASTNAME)
VALUES ('Peter','Wilson');
GO
SELECT [ID]
,[FIRSTNAME]
,[LASTNAME]
,[SPARSEDATASET]
FROM [SPARSETABLE]
SQL SERVER extended properties allows developers to load metadata information like description, Source etc on a table or column within a table. This will allow information to be documented and attached to SQL SERVER and also queryable.
--Add Extended Property for a table EXEC sp_addextendedproperty @name=N'Description', @value=N'Dimension with the list of all products.' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DIM_PRODUCTS' --Update Extended Property for the table EXEC sp_updateextendedproperty @name=N'Description', @value=N'Dimension with the list of all available products.' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DIM_PRODUCTS' GO --Add Extended Property for a column EXEC sp_addextendedproperty @name=N'Description', @value=N'Name of the Product.' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DIM_PRODUCTS', @level2type=N'COLUMN', @level2name=N'PRODUCT_NAME' --Update Extended Property for a column EXEC sp_updateextendedproperty @name=N'Description', @value=N'Name of the Product' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DIM_PRODUCTS', @level2type=N'COLUMN', @level2name=N'PRODUCT_NAME' GO --Query Extended Properties SELECT objname, name, value FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', default, NULL, NULL) SELECT objtype, objname, name, value FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'DIM_PRODUCTS', 'column', default);
SQLCLR allows integration of .NET Framework CLR components in SQL SERVER. .NET CLR Assemblies reside and run from database rather than file system. SQL SERVER need to check only once during deployment. This boosts performance and reliability.
SQLCLR allows to write stored procedures, functions, triggers, user defined types and aggregates using variety of .NET Framework languages like C#, VB.NET etc.
When to use SQLCLR?
Don't use SQLCLR for querying data from SQL SERVER. It is better to use T-SQL for that. Instead use SQLCLR when you can't perform some tasks using native SQL functions like GZip Compression/Decompression, PGP Encryption/Decryption, complex string manipulations etc.
How to enable SQLCLR?
exec sp_configure 'clr enabled', 1; GO RECONFIGURE GO
Consider a scenario where you have a view object in a database named "Database A" tries to access a table from "Database A" as well as a table from "Database B". In this scenario to run the view the user must also be a user in Database B along with Database A. If these databases are tightly linked, cross database ownership chaining can be used where user can access the table from Database B only through the view from Database A but doesn't have explicit permissions to query the table in Database B.
Steps to enable Cross Database Ownership Chaining
Set both Database A and Database B in the chaining as trustworthy.
ALTER DATABASE Database1 SET TRUSTWORTHY ON;
ALTER DATABASE Database2 SET TRUSTWORTHY ON;
Make sure that both Tables inside the Database A and Database B belong to the same owner.
Enable Cross Database Ownership Chaining. This can be done on whole instance level or specific databases.
For instance level
Exec sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;
For database level
ALTER DATABASE Database1 SET DB_CHAINING ON;
ALTER DATABASE Database2 SET DB_CHAINING ON;
Yes, it is completely supported in SQL Server 2008 irrespective of the instance running on Windows Authentication or Mixed mode Authentication.
Make sure the AD Groups are created as Security Groups rather than Distribution Lists. If not created as Security Groups, then Nested AD Groups will not be working.
When I tried attaching a mdf file using SQL SERVER 2008 R2 I got the following error:
"Attach Database failed for Server "XXXXXX"
Database cannot be opened because it is version 706. This server supports version 661 and earlier
Solution
This is because the database I was trying to attach is SQL Server 2012. The only way to resolve this is to Generate Script from SQL Server 2012 then run it in SQL Server 2008 R2. This needs to be done for data too.