Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Logging Tables

This editorial was originally published on 25 Sept, 2015. It is being re-published as Steve is at the PASS Summit.

I was chatting in one of our DLM webinars and someone asked a question about logging data. I mentioned a few things, but this individual followed up and wanted more information. Specifically, how do you build a logging table?

It's an open ended question, but it's perfect for a poll. How do you construct a table that you use for capturing lots of information that you may or may not query later, but you might only query in a very ad hoc fashion when something has gone wrong. Usually that's the only time I look through logs, when I'm investigating some type of issue. With that in mind, this week the question is:

How do you design a logging table?

I'm looking for two things here: the DDL and the rational for the design. What is good about this design? How does this particular structure (with indexing) allow me to find answers to the questions I might need to ask.

I realize this is very open ended, but the concept of logging is also open-ended, used different ways for different purposes. If you have problems with logging structures, let us know what design techniques to avoid as well.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents

Managing a Recursive Group on SSRS Reporting Services Reports

Mohamed I. from SQLServerCentral

Learn how to implement a report that recursively walks a hierarchy in a table.

Using Five Types of JOIN in MYSQL

Additional Articles from Database Journal

Learn how to use the different types of joins available in a MySQL database.

Set yourself up for success in 2020

Additional Articles from Redgate

Is DevOps central to your 2020 IT Digital transformation? How does your IT strategy underpin the business objectives? Read our latest blog to ensure you’re ready for success next year.

From the SQL Server Central Blogs - Event-driven architecture (EDA) with Azure Data Factory - Triggers made easy

Rayis Imayev from Data Adventures

(2019-Oct-27) Creation or deletion files in your Azure Storage account may initiate data ingestion process and will support your event-driven data platform architecture. Image by Lars_Nissen_Photoart from PixabayMicrosoft recently introduced an...

From the SQL Server Central Blogs - Every Execution Plan Is An Estimated Plan

Grant Fritchey from The Scary DBA

I consider myself to be the most responsible for making such a huge deal about the differences between what is labeled as an Estimated Plan and an Actual Plan....

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The Computed Column Results

I have this computed column definition: 
ALTER TABLE dbo.Activities
ADD ProdOneCount AS CASE
                        WHEN ProductID = 1 THEN
                            CallCount
                    END;
GO
I then have added this row to my table: 
INSERT dbo.Activities
(
    UserID,
    TimeID,
    CallCount,
    ActivityTypeID,
    ProductID
)
VALUES
(
    N'12', 20, 30, 30,30
)
GO
That's the only row for a ProductID <> 1 in the table. When I issue this query, what happens? 
SELECT top 10
 *
 FROM dbo.Activities AS a
 WHERE a.ProductID > 1

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

The Top Ten Percent

What does this query do in SQL Server 2017 on a table with 500 rows?

SELECT
          TOP (10) PERCENT
          cs.SalesAmount,
          cs.CalendarYear,
          cs.Employee
 FROM     dbo.CalendarSales AS cs
 ORDER BY NEWID();

Answer: Returns a random 50 rows from the table

Explanation: The TOP (n) PERCENT is a valid clause. This would return 10 percent of the rows. In this case, with an ORDER BY NEWID(), a random 10 percent are returned. Ref:

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration
Maintenance Plans, Agent Jobs, and logging - Open an agent job that's been created by your maintenance plan Open the step Click Advanced Checkmark "Include step output in history" Click OK until you've closed out of the job Open the maintenance plan. Do anything or nothing at all and click save Go back to the job and look at the checkbox Does […]
SSAS remote connectivity issue - Hi , I installed SQL 2017 Deloper edition in my server. Installed with ABCDomian\Administrator. I am able to connect SSAS with windows credentials. Requirement is one user wants to connect SSAS remotely from his SQL management studio..He signin his PC with ABCDomain\xyz username. using his windows authentication not able to connect ssis. I noticed there […]
SSIS Connectivity issue - Hi, I installed SQL 2017 Developer edition. trying to connect all installed SSAS,SSRS,SSIS & DB engine. Iam able to connect all services except SSIS. When i checked the services in sql configuration manager all services are running. in services.msc i see SQL Server Integration services 14.0 also and it is running.. I installed SQL Server […]
SQL Server 2017 - Development
Try/Catch Not Working - I have a log table that gets an insert right at the beginning in order to show the process started. Then it should do the merge/insert and either mark it as failed or succeeded. The initial 'in progress' insert is not happening and the failure is not being logged either. I have purposely tried inserting […]
Convert European format values to US Format - Hi: Is there a function or some code to convert a number that's European (4.100,00 - dots in place of comma for thousands and comma for decimal point) into US (4,100.00)? -278.999 should be -278,999 621.562,61 should be 621,562.61   Any help is appreciated.   Thanks !
SQL Server 2016 - Administration
Cannot connect to DB after Reboot but can after SQL Server engine restart - After a reboot of the computer, we see in the SQL Error Log problems to connect to one of the database STORESQL.  Our software using this database cannot connect obviously.  But after a simple restart of the SQL engine, the connection to the database is possible.    Do you have an idea why?   The logs […]
Add multiple databases to availability group - Hello together, We have about 50 databases that are not in full recovery mode and not in always on included. Do you know what the fastest way is to add these databases into an Availability group and also Change to full recovery mode without SQL Mgmt Studio Wizard. Maybe someone has a script suggestion for […]
Enable "force encryption" on SQL server - My production DB server is running without SSL since day one (force encryption is off at configuration manager) Someone is asking to use SSL connection from application server to DB server. If i issue a certificate and enable force encryption, should all existing clients be changed to encrypted connections too? in this case, we have […]
SQL Server 2016 - Development and T-SQL
How to get previous value(last matched records results) in SQL query result - Hi Every one, I need your help to achieve results. Can you please help me any one? My requirement is get last matched records results for unmatched records. I mean when matching the Name column with "ABC" then display corresponding records results value(988777) in expected result. else display previous result value.  llly, when matching name […]
Administration - SQL Server 2014
Why Index column is NULL for DMV - sys.dm_db_index_physical_stats - I am running the DMV (sys.dm_db_index_physical_stats) to get the fragmentation for a database, however when I get the results the Index column is NULL for 3 tables even though the table in question has 2 x indexes on it and the avg_frag percentage is >80%.  Why is this?  I've tried DBCC INDEXDEFRAG for these 3 […]
Development - SQL Server 2014
Report runs forever.` - Not sure why this report runs continously... Select distinct H.Customs_Entry_Num as [Entry Num] ,H.Entry_Summary_Date ,L.Part_Num ,L.HTS_Num ,l.HTS_Value ,L.Line_Item_Duty AS Duty ,z.HTS_NUMBER ,z. PTNR_ID ,Z.ELIGIBILITY ,z.Created_Date ,z.COMPOSITE_PART FROM ADHOC.ATS_ESH H INNER JOIN adhoc.ATS_ESL L ON h.TRANS_SK = l.TRANS_SK LEFT JOIN [TSI].[ZATS_BROKER_FEED] Z ON L.Part_Num = Z.COMPOSITE_PART AND Z.CREATED_DATE = ( SELECT max(Z.CREATED_DATE) FROM [TSI].[ZATS_BROKER_FEED] Z WHERE […]
SQL Server 15 - Administration
Polybase on windows auth - Hello, im trying to config polybase to take data from my other sql and struggling a bit here. Does anyone finished such task successfully? The thing is to use windows authentication here. So i already have database master key. With that im going to create database scoped credentials: CREATE CREDENTIAL [AppCredWind] WITH IDENTITY = N'domain\user', […]
SQL Server 2008 - General
passing parameter to stored procedure in bcp command - Hi, I have to take output of stp using bcp command. the stp contains one parameter. I want to know how to pass parameter to stp and get the output. the batch contains below bcp command set DBName=DBLive set DBServerName=ServerLive set FileName=ClosePrice_N.csv bcp "EXEC "%DBName%..stp " " queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w How […]
SQL Azure - Administration
OPENJSON support for SQL Azure Encryption - Does OPENJSON supported for a Azure SQL DB encrypted with AE. especially for insert, update and delete.
Reporting Services
Maps - What is the best way to add location maps to SSRS - Hello, If I wanted to add a map of our employees and their work sites to an SSRS report. What is the best way to go about this. What is the best way to integrate maps? Thank you,
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -