Archive for the ‘ SQL 2008 ’ Category

Book review

I just got my copy of Instant MDX Queries for SQL Server 2012 by Nicholas Emond. I did a book review and its a great way to jump into SQL 2008 on SQL 2012 Mutidimensional

SQL Server 2008 Reporting Services installs with a local certificate but I have been running into the issue where the bindings on the local Reporting Sericves SSL cannot be removed and I get error “SSL binding already exists for the specified IP address and port combination” even after I have deleted the old binding.

I found this blog on Thinknook.com that describe the steps to manually remove. Ignore all the IIS stuff if you are using SQL 2008 and above.

Free Books

Microsoft has released free e-books and its good stuff.

Introducing Windows Server 2012

Introducing Microsoft SQL Server 2012

Introducing Microsoft SQL Server 2008 R2

 

Cool code peice to analyze table size

–Greate script to analyze table space used and what percentage of space the table uses in regards to the whole database

— Create the temp table for further querying
CREATE TABLE #temp(
rec_id  int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size  decimal(15,2))

— Get all tables, names, and sizes
EXEC sp_msforeachtable @command1=”insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ‘?'”,
@command2=”update #temp set table_name = ‘?’ where rec_id = (select max(rec_id) from #temp)”

— Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

— Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100

— Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC

— Comment out the following line if you want to do further querying
DROP TABLE #temp

 

 

 

Deleteing duplicates

In this example we have a table called employes with first name last name

Table

emp_id f_name l_name
1 mary mcluskey
2 mary mcluskey
3 james fredrick
4 ray blume
5 ray blume

First identify the dups

 SELECT f_name,l_name FROM employees
GROUP BY f_name,l_name
HAVING COUNT(*) > 1

Results

f_name l_name
ray blume
mary mcluskey 

 Delete dups

DELETE FROM employees WHERE emp_id IN
(SELECT MAX(emp_id) FROM employees
GROUP BY f_name,l_name
HAVING COUNT(*) > 1)

Results

 emp_i d f_name l_name
1 mary mcluskey
3 james fredrick
4 ray blume

Note you may have to use while exists when you have 3 dups of same as this only deletes max id where a name is repeated