Check the size of database files in SQL Server

How can we check the size of particular database files like .ldf and .mdf?

We can check the size which is occupied by the database files using the sys.database_files view.

To check the size of database files .ldf and .mdf use the below query:

use databasename

GO

SELECT

       Name AS DatabaseName

      ,Physical_name AS DBPath

      ,(size * 8)/1024 AS SizeMB

FROM sys.database_files

Output:

Here it shows the Database file name with location of file and size in MB unit.

Default character limit for VARCHAR DataType

We use many time even mostly VARCHAR DataType in SQL Server. But some time we get issue or confusion with the default limit or character size of VARCHAR datatype, and that comes to the logical error with your queries or in your compile code (Stored Procedure) when execute it and gives the wrong output.

We will see how it works when we do not define size with VARCHAR datatype and give us the unexpected output.

See the below two scenarios how VARCHAR datatype works with query:

Scenario 1:

1. VARCHAR datatype With Variable

--Varchar used with variable

DECLARE @a VARCHAR

SET @a = 'Paresh'

SELECT @a AS Value

Output:

Now here in this scenario we can see it gives output as only 1 character because when we use VARCHAR DataType with variable and not define any size it takes only single character size default.

Scenario 2:

2. VARCHAR datatype with Convert and Cast function

--Varchar used with CONVERT or CAST Fucntion

SELECT CONVERT(VARCHAR,'aaaaa bbbbbb cccccc dddddd eeeee ffffff ggggg hhhhhh') AS Value

SELECT CAST('aaaaa bbbbbb cccccc dddddd eeeee ffffff ggggg hhhhhh' AS VARCHAR) AS Value

Output:

Here we can see that when VARCHAR DataType use with Convert or Cast function then it takes the 30 character as default size for it and give the output with only 30 characters.

  

So here we need to keep in mind that when we use VARCHAR Datatype then MUST give the size whatever it requires as per the use.

DROP multiple tables using single DROP statement

We know that how to drop a table. But we can also drop multiple tables using only single drop statement. How? Let see using the below example:

Just create multiple tables like:

CREATE TABLE t1(ID1 INT)

CREATE TABLE t2(ID2 INT)

CREATE TABLE t3(ID3 INT)

Try to select the table data to confirm that tables are created successfully by:

SELECT * FROM t1

SELECT * FROM t2

SELECT * FROM t3

Tables are created, Now we are going to drop those tables using single drop statement as below:

DROP TABLE t1,t2,t3

That’s work perfectly..

Insert default value for newly adding column in existing records in table in SQL Server.

Here we see how we insert default values in newly added column in table which already has some records in it.

Sometime we are in situation that we need to add new column and we need to assign default property to that column. If the table in which we add new column is empty then we don’t have an issue to set default values but if that table already having some records and we are adding new column with default value then either we need to be empty the table or update the records with default values.

Now in this case if there is lots of record in table then it is not easy to do this. But we have one simple way to come out of this very easily.

Simply create one table with two columns, insert some records into it:

CREATE TABLE TestTable

(

      ID INT,

      Name VARCHAR(10)

)

INSERT INTO TestTable

VALUES(1,'PP'),(2,'AA')

SELECT * FROM TestTable

Output:

ID	Name
1	PP
2	AA

Now we are adding new column with default values in existing records using:

ALTER TABLE TestTable

ADD newColumn BIT DEFAULT(0) WITH VALUES

SELECT * FROM TestTable

Output:

ID	Name	newColumn
1	PP	0
2	AA	0

Display count for number of column in table with non zero values for each rows

declare @t table (ID INT IDENTITY(1,1),a int,b int,c int,d int)

insert into @t
values(1,0,0,0),(0,1,0,0),(1,2,0,0),(1,2,3,4),(1,2,3,0)

;with cte as
(
select * from @t
unpivot
(colvalue for colname in(a,b,c,d)) unpvt
)

select t.*,cnt from @t t
INNER JOIN (
select ID,COUNT(*) as cnt
from cte
WHERE colvalue > 0
GROUP BY ID
) x ON x.ID = t.ID

Output :

Remove non alphabetic character from string in SQL Server

Query to remove non alphabetic character from string in sql server:


DECLARE @str varchar(100) = 'Paresh@#$Patel#$!@#888'

SELECT CAST(
  (SELECT CASE
           WHEN SUBSTRING(@str, n, 1) LIKE '[A-Z]'
           THEN SUBSTRING(@str, n, 1)
           ELSE ''
          END
  FROM (SELECT number
        FROM master..spt_values
        WHERE type = 'P'
          AND number BETWEEN 1 AND 50) AS Number(n)
  WHERE n <= LEN(@str)
  FOR XML PATH('')) AS NVARCHAR(100))

Run this query you will get the output like :

PareshPatel

Find sequential discharge date from multiple discharge date

Create one table variable and insert the data as need :


DECLARE @table table(patID INT,AdmDate Datetime,DisDate Datetime)

INSERT INTO @table
values(1,'2012-01-10','2012-01-15'),
(1,'2012-01-16','2012-01-20'),
(1,'2012-01-21','2012-01-25'),
(2,'2012-01-10','2012-01-15'),
(2,'2012-01-17','2012-01-20')

After inserting data here is the query to find the sequential discharge date.


;WITH cte (PatientId, AdmDt, Disdt, AdmDt2, Rnk) AS
(
SELECT t1.PatientID, t1.AdmissionDate, t1.DischargeDate, t2.AdmissionDate, ROW_NUMBER() OVER(PARTITION BY t1.PatientId ORDER BY t1.PatientId, t1.AdmissionDate, t1.DischargeDate) AS Rnk
FROM @table t1
LEFT JOIN @table t2 ON (t1.PatientID = t2.PatientID AND DATEADD(DAY, 1, t1.DischargeDate) = t2.AdmissionDate)
),
cte1 (PatientId, AdmDt, DisDt, NextRnk) AS
(
SELECT D1.PatientId, D1.AdmDt, D1.Disdt,
CASE WHEN D1.AdmDt2 IS NULL THEN D1.Rnk ELSE  MIN(D2.Rnk) End AS NextRnk
FROM cte D1
LEFT JOIN cte D2 ON (D1.PatientId = D2.PatientId AND D2.Rnk > D1.Rnk AND D2.AdmDt2 IS NULL)
GROUP BY D1.PatientId, D1.AdmDt, D1.Disdt,D1.AdmDt2, D1.Rnk
)
SELECT D1.PatientId, MIN(D1.AdmDt) AS AdminssionDate, MAX(D1.DisDt) AS DischargeDate
FROM cte1 D1
GROUP BY D1.PatientId, D1.NextRnk
ORDER BY D1.PatientId, D1.NextRnk

This will gives the output as :