Thursday, 29 May 2014

Temp table Limitation


Temp tables are constructs which exist for a limited time in SQL Server, usually the lifetime of a connection. There are a few restrictions for temp tables, like they cannot be partitioned and cannot have FKs. They can have indexes and constraints. 

  • Temporary tables cannot be partitioned
  • Temporary tables cannot reference or be referenced by FOREIGN KEY constraints
  • When a session creates temporary table that contains a named constraint inside a transaction, another sessions cannot create a temporary table of the same name until the first session commits the transaction.


Wednesday, 26 June 2013

Retrieve the files (data file, and log files) name and location path fron sql server backup file

Retrieve the files (data file, and log files) name and location path fron sql server backup file say abc.bak

By using the following sql statement we can retrieve the same :)

RESTORE FILELISTONLY FROM DISK='E:\abc.bak'

Saturday, 22 June 2013

-- Recursive CTE to return the management chain leading all the way up to the CEO for a specified employee

-- Recursive CTE to return the management chain leading all the way up to the CEO for a specified employee

With EmpCTE as(
Select empid,mgrid, firstname, lastname, 0 as distance
from HR.Employees
where empid = 9

Union ALL

Select M.empid,M.mgrid, M.firstname, M.lastname, S.distance+1 as distance
from EmpCTE As S
Inner Join HR.Employees As M ON M.empid = S.mgrid
)
Select * from EmpCTE

Friday, 21 June 2013

NOT IN as same with <> ALL

NOT IN
========
SELECT *
 FROM Emptable
 WHERE (Departmentid NOT IN (SELECT Departmentid
                              FROM Emptable AS Emptable_1
                              WHERE (Empid < 5)
                             )
        )
 GO

<> ALL
======
SELECT *
 FROM Emptable
 WHERE (Departmentid <> ALL (SELECT Departmentid
                              FROM Emptable AS Emptable_1
                              WHERE (Empid < 5)
                             )
        )
 GO

***both query will produce same output.

Thursday, 6 June 2013

Why we prefer COALESCE standard function instead of ISNULL Non-standard function

-- Why we prefer COALESCE standard function instead of ISNULL Non-standard function
-- Execute the  following sql statement and judge the output


DECLARE
    @x AS VARCHAR(3) = NULL,
    @y AS VARCHAR(10) = '1234567890';

SELECT ISNULL(@x,@y) AS [ISNULL], COALESCE(@x,@y) AS [COALESCE]

Wednesday, 5 June 2013

SQL Server db backup related information




SQL Server db backup related information

SQL Server holds all db backup related information into the following tables:-

1.      SELECT * FROM msdb.dbo. backupset
2.      SELECT * FROM msdb.dbo.backupmediafamily

We may use the following query, to find the important backup related information
---- SQL statement start
select  top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
 ,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'Paylite4Alubaf%' -- databasename
order by a.backup_finish_date desc
--- SQL statement end

Thursday, 18 April 2013

SQL Services Reporting Services - SSRS [Pros & Cons]

SQL Services Reporting Services - SSRS
  • Pros
    • Flexibility in defining specific formatting and layout, specific item placement, multiple or composite report items, groups, headers, footers, subtotals
    • Report caching and snapshot for better performance especially if the source data query is complex and takes longer to execute
    • Creating report subscription for automatic report delivery via email or file share mode
    • Can combine multiple data sources (relational and OLAP data) into a single report
    • Additional charting options – map, area, range, scatter, polar, 3D, sparklines, data bars, etc
    • As similar as PPS dashboard, SSRS reports can also be deployed on a SharePoint site for collaboration
  • Cons
    • Harder to create dashboards and therefore often done by IT people
    • No automatic drill-through and drill-across features