Fatal error during installing SSMS or Vsts

 

       Many people are asking why this " Fatal error during installation (0x80070643)" error is coming while installing SSMS. Many people are struggling on this issue at the time of installing SSMS V16.5.3,and SSMSV18.0. Here I am sharing the tips to easily rectify  fatal error.

Below solutions will be helpful to rectifying fatal error at the time of installation.

1. Please check is there any antivirus are running in your system like Symantec if  yes disable it and retry the installation. In most of the cases antivirus are the culprit.

2. Please check system language or local language settings in control panel, System language and setup language should be same, Means if you are using Us version setup SSMS-Setup-ENU.exe then system language should be English(United state),and SSMS-Setup-FRA.exe for France.

3. Remove every trace of .NET and visual C++ from your system , Uninstall all the trace and retry the installation

4.Install the setup priority order, Install SSMS 2016 version then after try SSMS 2017.

5. Check any higher version of depending packages are installed (like ODBC drivers) if yes uninstall it and retry.

Hope this will help you to fix fatal error.

Reduce NULL value space in SQL server using SPARSE

 

Microsoft introduced a new feature called SPARSE in SQL server 2008.This will help to reduce the space of column having high proportion of NULL values. It will help optimizing the SQL storage usage.

 

Where we can use this SPARSE?

              This can be only used column has a high percentage of NULL value in it. Let’s see an example, if you are storing data in fixed column length like int or bigint, once you save NULL in bigint its consume 8 bytes and most of the data are NULL in that column it should be huge storage space based on its data volume. So this can be resolved by creating SPARSE column.

 

CREATE TABLE Emp_table

( 

       ID int IDENTITY (1,1),

       First_Name VARCHAR(50) NULL,

       Last_Name VARCHAR(50) NULL,

       Emp_tagId BIGINT SPARSE NULL

)ON [PRIMARY]

 

From the above script, Emp_tagId is the SPARSE column created to eliminate storage space used by NULL, It will take zero storage space for NULL values

Where we can’t use SPARSE?

       If your column contain less NULL value or it is saving less than 50% of record NULL then it is not good to have use SPARSE because SPARS column consume extra 4 bytes than declared size,it is storing in special structure. Let assume in above example 70% Emp_tagId consists data and rest of them are non-NULL value then it will take total 12 bytes to save non-NULL values, Means 8 bytes for non-null value and 4 bytes for SPARSE.

What are the basic data types

            The data type defines the operations that can be done on the data, the meaning of the data, and the way values of that type can be stored. Sharing some basic data types and its usages.

1)TINYINT / SMALLINT/ MEDIUMINT/ INT / BIGINT
2) FLOAT / DECIMAL
3) CHAR / VARCHAR / TEXT
4) DATETIME / TIMESTAMP

TINYINT / SMALLINT/ MEDIUMINT/ INT / BIGINT

• each of these handles integers, including negatives
• differ on max values they handle + storage size
• listed here in order of size (TINYINT is smallest)
• generally, use the smallest that gets the job done

FLOAT / DECIMAL

• FLOAT and DECIMAL handle decimals places
• FLOAT is 'approximate'
• DECIMAL is an exact data type
• Use DECIMAL for dollars and cents to get better precision

CHAR / VARCHAR / TEXT

• All of these data types store string values
• CHAR and VARCHAR can handle up to 255 characters
• CHAR(30): stores 30 characters, including trailing spaces
• VARCHAR(30): UP TO 30 characters (no trailing spaces)
• TEXT can handle ~65.5k characters (good for long strings)

DATETIME / TIMESTAMP

• Both handle times, to the second
• DATETIME: YYYY-MM-DD HH:MM:SS
• TIMESTAMP: YYYYMMDDHHMMSS