Get All Table Rows and Table Size in SQL Server

SELECT t.NAME AS TableName,s.Name AS SchemaName,p.rows AS RowCounts,SUM(a.total_pages) * 8 AS TotalSpaceKB,SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY RowCounts desc

SQL Server - Difference between clustered and a non-clustered index

What are the difference between clustered and a non-clustered index?

1). A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

2). A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Difference between WHERE clause and HAVING clause

WHERE and HAVING both filters out records based on one or more conditions.

The difference is,

WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example. 

Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80

What is the difference between inner and outer join?.

Inner Join

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Outer Join

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)

Pivot In SQL Query

We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

SELECT *
FROM (
    SELECT
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
        InvoiceAmount as Amount
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

SQL Wildcards

A wildcard character can be used to substitute for any other character(s) in a string.

Using the SQL % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

SELECT * FROM table_Name
WHERE column_Name LIKE 'ber%';

Using the SQL _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by "erlin":

SELECT * FROM table_Name
WHERE column_Name LIKE '_erlin';

Using the SQL [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

SELECT * FROM table_Name
WHERE column_Name LIKE '[bsp]%';


The following SQL statement selects all customers with a City starting with "a", "b", or "c":

SELECT * FROM table_Name
WHERE column_Name LIKE '[a-c]%';

The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

SELECT * FROM table_Name
WHERE column_Name LIKE '[!bsp]%';

What is SSRS and Why SSRS is asked for in many Job Opening?

This example is from the Beginning SSRS by Kathi Kallenberger. Supporting files are available with a free download from the www.Joes2Pros.com web site.

This will be a 5 day blog post in getting started with SSRS. Today will show the importance of SSRS in the business.

Why is SSRS asked for in so many job openings?

If you talk to an SSRS expert it’s very clear to them exactly why companies really need this invention and how it saves time and adds business value. You don’t have to be an SSRS expert to know its value or to start using it. For example you don’t have to be an airline pilot to know the usefulness of modern transportation. Even the people who don’t know how to run SSRS but need the reports can tell you why that is needed. This blog post will go into why SSRS is an important invention by showing how it improves the usage of information in your company.

Before SSRS there has always been a need for a company to benefit from the use of its own information. Excel spreadsheets have been a popular way to do this for a long time. With SSRS you can still use this solution and gain many other options too.

A friend of mine told me a story about doing database work in the 90s for a major company and how he wished SSRS was available back then. The Vice President of the marketing channel would often come to him just before an important meeting with the board of directors. He often needed to show how certain product sales were performing over time. All this information was in the database so it was my friend’s job to get the information out and organized into a medium the VP could use. This medium was usually Excel. The VP often had meetings all over the world where he showcased this Excel report.

The solution to get the VP to him anywhere he was in the world was an Excel file attached to an e-mail. This worked pretty well but with some drawbacks. One time my friend sent the wrong file in the e-mail. A few minutes later my friend realized his mistake and sent another frantic e-mail to VP. This one was saying to ignore the last e-mail and use this newer one. Would the VP see the correct e-mail in time?

If SSRS had been available, my friend could have created a solution that let the VP run the report any time he wished. The report could have been published to the company intranet where the VP could run it from any of the offices he happened to be traveling to that month. There is a fair amount of work up front to develop and publish the report, but once that work is completed, the report can be reused as many times as needed. My friend could even be on vacation for the first day of the monthly and the VP can get his real-time report.

Not only could the report show the most recent data, the VP could choose to view reports of previous months with just a few clicks. The deployed SSRS is user friendly, and can also be configured to protect reports from being run by the wrong people.

Tomorrow’s Post

Tomorrow’s blog post will show how to know if you already have SSRS installed.

If you want to learn SSRS in easy to simple words – I strongly recommend you to get Beginning SSRS book from Joes 2 Pros.

Source

Difference Between INNER JOIN and JOIN

Query using INNER JOIN

SELECT * FROM Table1 INNER JOIN  Table2 ON Table1.Column1 = Table2.Column1

Query using JOIN

SELECT * FROM Table1 JOIN  Table2 ON Table1.Column1 = Table2.Column1

The question is what is the difference between above two syntax.

Here is the answer – They are equal to each other. There is absolutely no difference between them. They are equal in performance as well as implementation. JOIN is actually shorter version of INNER JOIN.

Personally I prefer to write INNER JOIN because it is much cleaner to read and it avoids any confusion if there is related to JOIN. For example if users had written INNER JOIN instead of JOIN there would have been no confusion in mind and hence there was no need to have original question.

Source

Delete duplicate rows in SQL Server and Oracle

 SQL SERVER

DELETE FROM Table1 WHERE ID NOT IN
(  SELECT MAX(ID) FROM Table1
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 )






WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM TableName
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO



AND In ORACLE 
 



DELETE FROM TABLE1
WHERE ROWID NOT IN (SELECT   MIN (ROWID)
FROM TABLE1
GROUP BY COLUMN1, COLUMN2, COLUMN3, COLUMN4);

Count of the number of records in a SQL Table

Name 3 ways to get an accurate count of the number of records in a SQL Table?

SELECT * FROM TableName

SELECT COUNT(*) FROM TableName

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TableName') AND indid < 2