How to find the Disk Space Used by SQL tables ?

In SQL server management studio we have the option to view the Disk space used by the tables.

Select the Database --> Go to Reports --> Select Standard Reports --> Disk Usage by Top Tables as shown below.

Once you selected the above option the following report will appear, there you can see the total no of records and its disk space used by the tables

How to create a Maintenance Plan in SQL server ?

Following are the steps to create the maintenance plan in SQL server.

Step 1

Go To Management -- > Maintenance Plan --> Right Click -->Select New Maintenance Plan as shown below.

Step 2

Provide the Name of the maintenance plan as given below.

Step 3

The toolbox  will appear automatically and from the toolbox select the Backup Database Task as shown below.

Step 4

After selecting the Task, just drag and drop it as shown below.

Step 5

The following backup database task window will appear once you double clicked the back up task.

Step 6

You can choose the required database and you can ignore the database if the database is offline by choosing the below checkbox as shown below.

Step 7

You can choose the path for the backup file to store and choose the back compression as well.

Step 8

You can generate the T-SQL script using the following T-SQL option as shown below.

Step 9

You can schedule the job by clicking the following button as shown below.

Step 10

You get to see the Job schedule properties and here you can give the job name for easy identification and select the schedule type as well.

Enabled option to be checked always.

Step 11

You can give the occurrence of the job such as Daily,Weekly and Monthly as shown below.

Choose the occurrence time and duration as well.

Step 12

Once you done with your changes click OK button.

Step 13

Save the maintenance job as shown below.

Step 14

You get to see the created plan under the management option and also the job under the jobs folder as shown below.

.

Step 15

You can start the job by selecting the Start Job at Step as shown below.

Finally we have created the Maintenance Plan successfully.

NULLIF function in SQL server ?

NULLIF function in SQL server is used to check the two given expression are equal.

Syntax

NULLIF (Expression,Expression)

Example 

In above example the function check for the both expression value, it returns the null value if the both expression are equal else it will return the first expression value.

This function is equal to case function in SQL server.

How to get the Host-Name in SQL server ?

You can get the Host Name in SQL server using the following function.

Syntax

HOST_NAME()

Example

Select Host_Name()

This will be very helpful if you want to trace the query using SQL server profiler.

How to import data from Excel Using SSMS ?

Following are the steps to import data from Excel to SQL server.

 Right Click the database --> Select Tasks --> Import Data as shown below.

  

 Click Next button.

Choose the data-source as Microsoft Excel and select the excel path which you want to import.

If the first row has column names then select the option as shown below.

Choose the destination source as SQL server native client and choose the authentication based on your configuration.

If you want to copy entire columns and its data you can choose the first option.

You can choose the second option if you want to transfer the data based on query.

You can choose the sheet from the excel source and give the destination table name in the destination as shown below.

You can also change the destination table data type by choosing the edit mapping option.

Once done click next option.

Click finish button to import the data.

As you can see the data has been imported successfully and you can see the no of rows transferred in the below image.

 After importing the data you can check the destination table name by running the following query as shown below.

The excel data has been imported successfully.

How to generate scripts in SQL server ?

Following are the steps to generate the scripts in SQL server.

Step 1 

Right Click the database -- > Select Tasks -->Generate Scripts as shown below.

 Step 2 

Click Next button

Step 3

You can choose any database from the following wizard and to generate scripts for all database objects choose the option "Script all objects in the selected database".

Step 4

Here is the customization wizard to generate the scripts based on our requirement.

You can generate the data by changing the script data option as shown below.

Step 5

Here you can choose the objects which you want to generate scripts by choosing the below option.

Step 6

I have selected only the tables for demo purpose and in the following wizard i have selected two tables as shown below.

Step 7

The following wizard is the output option for script file.

You can save the scripts to new file or in a new query window as shown below.

Step 8

Finally the following wizard is the summary wizard to view the selected options.

Step 9

After clicking finish button you can see the script has been generated in the new query window as shown below.

Error Code : 18456 Login Failed for User in SQL server ?

The following error will occur while connecting the SQL server using windows authentication.

The root cause for above error is the user account has been disabled.

We need to enable the user account by login in to system administrator (Sa) account as shown below.

Go to Security --> Logins --> Select the user account ,right click the account and select properties.

The Login properties will be displayed as shown below.

In the above properties the permission is denied and login is disabled.

Grant the permission and enable the login as shown below.

Once done click OK button and you can login with the user account.

ROWCOUNT_BIG function in SQL server ?

ROWCOUNT_BIG() function is used to return the no of rows affected by the last statement.

This function operates like @@ROWCOUNT function in SQL server.

@@ROWCOUNT  Returns INT datatype but ROWCOUNT_BIG returns BIGINT.

Syntax

ROWCOUNT_BIG ()

This function returns the no of affected by the last statement such as select, insert, update and delete statements.

Failed to Update Database , because the database is Read-Only.

The update statement will be failed due to the Read only mode of the database.

To resolve the error please follow the below steps.

Change the Database Read only to Normal Mode.

Please Refer the replication of the error in the below image.

You can see the database color is changed to grey color and in bracket it is marked as Read only.

Change the database mode using below T-SQL statement as shown below.

Once executed successfully yon can refresh the database and found the database is coming back to normal mode as shown below.

After changed to Read Write mode, you can execute the update statement and it will be updated successfully as shown below.

Steps to change the Mode of the database to read only

Execute the below T-SQL statement.

You can noticed that the database mode has been changed to Read-Only mode as shown below.

How to resolve the connection error in SQL server ?

"Network related or instance specific error occurred while establishing a connection to the SQL server".

I believe that most of the developers faced the following issue while connecting the SQL server.

To resolve the above error please follow the below steps.

Go to SQL server configuration manager using Start -- > SQL server configuration Manager.

After opening the Configuration manager, select the SQL server instance as shown below.

You can see that the SQL service has been stopped and this is the root cause for the error.Right click the instance and select Start option.

Now the SQL service is preparing to start as shown below.

Once started you can see that the SQL service is running now.

Now if you try to connect the SQL server instance the server instance will be connected successfully as shown below.

The permanent fix for the above error is to set the startup type of the SQL service instance as Automatic.So that the service will be started automatically once you logged in the system.