Database Reference
In-Depth Information
Within the TSQL statements, we have used the FILESTREAM attribute, which is used to handle
the data. By default, Win32 streaming APIs is preferred to perform actual read and write
operations using the OpenSqlFileStream API process. The FILESYSTEM attribute of the
VARBINARY(MAX) data type is used to store unstructured data on the file system, which is
not readable from the operating system.
On the new collations, the Windows or SQL collation can be explicitly defined during the
CREATE TABLE or ALTER TABLE operation for columns that use VARCHAR , CHAR , NCHAR , and
NVARCHAR data types. The collations handle three settings on SQL Server, which are:
F Code-page used to store non-Unicode character data types
F Sort order for non-Unicode character data types
F Sort order for Unicode data types
The default collation for the SQL Server instance is determined during the installation process
where we can choose the default or select the appropriate settings for the data storage. In a
multi-instance and multi-database environment, when we define different collations within the
same database or across the databases, we may encounter data compatibility issues and the
cross-collation join statement may not always work. If a data transfer process is used, then it
may result in corrupted data.
There's more...
The SQL Server service account must have access to the FILESTREAM share in order to
handle unstructured data storage. If, in case, the share is created after the installation of SQL
Server, then the change can only take place on operating system level and SQL Server level;
we need to restart the SQL Server services.
Designing data partitioning solutions for
tables and indexes
As part of performance improvement techniques, partitioning the table or index may improve
the query performance depending upon the query types and hardware configuration. SQL
Server 2005 introduced the data partitioning solutions and from the beginning of SQL Server
2008, this feature has been enhanced with native table and index partitioning, which provides
better scalability to the data platform.
As the data grows, tables grow larger and similarly, the data access time also tends to
increase. By designing the data partitioning solutions with appropriate hardware, we will
be able to build a scalable and high performance platform. In this recipe, we will look at
designing data partitioning solutions for tables and indexes using SQL Server 2008 R2.
This solution will essentially allow us to manage large tables and indexes at a lower level of
granularity. The partitioning solution is differentiated as horizontal and vertical.
 
Search WWH ::




Custom Search