Browsing Category
SQL and SQL Server database topics. Includes design, issue resolution and tips.
We are all familiar with seeing our data in a vertical format from SSMS. Here is a sample:
SELECT * FROM #EmployeeJobs Employee_ID Job_id 100 500 100 501 100 502 100 503 100 504 191 501 191 502 191 503 191 505 188 503 188 504 |
But what if you, or your customer want’s the data horizontally? That is easy enough with a CTE. Continue reading “Display results Horizontally in SQL Server”
I recently had to use back end data from SQL Server to deliver several reports in excel. All of my SQL code gave me results in seconds; however, the users had distinct formatting requests. I would have to use a few excel tricks to get things just right in each report.
Continue reading “Working with Seconds in Excel”
I ran into a case when I needed to display some results in the HH:MM:SS format. We all know and love DATEDIFF, which gives you whole number results. Using DATEDIFF, I was getting results like this:
-- Declare some variables for the requested timeframe DECLARE @START_DATE DATETIME DECLARE @END_DATE DATETIME-- Set the timeframe variables SET @START_DATE = '2011-01-01 16:00:00' SET @END_DATE = '2011-01-01 22:47:22' SELECT DATEDIFF(second,@Start_date, @END_DATE) AS Segundos |
Segundos 24442 (1 row(s) affected) |
Continue reading “Using datediff to display results as HH:MM:SS”
If you’re like me, you like using descriptive variable, procedure and function names. Along with those, descriptive transaction names are great, so you know exactly what type of havoc you were throwing at SQL Server. But, if you’re too descriptive, … Continue reading Long TRANSACTION Identifier is Long
Rudy Rodarte is thinking about taking an MCM immersion class given by the good folks over at http://www.sqlskills.com. Continue reading Rudy: An MCITP Today. Rudy: An MCM Tomorrow?
Reset and verify IDENTITY values in SQL Server using DBCC. Continue reading Reset an Identity Column in SQL Server
Everyone is worried about their database size. Even though storage is relatively cheap, if your database is growing linearly or exponentially, you’re going to run into issues down the road. One quick way to check things out is to look at your table sizes. I looked all over the web and found Mitch Sellers’ blog with several great SQL Table Size scripts. Here are 2 of the good ones from the comments: EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'” This method is quick, but not very clean. You’ll get a separate result for each table, so you really cannot sort. Plus, I … Continue reading Determine Tables Sizes in SQL Server
