SQL Server: Remove SQL Express from Server Name

We will learn how we can convert named instances to default instances. We will connect the server without writing SQL Express. 

Step 1: Open SQL Server Configuration Manager. Search in the laptop for SQL Server Configuration and select as shown in the snapshot below.

Step 2: Click SQL Server Network Configuration and Click Protocols for INSTANCENAME (i.e SQLEXPRESS)

Step 3: Right Click TCP/IP and click Enabled

Step 4: Right-click TCP/IP and go to Properties, Click on  the IP Addresses tab and scroll down to the IP All section

Step 5: Clear the field TCP Dynamic Ports (i.e. empty/blank) and set TCP Port to 1433. Press Ok

Step 6: Go to SQL Server Services and Right-click your SQL Server (INSTANCE NAME) and click Restart 

This will make the named instance listen on the default port. Now, you no need to write SQL Express in the Server Name.

Check SQL Database size

SQL Query to calculate database size

Below is the query to check the disk space.

SELECT      sys.databases.name as DBName,
            CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Disk Size]
FROM        sys.databases 
JOIN        sys.master_files
ON          sys.databases.database_id=sys.master_files.database_id
GROUP BY    sys.databases.name
ORDER BY    sys.databases.name

How to check log size and clear log

DBCC SQLPERF(logspace)

DBCC LOGINFO

Below is the query to shrink the log. The first parameter is the log name. you can get the name by pressing the right click on the database and click Properties and then select Files Tab. The second parameter is database size in MB

DBCC SHRINKFILE (ReportServer_log, 8);

Change Rows to Columns - Pivot in SQL Server

PIVOT

In this article, we will discuss PIVOT keyword in Sql Server. Pivot is responsible to  convert Rows to Columns in Sql Server.

Let's take the example.

Step 1: Declare SQL table and add some dummy data

DECLARE @ScoreTable TABLE
(
MatchType VARCHAR(50),
Year INT,
Score  INT
)

INSERT INTO @ScoreTable VALUES('OneDay',2012,1000)
INSERT INTO @ScoreTable VALUES('Test',2012,2200)
INSERT INTO @ScoreTable VALUES('OneDay',2013,800)
INSERT INTO @ScoreTable VALUES('Test',2013,370)
INSERT INTO @ScoreTable VALUES('Test',2014,1100)


Step 2: Run the Select query and see the default data

This is not mandatory step. However, just check you dummy data.

SELECT * FROM @ScoreTable

In above query, you can see we have 3 columns. MatchType, Year and Score.
We will change Year Rows data in Columns.


Step 3: Write query to covert rows to columns


SELECT * FROM @ScoreTable
PIVOT(SUM(Score)
FOR Year IN ([2012], [2013],[2014])) AS PVTTable




In above snapshot, you can see query changed year to columns and score under each column.


This is all about the article. I hope you like it.