European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server Hosting - HostForLIFE :: SPARSE Column in SQL Server

clock January 9, 2023 06:58 by author Peter

In this article, we will learn about SPARSE Column in SQL Server. The SPARSE column is a good feature of SQL Server. It helps us to reduce the space requirements for null values. Using a SPARSE column, we may save up to 20 to 40 percent of space.

SPARSE Column in SQL ServerA SPARSE column is a common column with optimized storage for NULL values. It also reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. In other words, a SPARSE column is better at managing NULL and ZERO values in SQL Server. It does not occupy any space in the database. Using a SPARSE column, we may save up to 20 to 40 percent of the area. We can define a column as a SPARSE column using the CREATE TABLE or ALTER TABLE statements.
CREATE TABLE TableName
(
      .....
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
      .....
)


We may also add/change a column from the graphical view.

Example
In this example, I have created two tables with the same number of columns and the same data type, but one table's columns are created as a SPARSE column. Each table contains 500+ rows.
CREATE TABLE TableName
(
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
)

CREATE TABLE TableName1
(
      Col1 INT ,
      Col2 VARCHAR(100) ,
      Col3 DateTime
)


Using the sp_spaceused stored procedure, we can determine the space occupied by the table data.
sp_spaceused 'TableName'
GO
sp_spaceused 'TableName1'

Advantages of a SPARSE column
    A SPARSE column saves database space when there are zero or null values.
    INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
    We can get more benefits from Filtered indexes on a SPARSE column.
    We can use SPARSE columns with change tracking and change data capture.

Limitations of a SPARSE column
    A SPARSE column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
    A SPARSE column cannot be data types like text, ntext, image, timestamp, user-defined data type, geometry, or geography.
    It cannot have a default value and bounded-to rule.
    A SPARSE column cannot be part of a clustered index or a unique primary key index and partition key of a clustered index or heap.
    Merge replication does not support SPARSE columns.
    The SPARSE property of a column is not preserved when the table is copied.

HostForLIFE SQL Server 2019 Hosting
HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server Hosting - HostForLIFE.eu :: Repair SQL Database From Suspect Mode

clock June 14, 2016 20:11 by author Anthony

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

Free ASP.NET Hosting - Europe

  • Data files or log files are corrupt.
  • Database server was shut down improperly
  • Lack of Disk Space
  • SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

  • Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

  • Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

  • Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  • For safety, take the backup of the database.
  • Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
  • There is no way to go back to the previous state of the database.
  • So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

  • Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

  • Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in