TEMPDB Round Robin is not working!! It is filled up I have a 16 Core CPU processor and I have created 8 tempdb datafiles with initial size as 2,000 MB with 10% growth restricted to 10,000 MB. The log file is unrestricted and it is in simple recovery mode.

The tempdb is filling up the space, it is not going through the round robin face. Could you please help me here.

You likely need to implement Trace Flag 1117 to enable proportional fill (round-robin) growth on your server.  Be aware that TF 1117 affects all databases, not just tempdb.
From technet.com:

Trace flag 1117 (-T1117) can help keep  data files the same size in case DB needs to autogrow. Say you have eight DB data files with an initial size of 1000MB and autogrow of 200MB each. If the t database needs to autogrow, the default behavior is for one of these eight files to grow by the specified increment (200MB in this case). This will happen when all of the files are full, and after one file grows, it will be about 83% full (1000MB allocated from 1200MB total). The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space, so new extents will be written to the one file that just grew. This defeats the benefit obtained by striping tempdb IO activity (especially extent allocations and de-allocations) across all of the available files.
When -T1117 is enabled, it will cause all the files in a filegroup to autogrow together by their specified autogrow increment. In the DB autogrow scenario described above, instead of one file growing by 200MB, all eight files will grow by 200MB when an autogrow event occurs. This means that all the files will have about the same percent of free space after autogrow, and the benefits of evenly distributing IO will be preserved.
It’s important to understand that –T1117 applies to the entire SQL Server instance, not just to one DB, and it affects all files in the same filegroup in a database. This means that if there are other databases that have multiple files defined for the same filegroup, and an autogrow event occurs in that filegroup, all of the files in the filegroup will autogrow at the same time (by the autogrow increment specified for each file). If there are multiple filegroups in the same database, an autogrow event that occurs in one filegroup will not affect the other filegroups. For a filegroup allocated in a single file, -T1117 has no effect.
When deciding whether to enable –T1117, consider the instance-wide implications and how it may affect other databases that have multiple files for the same filegroup. To avoid blasting your harddrives it is recommended to configure the maximum size of your database files. Always the best option is to presize your files to the assumed maximum.

You may also need to implement trace flag 1118 if you are experiencing tempdb contention.  See this question for more info on that.


SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors In our test lab I've been experimenting with different jobs to keep our critical indexes from becoming too fragmented.

I'm currently using the approach described here: sys.dm_db_index_physical_stats (under the Examples -> D section: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes).

Basically every hour I query the dm_db_index_physical_stats dynamic management view and if an index is between 5% and 30% fragmented I reorganize it, if it's greater than 30% fragmented I rebuild it. It seems to work fine during most of our testing, however, twice I've run into a problem where the scheduled job fails with an error:

  The operating system returned error 23(Data error (cyclic redundancy
  check).) to SQL Server during a read at offset 0x00000eae3b2000 in
  file 'C:\Program Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\database.mdf'.
  Additional messages in the SQL Server error log and system event log may provide more detail.
  This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. [SQLSTATE HY000] (Error 823)

When I run DBCC CHECKDB a problem is reported in one of my indexes, the only way I'm able to fix this problem is by using

DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS)

I'm not positive, but I suspect this error is caused by rebuilding or reorganizing indexes while my load tests are running in the test lab.

I've searched around and found nobody else reporting this consistency error related to rebuilding indexes. You can see more information about my problem on my blog post: SQL Server Index Corruption: CRC Error & DBCC CHECKDB

Is my approach to tuning indexes flawed? Should I not be trying to rebuild indexes on a "live" database (while traffic is hitting it)? Should I be using SQL Server Enterprise Edition's feature of rebuilding an index with (online=on)? Any help is appreciated.

I'm running SQL Server 2008 R2 Standard.

The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read

This indicates the operating system detected an issue reading data from the hard disk itself.  When data is written onto the filesystem, the operating system calculates a CRC code for each block written; when that data is subsequently read back the operating system performs the CRC operation again on the read data and compares it with the CRC stored in the filesystem.  If these two CRC codes don't match the OS reports error 23.  I'd be very surprised if there isn't a hardware issue with either the drive itself, or perhaps some other component such as the motherboard or drive controller.

WITH (ONLINE=ON) in the Enterprise Edition of SQL Server will have no effect whatsoever on this issue.  Online index operations vs offline index operations are no different at the operating system layer; data is read and written as necessary to either rebuild or recreate the index.


SQL Azure dm_exec_query_stats clearing? I'm doing a lot of work with SQL Azure at the moment, trying to improve performance of a web app.  I am using the dm_exec_query_stats view to look for poor performing queries.

Does anyone know a way to clear the query stats in Azure?  DBCC FREEPROCCACHE isn't supported.  I can use sp_recompile 'procedure' to clear stored procedures/functions etc, but the site uses some LINQ-generated queries which can't be cleared in this manner.

Old question I know, but if anyone is still looking for one potential workaround...

I wrote a small utility (shared on my blog) that makes life easier when working with this DMV.  This utility takes a baseline of the query stats to act as an effective zero.

The utility then queries the DMV repeatedly and works out the changes to show what has executed over the time since the last call into the DMV.

For more details, see:
http://cbailiss.wordpress.com/2014/10/01/free-tool-sql-workload-profiler-for-on-premise-and-azure-sql-db/


Trying to get BCNF out of this relation The table is:

Employee (
    EID,
    Ename,
    PID,
    ProjectName,
    MID,
    ManagerName )

For the functional dependencies I got:

EID==>(Ename)
PID==>(ProjectName)
MID==>(ManagerName)

and for BCNF tables I got:

Employee(EID,Ename)
Project(PID, ProjectName)
Manager(MID, ManagerName)

Is this done right?

Yes, it is.

Moreover, a correct table composed by two attributes is always in BCNF.

In fact, if we have R(A,B):

Proof

If A->B but B doesn't imply A, A is key => R(A,B) is in BCNF
If B->A but A doesn't imply B, B is key => R(A,B) is in BCNF
If B->A and A->B, keys: {A}, {B}; => R(A,B) is in BCNF


Select next and previous rows I have the following table:

CREATE TABLE post (
  id            bigint primary key,
  thread_id     bigint,
  is_notice     boolean,
  title         text,
  content       text
)

I display the list using the following query:

SELECT * FROM post ORDER BY is_notice desc, thread_id desc, id

Then, given the post selected by id(i.e. SELECT * FROM post where id=3), how do I retrieve the next and previous posts?

Using PostgreSQL's Window Functions, specifically LAG and LEAD, should be able to show you the previous and next entries in your table.

select *
from (
    select  id, thread_id, is_notice, title, content,
            lag(id) over (order by is_notice desc, thread_id desc, id asc) as prev,
            lead(id) over (order by is_notice desc, thread_id desc, id asc) as next
    from post
    ) x
where 3 IN (id, prev, next);

A demo can be found here:
http://sqlfiddle.com/#!15/9fd7a/8


Delete Joined to Derived Table Causes Index Scan and Table Spool Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates.

It seems that the query pattern produced by the bulk delete API method from this library is not optimized well by the SQL Server query optimizer.

I am using SQL Server 2014.  Consider the following table and index (names have been changed to protect the innocent):

CREATE TABLE [dbo].[a_staging_table]
(
    [staging_ky] [BIGINT] IDENTITY(1,1) NOT NULL,
    [col_a] [INT] NOT NULL,
    [col_b] [VARCHAR](15) NOT NULL,
    [col_c] [VARCHAR](256) NOT NULL,
    [col_d] [INT] NULL,
    [col_e] [INT] NOT NULL,
    [col_f] [VARCHAR](3) NOT NULL,
    [col_g] [VARCHAR](3) NULL,
    [col_h] [VARCHAR](20) NULL,
    [col_i] [CHAR](4) NOT NULL,
    [col_j] [INT] NOT NULL,
    [col_k] [VARCHAR](10) NULL,
    [col_l] [DATETIME] NOT NULL,
    [col_m] [VARCHAR](25) NOT NULL,
    [col_n] [DATETIME] NOT NULL,
    [col_o] [VARCHAR](25) NOT NULL,
    [col_p] [BIGINT] NOT NULL,
    [col_q] [BIT] NOT NULL,
    [col_r] [DATETIME] NULL,
    [col_s] [VARCHAR](25) NULL,
PRIMARY KEY CLUSTERED ([staging_ky] ASC)
);
GO

CREATE NONCLUSTERED INDEX [IDX_dbo_a_staging_table_col_p]
    ON [dbo].[a_staging_table]([col_p] ASC)
    WITH (FILLFACTOR = 100);
GO

The application is trying to delete all records from the table where col_p is equal to a certain value, and the generated SQL query from Entity Framework looks like this:

DELETE [dbo].[a_staging_table]
FROM [dbo].[a_staging_table] AS j0 INNER JOIN (
SELECT
    1 AS [C1],
    [Extent1].[staging_ky] AS [staging_ky]
    FROM [dbo].[a_staging_table] AS [Extent1]
    WHERE [Extent1].[col_p] = @p__linq__0
) AS j1 ON (j0.[staging_ky] = j1.[staging_ky])

Looking at the query plan, SQL Server does not optimize away the join between the table and itself, and instead, the plan does an index scan with a nested loops join and seek to the same index, followed by a table spool, prior to the delete.

A more traditional and optimal delete statement is something like this statement, which just does an index seek, prior to the clustered index delete:

DELETE [dbo].[a_staging_table]
FROM [dbo].[a_staging_table] AS j0
WHERE [j0].[col_p] = @p__linq__0

The actual query plans for both statements are posted at:https://www.brentozar.com/pastetheplan/?id=SygZoD6um

Is this query pattern generated by this library a known query anti-pattern for a delete statement in SQL Server?

Is this query pattern generated by this library a known query anti-pattern for a delete statement in SQL Server?

This strikes me as a slightly curious question to ask. Why does the answer to that question matter? What would you do with that information? If the library that you're using doesn't meet your business needs, then four options come to mind:

Use a different library.
Implement a workaround, for example creating a stored procedure to do the deletes with more efficient code.
Providing feedback to the author of the library.
Opening a UserVoice feedback item or support ticket with Microsoft to get better optimizer support for this type of query.

With all of that said, I think that most SQL Server people would view that query with suspicion. I also think that it's fair to say that joining to tables that you don't need is an anti-pattern.

All of the relational databases that I've worked with have limitations around the inferences that they can make for eliminating unnecessary joins. One of the reasons that you get that ugly plan in SQL Server is that there isn't optimizer support for eliminating an inner self-join on a table's primary key. This can be observed with the following simple demo:

CREATE TABLE [dbo].[a_staging_table]
(
    [staging_ky] [BIGINT] IDENTITY(1,1) NOT NULL,
    [col_p] [BIGINT] NOT NULL,
    [filler] [VARCHAR](100) NULL,
PRIMARY KEY CLUSTERED ([staging_ky] ASC)
);

INSERT INTO [dbo].[a_staging_table] WITH (TABLOCK)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE NONCLUSTERED INDEX [IDX_dbo_a_staging_table_col_p]
    ON [dbo].[a_staging_table]([col_p] ASC)
    WITH (FILLFACTOR = 100);

The following query:

SELECT j0.[staging_ky]
FROM [dbo].[a_staging_table] AS j0
LEFT OUTER JOIN [dbo].[a_staging_table] AS j1
ON j0.[staging_ky] = j1.[staging_ky];

Results in join elimination:

But a similar query with an INNER JOIN does not:

The optimizer doesn't support every possible query transformation. In this example, as programmers we can deduce that a join isn't necessary but the optimizer cannot. This might be unsupported in the product because Microsoft viewed this scenario as not common enough to address, the check was not worth the compile time overhead, or because it would be a difficult check to implement.

For completeness, the spool is present in the query plan due to halloween protection.


Why would a table with a Clustered Columnstore Index have many open rowgroups? I was experiencing some performance issues with a query yesterday and upon further investigation, I noticed what I believe is odd behavior with a clustered columnstore index that I'm trying to get to the bottom of.

The table is

CREATE TABLE [dbo].[NetworkVisits](
    [SiteId] [int] NOT NULL,
    [AccountId] [int] NOT NULL,
    [CreationDate] [date] NOT NULL,
    [UserHistoryId] [int] NOT NULL
)

with the index:

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_NetworkVisits]
   ON [dbo].[NetworkVisits] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

The table currently has 1.3 Billion rows in it and we are constantly inserting new rows to it. When I say constantly, I mean all the time. It's a steady stream of inserting one row at a time to the table.

Insert Into NetworkVisits (SiteId, AccountId, CreationDate, UserHistoryId)
Values (@SiteId, @AccountId, @CreationDate, @UserHistoryId)

Execution plan here

I also have a scheduled job that runs every 4 hours to delete duplicate rows from the table. The query is:

With NetworkVisitsRows
  As (Select SiteId, UserHistoryId, Row_Number() Over (Partition By SiteId, UserHistoryId
                                    Order By CreationDate Asc) RowNum
        From NetworkVisits
       Where CreationDate > GETUTCDATE() - 30)
DELETE
FROM NetworkVisitsRows
WHERE RowNum > 1
Option (MaxDop 48)

The execution plan has been pasted here.

While digging into the issue, I noticed that the NetworkVisits table had roughly 2000 rowgroups in it, with about 800 of them being in an open state and no where near the max allowed (1048576). Here is a small sample of what I was seeing:

I ran a reorganize on the index, which compressed all but 1 rowgroup, but this morning I checked again and we again have multiple open rowgroups - the one that was created yesterday after the reorganize, then 3 others each created roughly around the time the deletion job ran:

TableName       IndexName           type_desc               state_desc  total_rows  deleted_rows    created_time
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        36754       0               2019-12-18 18:30:54.217
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        172103      0               2019-12-18 20:02:06.547
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        132628      0               2019-12-19 04:03:10.713
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        397718      0               2019-12-19 08:02:13.063

I'm trying to determine what possibly could be causing this to create new rowgroups instead of using the existing one.

Is it possibly memory pressure or contention between the insert and the delete? Is this behavior documented anywhere?

We're running SQL Server 2017 CU 16 Enterprise Edition on this server.

The INSERT is MAXDOP 0, the DELETE is MAXDOP 48. The only closed rowgroups are the ones from the initial BULKLOAD and then the REORG_FORCED that I did yesterday, so the trim reasons in sys.dm_db_column_store_row_group_physical_stats are REORG and NO_TRIM respectively. There are no closed rowgroups beyond those. There are no updates being run against this table. We average about 520 executions per minute on the insert statement. There is no partitioning on the table.

I am aware of trickle inserts. We do the same thing elsewhere and are not experiencing the same issue with multiple open row groups. Our suspicion is it has to do with the delete. Each newly created row group is around the time of the scheduled deletion job. There are only two delta stores showing deleted rows. We don't actually delete a lot of data from this table, for example during one execution yesterday it deleted 266 rows.

With constant trickle inserts, you very well may end up with numerous open deltastore rowgroups.  The reason for this is that when an insert starts, a new rowgroup is created if all of the existing ones are locked.  From Stairway to Columnstore Indexes Level 5: Adding New Data To Columnstore Indexes

  Any insert of 102,399 or fewer rows is considered a "trickle insert". These rows are added to an open deltastore if one is available (and not locked), or else a new deltastore rowgroup is created for them.

In general, the columnstore index design is optimized for bulk inserts, and when using trickle inserts you'll need to run the reorg on a periodic basis.

Another option, recommended in the Microsoft documentation, is to trickle into a staging table (heap), and when it gets over 102,400 rows, insert those rows into the columstore index.  See Columnstore indexes - Data loading guidance

In any case, after deleting a lot of data, a reorg is recommended on a columnstore index so that the data will actually be deleted, and the resulting deltastore rowgroups will get cleaned up.


Mirroring issue with mirror server I am running two Windows Servers 2012 R2 with SQL Server 2014 Standard Edition. I have strange issue on mirroring.

I configured mirroring on for 15 databases, no Witness server. The Principal server show all database is Principal, Synchronized and the Mirror is Mirror, Synchronized/Restoring.

When I open Database Mirror Monitor I see Role Principal is in Mirroring State Synchronized but the Mirror's Role is not connected. I have fail over in both directions and the database came up on the mirror server normally. data is replicating for troubleshooting purpose I have done below steps.

I have seen in error log in principle nothing is related to mirroring  traced I can see most of the errors in principle are Error: 18456, Severity: 14, State: 38.
I have seen errors in mirroring server for all the databases are Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://server.company.com:5022'. Error: 1474, Severity: 16, State:
I have tried to resolve by stop and start the endpoint its was not resolved.
I have tried pause and resume it is also not resolved.
I have verified TCP/IP it is enabled on both the servers.
I have tried to configure alias it is also not resolve.
I have telnet from each other, it seems have  no communication issues
I have verified connect rights to domain level same service account for both principle and mirror.
Earlier it is SQL server 2014 sp2 ,today I have updated with latest service sp3 even though still I can see same in mirror monitor Not connected to mirror.

Please help on this issue, how to resolve this?

SQL 2014 std does not support AlWays on Availability Group , You need Enterprise Edition,   But SQL 2016 std edition supports basic availability group features

you can check the state of the mirror by using the query below.

this will give you a starting point for further investigations.

--==============================================================================
-- query that shows the current state of each database in the mirroring
--==============================================================================
  SELECT   db_name(sd.[database_id])           AS [Database Name]
          ,sd.mirroring_state                  AS [Mirror State]
          ,sd.mirroring_state_desc             AS [Mirror State]
          ,sd.mirroring_partner_name           AS [Partner Name]
          ,sd.mirroring_role_desc              AS [Mirror Role]
          ,sd.mirroring_safety_level_desc      AS [Safety Level]
          ,sd.mirroring_witness_name           AS [Witness]
          ,sd.mirroring_connection_timeout AS [Timeout(sec)]
    FROM sys.database_mirroring AS sd
    WHERE mirroring_guid IS NOT null
    ORDER BY [Database Name];

make sure the sql server service account has all the relevant permissions on both servers.

I am not sure of your environment, but depending on the query below, and what I could find in the logs, on a convenient time, plan a restarting of the sql server service (which will cause a failover) and back.

Another thing that has worked for me in the past, is scripting the whole thing, then dropping it, and then setting it up again through the script, one database a time.

that way you can try to narrow down the problem.

there is a very similar question:
Database Mirroring error, but everything looks OK

the answer recommends looking at the permissions. Do that first.

another thing is to do a failover following the example below:
(the original had many databases, I left only one DB per server, but you can get the idea)

--============================================================================
--instructions:
--for each server,
--1) get the list of the alter database set safety full
--2) paste that script below
--3) run it
--4) get the list of the alter database set partner failover
--5) run the first line make sure it goes through
--6) run the other lines - failover all the databases
--version 20140624 Anne and Marcello

--============================================================================

--============================================================================
-- before failing over
-- the safety mode should be changed to FULL (SYNCHRONOUS)
--============================================================================

select 'ALTER DATABASE '+quotename(db_name(database_id))+
' SET SAFETY FULL'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
 or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)

*
-- sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY FULL

*
--sqlweblon4
ALTER DATABASE [USAccount] SET SAFETY FULL

--============================================================================
-- generate the failover scripts
--============================================================================
select 'ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER FAILOVER'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
 or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)

*
--sqlweblon3 (principal)
ALTER DATABASE [US14AUTPProduct] SET PARTNER FAILOVER

*
--sqlWeblon4 (principal)
ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER
ALTER DATABASE [USAccount] SET PARTNER FAILOVER
ALTER DATABASE [USProductOffer] SET PARTNER FAILOVER
ALTER DATABASE [USStock] SET PARTNER FAILOVER

ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER

/** IF YOU ARE FAILING FROM DR TO LIVE CHECK THE LITESPEED
ACTIVITY SERVICE IS RUNNING ON THE SERVER. IF IT IS NOT LITESPEED
SQL AGENT JOBS WILL FAIL **/

--============================================================================
-- after the failover change the safety mode back to ASYNCHRONOUS
-- this is to be done on what is now the principal
--============================================================================

-- DR-sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY OFF


Is my restore process being badly affected by this third-party non copy-only backup? This question may read like a duplicate, but is situation-based, and is posted from confusion applying the knowledge from other answers.

I've read dozens of articles (among 1, 2, 3, 4), but am finding conflicting opinions (based on my understanding, which is now suffering from information overload, or perhaps not including enough information in my other questions).  I am creating this question therefore to get a definitive answer based on my situation.

Given the following backup scenario, I need to know whether the third-party backup software would prevent me from performing a full recovery to the point of the latest backup (18:00)?

Time  | Action                                       | Device
------|----------------------------------------------|----------------------------
12:00 | Full backup (non copy_only)                  | D:\MyBackupDevice
13:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
14:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
15:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
16:00 | Full backup (non copy_only) VSS snapshot     | Third-party off-site device
17:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
18:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice
19:00 | Disaster strikes                             |

My restore goal is to restore to the point of the 18:00 backup (I know there is a tail-log backup that can be added in as well to get the remaining transactions up until the disaster, but let's keep things simple for now).

Based on this answer, I believe that the third-party backup causes a conflict with my own transaction log restoration (as per this answer), which prevents me from performing my restore to that point.   My understanding is that the transaction log backup contains data since the last full non copy_only backup.

Is this correct?  Does the third-party backup prevent my own restore routine from working because it is a non copy_only backup?

Maybe this will help you understand. Let's do a demo! We'll make a dummy database with some dummy data.

USE master;

/*Create a dummy database*/
CREATE DATABASE LogRestoreTest

/*We full now*/
ALTER DATABASE LogRestoreTest SET RECOVERY FULL

/*Context is everything*/
USE LogRestoreTest

/*If nothing changes, do we even need a log backup?*/
CREATE TABLE dbo.t1 (Id INT)

Now we'll take one full backup. Just one. Promise.

/*Take a full backup, dummy*/

BACKUP DATABASE LogRestoreTest
TO DISK = 'F:\Backup\LRT_FULL.bak'
WITH INIT, FORMAT, COMPRESSION

Now we'll make some changes and take some log backups. Just like real life. It's fun. No drinking yet.

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a log backup*/
BACKUP LOG LogRestoreTest
TO DISK = 'F:\Backup\LRT_LOG_1.trn'
WITH INIT, FORMAT, COMPRESSION

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another log backup*/
BACKUP LOG LogRestoreTest
TO DISK = 'F:\Backup\LRT_LOG_2.trn'
WITH INIT, FORMAT, COMPRESSION

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

Now we'll take an 'out of band' full backup, and another log backup.

/*A second full backup appears!*/
BACKUP DATABASE LogRestoreTest
TO DISK = 'F:\Backup\LRT_FULL_2.bak'
WITH INIT, FORMAT, COMPRESSION

/*Take another log backup*/
BACKUP LOG LogRestoreTest
TO DISK = 'F:\Backup\LRT_LOG_3.trn'
WITH INIT, FORMAT, COMPRESSION

If I want to restore data to the third log backup, I have two options.

Restore the first full backup, and all three log backups:

/*Restore the full backup*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_FULL.bak'
WITH REPLACE, NORECOVERY

/*Square one*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_1.trn'
WITH NORECOVERY

/*What about to here?*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_2.trn'
WITH NORECOVERY

/*Obligatory Your Mom*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_3.trn'
WITH NORECOVERY

Or I can restore the most recent full and then the final log file:

/*Restore the full backup*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_FULL_2.bak'
WITH REPLACE, NORECOVERY

/*What happens if I try to jump the restores?*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_3.trn'
WITH NORECOVERY

You'll have to forgive me not explaining differential backups here, but there are plenty of resources out there that cover DBA 101 material.

Hope this helps!


Bypass MyISAM table lock I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action because the whole table is locked.

Is there a possibility or a workaround to bypass that table lock without getting a corrupt table? It seems that InnoDB is the right way (because of row locking), but InnoDB includes fulltext search only with a mysql version greater than 5.6 and this is not included in the current debian repository, even more the new debian wheezy has not mysql 5.6 included.

What can I do in my case?

Since your main issue is the FULLTEXT index, I have two storage engines suggestions
SUGGESTION #1 : MyISAM
The MyISAM storage engine does a full table lock on the following:

ALTER TABLE
DELETE
UPDATE
INSERT

The only exception to the table lock blocking everything would be setting concurrent_insert. (See MySQL Documentation on Concurrent Inserts)
Try setting this in my.cnf
[mysqld]
concurrent_insert = ALWAYS

or
[mysqld]
concurrent_insert = 2

Adding new rows to a MyISAM table bypasses table locking only in this circumstance. You will have to experiment with adding rows to the table while updating fulltext indexed columns in another row of the table.
SUGGESTION # 2 : InnoDB
As of Feb 5, 2013, MySQL 5.6 is not out of beta and is now GA. InnoDB in MySQL 5.6 supports FULLTEXT indexing. Since doing this in an ACID-compliant Database is what you essentially need, look into upgrading. Do no load any preinstalled MySQL 5.6 packages if they predate Feb 5, 2013.


Is it possible to list column entry only once? While this is not a huge deal, I'm just wondering if this is possible (I'm using MySQL in case that matters).

I have a query that returns something like this:

Basket      Contents
basket1     apple
basket1     orange
basket1     pear
basket2     lemon
basket2     banana
basket2     lime

Is it possible using only SQL to get this output?

Basket      Contents
basket1     apple
            orange
            pear
basket2     lemon
            banana
            lime

In other words, is it possible to list the column entry only once for each entry?  Thank you.

I would say this is easier on the representational layer but if you want it in pure MySQL this will do it:

set @current='';
select show_basket, content
from (
    select case
        when @current = basket
            then ''
            else basket
        end as show_basket,
        content, @current:=basket
    from test_131547
    order by basket
) a;

Output:

+-------------+---------+
| show_basket | content |
+-------------+---------+
| 1           | lemon   |
|             | lime    |
|             | apple   |
|             | banana  |
| 2           | kiwi    |
|             | apple   |
|             | orange  |
+-------------+---------+


How does SQL Server generate a query execution plan that adds up to 6,000%? Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still trust sql-server if it cannot even count to 100%?

Characteristics of the table:

clustered on a non-identity column
12 indexes, one of them being the date_entered column in question
60,000 records
26 columns of various types and length
PAGE compressed table

Has anyone seen this before and what causes the plan to look so skewed?

And below from SQL Sentry Plan Explorer

This bug has been known for years, and won't ever be fixed. I first reported it 6 years ago but it has existed far longer than that. These are just estimated costs and don't really have any bearing on the optimizer itself and whether you can trust it. It's simply the showplan output that has some questionable math.

Connect #267530
Connect #370798
Connect #436184
Connect #621330

Solution: use SentryOne Plan Explorer, which tends to get the math right (or simply stop looking at estimated costs and look at more meaningful metrics). If you open this plan in Plan Explorer, the percentages will add up correctly.


How to tell which indexes are not being used Postgresql 9.3
Debian 7

I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they are not used at all.

Is there any usage statistics somewhere or some trick query to do that?

You can monitor the index usage through pg_stat_user_indexes and pg_statio_user_indexes

More details about the statistics collector can be found in the manual: http://www.postgresql.org/docs/current/static/monitoring-stats.html

You should be careful with dropping unused unique indexes though. They might not be used for reading, but they are most probably vital to the integrity of your data.


PostgreSQL Select statement produces errors in datagrip but not pgAdmin4 I can run the following script without errors in pgadmin4

SELECT * from tbl

I have tried the following in datagrip

SELECT * from tbl

and

SELECT * from public.tbl

Neither of the above scripts worked. The following error was returned:

[2018-05-12 21:16:44] [42P01] ERROR: relation "public.tbl" does not exist
[2018-05-12 21:16:44] Position: 15

This ended up being really simple and a very silly mistake, but hopefully this will help someone else.

I simply needed to select the correct database. The default is postgres. The database I had my tables in was webadmin. You can change the database when you make your connection in datagrip. The screen where I made the change looked like this


Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables? My case:

I have many organisms (for example human, mouse and etc.) and a few models for each one. All models are independent from each other and I will never query them together.

Normally I will do it like that:

PK (organism, model) is FK in one big table with models rows. But I do not need have them in one place, because data under different (organism, model) are independent, so I will always query a sub set of that big table base at FK (organism, model).

My idea is use PK (organism, model) as a TABLE_NAMEs for many separated tables, each one contains independent data.
I am not proficient in DB, and I can not find how should I structured data like this. Can you help me? Is my idea OK? Should I solve it in other way? Where can I find any clues?

summarizing:

PK ---> TABLE_NAME

If the data in each subset it truly independent and will never be reported upon together then modelling them as one table per entity type is perfectly valid. The fact that the table name will match a key in another table isn't something the database will care about - that table just becomes a list that tells the business logic what entity tables to expect to exist.

Even the "if you will never report on them together" can be relaxed to "if you won't need to report on them live together in an efficient manner" because there is the etl-to-datawarehouse option for offline analysis and less than efficient (but workable) options for ad-hoc reporting.

Of course the database layer is not going to be able to enforce the relationship between table name and data in another table: FKs can only be defined on data not schema elements.

I suspect that you don't want the data to be completely independent though. If I've understood you then what you describe is a classic example where simple table inheritance is applicable and what you want is something like:

                   Organisms            HumanModel1Details
OrganismTypes      ===============      ==================
=============      OrganismID (PK) <-<- OrganismID (FK)
Organism (PK) <-<- Organism   (FK)  |   SpecificDetail1
Model    (PK) <-<- Model      (FK)  |        ...
                   CommonProperty1  |   SpecificDetailN
                        ...         |
                   CommonPropertyN  |
                                    |
                                    |   RatModel4Details
                                    |   ================
                                    `<- OrganismID (FK)
                                        SpecificDetail1
                                             ...
                                        SpecificDetailN

Your business logic sill needs to know that Organism.OrganismType='Human' & Organism.Model='Model1' means that the details are in the table HumanModel1Details and the database can't help you enforce that. The common properties at very least include something like "display name" which will be used in your UI. Having OrganismTypes as a separate table is optional but helpful; the database can then use the FK in Organism enforce that each has a valid combination from those defined in OrganismTypes, and you can add properties to each type too (perhaps properties, or just a free text field, describing what differentiates it from other models.

You may wish to consider splitting by just Organism, not organism and model, or having two layers of inheritance (but that is probably "over modelling" and getting more complex than you need).

This is only speculation thought. As ypercube requests in his comment: please give more detail such as sample data so that we can better understand what you are asking. Use the "edit" link to add the detail to your question rather than responding via comments.

Search and read around the concept of "table inheritance", you'll find examples in many places as it is a pretty common pattern. Many ORM based frameworks such as Hibernate (see http://hibernate.org/orm/what-is-an-orm/) use it for certain model types.


Using Linked Server to store data I have a linked server setup on my source production server. I also have a secondary server which is the linked server. What I was looking to do is to execute a stroed proc and log connections to the linked server. However I seem to be having issues.

  The object name 'MetricDB.Baseline.dbo.WhoIsActive' contains more than
  the maximum number of prefixes. The maximum is 2.

Metric DB is the destination server which is configured on the source server as a linked server.

I been using Adam's sp which was described by Brent here:
https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

Due to space issues on my production DB I wanted to log it all to another server. Has anyone done this previously? or know if I'm missing something?

Thanks

Why wouldn't you try

EXEC MetricDB.Baseline.dbo.sp_WhoIsActive

Indeed, the name of the stored procedure is sp_whoisactive, not whoisactive, unless it is renamed, of course.
It could work...
Though it brings distributed query issue with XML for me.


Upgrading SSIS packages I found that packages on a 2012 SQL Server could only be accessed using an older version of SSIS, and when digging the subject if found that I should perhaps update those packages, and that there are probably some obsolete packages in there.

My questions is: Can I somehow make sure that those in the SERV-XXX folder will upgraded safely ? (they are are simple exports from a view to a local CSV or XLS or TXT).

To restate, you have packages in SQL Server 2008 R2 and you wish to migrate them to SQL Server 2012. The packages are generally export to "file format".

The only way to know if they upgrade successfully is to upgrade them and find out. The biggest challenge I recall was with packages that had Script Components (not Tasks) as the pipeline has some biggish structural changes. The other obvious caveat is if you used a custom third party/in house Task/Component. That'll require manual migration and modification of the packages in visual studio.

You have two options with your upgrade. SSIS now has two deployment models. Package deployment model, a.k.a. Classic/Legacy, which is what you have here. Packages can be deployed to file system or msdb, you control your configuration, logging, versioning, etc.

Project deployment model is the new hotness. There is a dedicated SSISDB catalog that handles all the configuration, logging, versioning etc for you. I generally favor it over the former as it provides a better management and execution experience. Migrating package deployment model projects to project deployment model requires changing the packages themselves. The upgrade wizard will help in that but it will strip out logging and configuration as it wants you to use the new way. This will require more testing than the simple smoke test required with a package deployment model upgrade path. Whether it's worth the effort, especially in a change control heavy environment is best answered by you.

An approach for package deployment upgrade

dtutil.exe is the command line tool for deploying SSIS packages. Assuming standard install path, you'd be looking for the dtutil that is in C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe to extract the packages as is. When a newer version of SSIS tooling touches a previous version package, the first thing that happens is an in memory upgrade to that version of SSIS. I mention this as if you didn't use source control, you might want to extract the packages "as is" out of the existing 2008 R2 instance before deploying to your 2012 instance. I have a script for that

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename')
    -- if you need to really make this generic
    'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath LIKE '\SERV%';

If you like to live dangerously, you could change the final SELECT to skip the file system and directly deploy to the other server. This assumes you've already created the same folder structure over there.

'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /destserver MyShiny2012Box /copy SQL;"'+ F.FolderPath + '\' + P.PackageName + '"' AS cmd

I've eyeballed the above and it looks approximately correct but you should test it. It should render a command similar to the following

dtutil /sourceserver localhost /SQL "\SERV-ABC\Package" /destserv MyShiny2012Box /copy SQL;"\SERV-ABC\Package"

For that to work, you'd need to make sure dtutil is the 2012 version which should be in C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe

From the comments

Don't confuse the tooling with the installed products and/or servers. Starting with 2005, there is a version of SSMS that was released with each SQL Server version (this gets murky as of SQL Server 2016). SSMS has an "object explorer". The Database Engine portion of Object explorer can be used to manage all versions of SQL Server and the common recommendation is to always use the latest version of SSMS in your environment as it'll have the most bug fixes etc.

Where this advice can fail you if you use the "other" options for Object Explorer. SSIS is the only thing I use with regularity so I'll address that.

You must use the version of SSMS that matches your target SQL Server instance for it to "work." In the screenshot, you're attached to the 2008 R2 server's Integration Services ... whatever that is but ignore it. Yes, ignore that blecherous tool as it's useless. It doesn't help you if you have multiple instances running and it only runs packages in ... 32 or 64 bit, I don't remember which.

Instead, use the one Object Explorer type that works across your instances - the database engine one. SSIS packages stored in "the database" are going to be in the msdb. Use the above query or go exploring those tables yourself. All the data is there. There are tsql procedures to move packages about, store them out, etc although I prefer dtutil myself.


Repair by Sort not working I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql manual learned about how to import by using repair by sort. This however started my problem. Although show processlist shows Repair by sort I cant see the tmp tables that it supposedly creates for the process not to mention when I check the /mysql/data directory .myd and .myi files totals the .sql file but the process does not end. I have left the process open for 2 days without any change at which time I killed it.

Could this be because the computer I have to use as mysql server is wamp or cause of the hardware limitations which has 2.gen i5 with 4GB Ram and 1 500GB HDD with 2 partitions or could the dump I have been given is corrupted?

Thnx in advance for any nudge in the right direction to this newbie about mysql and well databases in general. (Below is the parts of the my.ini with the help of percona config tool)

tmpdir = D:/temp
key_buffer_size = 2344M
myisam_file_sort_size = 80G
myisam_max_sort_file_size = 80G
myisam_sort_buffer_size=164M
join_buffer=1M
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
query_cache_size=132M
table_cache=1024
thread_cache_size=256
max_allowed_packet=156M

The tmp tables won't be in /mysql/data, but rather in the subdirectory corresponding to the database.  Or maybe in the tmpdir directory.

key_buffer_size = 2344M -- that is just for indexes.  In a 4GB machine, that does not leave enough room for data.  Change it to 700M.

thread_cache_size -- 0 is better for Windows.

query_cache_size=132M -- too big; drop to 50M

max_allowed_packet=156M -- Probably too high.  Suggest 32M.

None of those necessarily explain the symptoms you have.  Can you tell if mysql is "swapping"?  If it is, that would be deadly, and my suggestions would probably prevent swapping.

Is the "repair" because of a crash?  If so, you should seriously consider switching to InnoDB.  At least put it on your roadmap.  But be aware that the disk footprint with be 2-3 times as big.

"Repair by sorting" would, I think, proceed as follows:

Load all the data into the #sql...MYD file.  Meanwhile the .MYI file would be 1K or empty.
Read the data, sort it, and load it into the .MYI for one index.
Ditto for second index.

I do not know if having two unique index complicates things, and necessitates use of the key_buffer.


Understanding how to choose between fields and tags in InfluxDB What are some good rules and examples of how to choose between storing data in fields vs. tags when designing InfluxDB schemas?
What I've found so far is:

a measurement that changes over time should be a field, and metadata about the measurement should be in tags
tags and fields are effectively columns in the table. tags are indexed, fields are not
the values that are highly variant and not usually part of a WHERE clause are put into fields
Store data in fields if you plan to use them with an InfluxQL function
Tags containing highly variable information like UUIDs, hashes, and random strings will lead to a large number of series in the database, known colloquially as high series cardinality. High series cardinality is a primary driver of high memory usage for many database workloads.

But let's say you store filled orders in an e-commerce application: order id, sale price, currency.

Should the order id be a tag, or a field?
Should the currency be a tag, or a field?

I just read a tutorial that said fields are data and tags are metadata. That is a very intuitive definition.

The example had pressure and temperature fields and a weather station tag. Again, crystal clear and perfectly matches the description.

Unfortunately, they then said that if you are querying on pressure or temperature and not weather station, you should flip the field and tag designations around. In other words the definitions provided for field and tag are meaningless.

The simple solution is to stipulate that fields can either be indexed or not indexed. Fields that are indexed are called tags. Use tag when you need to index a field (to dramatically improve query speed for example).


ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes I got that error when I tried to execute this query:

select *
from lc_history2 lc
where  lc.view_name <> 'LC Main'
and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc = l.lc)
 and lc.modifiedtime >= sysdate-30

ORU-10027 will appear if you are using a lot of dbms_output.put_line in your PL/SQL with a small buffer amount. If you are just debugging then you should set this to a large amount (dbms_output.enable(10000000) is traditional though from 10g onwards it can be unlimted: dbms_output.enable(null)) but try to remove them when going to production.


What program should I use to open .db3 files? I have a series of .db3 files in my computer. They are like filename1.db3, filename2.db3, filename3.db3, filename4.db3 and so on.

I can open filename1.db3 with sqlite editor. But when I open filename2.db3 file with sqlite editor, the sqlite editor gave me error like "It is not correct format".

What program should I use to open all of those .db3 files together? Thanks in advance.

In theory you should know where the file came from and ask the person who gave it to you. A file extension could mean anything. In the old days, .db3 extension used to be for dBase III data files. It also could be for SQLLite binary dump file. See for example Importing SQLite db3 files. If you have problems, it may be attributed to many factors, version compatibility may be one of them. You may also want to perform an integrity check as in here:Pragma Check.


Return 1st or last row from NTILE Is is possible to return the first (or last) row from a NTILE partition in SQL SERVER?

For example, on this fiddle I wish I could have the last member from each group without the comma.

SQL Fiddle:
http://sqlfiddle.com/#!18/15223/4

You could do something like this:

WITH your_mom
    AS (   SELECT   T.ID, CAST(T.ID AS VARCHAR) + ',' AS [FORMATED ID], NTILE(3) OVER ( ORDER BY T.ID ) AS [GROUP]
           FROM     #TEST AS T )
SELECT  *
FROM    your_mom AS b
WHERE   b.ID = (   SELECT   MAX(b2.ID)
                   FROM     your_mom AS b2
                   WHERE    b2.[GROUP] = b.[GROUP] );


MySQL InnoDB table accepts Non-Existent Foreign Key Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page.

Working on Windows 7 64-bit.

I've created the tables I needed and specified each foreign key in the "Relation view" (on Structure tab). All the tables are InnoDB and the default engine is set to InnoDB.

Assuming I have the tables A and B, and B has a foreign key to the column ID of the table A.

When inserting a line in table B, if I write 0 (zero) on the foreign key column and submit, it accepts.

In the Browse tab it shows the line with the foreign key column with the value 0 (zero).

When I click on the value (link to the that line on the table A), I go to that table with zero results.

In the variables tab, the "foreign key checks" variable is "ON".

But the foreign keys are not checked or the insert would fail since there is no ID 0 on table A. What have I done wrong or forget?

  Please run SHOW CREATE TABLE yourtable\G for both table A and table
  B and post it in the question. –  RolandoMySQLDBA

Table A

CREATE TABLE `competicaogeral` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Table B

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Please check that the FOREIGN KEY is in the table design of your table B.

EXAMPLE

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

Facts

Table A is the table Staff
Table B is the table projects
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE requires StaffID in projects to reference a row in Staff with the same StaffID

Your Original Question

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

You need a foreign key reference

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`),
 FOREIGN KEY (CompeticaoGeral_ID) REFERENCES competicaogeral(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The SQL to do this would be

ALTER TABLE competicao ADD FOREIGN KEY (CompeticaoGeral_ID)
REFERENCES competicaogeral(ID) ON DELETE CASCADE;

GIVE IT A TRY !!!


Using SQL Server with BitLocker I have an application that is running on a device that has two partitions: C and D. The application runs off of C while D contains the SQL Server database (mdf file). In addition, D is also encrypted using BitLocker.

We are using the code-first approach where the database is created on the fly, based on the specified entities within our code. If the database has not yet been created and I turn BitLocker on, the application cannot create the DB and I get a 'Directory lookup exception', with the message 'This drive is locked by BitLocker'. Though, as soon as I turn off BitLocker, it gets created as expected and everything is fine. I turn on BitLocker again, and this time I get a different exception, 'the login failed for user sa'. Once again, this works when I turn BitLocker off. Is there something different that I need to do since we are using the SQL sa user to login?

According to Using SQL Server 2012 Standard with Bitlocker

I  need to make sure that the SQL Server Service account is the one used for turning BitLocker on. I've changed the account (Services -> Sql Server -> Properties -> LogOn) to the user account I used for turning BitLocker on, but that does not seem to do the trick.

I agree with Jithin R and ensuring the SQL Server "service" account (what you have set in the services.msc for the login as, and ensuring it has at least MODIFY or FULL permissions at the NTFS folder level where the MDF and LDF files are created when they code dynamically creates the DB).

It sure sounds like an NTFS permission issue with whatever security context is being used to created the LDF and MDF files does NOT have permission on the disk partition to create the DB files when initiated.

The security context will need at least LIST to traverse all folders and subfolders to the level of the folder where the DB files will be created, and where those files are created, it'll need MODIFY or FULL permissions there but MODIFY access should suffice.

I'm not sure why BitLocker being enabled or disabled on this disk would have an effect on this but perhaps BitLocker enabling in your configuration changes the NTFS permissions of the folders where the DB is being created.


What is meant by "churn"? I came across the following sentence in this blog post:

  Know your data - this helps you make the right decisions in terms of
  data types/nullability and churn helps with long term maintenance
  goals (and initial maintenance plans)

I can't figure out what is meant by "churn." What is this? I only find plenty of articles talking about "churn" without saying what it is.

"Churn" in the most common usage is the rate that existing members of a group leave the group (for example, customers of a company stop being customers- if a company has 15% annual churn, 15% of its customers leave every year).

In this context, then, it would seem that the author is saying that you have to understand the rate at which records may be deleted or otherwise removed from tables in order to understand how to best plan the maintenance of the system.


Ensure N:ALL relation ship for some rows I have a simple N:M relation ship:

Table User:

Column ID
Column username
Column is_superuser (Boolean)

Table Permission:

Column ID
Column Name

Table UserPermission:

Column ID
ForeignKey User
ForeignKey Permission

I want to ensure that if the user has "is_superuser=True" then he should have all available Permission set via N:M Table UserPermission.

Typical operations:

Update or insert on table "User": user gets set "is_superuser". Now the UserPermission needs to have all permissions for this user
A new permission gets added to the Permission table. Now the UserPermission needs to get N new rows (for each user with is_superuser=True).

How to ensure this with Postgres 9.6?

Sub-question (please leave a comment): Is there a name for this pattern?

This is a mistake. Just join the tables and infer permissions.

CREATE TABLE users (
  userid       serial  PRIMARY KEY,
  username     text    UNIQUE,
  is_superuser bool    NOT NULL DEFAULT false
);
CREATE TABLE permissions (
  permid  serial PRIMARY KEY,
  name    text
);
CREATE TABLE user_permission (
  permid  int NOT NULL REFERENCES permissions,
  userid  int NOT NULL REFERENCES users,
  PRIMARY KEY (permid, userid)
);

INSERT INTO users ( username, is_superuser ) VALUES
  ( 'ecarroll', true ),
  ( 'jbob', false );

INSERT INTO permissions (name) VALUES ('delete');
INSERT INTO permissions (name) VALUES ('select');

INSERT INTO user_permission (permid, userid) VALUES (2,2);

Now we write a query that cross joins users with permissions so we can see all a users permissions. And, then we left outer join that for all users that are not superusers, with the linking table. We infer if they has_permission if they're is_superuser OR /have entry in linking table/. The is_superuser is a superior source: we no longer even care about what's in the linking table if we have that bool set. This makes your job simple and efficient. You don't have to keep the tables in sync.

SELECT
  users.userid,
  users.username,
  permissions.name,
  users.is_superuser OR user_permission.userid IS NOT NULL AS has_permission
FROM users
CROSS JOIN permissions
LEFT OUTER JOIN user_permission
  ON NOT is_superuser
  AND user_permission.userid = users.userid
  AND user_permission.permid = permissions.permid
ORDER BY users.userid;

Produces

 userid | username |  name  | has_permission
--------+----------+--------+----------------
      1 | ecarroll | delete | t
      1 | ecarroll | select | t
      2 | jbob     | delete | f
      2 | jbob     | select | t
(4 rows)


How do I stop, tune and restart a MariaDB Galera cluster? So I have 4 nodes running on some fast but smallist (ram wise) vps machines. This is very cool but they are using 100% of the memory available. I need to turn off performance schema and possibly lower some other settings to get this running at or near 7-800 mbs.

Before I try to do service mysqld stop, I thought I better ask.

So I will chime in the answer here now that I have done this. So after tuning the mysqld settings I did a plain old service mysql restart and it worked perfectly. As long as you have EDIT please see above, grow in odds with at least 3 running running it does not seem to care at a restart.


how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger? How do I prevent UPDATE on any record in a given table, but allow INSERT, I already have created a trigger which prevents update but I found that it also prevents INSERT.
EDIT
I can INSERT new row/record from the database itself but not from the form for a new record of the application that is connected to this database, after submitting the form, the trigger is being executed.
It's a web application built with Django.
TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION prevent_updte()
RETURNS trigger AS
$BODY$
BEGIN
RAISE EXCEPTION 'Data modification not allowed';
END;
$BODY$
LANGUAGE plpgsql

TRIGGER
CREATE Trigger prevent_update
BEFORE UPDATE ON purchases_purchase
FOR EACH ROW
EXECUTE PROCEDURE prevent_update();

You can use GRANT & REVOKE for this purpose:

 CREATE TABLE TEST (ID int, NAME text);

 REVOKE ALL ON TABLE TEST FROM CURRENT_USER;

 GRANT INSERT ON TABLE TEST TO CURRENT_USER;
 GRANT SELECT ON TABLE TEST TO CURRENT_USER;

 INSERT INTO TEST VALUES (1, 'NAME 1'),(2, 'NAME 2');

UPDATE TEST SET NAME='NAME 3' WHERE ID = 1;
ERROR:  permission denied for table test

db<>fiddle here


ER-Model for Borrowing-Lending-Network? I plan on creating my first database so this is going to be a newbie question. My database is supposed to be for a Borrowing-Lending-Network where users can borrow products from other users. Creating one user account should be sufficient for being able to borrow and lend and the same time. The only exception should be that a user cannot borrow products that were offered by himself.

So my question is which of these two options is the better one:

Having one entity for user which can borrow and lend. Entity name "USER".
Having two different entities for both borrowers and givers in order to prevent a user from borrowing his own products?

Have a single User table.  Then if anything changes, such as a phone number, it changes for the lender and the borrower simultaneously without awkward reconciliation between the two tables.

Your Loan table will look something like this

Loan
----
LenderID        -- A foreign key pointing to User
BorrowerID      -- Also a foreign key pointing to User
ItemID

Then create a constraint on the table to ensure LenderID <> BorrowerID.  You don't say which RDBMS you're using but many of the major ones support this.  If anyone tries to lend an item to himself the DBMS will throw an error and refuse to store the data.


Error when creating a stored procedure in postgreSQL-10 I couldn't get my first stored procedure to work, so I copied this example from the manual:

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

and I get the following error message:

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE insert_data(a integer, b integer)
               ^
SQL state: 42601
Character: 8

What am I doing wrong?

I'm running the database on localhost, running as admin user. I installed it myself. Installation process completed without errors, and I am able to create tables, functions, triggers, etc.

As mentioned in the comments, PostgreSQL-10 does not have stored procedures. Same is true about earlier versions.

Workaround alternatives:

Upgrade to version 11 or later
Use a function

Thanks, @Erwin Brandstetter and @a_horse_with_no_name


CREATE as SELECT with CONSTRAINT? I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null.

I cannot find valid SQL for this in the Postgres Documentation, so it seems it is not possible.

What would be the best approach to achieve this kind of functionality? I would like creation of the table to fail if the constraint is violated.

I cannot create the table with the constraint beforehand because there might be some variation in some of the columns, as they are the result of a dynamic process.

A variation on @a_horse_with_no_name's answer would be to first create the table with the constraint and then insert into the table, still using a transaction, so everything rolls back in case of constraint violations. This is something you should consider if the rows to be inserted are a lot (i.e. in the hundreds of thousands or millions).

If it's difficult to create the create table statement dynamically, you could separate that part into 2 statements, with something like this:

begin transaction ;
    create table new_table                       -- create the table
    as
    select v.*
    from (select ....) as v                      -- as before
    where false ;                                -- but insert 0 rows

    alter table new_table add constraint ... ;   -- add the constraint

    insert into new_table                        -- and then attempt
    select ... ;                                 -- the insert
end ;

The difference with the other way is that the constraint will be there before any row is inserted into the table. And since NOT NULL and CHECK constraints are not deferrable but always checked immediately when a row is inserted or modified (and not at the end of the statement), your insert statement will fail (and rollback) with the first row that violates the constraint(s) you have placed.

If you are inserting multi-millions of rows, it will be faster, both because it may find an error more quickly but also because it will have to rollback less insertions.


Saving SQL Server backup file in multiple parts I am taking daily backups of a SQL Server database. Right now the .bak file is about 2GB and it is growing day by day. There is a scheduled job is running which moves this .bak file from one location to another.

Is there a way to save the .bak file in chunks - like part1.bak, part2.bak etc..

so it will be much easier to move small data and merge at destination?

Normal backup

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

to split the size into 3 use the following code, it will split the size of 3 gb into 1 GB each file
you can add more files or less depending on the size you want

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME TO

DISK = 'D:\DBBackups\ReportServer\DBNAME _Split1.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split2.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split3.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

for more details

http://beyondrelational.com/modules/2/blogs/88/posts/10153/sql-server-split-database-full-backup-to-multiple-files.aspx


Remove String from column based on character i have table :

table : mytable

id  name
1   abcd ( dljdf
2   kjlkjkldjf ( ( ( ;dfld )

I want to remove string starting from character '('.

Result would look like

id  name
1   abcd
2   kjlkjkldjf

I tried using Trim Function but it did not give me required result

Any help plz.

SELECT SUBSTRING_INDEX(name, '(', 1) FROM test;

UPDATE test
    SET name = TRIM(SUBSTRING_INDEX(name, '(', 1));

(assuming you want to get rid of the space, too)


Unknown Db file format - best way to find out? A client has given me the data he wants visualised in my project - but I don't know what DB format it's in. He's pretty unclear, also..
I have access to the table structures, but don't know how to read it in. It's definitely not a flat file.

There are several files for each dataset:

file.dat (biggest by far)
file.id (small)
file.ind (half the size of *.dat)

The first few bytes in the *.dat file are 03 6E 02 14 14 15 19 00 (in case magic numbers are used).

Do these extensions ring any bells? Is there some software to determine the format?

If you are on a *nix system, you can try using the "strings" command on each file to peek at the contents. A header may give you a clue.

e.g.

strings file.dat | head

Here's an example run against a quicky sqlite3 database file.

~$ sqlite3 test.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table testtable (id INT);
sqlite> insert into testtable values (1);
sqlite> .quit
~$ strings test.db
SQLite format 3
Ktabletesttabletesttable
CREATE TABLE testtable (id INT)


Create user with the replication access only Is it possible to have a user in mysql who can only have an access to the replication?

What I need is to monitor the replication to show if the replication is up and running or not. But, I want that the mysql user has only access to the replication infos, not everything.

https://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html

You have to grant one of two permissions - SUPER or REPLICATION_CLIENT.
REPLICATION_CLIENT would be your minimum, so probably what you're after?


Is the 8KB row limit a "hard limit"? Per the SQL Server docs, it looks like if I have one or more varchar/nvarchar columns that cause row length to exceed 8KB, SQL Server will magically handle it.

Scenario:
- Table has 100 cols
- Max current row length is 3.4KB
- But if every varchar/nvarchar field was populated to max, the row length would hit 14kb

From how I interpret the Books Online details linked above, this scenario will work out OK (e.g. nothing will break and everything will work).

Is my assumption true?

Using the undocumented features DBCC IND and DBCC PAGE, you can pretty easily tell exactly what SQL Server does with rows that exceed the "maximum" size of 8,060 bytes.

To show how this works, I'll create a simple table in tempdb where the row width is 10,000 bytes, then run DBCC IND to get a list of pages where the data is stored.  We'll then run DBCC PAGE to see the content of those pages.

First, we create the table with a maximum width of 10,000 bytes:

SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.WidthTest', N'U') IS NOT NULL
DROP TABLE dbo.WidthTest;
CREATE TABLE dbo.WidthTest
(
      Col01 varchar(1000) NOT NULL
    , Col02 varchar(1000) NOT NULL
    , Col03 varchar(1000) NOT NULL
    , Col04 varchar(1000) NOT NULL
    , Col05 varchar(1000) NOT NULL
    , Col06 varchar(1000) NOT NULL
    , Col07 varchar(1000) NOT NULL
    , Col08 varchar(1000) NOT NULL
    , Col09 varchar(1000) NOT NULL
    , Col10 varchar(1000) NOT NULL
) ON [PRIMARY];

And populate that with a single row:

INSERT INTO dbo.WidthTest (Col01, Col02, Col03, Col04, Col05, Col06, Col07, Col08, Col09, Col10)
VALUES (
      REPLICATE('A', 1000)
    , REPLICATE('B', 1000)
    , REPLICATE('C', 1000)
    , REPLICATE('D', 1000)
    , REPLICATE('E', 1000)
    , REPLICATE('F', 1000)
    , REPLICATE('G', 1000)
    , REPLICATE('H', 1000)
    , REPLICATE('I', 1000)
    , REPLICATE('J', 1000)
    );

Here we're creating a table to hold the DBCC IND output, and populating that table:

DECLARE @fileID int;
DECLARE @pageID int;
DECLARE @slotID int
DECLARE @cmd nvarchar(max);

IF OBJECT_ID(N'dbo.IndexDetails', N'U') IS NOT NULL
DROP TABLE dbo.IndexDetails;
CREATE TABLE dbo.IndexDetails
(
      PageFID int NULL
    , PagePID int NULL
    , IAMFID int NULL
    , IAMPID int NULL
    , ObjectID int NULL
    , IndexID int NULL
    , PartitionNumber int NULL
    , PartitionID bigint NULL
    , iam_chain_type varchar(100) NULL
    , PageType int NULL
    , IndexLevel int NULL
    , NextPageFID int NULL
    , NextPagePID int NULL
    , PrevPageFID int NULL
    , PrevPagePID int NULL
) ON [PRIMARY];

SET @cmd = 'DBCC IND (''tempdb'', ''dbo.WidthTest'', 1);';
INSERT INTO dbo.IndexDetails
(
      PageFID
    , PagePID
    , IAMFID
    , IAMPID
    , ObjectID
    , IndexID
    , PartitionNumber
    , PartitionID
    , iam_chain_type
    , PageType
    , IndexLevel
    , NextPageFID
    , NextPagePID
    , PrevPageFID
    , PrevPagePID
)
EXEC sys.sp_executesql @cmd;

Finally this cursor iterates over the contents of the dbo.IndexDetails table showing the page contents for each page used by the table:

DBCC TRACEON (3604); /* send DBCC PAGE output to the client (messages tab) */
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT PageFID, PagePID
FROM dbo.IndexDetails id
WHERE id.PageType IN (1, 3, 4) /* data pages, and text pages */;
OPEN cur;
FETCH NEXT FROM cur INTO @fileID, @pageID;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (tempdb, @fileID, @pageID, 1)
    FETCH NEXT FROM cur INTO @fileID, @pageID;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF (3604); /* send DBCC PAGE output to the error log (default) */

The interesting bits of output from the cursor are displayed on the Messages tab in SSMS.

This is the first page.  Notice the Record Type = PRIMARY_RECORD:

PAGE: (1:319464)

BUFFER:

BUF @0x0000000203A1C0C0

bpage = 0x000000017018E000          bhash = 0x0000000000000000          bpageno = (1:319464)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 950                         bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x000000017018E000

m_pageId = (1:319464)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 178209m_indexId (AllocUnitId.idInd) = 7680
Metadata: AllocUnitId = 2161727832816943104
Metadata: PartitionId = 1152921515708121088                              Metadata: IndexId = 0
Metadata: ObjectId = 1013578649     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 4                         m_slotCnt = 1                       m_freeCnt = 992
m_freeData = 7198                   m_reservedCnt = 0                   m_lsn = (60:220956:448)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED
PFS (1:315432) = 0x43 ALLOCATED  95_PCT_FULL                             DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 7102, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 7102
Memory Dump @0x000000000DBAA060

0000000000000000:  0......ü..6.N.f.N.6.
0000000000000014:  ....î.Ö.¾..........>
0000000000000028:  ..è...àß............
000000000000003C:  ..I...è...àß........
0000000000000050:  ......2_..è...àß....
0000000000000064:  ..DDDDDDDDDDDDDDDDDD
0000000000000078:  DDDDDDDDDDDDDDDDDDDD
000000000000008C:  DDDDDDDDDDDDDDDDDDDD
00000000000000A0:  DDDDDDDDDDDDDDDDDDDD
00000000000000B4:  DDDDDDDDDDDDDDDDDDDD
00000000000000C8:  DDDDDDDDDDDDDDDDDDDD
00000000000000DC:  DDDDDDDDDDDDDDDDDDDD
00000000000000F0:  DDDDDDDDDDDDDDDDDDDD
0000000000000104:  DDDDDDDDDDDDDDDDDDDD
0000000000000118:  DDDDDDDDDDDDDDDDDDDD
000000000000012C:  DDDDDDDDDDDDDDDDDDDD
0000000000000140:  DDDDDDDDDDDDDDDDDDDD
0000000000000154:  DDDDDDDDDDDDDDDDDDDD
0000000000000168:  DDDDDDDDDDDDDDDDDDDD
000000000000017C:  DDDDDDDDDDDDDDDDDDDD
0000000000000190:  DDDDDDDDDDDDDDDDDDDD
00000000000001A4:  DDDDDDDDDDDDDDDDDDDD
00000000000001B8:  DDDDDDDDDDDDDDDDDDDD
00000000000001CC:  DDDDDDDDDDDDDDDDDDDD
00000000000001E0:  DDDDDDDDDDDDDDDDDDDD
00000000000001F4:  DDDDDDDDDDDDDDDDDDDD
0000000000000208:  DDDDDDDDDDDDDDDDDDDD
000000000000021C:  DDDDDDDDDDDDDDDDDDDD
0000000000000230:  DDDDDDDDDDDDDDDDDDDD
0000000000000244:  DDDDDDDDDDDDDDDDDDDD
0000000000000258:  DDDDDDDDDDDDDDDDDDDD
000000000000026C:  DDDDDDDDDDDDDDDDDDDD
0000000000000280:  DDDDDDDDDDDDDDDDDDDD
0000000000000294:  DDDDDDDDDDDDDDDDDDDD
00000000000002A8:  DDDDDDDDDDDDDDDDDDDD
00000000000002BC:  DDDDDDDDDDDDDDDDDDDD
00000000000002D0:  DDDDDDDDDDDDDDDDDDDD
00000000000002E4:  DDDDDDDDDDDDDDDDDDDD
00000000000002F8:  DDDDDDDDDDDDDDDDDDDD
000000000000030C:  DDDDDDDDDDDDDDDDDDDD
0000000000000320:  DDDDDDDDDDDDDDDDDDDD
0000000000000334:  DDDDDDDDDDDDDDDDDDDD
0000000000000348:  DDDDDDDDDDDDDDDDDDDD
000000000000035C:  DDDDDDDDDDDDDDDDDDDD
0000000000000370:  DDDDDDDDDDDDDDDDDDDD
0000000000000384:  DDDDDDDDDDDDDDDDDDDD
0000000000000398:  DDDDDDDDDDDDDDDDDDDD
00000000000003AC:  DDDDDDDDDDDDDDDDDDDD
00000000000003C0:  DDDDDDDDDDDDDDDDDDDD
00000000000003D4:  DDDDDDDDDDDDDDDDDDDD
00000000000003E8:  DDDDDDDDDDDDDDDDDDDD
00000000000003FC:  DDDDDDDDDDDDDDDDDDDD
0000000000000410:  DDDDDDDDDDDDDDDDDDDD
0000000000000424:  DDDDDDDDDDDDDDDDDDDD
0000000000000438:  DDDDDDDDDDDDDDDDDDDD
000000000000044C:  DDEEEEEEEEEEEEEEEEEE
0000000000000460:  EEEEEEEEEEEEEEEEEEEE
0000000000000474:  EEEEEEEEEEEEEEEEEEEE
0000000000000488:  EEEEEEEEEEEEEEEEEEEE
000000000000049C:  EEEEEEEEEEEEEEEEEEEE
00000000000004B0:  EEEEEEEEEEEEEEEEEEEE
00000000000004C4:  EEEEEEEEEEEEEEEEEEEE
00000000000004D8:  EEEEEEEEEEEEEEEEEEEE
00000000000004EC:  EEEEEEEEEEEEEEEEEEEE
0000000000000500:  EEEEEEEEEEEEEEEEEEEE
0000000000000514:  EEEEEEEEEEEEEEEEEEEE
0000000000000528:  EEEEEEEEEEEEEEEEEEEE
000000000000053C:  EEEEEEEEEEEEEEEEEEEE
0000000000000550:  EEEEEEEEEEEEEEEEEEEE
0000000000000564:  EEEEEEEEEEEEEEEEEEEE
0000000000000578:  EEEEEEEEEEEEEEEEEEEE
000000000000058C:  EEEEEEEEEEEEEEEEEEEE
00000000000005A0:  EEEEEEEEEEEEEEEEEEEE
00000000000005B4:  EEEEEEEEEEEEEEEEEEEE
00000000000005C8:  EEEEEEEEEEEEEEEEEEEE
00000000000005DC:  EEEEEEEEEEEEEEEEEEEE
00000000000005F0:  EEEEEEEEEEEEEEEEEEEE
0000000000000604:  EEEEEEEEEEEEEEEEEEEE
0000000000000618:  EEEEEEEEEEEEEEEEEEEE
000000000000062C:  EEEEEEEEEEEEEEEEEEEE
0000000000000640:  EEEEEEEEEEEEEEEEEEEE
0000000000000654:  EEEEEEEEEEEEEEEEEEEE
0000000000000668:  EEEEEEEEEEEEEEEEEEEE
000000000000067C:  EEEEEEEEEEEEEEEEEEEE
0000000000000690:  EEEEEEEEEEEEEEEEEEEE
00000000000006A4:  EEEEEEEEEEEEEEEEEEEE
00000000000006B8:  EEEEEEEEEEEEEEEEEEEE
00000000000006CC:  EEEEEEEEEEEEEEEEEEEE
00000000000006E0:  EEEEEEEEEEEEEEEEEEEE
00000000000006F4:  EEEEEEEEEEEEEEEEEEEE
0000000000000708:  EEEEEEEEEEEEEEEEEEEE
000000000000071C:  EEEEEEEEEEEEEEEEEEEE
0000000000000730:  EEEEEEEEEEEEEEEEEEEE
0000000000000744:  EEEEEEEEEEEEEEEEEEEE
0000000000000758:  EEEEEEEEEEEEEEEEEEEE
000000000000076C:  EEEEEEEEEEEEEEEEEEEE
0000000000000780:  EEEEEEEEEEEEEEEEEEEE
0000000000000794:  EEEEEEEEEEEEEEEEEEEE
00000000000007A8:  EEEEEEEEEEEEEEEEEEEE
00000000000007BC:  EEEEEEEEEEEEEEEEEEEE
00000000000007D0:  EEEEEEEEEEEEEEEEEEEE
00000000000007E4:  EEEEEEEEEEEEEEEEEEEE
00000000000007F8:  EEEEEEEEEEEEEEEEEEEE
000000000000080C:  EEEEEEEEEEEEEEEEEEEE
0000000000000820:  EEEEEEEEEEEEEEEEEEEE
0000000000000834:  EEFFFFFFFFFFFFFFFFFF
0000000000000848:  FFFFFFFFFFFFFFFFFFFF
000000000000085C:  FFFFFFFFFFFFFFFFFFFF
0000000000000870:  FFFFFFFFFFFFFFFFFFFF
0000000000000884:  FFFFFFFFFFFFFFFFFFFF
0000000000000898:  FFFFFFFFFFFFFFFFFFFF
00000000000008AC:  FFFFFFFFFFFFFFFFFFFF
00000000000008C0:  FFFFFFFFFFFFFFFFFFFF
00000000000008D4:  FFFFFFFFFFFFFFFFFFFF
00000000000008E8:  FFFFFFFFFFFFFFFFFFFF
00000000000008FC:  FFFFFFFFFFFFFFFFFFFF
0000000000000910:  FFFFFFFFFFFFFFFFFFFF
0000000000000924:  FFFFFFFFFFFFFFFFFFFF
0000000000000938:  FFFFFFFFFFFFFFFFFFFF
000000000000094C:  FFFFFFFFFFFFFFFFFFFF
0000000000000960:  FFFFFFFFFFFFFFFFFFFF
0000000000000974:  FFFFFFFFFFFFFFFFFFFF
0000000000000988:  FFFFFFFFFFFFFFFFFFFF
000000000000099C:  FFFFFFFFFFFFFFFFFFFF
00000000000009B0:  FFFFFFFFFFFFFFFFFFFF
00000000000009C4:  FFFFFFFFFFFFFFFFFFFF
00000000000009D8:  FFFFFFFFFFFFFFFFFFFF
00000000000009EC:  FFFFFFFFFFFFFFFFFFFF
0000000000000A00:  FFFFFFFFFFFFFFFFFFFF
0000000000000A14:  FFFFFFFFFFFFFFFFFFFF
0000000000000A28:  FFFFFFFFFFFFFFFFFFFF
0000000000000A3C:  FFFFFFFFFFFFFFFFFFFF
0000000000000A50:  FFFFFFFFFFFFFFFFFFFF
0000000000000A64:  FFFFFFFFFFFFFFFFFFFF
0000000000000A78:  FFFFFFFFFFFFFFFFFFFF
0000000000000A8C:  FFFFFFFFFFFFFFFFFFFF
0000000000000AA0:  FFFFFFFFFFFFFFFFFFFF
0000000000000AB4:  FFFFFFFFFFFFFFFFFFFF
0000000000000AC8:  FFFFFFFFFFFFFFFFFFFF
0000000000000ADC:  FFFFFFFFFFFFFFFFFFFF
0000000000000AF0:  FFFFFFFFFFFFFFFFFFFF
0000000000000B04:  FFFFFFFFFFFFFFFFFFFF
0000000000000B18:  FFFFFFFFFFFFFFFFFFFF
0000000000000B2C:  FFFFFFFFFFFFFFFFFFFF
0000000000000B40:  FFFFFFFFFFFFFFFFFFFF
0000000000000B54:  FFFFFFFFFFFFFFFFFFFF
0000000000000B68:  FFFFFFFFFFFFFFFFFFFF
0000000000000B7C:  FFFFFFFFFFFFFFFFFFFF
0000000000000B90:  FFFFFFFFFFFFFFFFFFFF
0000000000000BA4:  FFFFFFFFFFFFFFFFFFFF
0000000000000BB8:  FFFFFFFFFFFFFFFFFFFF
0000000000000BCC:  FFFFFFFFFFFFFFFFFFFF
0000000000000BE0:  FFFFFFFFFFFFFFFFFFFF
0000000000000BF4:  FFFFFFFFFFFFFFFFFFFF
0000000000000C08:  FFFFFFFFFFFFFFFFFFFF
0000000000000C1C:  FFGGGGGGGGGGGGGGGGGG
0000000000000C30:  GGGGGGGGGGGGGGGGGGGG
0000000000000C44:  GGGGGGGGGGGGGGGGGGGG
0000000000000C58:  GGGGGGGGGGGGGGGGGGGG
0000000000000C6C:  GGGGGGGGGGGGGGGGGGGG
0000000000000C80:  GGGGGGGGGGGGGGGGGGGG
0000000000000C94:  GGGGGGGGGGGGGGGGGGGG
0000000000000CA8:  GGGGGGGGGGGGGGGGGGGG
0000000000000CBC:  GGGGGGGGGGGGGGGGGGGG
0000000000000CD0:  GGGGGGGGGGGGGGGGGGGG
0000000000000E38:  GGGGGGGGGGGGGGGGGGGG
0000000000000E4C:  GGGGGGGGGGGGGGGGGGGG
0000000000000E60:  GGGGGGGGGGGGGGGGGGGG
0000000000000E74:  GGGGGGGGGGGGGGGGGGGG
0000000000000E88:  GGGGGGGGGGGGGGGGGGGG
0000000000000E9C:  GGGGGGGGGGGGGGGGGGGG
0000000000000EB0:  GGGGGGGGGGGGGGGGGGGG
0000000000000EC4:  GGGGGGGGGGGGGGGGGGGG
0000000000000ED8:  GGGGGGGGGGGGGGGGGGGG
0000000000000EEC:  GGGGGGGGGGGGGGGGGGGG
0000000000000F00:  GGGGGGGGGGGGGGGGGGGG
0000000000000F78:  GGGGGGGGGGGGGGGGGGGG
0000000000000F8C:  GGGGGGGGGGGGGGGGGGGG
0000000000000FA0:  GGGGGGGGGGGGGGGGGGGG
0000000000000FB4:  GGGGGGGGGGGGGGGGGGGG
0000000000000FC8:  GGGGGGGGGGGGGGGGGGGG
0000000000000FDC:  GGGGGGGGGGGGGGGGGGGG
0000000000000FF0:  GGGGGGGGGGGGGGGGGGGG
0000000000001004:  GGHHHHHHHHHHHHHHHHHH
0000000000001018:  HHHHHHHHHHHHHHHHHHHH
000000000000102C:  HHHHHHHHHHHHHHHHHHHH
0000000000001040:  HHHHHHHHHHHHHHHHHHHH
0000000000001054:  HHHHHHHHHHHHHHHHHHHH
0000000000001194:  HHHHHHHHHHHHHHHHHHHH
00000000000011A8:  HHHHHHHHHHHHHHHHHHHH
00000000000011BC:  HHHHHHHHHHHHHHHHHHHH
00000000000011D0:  HHHHHHHHHHHHHHHHHHHH
00000000000011E4:  HHHHHHHHHHHHHHHHHHHH
00000000000011F8:  HHHHHHHHHHHHHHHHHHHH
000000000000120C:  HHHHHHHHHHHHHHHHHHHH
0000000000001220:  HHHHHHHHHHHHHHHHHHHH
0000000000001234:  HHHHHHHHHHHHHHHHHHHH
0000000000001248:  HHHHHHHHHHHHHHHHHHHH
000000000000125C:  HHHHHHHHHHHHHHHHHHHH
0000000000001270:  HHHHHHHHHHHHHHHHHHHH
0000000000001284:  HHHHHHHHHHHHHHHHHHHH
0000000000001298:  HHHHHHHHHHHHHHHHHHHH
00000000000012AC:  HHHHHHHHHHHHHHHHHHHH
00000000000012C0:  HHHHHHHHHHHHHHHHHHHH
00000000000012D4:  HHHHHHHHHHHHHHHHHHHH
00000000000012E8:  HHHHHHHHHHHHHHHHHHHH
00000000000012FC:  HHHHHHHHHHHHHHHHHHHH
0000000000001310:  HHHHHHHHHHHHHHHHHHHH
0000000000001324:  HHHHHHHHHHHHHHHHHHHH
0000000000001338:  HHHHHHHHHHHHHHHHHHHH
000000000000134C:  HHHHHHHHHHHHHHHHHHHH
0000000000001360:  HHHHHHHHHHHHHHHHHHHH
0000000000001374:  HHHHHHHHHHHHHHHHHHHH
0000000000001388:  HHHHHHHHHHHHHHHHHHHH
000000000000139C:  HHHHHHHHHHHHHHHHHHHH
00000000000013B0:  HHHHHHHHHHHHHHHHHHHH
00000000000013C4:  HHHHHHHHHHHHHHHHHHHH
00000000000013D8:  HHHHHHHHHHHHHHHHHHHH
00000000000013EC:  HHIIIIIIIIIIIIIIIIII
0000000000001400:  IIIIIIIIIIIIIIIIIIII
0000000000001414:  IIIIIIIIIIIIIIIIIIII
0000000000001428:  IIIIIIIIIIIIIIIIIIII
000000000000143C:  IIIIIIIIIIIIIIIIIIII
0000000000001450:  IIIIIIIIIIIIIIIIIIII
0000000000001464:  IIIIIIIIIIIIIIIIIIII
0000000000001478:  IIIIIIIIIIIIIIIIIIII
000000000000148C:  IIIIIIIIIIIIIIIIIIII
00000000000014A0:  IIIIIIIIIIIIIIIIIIII
00000000000014B4:  IIIIIIIIIIIIIIIIIIII
00000000000014C8:  IIIIIIIIIIIIIIIIIIII
00000000000014DC:  IIIIIIIIIIIIIIIIIIII
00000000000014F0:  IIIIIIIIIIIIIIIIIIII
0000000000001504:  IIIIIIIIIIIIIIIIIIII
0000000000001518:  IIIIIIIIIIIIIIIIIIII
000000000000152C:  IIIIIIIIIIIIIIIIIIII
0000000000001540:  IIIIIIIIIIIIIIIIIIII
0000000000001554:  IIIIIIIIIIIIIIIIIIII
0000000000001568:  IIIIIIIIIIIIIIIIIIII
000000000000157C:  IIIIIIIIIIIIIIIIIIII
0000000000001590:  IIIIIIIIIIIIIIIIIIII
00000000000015A4:  IIIIIIIIIIIIIIIIIIII
00000000000015B8:  IIIIIIIIIIIIIIIIIIII
00000000000015CC:  IIIIIIIIIIIIIIIIIIII
00000000000015E0:  IIIIIIIIIIIIIIIIIIII
00000000000015F4:  IIIIIIIIIIIIIIIIIIII
0000000000001608:  IIIIIIIIIIIIIIIIIIII
000000000000161C:  IIIIIIIIIIIIIIIIIIII
0000000000001630:  IIIIIIIIIIIIIIIIIIII
0000000000001644:  IIIIIIIIIIIIIIIIIIII
0000000000001658:  IIIIIIIIIIIIIIIIIIII
000000000000166C:  IIIIIIIIIIIIIIIIIIII
0000000000001680:  IIIIIIIIIIIIIIIIIIII
0000000000001694:  IIIIIIIIIIIIIIIIIIII
00000000000016A8:  IIIIIIIIIIIIIIIIIIII
00000000000016BC:  IIIIIIIIIIIIIIIIIIII
00000000000016D0:  IIIIIIIIIIIIIIIIIIII
00000000000016E4:  IIIIIIIIIIIIIIIIIIII
00000000000016F8:  IIIIIIIIIIIIIIIIIIII
000000000000170C:  IIIIIIIIIIIIIIIIIIII
0000000000001720:  IIIIIIIIIIIIIIIIIIII
0000000000001734:  IIIIIIIIIIIIIIIIIIII
0000000000001748:  IIIIIIIIIIIIIIIIIIII
000000000000175C:  IIIIIIIIIIIIIIIIIIII
0000000000001770:  IIIIIIIIIIIIIIIIIIII
0000000000001784:  IIIIIIIIIIIIIIIIIIII
0000000000001798:  IIIIIIIIIIIIIIIIIIII
00000000000017AC:  IIIIIIIIIIIIIIIIIIII
00000000000017C0:  IIIIIIIIIIIIIIIIIIII
00000000000017D4:  IIJJJJJJJJJJJJJJJJJJ
00000000000017E8:  JJJJJJJJJJJJJJJJJJJJ
00000000000017FC:  JJJJJJJJJJJJJJJJJJJJ
0000000000001810:  JJJJJJJJJJJJJJJJJJJJ
0000000000001824:  JJJJJJJJJJJJJJJJJJJJ
0000000000001838:  JJJJJJJJJJJJJJJJJJJJ
000000000000184C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001860:  JJJJJJJJJJJJJJJJJJJJ
0000000000001874:  JJJJJJJJJJJJJJJJJJJJ
0000000000001888:  JJJJJJJJJJJJJJJJJJJJ
000000000000189C:  JJJJJJJJJJJJJJJJJJJJ
00000000000018B0:  JJJJJJJJJJJJJJJJJJJJ
00000000000018C4:  JJJJJJJJJJJJJJJJJJJJ
00000000000018D8:  JJJJJJJJJJJJJJJJJJJJ
00000000000018EC:  JJJJJJJJJJJJJJJJJJJJ
0000000000001900:  JJJJJJJJJJJJJJJJJJJJ
0000000000001914:  JJJJJJJJJJJJJJJJJJJJ
0000000000001928:  JJJJJJJJJJJJJJJJJJJJ
000000000000193C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001950:  JJJJJJJJJJJJJJJJJJJJ
0000000000001964:  JJJJJJJJJJJJJJJJJJJJ
0000000000001978:  JJJJJJJJJJJJJJJJJJJJ
000000000000198C:  JJJJJJJJJJJJJJJJJJJJ
00000000000019A0:  JJJJJJJJJJJJJJJJJJJJ
00000000000019B4:  JJJJJJJJJJJJJJJJJJJJ
00000000000019C8:  JJJJJJJJJJJJJJJJJJJJ
00000000000019DC:  JJJJJJJJJJJJJJJJJJJJ
00000000000019F0:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A04:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A18:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A2C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A40:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A54:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A68:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A7C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A90:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AA4:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AB8:  JJJJJJJJJJJJJJJJJJJJ
0000000000001ACC:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AE0:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AF4:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B08:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B1C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B30:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B44:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B58:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B6C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B80:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B94:  JJJJJJJJJJJJJJJJJJJJ
0000000000001BA8:  JJJJJJJJJJJJJJJJJJJJ
0000000000001BBC:  JJ

OFFSET TABLE:

Row - Offset
0 (0x0) - 96 (0x60)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've removed the hexaxdecimal content of the actual page dump to keep my post under the 30,000 character limit.  As you can see from the page dump above, the primary page in my example does not contain the first 3 columns, A, B, and C - they are stored in the row overflow region, shown next, with Record Type = BLOB_FRAGMENT:

PAGE: (1:319456)

BUFFER:

BUF @0x0000000203A19240

bpage = 0x0000000170186000          bhash = 0x0000000000000000          bpageno = (1:319456)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 950                         bstat = 0x10b
blog = 0x1212121c                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000170186000

m_pageId = (1:319456)               m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 178834m_indexId (AllocUnitId.idInd) = 7936
Metadata: AllocUnitId = 2233785426895831040
Metadata: PartitionId = 1152921515708121088                              Metadata: IndexId = 0
Metadata: ObjectId = 1013578649     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 3                       m_freeCnt = 5048
m_freeData = 3166                   m_reservedCnt = 0                   m_lsn = (60:220956:430)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED
PFS (1:315432) = 0x41 ALLOCATED  50_PCT_FULL                             DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 1014, DumpStyle BYTE

Record Type = BLOB_FRAGMENT         Record Attributes =                 Record Size = 1014

Memory Dump @0x000000000DBAA060

0000000000000000:  ..ö....>......AAAAAA
0000000000000014:  AAAAAAAAAAAAAAAAAAAA
0000000000000028:  AAAAAAAAAAAAAAAAAAAA
000000000000003C:  AAAAAAAAAAAAAAAAAAAA
0000000000000050:  AAAAAAAAAAAAAAAAAAAA
0000000000000064:  AAAAAAAAAAAAAAAAAAAA
0000000000000078:  AAAAAAAAAAAAAAAAAAAA
000000000000008C:  AAAAAAAAAAAAAAAAAAAA
00000000000000A0:  AAAAAAAAAAAAAAAAAAAA
00000000000000B4:  AAAAAAAAAAAAAAAAAAAA
00000000000000C8:  AAAAAAAAAAAAAAAAAAAA
00000000000000DC:  AAAAAAAAAAAAAAAAAAAA
00000000000000F0:  AAAAAAAAAAAAAAAAAAAA
0000000000000104:  AAAAAAAAAAAAAAAAAAAA
0000000000000118:  AAAAAAAAAAAAAAAAAAAA
000000000000012C:  AAAAAAAAAAAAAAAAAAAA
0000000000000140:  AAAAAAAAAAAAAAAAAAAA
0000000000000154:  AAAAAAAAAAAAAAAAAAAA
0000000000000168:  AAAAAAAAAAAAAAAAAAAA
000000000000017C:  AAAAAAAAAAAAAAAAAAAA
0000000000000190:  AAAAAAAAAAAAAAAAAAAA
00000000000001A4:  AAAAAAAAAAAAAAAAAAAA
00000000000001B8:  AAAAAAAAAAAAAAAAAAAA
00000000000001CC:  AAAAAAAAAAAAAAAAAAAA
00000000000001E0:  AAAAAAAAAAAAAAAAAAAA
00000000000001F4:  AAAAAAAAAAAAAAAAAAAA
0000000000000208:  AAAAAAAAAAAAAAAAAAAA
000000000000021C:  AAAAAAAAAAAAAAAAAAAA
0000000000000230:  AAAAAAAAAAAAAAAAAAAA
0000000000000244:  AAAAAAAAAAAAAAAAAAAA
0000000000000258:  AAAAAAAAAAAAAAAAAAAA
000000000000026C:  AAAAAAAAAAAAAAAAAAAA
0000000000000280:  AAAAAAAAAAAAAAAAAAAA
0000000000000294:  AAAAAAAAAAAAAAAAAAAA
00000000000002A8:  AAAAAAAAAAAAAAAAAAAA
00000000000002BC:  AAAAAAAAAAAAAAAAAAAA
00000000000002D0:  AAAAAAAAAAAAAAAAAAAA
00000000000002E4:  AAAAAAAAAAAAAAAAAAAA
00000000000002F8:  AAAAAAAAAAAAAAAAAAAA
000000000000030C:  AAAAAAAAAAAAAAAAAAAA
0000000000000320:  AAAAAAAAAAAAAAAAAAAA
0000000000000334:  AAAAAAAAAAAAAAAAAAAA
0000000000000348:  AAAAAAAAAAAAAAAAAAAA
000000000000035C:  AAAAAAAAAAAAAAAAAAAA
0000000000000370:  AAAAAAAAAAAAAAAAAAAA
0000000000000384:  AAAAAAAAAAAAAAAAAAAA
0000000000000398:  AAAAAAAAAAAAAAAAAAAA
00000000000003AC:  AAAAAAAAAAAAAAAAAAAA
00000000000003C0:  AAAAAAAAAAAAAAAAAAAA
00000000000003D4:  AAAAAAAAAAAAAAAAAAAA
00000000000003E8:  AAAAAAAAAAAAAA

Slot 1, Offset 0x464, Length 1014, DumpStyle BYTE

Record Type = BLOB_FRAGMENT         Record Attributes =                 Record Size = 1014

Memory Dump @0x000000000DBAA464

0000000000000000:  ..ö...I.......BBBBBB
0000000000000014:  BBBBBBBBBBBBBBBBBBBB
0000000000000028:  BBBBBBBBBBBBBBBBBBBB
000000000000003C:  BBBBBBBBBBBBBBBBBBBB
0000000000000050:  BBBBBBBBBBBBBBBBBBBB
0000000000000064:  BBBBBBBBBBBBBBBBBBBB
0000000000000078:  BBBBBBBBBBBBBBBBBBBB
000000000000008C:  BBBBBBBBBBBBBBBBBBBB
00000000000000A0:  BBBBBBBBBBBBBBBBBBBB
00000000000000B4:  BBBBBBBBBBBBBBBBBBBB
00000000000000C8:  BBBBBBBBBBBBBBBBBBBB
00000000000000DC:  BBBBBBBBBBBBBBBBBBBB
00000000000000F0:  BBBBBBBBBBBBBBBBBBBB
0000000000000104:  BBBBBBBBBBBBBBBBBBBB
0000000000000118:  BBBBBBBBBBBBBBBBBBBB
000000000000012C:  BBBBBBBBBBBBBBBBBBBB
0000000000000140:  BBBBBBBBBBBBBBBBBBBB
0000000000000154:  BBBBBBBBBBBBBBBBBBBB
0000000000000168:  BBBBBBBBBBBBBBBBBBBB
000000000000017C:  BBBBBBBBBBBBBBBBBBBB
0000000000000190:  BBBBBBBBBBBBBBBBBBBB
00000000000001A4:  BBBBBBBBBBBBBBBBBBBB
00000000000001B8:  BBBBBBBBBBBBBBBBBBBB
00000000000001CC:  BBBBBBBBBBBBBBBBBBBB
00000000000001E0:  BBBBBBBBBBBBBBBBBBBB
00000000000001F4:  BBBBBBBBBBBBBBBBBBBB
0000000000000208:  BBBBBBBBBBBBBBBBBBBB
000000000000021C:  BBBBBBBBBBBBBBBBBBBB
0000000000000230:  BBBBBBBBBBBBBBBBBBBB
0000000000000244:  BBBBBBBBBBBBBBBBBBBB
0000000000000258:  BBBBBBBBBBBBBBBBBBBB
000000000000026C:  BBBBBBBBBBBBBBBBBBBB
0000000000000280:  BBBBBBBBBBBBBBBBBBBB
0000000000000294:  BBBBBBBBBBBBBBBBBBBB
00000000000002A8:  BBBBBBBBBBBBBBBBBBBB
00000000000002BC:  BBBBBBBBBBBBBBBBBBBB
00000000000002D0:  BBBBBBBBBBBBBBBBBBBB
00000000000002E4:  BBBBBBBBBBBBBBBBBBBB
00000000000002F8:  BBBBBBBBBBBBBBBBBBBB
000000000000030C:  BBBBBBBBBBBBBBBBBBBB
0000000000000320:  BBBBBBBBBBBBBBBBBBBB
0000000000000334:  BBBBBBBBBBBBBBBBBBBB
0000000000000348:  BBBBBBBBBBBBBBBBBBBB
000000000000035C:  BBBBBBBBBBBBBBBBBBBB
0000000000000370:  BBBBBBBBBBBBBBBBBBBB
0000000000000384:  BBBBBBBBBBBBBBBBBBBB
0000000000000398:  BBBBBBBBBBBBBBBBBBBB
00000000000003AC:  BBBBBBBBBBBBBBBBBBBB
00000000000003C0:  BBBBBBBBBBBBBBBBBBBB
00000000000003D4:  BBBBBBBBBBBBBBBBBBBB
00000000000003E8:  BBBBBBBBBBBBBB

Slot 2, Offset 0x868, Length 1014, DumpStyle BYTE

Record Type = BLOB_FRAGMENT         Record Attributes =                 Record Size = 1014

Memory Dump @0x000000000DBAA868

0000000000000000:   ..ö...2_......CCCCCC
0000000000000014:   CCCCCCCCCCCCCCCCCCCC
0000000000000028:   CCCCCCCCCCCCCCCCCCCC
000000000000003C:   CCCCCCCCCCCCCCCCCCCC
0000000000000050:   CCCCCCCCCCCCCCCCCCCC
0000000000000064:   CCCCCCCCCCCCCCCCCCCC
0000000000000078:   CCCCCCCCCCCCCCCCCCCC
000000000000008C:   CCCCCCCCCCCCCCCCCCCC
00000000000000A0:   CCCCCCCCCCCCCCCCCCCC
00000000000000B4:   CCCCCCCCCCCCCCCCCCCC
00000000000000C8:   CCCCCCCCCCCCCCCCCCCC
00000000000000DC:   CCCCCCCCCCCCCCCCCCCC
00000000000000F0:   CCCCCCCCCCCCCCCCCCCC
0000000000000104:   CCCCCCCCCCCCCCCCCCCC
0000000000000118:   CCCCCCCCCCCCCCCCCCCC
000000000000012C:   CCCCCCCCCCCCCCCCCCCC
0000000000000140:   CCCCCCCCCCCCCCCCCCCC
0000000000000154:   CCCCCCCCCCCCCCCCCCCC
0000000000000168:   CCCCCCCCCCCCCCCCCCCC
000000000000017C:   CCCCCCCCCCCCCCCCCCCC
0000000000000190:   CCCCCCCCCCCCCCCCCCCC
00000000000001A4:   CCCCCCCCCCCCCCCCCCCC
00000000000001B8:   CCCCCCCCCCCCCCCCCCCC
00000000000001CC:   CCCCCCCCCCCCCCCCCCCC
00000000000001E0:   CCCCCCCCCCCCCCCCCCCC
00000000000001F4:   CCCCCCCCCCCCCCCCCCCC
0000000000000208:   CCCCCCCCCCCCCCCCCCCC
000000000000021C:   CCCCCCCCCCCCCCCCCCCC
0000000000000230:   CCCCCCCCCCCCCCCCCCCC
0000000000000244:   CCCCCCCCCCCCCCCCCCCC
0000000000000258:   CCCCCCCCCCCCCCCCCCCC
000000000000026C:   CCCCCCCCCCCCCCCCCCCC
0000000000000280:   CCCCCCCCCCCCCCCCCCCC
0000000000000294:   CCCCCCCCCCCCCCCCCCCC
00000000000002A8:   CCCCCCCCCCCCCCCCCCCC
00000000000002BC:   CCCCCCCCCCCCCCCCCCCC
00000000000002D0:   CCCCCCCCCCCCCCCCCCCC
00000000000002E4:   CCCCCCCCCCCCCCCCCCCC
00000000000002F8:   CCCCCCCCCCCCCCCCCCCC
000000000000030C:   CCCCCCCCCCCCCCCCCCCC
0000000000000320:   CCCCCCCCCCCCCCCCCCCC
0000000000000334:   CCCCCCCCCCCCCCCCCCCC
0000000000000348:   CCCCCCCCCCCCCCCCCCCC
000000000000035C:   CCCCCCCCCCCCCCCCCCCC
0000000000000370:   CCCCCCCCCCCCCCCCCCCC
0000000000000384:   CCCCCCCCCCCCCCCCCCCC
0000000000000398:   CCCCCCCCCCCCCCCCCCCC
00000000000003AC:   CCCCCCCCCCCCCCCCCCCC
00000000000003C0:   CCCCCCCCCCCCCCCCCCCC
00000000000003D4:   CCCCCCCCCCCCCCCCCCCC
00000000000003E8:   CCCCCCCCCCCCCC


PostgreSQL, merge join results I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results.

Table "job" : parent_id, target_id, start_date, end_date, jobposition_id

Table "study" : parent_id, target_id, start_date, end_date

At this moment if I do something like :

SELECT *
FROM user AS u
LEFT JOIN study AS s ON s.parent_id=u.id OR s.target_id=u.id
LEFT JOIN job AS j ON j.parent_id=u.id OR j.target_id=u.id
WHERE u.id=2;

I got a resultset with every common column doubled (I have 2 times parent_id, target_id, start_date, end_date).

How can I get a simple resultset with only one column parent_id, etc (and a jobposition_id column set to NULL for every study result) ?

Thanks

Your query uses SELECT * ... which will select every column from the tables (in the order that they exist within the tables).  So if you have:

CREATE TABLE t1 (
  id bigserial primary key, -- bleh - never use just "id" .. this is only for example
  desc text
);

CREATE TABLE t2 (
  id bigserial primary key,
  t1_id bigint references t1 (id),
  desc text
);

select
  *
from t1
join t2 on t1.id = t2.t1_id

The query will give you the following columns in your result set:

id, desc, id, t1_id, desc

The simple solution is to basically NEVER use "*" ... always SPECIFY which columns you want .. and then you can also ALIAS those columns..

select
  t1.id AS t1_id,
  t1.desc AS t1_desc,
  t2.id AS t2_id,
  t2.desc AS t2_desc
from t1
join t2 on t1.id = t2.t1_id;

This query will return the following column names

t1_id, t1_desc, t2_id, t2_desc

One major problem with using "*" in your queries is that if the table structure ever changes, it will affect every single query that is using that table, which will often times break code.  Where as if you are specifying the column names, the chances of that happening are significantly reduced.  The resulting error messages that the database will give you are also a lot easier to understand when it comes to "what broke?"