Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries I have a 'Users' table with columns user_email, user_company_id and user_status. The user_status column is an enum with values '1' or '0' which represents the users being either active or inactive. Is there a way to apply a unique constraint to these 3 columns such that it only allows one unique, active user email for a specific company but any number of duplicate entires for inactive emails?

E.g.: Consider a 'Users' table with the following entries

CREATE TABLE users(
  user_id BIGINT(10) PRIMARY KEY AUTO_INCREMENT,
  user_email VARCHAR(255) NOT NULL,
  user_companyid BIGINT(10) NOT NULL,
  user_status enum('1', '0'))

INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (1,'test1@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (2,'test2@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (3,'test1@gmail.com','777','1');

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | test1@gmail.com |            555 | 1
      2 | test2@gmail.com |            555 | 1
      3 | test1@gmail.com |            777 | 1

I shouldn't be able to add an existing, active email for a specfic company twice; the following should fail:

INSERT INTO users(user_id, user_email, user_companyid, user_status)
 VALUES (4,'test1@gmail.com','555','1');

If I update the status of one of the active users to '0' (inactive), I should be able to insert the same email again since the previous email status is inactive. The following should succeed:

UPDATE users SET user_status = '0' WHERE user_id = 1;

INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (4,'test1@gmail.com','555','1');

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | test1@gmail.com |            555 | 0
      2 | test2@gmail.com |            555 | 1
      3 | test1@gmail.com |            777 | 1
      4 | test1@gmail.com |            555 | 1

Also, the constraint should allow duplicate entries for inactive user emails. This should also succeed:

UPDATE users SET user_status = '0' WHERE user_id = 4;

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | test1@gmail.com |            555 | 0
      2 | test2@gmail.com |            555 | 1
      3 | test1@gmail.com |            777 | 1
      4 | test1@gmail.com |            555 | 0

This did not work because auto_increment columns cannot be referenced by a generated column, but I will add it anyhow since it demonstrates a technique that can be useful. The idea is to use a generated column, that when user_status = 0 maps to something guaranteed unique (primary key) and otherwise maps to a constant. Then this column can be included in a UNIQUE constraint together with the columns that should be unique under condition:

CREATE TABLE users
( user_id BIGINT PRIMARY KEY  -- auto_increment had to be removed
, user_email VARCHAR(255) NOT NULL
, user_companyid BIGINT NOT NULL
, user_status enum('1', '0')
, gencol BIGINT GENERATED ALWAYS as
    ( CASE WHEN user_status = 1
           THEN -1
           ELSE user_id
      END
    ) NOT NULL
);

ALTER TABLE users ADD CONSTRAINT ak1
    UNIQUE (user_email, user_companyid, gencol);

INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (1,'test1@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (2,'test2@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (3,'test1@gmail.com','777','1');

-- Fails
-- INSERT INTO users(user_id, user_email, user_companyid, user_status)
-- VALUES (4,'test1@gmail.com','555','1');

UPDATE users SET user_status = '0' WHERE user_id = 1;

-- Succeeds
INSERT INTO users(user_id, user_email, user_companyid, user_status)
VALUES (4,'test1@gmail.com','555','1');


Dynamically Define a Range in a Dimension I have an issue that I face every time I decide to build a cube, and I haven't found a way to overcome it yet.

The issue is how to allow the user to define a range of things automatically without having the need to hardcode them in the dimension. I will explain my problem in an example.

I have a table called Customers:

this is the data in the table:

I want to display the data in a pivot style and group up the Salary and Age in defined ranges like below:

I wrote this script and defined the ranges:

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = case
        when cast(salary as float) <= 500 then
            '0 - 500'
        when cast(salary as float) between 501 and 1000 then
            '501 - 1000'
        when cast(salary as float) between 1001 and 2000 then
            '1001 - 2000'
        when cast(salary as float) > 2000 then
            '2001+'
        end,
        [AgeRange] = case
        when cast(age as float) < 15 then
            'below 15'
        when cast(age as float) between 15 and 19 then
            '15 - 19'
        when cast(age as float) between 20 and 29 then
            '20 - 29'
        when cast(age as float) between 30 and 39 then
            '30 - 39'
        when cast(age as float) >= 40 then
            '40+'
        end
  FROM [Customers]
GO

My ranges are hard coded and defined. When I copy the data to Excel and view it in a pivot table, it appears like below:

My problem is I want to create a cube by converting the Customers table into a fact table and create 2 dimension tables SalaryDim & AgeDim.

The SalaryDim table has 2 columns (SalaryKey,SalaryRange) and the AgeDim table is similar (ageKey,AgeRange). My Customer fact table has:

Customer
[CustId]
[CustName]
[AgeKey] --> foreign Key to AgeDim
[Salarykey] --> foreign Key to SalaryDim

I still have to define my ranges inside these dimensions. Every time I connect an Excel pivot to my cube, I can only see these hardcoded defined ranges.

My question is how to define ranges dynamically from the pivot table directly, without creating the range dimensions like AgeDim and SalaryDim. I don't want to only be stuck to the ranges defined in the dimension.

The range defined is '0-25' , '26-30' , '31- 50'. I might want to change it to '0-20', '21-31' , '32-42' and so on, and users request different ranges every time.

Every time I change it, I have to change the dimension. How can I improve this process?

It would be great to have a solution implemented in the cube, so that whatever BI client tool that connects to the cube can define the ranges, but I wouldn't mind if there is a good way using Excel only.

HOW TO DO THIS WITH T-SQL:

As requested this is an alternative to my previous answer that showed how to do it per-user with Excel.  This answer shows how to do the same thing shared/centrally using T-SQL instead.  I do not know how to do Cubes, MDX or the SSAS stuff for this, so maybe Benoit or someone who does know that can post its equivalent...

1. Add SalaryRanges SQL Table and View

Create a new table called "SalaryRangeData" with the following command:

Create Table SalaryRangeData(MinVal INT Primary Key)

Add calculated columns by wrapping it in a View with this command:

CREATE VIEW SalaryRanges As
WITH
  cteSequence As
(
    Select  MinVal,
            ROW_NUMBER() OVER(Order By MinVal ASC) As Sequence
    From    SalaryRangeData
)
SELECT
    D.Sequence,
    D.MinVal,
    COALESCE(N.MinVal - 1, 2147483645)  As MaxVal,
    CAST(D.MinVal As Varchar(32))
    + COALESCE(' - ' + CAST(N.MinVal - 1 As Varchar(32)), '+')
                        As RangeVals
FROM        cteSequence As D
LEFT JOIN   cteSequence As N ON N.Sequence = D.Sequence + 1

Right-click on the table in SSMS and select "Edit Top 200 Rows".  Then enter the following values into the MinVal cells: 0, 501, 1001, and 2001 (order doesn't matter for SQL Server, it will create it for us).  Close the table-row editor and do a SELECT * FROM SalaryRanges to see all of the rows and range information.

2. Add AgeRanges SQL Table and View

Do the exact same steps as in #1 above, except replace all occurrences of "Salary" with "Age".  This should make the table "AgeRangeData" and the view "AgeRanges".

Enter the following values into the AgeRangeData [MinVal] column: 0, 15, 20, 30, and 40.

3. Add Ranges to The Data

Replace your SELECT statement with CASE expressions for retrieving the data and ranges with the following one:

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

4. Everything Else, The Same As Now

From here on, just do everything the same as you currently are.  The ranges should all show up in your PivotTable as they currently do.

5. Test The Magic

Go to the SalaryRangeData table-row editor in SSMS again and delete the existing rows and then insert the following values: 0, 101, 201, 301, ... 2001 (again, order doesn't matter for the T-SQL solution).  Go back to your PivotTable and refresh the data.  And just like the Excel solution, the PivotTable ranges should be automatically changed.

Addition

HOW ADD IT TO A CUBE:

1. Create a View

CREATE VIEW CustomerView As
SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

1. Create a a BI Project in Visual studio and add the CustomerView

Connect to the Database, and add the CustomerView View in the Data Source Views to be the Fact table

2. Create A cube and Define Measure & Dimension

we only need customerId, as a measure for customer count and will have the same fact table as a dimension

3. Add Attributes to the Dimension

4. Connect to Cube from Excel

5. View the Data of the cube in the Excel

6. for Any changes in the Ranges just reprocess the Dimension & cube

if you need to change the Ranges, change the data in the SalaryRangeData and AgeRangeData and then just reprocess the dimensions and the cube


Backing up database without transaction log When I backup a database is there a way to exclude the transaction log in that backup? Or am I thinking about this wrong and to restore the original database the content of the transaction log is required?

I cannot shrink the transaction log because the database is using replication and I would have to break the replication.

If I issue this command and then restore the database it comes back with a 50 GB log file:

BACKUP DATABASE DatabaseName TO DISK = 'c:\temp\Database.bak'

I have to change the restored database to simple and then shrink the log file before the 50GB is freed. I cannot change the original database to simple because it is a replicated copy.

It looks like your confusing various concepts here. Start with Transaction Log Management and Backup Overview topics in Books Online.

Is the root problem that when you restore the full backup to another server, the transaction log is consuming more space than you have available?

If so, the hack I described in an answer to the question Is it possible to restore sql-server bak and shrink the log at the same time? might be useful.

Is your transaction log growing, and growing, and growing because your database is in full recovery but you aren't taking log backups? See here and here.

  When I backup a database to a bak file is there a way to exclude the
  transaction log in that backup?

No. A data backup will include part of the transaction log, at least the section of the log that was generated since the backup started. It shouldn't however be a significant proportion of the log unless you have very long running transactions.

Note that when you restore a data backup, the database will be recreated with the same data and log file sizes as were present in the source database, regardless of how full the data file was or how large the active part of the log was. If this is the problem you're experiencing, see my earlier hack for working around a lack of space to restore.

  Or am I thinking about this wrong, to restore the original database is
  the content of the transaction log required?

See above, a proportion of the log will be included in a full backup.

  I cannot shrink the transaction log because the database is using
  replication and I would have to break the replication.

No reason why you can't shrink the log with replication in place (you'll only be able to shrink the inactive portion) but:

It won't make any difference to the size of your data backup.
It will probably grow back to it's current size.
The large transaction log is probably due to not taking log backups.
You shouldn't be shrinking the log.


What is difference between tempdb log and database log? In which circumstances these log files uses? As we all know there are two types of log file (*.ldf) comes into the picture whenever any large query getting executed 1. database log (transaction log) file 2. tempdb log.

Please describe me how SQL server uses thes files while any query executed?

2nd thing i want to know, when it is safe to shrink log files (database log, tempdb log)? In which case we shouldn't log files?

MS SQL Server is an ACID compliant RDBMS.  One of the critical tools involved in providing ACID compliance is a concept of a transaction log.  The transaction log is where the pages modified is saved, so it can revert them and put them back in the previous state if you need to rollback a transaction.  There is much to consider here but you want to ensure you size your VLFs and manage it in advance.

TempDB is a temporary area used by MS SQL Server to store objects like temp tables, join results, some caching, etc.  Unless you have performance issues, you generally don't want to worry about this, don't even back it up.  It get's rebuilt on startup.  It is good practice to create at least 2 or maybe up to 4 physical files for TempDB so you don't get PAGELATCH contention.

Paul Randall (An authority on MS SQL Server) recommends letting your transaction log be at whatever size it grows to after a full weeks activity after reindexing.  Really, unless it grows a lot, don't worry too much about it.  Just make sure to avoid growths.


Db design for a quiz with variable number of answer choices, and each worth different score in a different category? I'm working on a question/answer project and having a hard time coming up with a suitable database structure.

Each question can have from 1 - "n" answers, and each answer is associated with one of 6 predefined categories.

For example:

Q1) Which magazine would you prefer to read?

Science (+1 for Science category)
Wired (+1 for Science, +1 for Tech)
Vogue (+1 for Fashion)
Time (+1 for Information, +1 for Social)
Fortune (+1 for Business)
People (+1 for Social)

Q2) Have you taken an art class?

Yes (+1 for Artistic)
No (+0 for Artistic)

Q3) What's the best part of a movie?

Action (+1 for Science)
Story (+1 for Social)
Music (+1 for Artistic)
Acting (+1 for Artistic)

I know I should have one table for Questions, and a separate table for Answers linked by question_ID, but I'm not sure how to deal with a variable number of answer choices that are different for each question, and their corresponding categories.

Consider a data model like this:

You have a list of quesitons in the QUESTION table.  Each question can have two or more possible options for answers in the OPTION table.  For each option there are one or more category score changes which are noted in OPTION_CATEGORY.  When a USER takes the quiz their answers (i.e. choices of options) are recorded in ANSWER.

Note that with this design you can change someone's score in multiple categories with a single answer (such as in your example of reading WIRED magazine) and you also have the option of changing someone's score in a category by an increment other than 1 (as in your example of not having taken an art class).  You could even change someones score by more than 1, for example in your question 1 you could score +10 in the science category for prefering to read Philosophical Transactions of the Royal Society!


How to dispose of large tables with the least impact to log shipping? Have two semi large related tables (284,6876,609 rows - 156,785,176 KB and 4,2628,162 rows - 2,212,464 KB) in a log shipped db that needs to be either purged or dropped.   How can I dispose of these two tables while minimizing impact to log shipping?

If you DROP the tables, it'll cause minimal logs to be generated because it doesn't immediately force garbage collection to occur on the used space, it merely basically just removes the reference. You similarly can accomplish the same outcome with TRUNCATE (though I think on large tables there could be more overhead than DROP, I'll have to research this and update my answer accordingly).
This is a really good article that details why TRUNCATE and DROP operations are seemlessly quick and generate minimal logs, which is due to a feature of SQL Server called deferred drop.
Either way, avoid DELETE otherwise that will generate a ton of logs that will need to get shipped and reapplied at the target database.
This StackOverflow Question has multiple answers with good information about efficiently removing data from a large table (some maybe not specificly related to your question, but good for future reference or other users with similar problems).


Is it possible to exclude specific users in log activity of PostgreSQL? I need to monitor the activity of users in our databases. I have set the following parameters in postgresql.conf:

log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%t %a %d %h %u |'

However, I realize that most of the log files are filled with statements executed by the postgres user, which is used by scripts I wrote for maintenance tasks: recalculate materialized views, pg_dump, pg_restore, extract views as tabulated files, etc.
The result is daily log files having over 12 Mb in size.

Is there a way to exclude specific users' activity from the log?

Using the ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout.

Setting log_min_duration_statement=-1 (1st login):

psql console

P:\>psql -U postgres -h 132.156.208.45 -d my_db

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 0
(1 ligne)

my_db=# SELECT COUNT(*) FROM organisms;
 count
-------
   153
(1 ligne)

my_db=# ALTER ROLE postgres SET log_min_duration_statement=-1;
ALTER ROLE

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 0
(1 ligne)

my_db=# SELECT COUNT(*) FROM mv_rings;
 count
--------
 115270
(1 ligne)

my_db=# \q

Result on the log:

Note that within that session, all statements are visible in the log even after setting log_min_duration_statement=-1.

2015-10-15 14:14:01 EDT [unknown] my_db 123.456.789.012 postgres |LOG:  connection authorized: user=postgres database=my_db
2015-10-15 14:14:08 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 0.000 ms  statement: SHOW log_min_duration_statement;
2015-10-15 14:15:26 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 32.000 ms  statement: SELECT COUNT(*) FROM organisms;
2015-10-15 14:15:45 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 31.000 ms  statement: ALTER ROLE postgres SET log_min_duration_statement=-1;
2015-10-15 14:16:08 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 0.000 ms  statement: SHOW log_min_duration_statement;
2015-10-15 14:17:10 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 2059.000 ms  statement: SELECT COUNT(*) FROM mv_rings;
2015-10-15 14:17:29 EDT psql my_db 123.456.789.012 postgres |LOG:  disconnection: session time: 0:03:27.450 user=postgres database=my_db host=123.456.789.012 port=65269

Seeing the effect of log_min_duration_statement (2nd login):

psql console

P:\>psql -U postgres -h 132.156.208.45 -d my_db

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 ligne)

my_db=# SELECT COUNT(*) FROM germplasms;
 count
--------
 475290
(1 ligne)

my_db=# \q

Result on the log:

As expected none of the statements are logged.

2015-10-15 14:17:44 EDT [unknown] my_db 123.456.789.012 postgres |LOG:  connection authorized: user=postgres database=my_db
2015-10-15 14:20:27 EDT psql my_db 123.456.789.012 postgres |LOG:  disconnection: session time: 0:02:43.333 user=postgres database=my_db host=123.456.789.012 port=49372


MDF file is close to 900gb but my calculation of reserved table space is less than 30gb... what am I missing? In a nutshell, my server is running out of free hard drive space so I ran the script found here to get an idea of tables I could possibly delete old data from:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10932
but when I got the results it seems that deleting old data doesn't seem to be my solution. I have a feeling there is something else I should be aware of here...

results from sp_spaceused

database_name   database_size   unallocated space
prod            993333.19 MB    94327.32 MB

reserved        data         index_size unused
834819768 KB    833565632 KB 1065480 KB 188656 KB

The results of my "find big tables" script:

reserved_KB     data_KB     index_size_KB   unused_KB
27,257,368      26,126,568  1,003,184       127,616

Since it sounds like you purged data from you tables, the next step is to release the free space within the datafile (.mdf) back to the OS.  You can do this via a DBCC SHRINKFILE statement.

If this doesn't result in releasing sufficient disk back to the OS, you should rebuild indexes on those tables you performed heavy purge operations against in case they are heap tables and then re-issue the DBCC SHRINKFILE command.

I would suggest leaving 20 - 30% free space in the mdf, which if I'm reading the question right, should be 36GB - 40GB in your case. This will avoid growth operations from occurring in the near future.

Finally, after the SHRINKFILE operation completes, you'll want to REBUILD any heavily fragmented indexes as the SHRINKFILE operation will cause fragmentation for any indexes that were stored toward the tail end of the data file.

EDIT: So it seems your calculation of reserved table space (as referenced in the question header) is off by a significant margin based on the comments on this answer.  In this case, what I would suggest you do is run the following statement to get an idea of what's using your disk space:

DECLARE @String NVARCHAR (4000), @ExecString NVARCHAR ( 4000)

DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases
        WHERE name LIKE '%'

CREATE TABLE ##tmpAllDBs
(
     DBID               INT,
     DatabaseName     SYSNAME,
     Name               SYSNAME,
     FileName          SYSNAME,
     Size               INT,
     UsedSpace          INT,
     ID                    INT,
     FileGroup          SYSNAME
)

SET @String = '
USE [?]

DECLARE     @PageSize     FLOAT
SELECT     @PageSize = v.low/1024.0
FROM     [master].[dbo].[spt_values] v
WHERE     v.number=1
          AND v.type=''E''

CREATE TABLE #tmpspc
(     Fileid               INT,
     FileGroup          INT,
     TotalExtents     INT,
     UsedExtents          INT,
     Name               SYSNAME,
     FileName          NCHAR(520)
)
INSERT #tmpspc
EXEC (''DBCC ShowFileStats'')

CREATE TABLE #tmplogspc
(     DatabaseName     SYSNAME,
     LogSize               FLOAT,
     SpaceUsedPerc     FLOAT,
     Status               BIT
)
INSERT #tmplogspc
EXEC (''DBCC SQLPerf(LogSpace)'')

INSERT INTO ##tmpAllDBs
SELECT     db_id() AS dbID,
          db_name() AS [DatabaseName],
          RTRIM(s.name) AS [Name],
          RTRIM(s.filename) AS [FileName],
          (s.size * @PageSize) AS [Size],
          CAST(tspc.UsedExtents * CONVERT(FLOAT,64) AS FLOAT) AS [UsedSpace],
          CAST(s.fileid AS INT) AS [ID],
          RTRIM(g.groupname) AS [FileGroup]
FROM     [dbo].[sysfilegroups] AS g
          INNER JOIN [dbo].[sysfiles] AS s
               ON s.groupid=CAST(g.groupid AS INT)
          INNER JOIN #tmpspc tspc
               ON tspc.Fileid = CAST(s.fileid AS INT)

UNION

SELECT     db_id() AS dbID,
          db_name() AS [DatabaseName],
          RTRIM(s.name) AS [Name],
          RTRIM(s.filename) AS [FileName],
          (s.size * @PageSize) AS [Size],
          (tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)
               / ( SELECT     COUNT(s_sub.name)
                    FROM     [dbo].[sysfiles] s_sub
                    WHERE     s_sub.groupid = 0)
          AS [UsedSpace],
          CAST(s.fileid AS INT) AS [ID],
          ''LOG FILE'' AS FileGroup
FROM     [dbo].[sysfiles] AS s
          INNER JOIN #tmplogspc tspclog
               ON tspclog.DatabaseName = db_name()
WHERE     (s.groupid = 0)

DROP TABLE #tmpspc
DROP TABLE #tmplogspc
'
--EXEC sp_MSforeachdb @String

DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

     SELECT @ExecString = REPLACE( @String, '?', @name )
        EXECUTE (@ExecString )

        FETCH NEXT FROM dbCursor
        INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

SELECT DBID , DatabaseName , Name, FileName, Size /1024.0 AS SizeMB, UsedSpace /1024.0 AS UsedSpaceMB,
     (1.0 * UsedSpace)/(1.0 * Size) AS PercentUsed , ID, FileGroup
FROM ##tmpAllDBs
ORDER BY 2, 8

DROP TABLE ##tmpAllDBs

This returns results that show how much size a file is using according to the OS (SizeMB), how much data is actually contained within the file (UsedSpaceMB) and some other information about the data files.  The script is ugly, but it works all the way back to SQL 2000.

With this output in had, what I would do is look for any files on User Databases where the PercentUsed column is low and the database is not tempdb (I'll talk about what to do with tempdb further below), and then run DBCC SHRINKFILE operations against said files.  This will give back the OS some space, but by no means does this mean your job is done.

Once this is complete, the next step is to identify what data you can purge or which objects you can drop.  Purge old data, drop unused indexes, and then rerun the script and see what else can be reduced in size.  Again, rebuilding indexes is recommended, but as you've already seen, Index Rebuild operations may cause data files to grow so you may have to weigh if a defragmented index is worth the extra consumption of disk space.

Other options available to you will depend on your SQL Server Version and Edition.  If you're running Enterprise or SQL 2016 Standard SP1 or later, you can take advantage of Table Compression.  This can help reduce the space used within your data files as well.

If you see that tempdb is consuming a large amount of space which is relatively unused, reduce the the size of the tempdb files, but make sure that if you have multiple .mdf files, you shrink them all to the same size as per MS's recommended guidelines.  Another word about tempdb, shrinking it is often an act of futility because there's often activity occurring within the database, so you may have to restart the instance to get these files to shrink down as desired.  Here's MS's documentation on the various ways to shrink tempdb.

Another possibility is that you have non-database related clutter on your drive(s).  In this case, simply deleting these files may be the fix for your issue.  I find using WinDirStat helps quickly identify where some of the larger files are.  Be sure you know what you're deleting though.  Sometimes there are lots of "seemingly worthless" files sitting in hidden directories, such as the WinSxS folder that look like they could be quickly deleted.  Don't recklessly delete files; make sure you only purge files you know are safe to remove.

Finally, ask for money and buy some additional storage.  Your server just may be at capacity and it's time for an upgrade.  Hardware isn't always the answer, but sometimes it can be the best answer.


T-SQL Purpose of MAX in this Group By Query I have run into some pre-existing SQL that I'm having a hard time uderstanding.

SELECT
    MAX(I.Symbol) Symbol
,   MAX(I.Ticker) CUSIP
,   MAX(I.Name) Name
,   SUM(H.Quantity) TotalQuantity
,   SUM(H.MarketValue) TotalMarketValue
,   MAX(H.PriceLC) Price
,   MAX(I.CategoryCode5) BUY_SELL
,   MAX(I.EquivFactor1) PriceTgt
,   MAX(P.LastPrice) CurrPrice
,   MAX(I.AssetClass) Target
,   MAX(I.Industry) Industry
,   MAX(I.CategoryCode1) Risk
FROM
    HOLDINGS_SECURE H
,   INVESTMENTS I
,   PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND H.Quantity > 0
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
GROUP BY
    I.Symbol

Symbol, Ticker, Name, CategoryCode5, Assest Class, Industry, and CategoryCode1 are all varchar fields. The remaining fields are decimals.

My best educated guess is that somehow max is being used to avoid multiple grouping columns but how can this return the correct results?

You say: "My best educated guess is that somehow max is being used to avoid multiple grouping columns"

That is correct.

and then: "... but how can this return the correct results?"

It returns correct results because the Symbol is the primary key in both the Investments and the Price tables. Therefore, any aggregate function over a P.column or an I.column is aggregating identical values. And MAX(c) when c is 2, 2, 2 or 2 is of course 2.

Could the query be written somehow else, possibly without all these aggregations? Yes, see a related question: Why do wildcards in GROUP BY statements not work?

It would have to be a rather long GROUP BY clause or have the aggregations moved into a subquery with only the Holdings_Secure table (where Symbol is not the Primary key) and then joined to the other two:

SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name

,   H.TotalQuantity
,   H.TotalMarketValue
,   H.Price

,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM
    ( SELECT
          SUM(Quantity) TotalQuantity
      ,   SUM(MarketValue) TotalMarketValue
      ,   MAX(PriceLC) Price
      ,   Symbol
      FROM
          HOLDINGS_SECURE
      WHERE
          Quantity > 0
      GROUP BY
          Symbol
    ) H
  JOIN
    INVESTMENTS I
      ON H.Symbol = I.Symbol
  JOIN
    PRICE P
      ON H.Symbol = P.Symbol
WHERE
        I.Product = 'stock'
    AND I.CategoryCode5 NOT IN ('X', '') ;


'Column reference is ambiguous' when upserting element into table I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set.

I've used info from this question: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null, it's quite related to what I have.

I tried to use this query, but when I run it, it errors with Column reference 'affiliate_code' is ambiguous:

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(affiliate_code, value3);

(the real values are substituted, of course).

If I replace affiliate_code = COALESCE(affiliate_code, value3) with affiliate_code = value3, everything works, but not in the way I want it to work.

How can I make this work?

Here is how my table is defined:

CREATE TABLE accounts (
  id VARCHAR NOT NULL UNIQUE,
  token VARCHAR NOT NULL,
  affiliate_code VARCHAR
);

From the docs,

  conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table. SELECT privilege is required on any column in the target table where corresponding excluded columns are read.

So instead, try this per ypercubeᵀᴹ

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(accounts.affiliate_code, excluded.affiliate_code);


Transaction Log Backups I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it.
We have couple databases where sometimes there may not be any activity for 20 to 30 minutes. We have Transaction Log Backup setup every 15 minutes.
My questions are:

Would there by any TR Log Backups for that time period?
What would happen if I happen to restore those transaction log ?

I'm sorry, I tried looking for answer on different places and books, but could not find it. If someone can please help me out.
Thank you in advance.

would there by any TR Log Backups for that time period?

If there is no activity at all, there would be not transactions. But there is no harm in taking that backup as a part of your backup schedule (which is every 15 mins).

It will look like -

/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

  what would happen if I happen to restore those transaction log ?

You will be able to restore them in sequence or you can possibly skip it as well- if there is NO gap in the Log Sequence Numbers of the log backups..


Can I create a new user in MS SQL from windows and provide the SID I am trying migrate databases and along with the database I am trying to also migrate the users without having to re-map the users. I was trying to recreate the windows user with the old server SID. Is this possible? I have an example of the code I have been trying to use but SQL gives me an incorrect syntax error.

Query:

USE [master]
GO
CREATE LOGIN [domain\ntuser] FROM WINDOWS
WITH
DEFAULT_DATABASE=[master],
SID=0x01000000000000000000000000000000000000000000000000000000;
GO

Error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '0x01000000000000000000000000000000000000000000000000000000'.

No, when creating a login from Windows, the SID is the Windows-provided one.

There is consequently no need to remap users; they automatically assume the same SID across servers within the same domain.

As an aside, instead of saying "SQL does not like it", you should provide the actual error message.  It helps us provide a more meaningful answer, and in most cases, such as this, it also provides the actual answer.


Investigating high SQL waits, especically LCK_M_U Recently I have been struggling with SQL server performance and although we have fixed a huge multitude of basic errors in the config it is still performing badly.  To clarify it is not overall performance but rather fairly frequent time outs (from the client application).  Previously I had looked to the memory as a cause but this has now been resolved and we are still getting the same behaviour.

Looking at the graphs from Management Data Warehouse I can see that LCK_M_U/X/IX are causing the majority of our waits around the time a user experiences a timeout. Everything I am reading states I need to look at the queries and processes running but I have yet to find anything aimed at a level I can understand.  The locks, as you can see in the picture below, seem to spike which coincides with the error on the users side.  Is there a clever DMV or some such that I can address to try and work out what query is being run that is creating the lock?  Is it a case of trawling through a trace to find the details?  Any guidance greatly appreciated and apologies if the information is not clear.

Locks tend to form chains and you are always interested mostly in what the process at the head of the chain is doing. Simply looking at lock wait times can be misleading because many processes can wait long times (thus increasing the wait times stats) but all be blocked by a single dog-slow process. What you likely see is a coalesce point: because locks granted have to be compatible with all current grantees and all pending waiters whenever a high level lock (X, or SCH_M) gets into the wait queue for a resource all subsequent requests queue up behind this.

To give an example: say you have a query running a report on a table, say it takes 5 mins. It hold an IS lock ont he table. This IS is compatible will all normal operations (reads and writes). A request comes in that wants SCH_M. Is incompatible with the IS so he's put in the queue. Now all of the sudden all other activity on the table goes into the queue, because every requests, reads or wites, are incompatible with this waiter. So all of the sudden all lock times spike. After 5 min the slow query finishes (it 'drains' in the DB jargon), the SCH_M is granted, it does its job in 5 ms and everyone else is free to proceed. This is just an (extreme) example, it doesn't have to be SCH_M. The idea is that just wait times don't tell the full story.

Luckily SQL Server itself can report blocking chains to you via an almost unknown feature called blocked process threshold. Mix in Blocked Process Report and DDL event notifications and you get a fully automated solution.

For an example see my answer at MS SQL Server third party transaction blocking monitor tools.

Another observation: You have 22.30% wait times on IX. This implies a object level lock occurred. Intent locks (IS, IX) are compatible so the fact that IX was blocked suggests that someone acquired an incompatible object level lock. A scan acquiring an S, or a big update escalating to X.


Why should the most restrictive condition be listed last in the where clause? The book I'm going through has given a rather poor explanation of why exactlt to place the most restrictive condition last in the WHERE clause.

Say that we have Query 1:

SELECT COUNT(*)
FROM TEST
WHERE LAST_NAME = 'SMITH'
AND CITY = 'INDIANAPOLIS';

And Query 2:

SELECT COUNT(*)
FROM TEST
WHERE CITY = 'INDIANAPOLIS'
AND LAST_NAME = 'SMITH';

and the first query takes 20 seconds, while the 2nd query only takes 10. The book goes on to say that "Because the 2nd query returned faster results and the most restrictive condition was listed last in the WHERE clause, it is safe to assume that the optimizer reads the WHERE clause from the bottom up.

I'm confused as to how I'm supposed to make that inference. Is it that filtering after 1 condition is much slower than the first filter?

That was the case many years ago (back when I started working with databases).  These days however the optimizers are smart enough that it doesn't matter what order you put the restrictions in the WHERE clause.

What @PieterGeerkens mentioned was something called caching where the optimizer is smart enough to take the information it needed for the query you ran and put it into memory.  This of course provides much quicker access than disk IO.  Then when you run the second query all (or most depending on the size of your data and RAM) of your data will already be in memory.  This means that your second query will go faster.  And again as Pieter said if you clear your cache then run the queries in reverse order the second query will again go faster.

I'm hoping your book is just really old but either way I would find a different one.  There are a number of good free books out there along with other free training materials.  Here is a page with some good links.  http://sqlstudies.com/study-and-reference-tools/


Create a new table (multiple columns) by selecting random values from an existing table I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows):

table1:

id
754
800
330
4
59

My goal is to create another table2 with two columns (source, target), where the values of both columns are a random selection of table1.id values. For example:

table2:

source | target
754    | 59
4      | 4
59     | 330

This is what I've done:

CREATE TABLE table2
(
  id serial NOT NULL,
  source integer,
  target integer,
  distance double precision
);

-- Select 300 table1.id values and insertion into table2.source
INSERT INTO table2(source)
SELECT id FROM table1 ORDER BY RANDOM() LIMIT 300;

-- Select 300 table1.id values and updating table2.target
UPDATE table2 SET target = i.id
FROM (SELECT id FROM table1 ORDER BY RANDOM() LIMIT 300) i;

I got the following result:

source | target
754    | 59
330    | 59
800    | 59

Unfortunately all the table2.target values are all the same. How can I update table2.target with different random values (like in the example) ? Or maybe UPDATE is not the good way for doing this?

I suggest a "data-modifying CTE":

WITH cte AS (
   SELECT *, row_number() OVER () AS rn
   FROM  (
      SELECT id
      FROM   tbl
      ORDER  BY random()
      LIMIT  600  -- 2 x 300
      ) sub
   )
INSERT INTO table2(source, target)
SELECT c1.id, c2.id
FROM   cte c1
JOIN   cte c2 ON c2.rn = c1.rn + 300;

In the CTE:

select 600 random rows (to create 300 new rows)
add a row number in the outer SELECT.

Then couple two values in a self join with 300 offset.

To get random rows from a huge table cheaply, consider:
Best way to select random rows PostgreSQL


Suboptimal query plan when updating partitioned table Background

I have a simple CTE used to update a declaratively-partitioned table.
The subquery runs quickly (1.7sec per EXPLAIN ANALYZE) and returns 3,769 records (CTE y in the query below).
The UPDATE seeks to update non-index columns of a declaratively-partitioned table. A few characteristics of the table:

Contains 21 million records
184 partitions -- yes, too many -- each child partition has primary/ foreign keys and indices
fillfactor=100 (plan to reduce to potentially use HOT on same pages, but not certain that query plan affected by lack of page space)

Problem arises upon UPDATE, per the incremental query plan shown (EXPLAIN)  after the subquery (via CTE) runs (several nested loops of hash joins):

-> Hash Join /* Run for each child partition, based on CTE PK = child table PK */
     -> Nested Loop
          -> CTE Scan
          -> Append
               -> Index Scan /* for index on each child partition */
                  ... /* Index scans (for each child partition) */
     -> Hash
          -> Seq Scan /* on child table */
   ... /* Hash Joins (for each child partition) */

Query

The following query is the UPDATE statement causing the issue. Basically, the query performs a couple functions using a value from parent_table that cannot be nested into a single SQL statement (so two CTEs used), then UPDATE the same parent_table for the result (the functions are expensive, so the result stored in the table itself).

WITH x AS (
  SELECT t."p1", t."p2", f(t."b1") OVER "win_x" AS "c1"
  FROM parent_table AS "t"
  WHERE t."p1" IN ('val1','val2')
  WINDOW "win_x" AS (PARTITION BY "p1" ORDER BY "p1","p2")
), y AS (
  SELECT x."p1", x."p2", f(x."c1") OVER "win_y" AS "c2"
  FROM x
  WINDOW "win_y" AS (PARTITION BY "p1" ORDER BY "p1","p2")
)
UPDATE parent_table AS "t2"
SET ("a1")=(t.”b2”*y."c2")
FROM y INNER JOIN parent_table AS "t" USING ("p1","p2")
WHERE t2."p1"=y."p1" AND t2."p2"=y."p2";

Table definition

Table defined as following:

CREATE TABLE IF NOT EXISTS parent_table (
   p1   integer,
   p2  timestamp without time zone,
   a1  numeric,
   b1  numeric,
   b2  numeric,
   c1  numeric,
   c2 numeric,
   ... /* additional 8 columns of numeric type */
   z1 numeric,
   z2 numeric,
CONSTRAINT lbound_z1 CHECK ( “z1”::numeric >= 0),
CONSTRAINT lbound_z2 CHECK ( “z2”::numeric >= 0)
PARTITION BY RANGE (EXTRACT(YEAR FROM p2), EXTRACT(MONTH FROM p2))
WITH (OIDS=‘false’)
TABLESPACE ts_ssd_raid10;

CREATE TABLE IF NOT EXISTS child_table_yyyy_mm PARTITION OF parent_table (
  CONSTRAINT child_pk PRIMARY KEY (p1, p2) WITH (FILLFACTOR=‘90’) USING INDEX TABLESPACE ts_idx_m2ssd
CONSTRAINT child_fk_other_child_yyyy_mm FOREIGN KEY (p1, p2) REFERENCES other_child_yyyy_mm (p1,p2) MATCH FULL WITH (FILLFACTOR=‘90’) USING INDEX TABLESPACE ts_idx_m2ssd )
FOR VALUES FROM (yyyy, mm) TO (yyyy, mm)
WITH (FILLFACTOR=‘90’, OIDS=‘false’)
TABLESPACE ts_ssd_raid10;
ALTER TABLE child_table CLUSTER ON “child_pk”;

Question

How can I perform the UPDATE without the nested loop hash joins for each of the 184 child tables?

System Info

Postgres version 10.3

Reduced the run time to 1-2sec by using a third CTE:

WITH x AS (
  SELECT t."p1", t."p2", t.”b2”, f(t."b1") OVER "win_x" AS "c1"
  FROM parent_table AS "t"
  WHERE t."p1" IN ('val1','val2')
  WINDOW "win_x" AS (PARTITION BY "p1" ORDER BY "p1","p2")
), y AS (
  SELECT x."p1", x."p2", x.”b2”, f(x."c1") OVER "win_y" AS "c2"
  FROM x
  WINDOW "win_y" AS (PARTITION BY "p1" ORDER BY "p1","p2")
), z AS (
  SELECT y.”p1", y.”p2", y.”b2”*y.”c2” AS “a1”
  FROM y
)
UPDATE parent_table AS "t”
SET “a1"=z.”a1”
FROM z
WHERE t.”p1"=z.”p1" AND t.”p2"=z.”p2";


Concept to keep track of changes in all tables In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions).

At the moment there are two approaches which might be the solution:

Create a table named history which stores all changes. Each time there is a change insert a new row in this table. It might have this fields:

id

target_table (name of the table)

target_id (ID of the saved entry)

created_at

data (serialized data of the saved row)
Add to each the table the following columns:

history_id (NULL: most recent entry, history_id = b.id: an old state of b, history.id pointing to nowhere: deleted object)

Are there any other approaches to keep track of all changes? Would you recommend one of my approaches?

The answer depends on what you intend to do with the data. Are you going to be doing frequent queries against it or do you only need it for the occasional lookup of what happened when, to rollback bad changes, or for regulatory reasons?

In the first case above, use the second process you descibed although I would add a column to denote the active record. I would also add a record created date. I wouild also create a view of just the active records and use that for allof my code that needs to see the current data.

In the second case above, I would have audit tables that are populated through triggers (the offective way to create audit records for any change to the db) and that also include the data of the change and the user or application that made the change as well as the old and new values.


How to determine used/free space within SQL database files? I normally use the following method to determine the free/used space within each file of a database:

Select *, fileproperty(name, 'SpaceUsed') as Used
From dbo.sysfiles

This returns total and used space in pages, which I then multiply by 8 to get KB (or divide by 128.0 to get MB).

I found another script than instead uses DBCC showfilestats and dbcc sqlperf(logspace) to return the TotalExtents and UsedExtents, which can then be multiplied by 64 to get KB (or divided by 16.0 to get MB).

Ignoring the extra columns, will these two always give identical values for free/total space? What about sp_spaceused?

Does their accuracy both depend on a recent DBCC UPDATEUSAGE?

Is there another, better method for determining used/free space? (I need this script to work on SQL 2000, 2005, and 2008 servers)

Partially related: Can you have a partially-allocated extent? (only 3 of the 8 pages within an extent are allocated, for example)

This one works for me and seems to be consistent on SQL 2000 to SQL Server 2012 CTP3:

SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
ORDER BY groupid DESC

An alternative (not compatible with SQL Server 200) that provides more information, suggested by Tri Effendi SS:

USE [database name]
GO
SELECT
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


Replicate only specific records of a table I have setup a merge replication and have got a new requirement:
The records of a table should not be replicated until the [state] column is changed from 0 to 1. I have created a filter for the replication to limit replication to records with state = 1, but now the records are removed from the subscriber when the state is set to 0.
Is it possible to keep those records on the subscriber?

for replicating an specific set of data, you can create a view and replicate it.

here you can see what data you can publish

there is a small detail though:

  All types of replication allow you to replicate views. The view (and
  its accompanying index, if it is an indexed view) can be copied to the
  Subscriber, but the base table must also be replicated.

  For indexed views, transactional replication also allows you to
  replicate the indexed view as a table rather than a view, eliminating
  the need to also replicate the base table. To do this, specify one of
  the "indexed view logbased" options for the @type parameter of
  sp_addarticle (Transact-SQL). For more information about using
  sp_addarticle, see Define an Article.


Using an IF statement for all rows in a query I'm working on a soccer database right now, and I am trying to solve one little problem.

So I have the table games, where I stored home team, away team, miscstats and boxscore:

game_id | hometeam_id | awayteam_id | miscstats_id | boxscore_id
1         2             4             1              1
2         3             7             2              2
3         4             3             3              3
4         8             5             4              4

In boxscores I store one row per player and match:

boxscore_id | player_id | team_id | goals | fouls
1             1           2         1       4
1             2           2         0       3
1             3           4         1       7
1             4           4         1       10
2             5           3         5       0

And in miscstats I store random stats, like number of yellow cards showed to the bench players:

miscstats_id | homebenchyellowcards | awaybenchyellowcards
1              3                      4
2              1                      5
3              2                      6
4              7                      10

Now, for some advanced statistics calculation, I would like to aggregate the number of homebenchyellowcards and awaybenchyellowcards for each team. So, for example, if my select function is correct, team number 3 has 7 bench yellow cards, 1 from game 2 (when team played at home) and 6 from game 3 (when team played away).

I am trying to do this:

SELECT (IF (e.hometeam_id=b.team_id, x.homebenchyellowcards, x.awaybenchyellowcards)) as
bench_yellow_cards FROM miscstats as x INNER JOIN games as e ON (e.miscstats_id=x.miscstats_id)
INNER JOIN boxscores as b ON (e.boxscore_id=b.boxscore_id) WHERE b.team_id = 2

But it gives me '1' as a result. I have been reading that this is because the 'if' function just analyses the first row when this condition is met (so, game_id 2) and then stops with the rest of the rows. I tried SUM statements too, but then it sums every row in homebenchyellowcards.

Anybody has an idea on how passing the if condition to all the rows in order to sum them properly?

Thanks in advance!

Your description looks messy. Expression from your SELECT statement will be evaluated for each row in received result set. I didn't understand why do you need to join boxscores table. Actually your original query should return more than one row. If you want to find the number of yellowcards for team_id = 2 you can use such query:

SELECT SUM(IF(e.hometeam_id = 2, x.homebenchyellowcards, x.awaybenchyellowcards))
FROM games AS e
JOIN miscstats AS x ON e.miscstats_id = x.miscstats_id
WHERE e.hometeam_id = 2 OR e.awayteam_id = 2;


Handling Bus Error in Mongo Service I have a Mongo service with which multiple hosts are interacting. The host on which the Mongo service is running is quite special -- it has 3TB of RAM. However, that host also throws intermittent Bus Error responses. If a Bus Error occurs within the Mongo service process, the service is halted and all celery (Python) processes that are interacting with the resource are served Connection Refused responses.

Is there a way to allow the Mongo service to somehow recover from a Bus Error? Can sharding help with this problem? Is there some other potential solution to the Bus Error that can be made at the application configuration level? I'd be grateful for any suggestions others can offer on this question!

I built Mongo from source on RedHat so I can use any recent version if that helps. The currently installed version is 3.6.4.

Handling Bus Error in Mongo Service

As per MongoDB documentation here It would also be helpful to run smartctl (part of smartmontools) to check for SMART hardware errors:

sudo  smartctl -a /dev/sdb

Even you can run the Linux fsck utility is used to check and repair Linux filesystems (ext2, ext3, ext4, etc.).

Depending on when was the last time a file system was checked, the system runs the fsck during boot time to check whether the filesystem is in consistent state. System administrator could also run it manually when there is a problem with the filesystems.

Make sure to execute the fsck on an unmounted file systems to avoid any data corruption issues.

For your further ref here and here


DMVs - Relating tasks to branches In this excellent article, Paul White demonstrates branches in parallel plans.

https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads

At the most fine grained level, we can look at tasks using sys.dm_os_tasks

Is there a way of relating tasks to a branch?
Is there a way of determining the count of branches for a current request?

Is there a way of determining the count of branches for a current request?

You can kind of get at that information with a query like this:

SELECT
    r.session_id,
    r.dop,
    r.parallel_worker_count
FROM sys.dm_exec_requests r
WHERE r.session_id = 62;

Note that there's nothing special about 62, it's just where my parallel query happened to be running.

You can see this query is running at DOP 4, and has a parallel worker count of 12.  So this means that the query likely has 3 parallel branches.  In this case it definitely does, as I'm reusing a demo query from my blog post about CXCONSUMER waits:

I'm not sure how this works out if SQL Server is able to reserve less than DOP * branches - the situation Paul describes in the blog post you linked to:

  The presence of blocking operators means that one or more parallel branches might be guaranteed to complete before others can start. Where this occurs, SQL Server can reuse the threads used to process a completed branch for a later branch in the sequence. SQL Server is very conservative about thread reservation, so only branches that are guaranteed to complete before another commences make use of this thread-reservation optimization.

To your other question:

  Is there a way of relating tasks to a branch?

That's covered in detail in Paul's answer on this other question: What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?

The way that query relates threads to branches is through the "nodeId" property, which you can find in the query's execution plan by hovering over the different operators.

As far as I know, there's nothing explicit in execution plans that identifies branches, or where they start and end, or which nodes belong to them.  It's just known that branches are bounded by exchange operators.

If you really wanted to "assign" a number to different branches for a running request, you could divide the exec_context_id by dop, letting it round to the nearest integer.

Modifying Paul's query from the linked answer, that idea would look like this:

DECLARE @session_id smallint = 51;

SELECT
    DOT.task_state,
    DOT.scheduler_id,
    DOT.exec_context_id,
    CASE
        WHEN DOT.exec_context_id = 0 THEN 'N/A'
        ELSE CONVERT(varchar(3), ((DOT.exec_context_id - 1) / DER.dop) + 1)
    END AS branch_id,
    DEQP.physical_operator_name,
    DEQP.node_id,
    DEQP.thread_id,
    DEQP.row_count,
    DOWT.wait_duration_ms,
    DOWT.wait_type,
    DOWT.resource_description
FROM sys.dm_os_tasks AS DOT
INNER JOIN sys.dm_exec_requests DER
    ON DOT.session_id = DER.session_id
OUTER APPLY
(
    -- What each thread did most recently
    SELECT *
    FROM sys.dm_exec_query_profiles AS DEQP
    WHERE
        DEQP.session_id = DOT.session_id
        AND DEQP.request_id = DOT.request_id
        AND DEQP.task_address = DOT.task_address
    ORDER BY
        DEQP.last_active_time DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DEQP
OUTER APPLY
(
    -- Longest wait, if any
    SELECT *
    FROM sys.dm_os_waiting_tasks AS DOWT
    WHERE
        DOWT.session_id = DOT.session_id
        AND DOWT.exec_context_id = DOT.exec_context_id
    ORDER BY
        DOWT.wait_duration_ms DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DOWT
WHERE
    DOT.session_id = @session_id
    --AND DOT.task_state = N'RUNNING'
ORDER BY
    DEQP.node_id,
    DOT.exec_context_id;


querying multiple values from one column we have a table called transaction_details.
when i write this query

SELECT
DESCRIPTION,
item,
cust_code,
pack_description,
uos,
pack,
total_value,
total_value_vat,
total_value_goods,
price_indicator
from TRANSACTION_DETAILS
where cust_code=1273
and item =371355
and item =371476
and item =371629
and item =370556
and item =370677
and item =370820
and item =370952
and item =371070
and item =371223
and item =372154
and item =372253
and item =371751
and item =371872

it shows me nothing in the result pane. we want to see the reult of these all the item one by one in single line of each item and dont want to be duplicate. thanks for helping.

SELECT
DESCRIPTION,
item,
cust_code,
pack_description,
uos,
pack,
total_value,
total_value_vat,
total_value_goods,
price_indicator
from TRANSACTION_DETAILS
where cust_code=1273
and item in
(
    371355,
    371476,
    371629,
    370556,
    370677,
    370820,
    370952,
    371070,
    371223,
    372154,
    372253,
    371751,
    371872
)

As for duplicates, you're going to have to decide how you want duplicates handled.  Using a GROUP BY, if there is a duplicate item you will have to figure out how to aggregate the rest of the data for the result set.  Feel free to give me that info and I can write up your aggregate query.


Database design - Projects with multiple Clients but only specific client contacts I am in the process of setting up a database that among other things will track projects, clients, contacts. I am however stuck on how to design one piece of relationship. The part of the database that relates to this question have the following layout.

Table: Project
ID           INT
ProjectNumber    NVARCHAR
...

Table: Client
ID            INT
Name          NVARCHAR
...

Table: ProjectClient
ID            INT
ClientID      INT (FK Client ID)
ProjectID         INT (FK Job ID)
PrimaryClient BIT (w unique index to prevent duplicates)

Table: ClientContact
ID
FirstName     NVARCHAR
LastName      NVARCHAR
ClientID      INT (FK Client ID)

Every project can have multiple clients (companies or individuals) and those can in turn have multiple contacts. My problem is that currently all contacts are assigned to the project where the client is assigned, I would like to be able to assign specific contacts to each project.

What would be the best way to accomplish that?

You need to add another table: ProjectContact that will be an intersection between ProjectClient and ClientContact.  This will let you say which contacts apply to a particular project.

The issue that you're going to have is controlling the integrity of the data.  You want to make sure that all project contacts come from clients that are actually on the project.  This can be done with declarative referential integrity (DRI) or by application logic.  Based on your table design, you seem to be pretty firmly in the camp of "every table gets a surrogate key" which makes using the DRI approach a little harder.

To use DRI for this, you want the to use a compound PK for both ProjectContract and ClientContact and in each case it should contain ClientID.  The same is true for the new intersection table, ProjectContact.  This will ensure that ClientID will be the same and your data must make sense.

Here is an illustration of what I'm suggesting...


How to systematically export all tables from a DB2 database schema to csv Is there a way using a DB2 database to systematically export all tables from a schema to separate csv files ? What I mean is to export all the tables in a schema without specifying table names. Maybe somehow getting all table names in a schema, then applying the export on those

You can easily create a small script that does this for you, or even do a loop on the command line. Assuming you are on a unix/linux platform something like below should do.

#> db2 connect to mydb

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema = 'DB2INST1'"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

You can replace DB2INST1 with whatever schema you are interested in. If you want tables from several schematas you can use in:

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema in ('DB2INST1', ...)"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

There are a lot of options that you can use to format the output from export, here I use modified by coldel but there is much more that you can configure. See:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

for more info.


How to pivot data in SQLite I've a table which contains text data containing a bunch of translations for different language. Each translation is for a specific label.

I need to generate a pivot table so to get quickly what's missing.

An example of the records is

1, en, hello
1, fr, bonjour
1, es, hola
2, en, how are you
2, fr,
3, es, come es stas

Although translations should be always there for all language, I'm not 100% sure this is the case. So missing fields have to be considered.

The desired outcome is this

|ID|EN|FR|ES|
|1|hello|bonjour|hola|
|2|how are you| |come es stas|

The challenge I have is that the column order may not always be the same when the database is populated, so in theory I should have a dynamic list of fields.

There is not a direct PIVOT function in SQLite, so I started experimenting with the group_concat obtaining a comma separated string.

SELECT DISTINCT language, group_concat(word, ',') OVER (PARTITION BY language) AS group_concat
FROM vocabulary;

I can ran past the results later on in Python if needed; the issue is that any missing value is not appending an empty item thus shifting all the concatenation by n, thus making this solution not valid.

I have attempted also to use the filter clause in the select predicate (though this mean hardcoding the columns), but I was not able to succeed.

Any idea on how this can be achieved?

The following query will return all IDs that do not have all translations:

SELECT id, COUNT(*) AS count
FROM vocabulary
GROUP BY id
HAVING count < (SELECT COUNT(DISTINCT language)
                FROM vocabulary);

If you want to know which languages are missing for each ID, use a compound query to look up those languages that are in the list of all languages but not in those for this ID:

WITH missing_ids AS (
  SELECT id, COUNT(*) AS count
  FROM vocabulary
  GROUP BY id
  HAVING count < (SELECT COUNT(DISTINCT language)
                  FROM vocabulary)
)
SELECT id,
       (SELECT group_concat(language)
        FROM (SELECT DISTINCT language FROM vocabulary
              EXCEPT
              SELECT language FROM vocabulary WHERE id = missing_ids.id)
       ) AS missing_languages
FROM missing_ids;


Entries disappeared, auto_increment also reversed, web app security vulnerability or DB Error? I am using Mediatemple's Grid Hosting (GS) platform to host a wordpress site. The DB tables are using the MyISAM engine.

I had noticed that a previous article had disappeared from the site. At first I thought it was a user mistake so I took a look at the DB backup that I had made soon after the article was posted and I noticed that the article had an ID of 11300 (auto_increment) and there were autosaves that had an ID all the way to 11307. When I checked the database, the ID of 11300 was occupied by a newer post which means that the value got re-set.

I did a diff of the database dump between the one taken just before the article disappeared and today. There was no content change other than the standard for some new posts and comments being added.

Mediatemple says it is a security vulnerability and that there is nothing wrong on their end. They suggested that I imported data (which I definitely didn't do) or a 3rd party doing it without my knowledge. But why would anyone go through the trouble of re-importing the database without adding any exploits or backdoors?

So my question is, is there any other possible explanation for this? Botched replication or some other oddity? I am not so much concerned about getting my data back but very concerned about future losses.

If you are using external DB Hosting, you should expect some intermittency due to the hosting. There are no native MySQL mechanisms in play. I have seen happen while evaluating XEROUND.
When I signed up for the 30-day trial, I was given two public IPs to access the MySQL Database. I loaded a lot of data on it. I was able to access it from one East-coast based public IP and the data was not immediately manifested from the West-coast based public IP till minutes later.
You have find out from Mediatemple if you can look into using their Reboot and Repair Utilities. You may also need to make sure data is being coalesced fast enough for snapsots to be reasonably replicated.
There is one major concern. Under the FAQ section I just saw something very, very disturbing for Mediatemple's Grid Hosting:

Which versions of LAMP software comes with my (gs)? The (gs)
Grid-Service currently features the following versions of software:
PHP 5 (click for specific version)
Perl v5.8.4
Python v2.4.4
Apache 2.0.54
MySQL 5.1.26

I really hope that is a misprint. Why?
The first GA release of MySQL 5.1 is 5.1.30. Mediatemple is offering MySQL 5.1.26.
Why on God's green earth would someone offer a Pre-GA version of MySQL 5.1?
Please, make sure you are using MySQL 5.5.21 (or 5.1.61 if you have to have MySQL 5.1)
UPDATE 2012-03-05 22:51 EST
You should setup MySQL Replication is addition as a secondary form of redundancy to an outside hosting service (such as Amazon EC2) for a while. That way, any missing rows should at the very least show up in the binary logs on the Master and the relay logs on the Slave.
If your data is missing in MediaTemplate but is present and accounted for on the Slave, I would consider switching out of MediaTemplate Grid Services. If it happens more than once, switch out immediately.
If the data on the Slave is exactly the same, you can rule out Database Replication at all levels. From here it is time for a deep dive into WordPress configuration. You can also look through the binary logs to see the data you had was once there at one point disappears at another point.


Warning SQL01271: The unnamed foreign key between tables [dbo].[Match] and [dbo].[Team] will be checked by checking the table I received this error while deploying a SQL Server DBProject

  DBProj.dbschema(0,0): Warning SQL01271: The unnamed foreign key
  between tables [dbo].[Match] and [dbo].[Team] will be checked by
  checking the table.  Name the foreign key to avoid checking the entire
  table and only check the new constraint.

My table definition is

CREATE TABLE [dbo].[Match]
(
    MatchID         int identity(11,2) PRIMARY KEY,
    TournamentID    int not null REFERENCES Tournament(TournamentID),
    Team1_ID        int not null REFERENCES Team(TeamID),
    Team2_ID        int not null REFERENCES Team(TeamID),
    MatchDate       date,
    MatchTime       time
)

What does this error means? What is the implication?

update

  actually, am concerned about the warning that it will check the
  entire table. Is it at creation time or every time? how does this
  affect performance?

Try the more verbose syntax

...
Team1_ID int not null CONSTRAINT FK_Match_Team1 FOREIGN KEY (TeamID) REFERENCES Team (TeamID),
Team1_ID int not null CONSTRAINT FK_Match_Team2 FOREIGN KEY (TeamID) REFERENCES Team (TeamID),
...

I'd suggest the lack of explicit constraint name is confusing VS because you have 2 match-team constraints. That is, it is a Visual Studio error not a SQL Server error. You won't get it in SSMS


Postgres: How can I force index usage for view with latest rates? We import currency rates into DB:

CREATE TABLE currency_rate (
    id int8 NOT NULL,
    date date NOT NULL,
    currency varchar(3) NOT NULL,
    rate numeric(12,6) NOT NULL,
    CONSTRAINT currency_rate_pk PRIMARY KEY (id)
);

ALTER TABLE currency_rate add constraint currency_rate_un UNIQUE (currency, date);

but actually we need only the latest available rate to work with.

It is cumbersome to write CTE with sort and distinct on (currency):

with cr as (
  select distinct on (currency) currency, rate from currency_rate
  order by currency, date)
select
  ...,
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join cr on cr.currency = nd.currency
...

Query has following execution plan that is nice:

  CTE cr
    ->  Result  (cost=0.28..69.66 rows=13 width=16)
          ->  Unique  (cost=0.28..69.66 rows=13 width=16)
                ->  Index Scan using currency_rate_un on currency_rate  (cost=0.28..67.17 rows=995 width=16)
  ...
              ->  Hash Join  (cost=1029.26..57129.68 rows=18 width=60)
                    Hash Cond: ((nd.currency)::text = (cr.currency)::text)

I created view:

CREATE OR REPLACE VIEW latest_currency_rate AS
SELECT
  DISTINCT ON (currency) currency, rate, date
FROM currency_rate
ORDER BY currency, date DESC;

but DB optimizer doesn't use index from currency_rate_un:

explain select * from latest_currency_rate;

Unique  (cost=60.83..65.38 rows=12 width=16)
  ->  Sort  (cost=60.83..63.10 rows=910 width=16)
        Sort Key: currency_rate.currency, currency_rate.date DESC
        ->  Seq Scan on currency_rate  (cost=0.00..16.10 rows=910 width=16)

and even for:

explain select * from latest_currency_rate where currency = 'USD';

Unique  (cost=16.87..17.13 rows=12 width=16)
  ->  Sort  (cost=16.87..17.13 rows=104 width=16)
        Sort Key: currency_rate.date DESC
        ->  Bitmap Heap Scan on currency_rate  (cost=5.08..13.38 rows=104 width=16)
              Recheck Cond: ((currency)::text = 'USD'::text)
              ->  Bitmap Index Scan on currency_rate_un  (cost=0.00..5.06 rows=104 width=0)
                    Index Cond: ((currency)::text = 'USD'::text)

Integration of new view to original query gives:

explain select
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join latest_currency_rate cr on cr.currency = nd.currency
...

...
 ->  Hash  (cost=73.54..73.54 rows=13 width=12)
       ->  Subquery Scan on cr  (cost=68.37..73.54 rows=13 width=12)
             ->  Unique  (cost=68.37..73.41 rows=13 width=16)
                   ->  Sort  (cost=68.37..70.89 rows=1008 width=16)
                         Sort Key: currency_rate.currency, currency_rate.date DESC
                         ->  Seq Scan on currency_rate  (cost=0.00..18.08 rows=1008 width=16)
...

Now I am puzzled. Why original CTE query uses Index Scan and view doesn't use the same index?

Should I rewrite view with some alternative trick (instead of distinct on)?

I am thinking about going with materialized view to avoid sequential scans...

Your view gives the correct answer, while your CTE gives the wrong answer, using the oldest date, not the newest.

If you want to use an index scan (although in my hands it doesn't actually make a difference in performance), specify DESC for both ORDER BY columns, or create an index for (currency, date DESC).


Adding a total sum below a column of prices, and not in its own column I am running this select query on my database:

Select Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate,
Sum(bid.bidPrice) over () as TotalBids
from Bid
inner join Item on bid.ItemId=Item.ItemId
where BidDate between '2016-08-24' and '2017-11-15'

I am getting the below result:

ItemId  ItemDescription BidPrice    BidDate     TotalBids
1       Frame           35         2016-08-24   3624
4       Wooden chair    40         2016-10-25   3624
2       Car             3000       2017-10-26   3624
3       Stand Fan       29         2017-10-30   3624
5       Black Sofa      400        2017-11-11   3624
6       Cabinet         120        2017-11-15   3624

My question is: Is it possible that instead of the Total Bids column with a total at each row I just get one total at the bottom of the BidPrice Column?

You can use a GROUP BY GROUPING SETS over the NULL set

SELECT itemid, itemdescription, biddate, totalbids, sum(bidprice)
FROM f
GROUP BY GROUPING SETS ( (itemid,itemdescription,biddate,totalbids), () );
 itemid | itemdescription |  biddate   | totalbids | sum
--------+-----------------+------------+-----------+------
      1 | Frame           | 2016-08-24 |      3624 |   35
      2 | Car             | 2017-10-26 |      3624 | 3000
      3 | Stand Fan       | 2017-10-30 |      3624 |   29
      4 | Wooden chair    | 2016-10-25 |      3624 |   40
      5 | Black Sofa      | 2017-11-11 |      3624 |  400
      6 | Cabinet         | 2017-11-15 |      3624 |  120
        |                 |            |           | 3624
(7 rows)

Verified to work with PostgreSQL. SQL Server 2014 and SQL Server 2016.

CREATE TABLE f(
  itemid int,
  itemdescription varchar(255),
  bidprice int,
  biddate date,
  totalbids int
)
INSERT INTO f VALUES
    ( 1, 'Frame       ', 35  , '2016-08-24', 3624 ),
    ( 4, 'Wooden chair', 40  , '2016-10-25', 3624 ),
    ( 2, 'Car         ', 3000, '2017-10-26', 3624 ),
    ( 3, 'Stand Fan   ', 29  , '2017-10-30', 3624 ),
    ( 5, 'Black Sofa  ', 400 , '2017-11-11', 3624 ),
    ( 6, 'Cabinet     ', 120 , '2017-11-15', 3624 );

The docs at PostgreSQL are much better explaining this

Another method of doing this is with a UNION,

WITH t AS (SELECT * FROM f)
SELECT *
FROM f
UNION ALL
  SELECT null, null, (SELECT sum(bidprice) FROM f), null, null;

For your query, the first method would look like this..

SELECT Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate, sum(bidPrice)
FROM Bid
INNER JOIN Item ON bid.ItemId=Item.ItemId
WHERE BidDate between '2016-08-24' and '2017-11-15'
GROUP BY GROUPING SETS ( (Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate), () )


Adding and populating column in Postgres with data from existing column I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with the IP address from rows who's TEXT column is an IP so I can take advantage of the IP Address and IP Network features in Postgres.

Adding the column is easy enough, but when I go to run UPDATE foo SET ip_addr = data::inet I (expectedly) get an ERROR: invalid input syntax for type inet for the first row that's a domain name an the update stops.

Is there any way to skip the rows that aren't IP addresses, or am I going to have to write a function for this? I'm needing to do it as part of a migration using a Golang migration tool (github.com/mattes/migrate) so I was hoping the UPDATE command would be sufficient.

To achieve this, you need to do the following:

(It's implicitly understood that your ALTER TABLE succeeded).

ty=# CREATE TABLE a(b text, c inet);
CREATE TABLE

Insert a text record with an embedded IP address.

ty=# INSERT INTO a VALUES('adfasfsaa 232.123.221.10', NULL);
INSERT 0 1

And then update the second column with the first by extracting the IP address (as a SUBSTRING) using a regular expression and then (CASTing the resultant substring as an inet type).

ty=# UPDATE a SET c = SUBSTRING(b, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}')::inet;
UPDATE 1

Et voilà!

ty=# SELECT * FROM a;
            b             |       c
--------------------------+----------------
 adfasfsaa 232.123.221.10 | 232.123.221.10
(1 row)

A more accurate regex to extract IP addresses from text might be - depending on your use case:

'\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b'

If there's more than one IP address in the string, you will get the first occurrence of an IP address in that string.

I also tested (the simple regex) on a string like 'https://123.23.21.12/sadf.net' and it works - looks like the simple one will be sufficient for most use cases.


Create MySQL CHILD and PARENT records at the same time and set CHILD.PARENT_ID by using MySQL Triggers I want to create relationships between parent and child when booth doesn't exist and when new child is created. This somehow can be done with Triggers but i don't know how to work with SQL.

After some googling i came up with Trigger concept bellow.

Create child
Create parent
Get last parent id
Update CURRENT child record with parent_id

Could somebody help me to achieve this?

DELIMITER $$
CREATE
    TRIGGER `database_name`.`trigger_name` AFTER INSERT
    ON `database_name`.`child_table`
    FOR EACH ROW BEGIN

    /*Step 1 - Create Parent record.*/
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (``);

    /*Step 2 - Get last inserted ID of just created Parent record
      and assign to the variable*/
    DECLARE last_id int;

    SELECT LAST_INSERT_ID INTO last_id;
    FROM information_schema.tables
    WHERE table_name = `parent_table`
    AND table_schema = `database_name`;

    /*Step 3 - Update current record child_table.parent_id with last_id variable*/
    UPDATE NEW.parent_id = last_id;
END$$
DELIMITER ;

Or something like this, but it has errors.

DELIMITER $$

DROP TRIGGER IF EXISTS create_new_child; $$

CREATE
    TRIGGER create_new_child BEFORE INSERT
    ON child
    FOR EACH ROW BEGIN
    DECLARE last_id INT;
        SELECT AUTO_INCREMENT INTO last_id
        FROM information_schema.TABLES
        WHERE TABLE_NAME = parent
        AND TABLE_SCHEMA = DATABASE();
    INSERT INTO parent (title) VALUES (`TEST`);
    SET NEW.parent_id = last_id;
    END$$
DELIMITER ;

Unknown column 'parent' in 'where clause'

I think you have to perform this trigger before insert child row and there is a lot of errors in your code.

You must declare variables in the beginning of the script.
You give values to variables with SET
Only LAST_INSERT_ID() function is necessary and if you perform the trigger before update, you only have to set NEW value, and that's it.

DELIMITER $$
DROP TRIGGER `database_name `.`trigger_name` $$
CREATE
    TRIGGER `database_name `.`trigger_name` BEFORE INSERT
    ON `database_name `.`child_table`
    FOR EACH ROW BEGIN
    DECLARE last_id int;
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (0);

    SET NEW.parent_id = LAST_INSERT_ID();
END $$
DELIMITER;


Pros/Cons of using multiple databases vs using a single database I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented.

While I don't agree, I'm having trouble collecting good arguments to use a single database (splitting the tables into logical domains).

One argument I have so far is data integrity (I can't use foreign keys between databases).

What are good pros/cons for using a single or multiple databases?

[summary so far]

Arguments against multiple databases:

Losing data integrity (can't use foreign keys over databases)
Losing restore integrity
Gaining complexity (db users/roles)
Small odds server/database will go down

Solutions:

Use schemas to separate domains.
POC: Use dummy data to prove the point in 7/1 db's execution plans

None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true?

Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference

You lose:

data integrity
restore integrity (data in DB7 will be later then DB1)

You gain complexity:

security (users, roles etc) have to be in all databases
you'll have some data that doesn't fit into 1 database nicely

Options:

splitting a database onto separate disks can be done with filegroups
use schemas to logically separate data (based on other answer)


trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example? I am trying to learn how to turn XML into heirarchical SQL tables.

I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone could help figure out what is going on line by line on this code, particularly after the xml has been loaded onto @XML

--I understand this part, just making the tables

DECLARE @Books TABLE (BookID int identity(1,1),BookTitle varchar(50),BookLanguage varchar(20),BookPrice decimal(18,2))
DECLARE @Topics TABLE (TopicID int identity(1,1),BookID int,TopicTitile varchar(50),Page int)

--I understand this part, defining the @xml variable to be the xml below.. just a usual xml...

DECLARE @xml XML
SET @xml = '
<bookstore>
    <name>My Bookstore</name>

    <location>New York</location>

    <book>
        <title lang=&quot;eng&quot;>Harry Potter</title>
        <price>29.99</price>
        <tableOfContents>
            <topic>
                <title>Harry Potter Topic 1</title>
                <page>2</page>
            </topic>
            <topic>
                <title>Harry Potter Topic 2</title>
                <page>5</page>
            </topic>
        </tableOfContents>
    </book>
    <book>
        <title lang=&quot;eng&quot;>Learning XML</title>
        <price>39.95</price>
        <tableOfContents>
            <topic>
                <title>Learning XML Topic 1</title>
                <page>1</page>
            </topic>
            <topic>
                <title>Learning XML Topic 2</title>
                <page>2</page>
            </topic>
        </tableOfContents>
    </book>
</bookstore>'

--what is going on below here?  I am familiar with inserting data into tables,
--but what kind of insert is this where you are selecting some things and then doing a
--from @xml.nodes also, what is that T(c) at the end?  and do we always have to put
--a [1] after each xml element to denote we are referring to the first one we encounter?

INSERT INTO @Books
SELECT T.c.value('title[1]','varchar(50)') AS 'BookTitle',
    T.c.value('(title/@lang)[1]','varchar(20)') AS 'BookLanguage',
    T.c.value('price[1]','decimal(18,2)') AS 'BookPrice'
FROM @xml.nodes('/bookstore/book') T(c)

--what is going on here as well?  what is n(x) ?
--could you explain this line by line-ish as well? I ran this on
--SQL Server Management Studio and noticed that both of the 'topic titles' for each
--book got inserted.  Where in the code did those get put into the table?

INSERT INTO @Topics
SELECT b.BookID,n.x.value('title[1]','varchar(50)') AS 'TopicTitile',
    n.x.value('page[1]','int') AS 'TopicPage'
FROM @Books b
cross apply @xml.nodes('/bookstore/book/tableOfContents/topic[../../title=sql:column("b.BookTitle")]') n(x)

--below here is just regular sql selects so this makes sense.

SELECT BookID,BookTitle,BookLanguage,BookPrice FROM @Books
SELECT TopicID,BookID,TopicTitile,Page FROM @Topics

The forum I was referring to and trying to learn from with the old post is:

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/7216ccc9-c1d7-418d-95a2-ec3a96de2c27

BOL says:

syntax:

nodes (XQuery) as Table(Column)

Here is simple example:

DECLARE @x xml ;
SET @x='<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>';

SELECT T.c.query('.') AS result
FROM   @x.nodes('/Root/row') T(c);
GO

It is a special notician to "convert" or shred xml data type into relational data. It just maps xml parts into the table columns. T - table, c - column, nodes() - method

value (XQuery, SQLType)

So T.c.value('title[1]','varchar(50)') reads value of element title and converts it to varchar(50) data type. [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton (just it confuses me, it means the first element in group in XPath).

So T.c.value('(title/@lang)[1]','varchar(20)') reads value of attribute lang at element title and converts it into varchar(20) data type.

And @xml.nodes('/bookstore/book') is located the point to start reading xml, in this case it returns all book elements (nodes) from this xml.

This query has 2 aliases T1(Locations) and T2(steps)

ADDED

SELECT
ProductModelID
, Locations.value('./@LocationID','int') as LocID
, steps.query('.') as Step
FROM Production.ProductModel

CROSS APPLY Instructions.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location')
as T1(Locations)

CROSS APPLY T1.Locations.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
./MI:step ')
as T2(steps)
GO


This certain MySQL database organisation Hypothetically, let's say that I have three tables:

- Products (id, product_name, added_timestamp)
- Individuals (id, name, last_name)
- Companies (id, company_name, company_number, company_address)

Now, let's say that both individuals and companies can add their products to my virtual shop.

Now, let's say I want to display all the added products sorted from descending order by the Products.added_timestamp column. However, I don't know how to connect Products with Individuals and Companies and display all the data.

How would I approach this problem?

You can approach this by creating the relevant foreign keys so you can use relations in your databases.
Here is a nice read about relational databases.
I strongly recommend it for you to read it, sou you can get the hang of the relational design idea.

Edit:
In your particular case i would to this:

Products (id, product_name, added_timestamp, seller_type, seller_id)
Individuals (id, name, last_name)
Companies (id, company_name, company_number, company_address)

where seller_type can be either 'i' as Individual or 'c' Company, and seller_id is the corresponding foreign id.


MySQL monitor prompt does not return for slave I'm trying to fix a replication error (or an ongoing failure to replicate) between our master and slave MySQL databases.

The procedure I have asks me to use MySQL Monitor to stop the slave.

stop slave;

When I try this, however, the prompt never returns. I've left it for several minutes before Ctrl+C'ing out.

Interestingly, the same thing occurs with:

show slave status;

...but

show master status;

Works fine. Is there some other way I can kill the slave process off, ideally without interrupting the master?

When this happens, I immediately go to the Slave and
If you have MySQL 5.1+
SELECT id FROM information_schema.processlist WHERE user='system user';

This will show you the Process ID of the IO Thread and SQL Thread of Replication.
If you have MySQL 5.0
SHOW PROCESSLIST;

You would have look for the two rows in the display that has 'system user' as the user.
Once you find the IDs...
You can run the KILL or KILL QUERY command against the Process IDs.
CAVEAT
This is a possibility that evn the KILL command can hang if the query must come to a completion (such as a transaction that must fully commit or fully rollback).
Master is oblivious to all this.
Why does SHOW MASTER STATUS; work just fine ???
Running SHOW MASTER STATUS; simply checks for binary logs on the DB Server. There are no DB threads to read, parse, examine, and coalesce.
Running SHOW SLAVE STATUS\G has to reconcile three(3) aspects:

Aspect #1 : the Contents of master.info
Aspect #2 : the current list of relay logs
Aspect #3 : information acquired from the IO and SQL threads (live)

This reconciliation helps produce the output of SHOW SLAVE STATUS\G. It's Aspect #3 that makes SHOW SLAVE STATUS\G freeze attempts to kill the SQL thread on a long running query.


With and without autoincremented id performance I am using myqsl an sql server for quite long time. I am C# developer. I always used to create for each table first column Id as primary key and auto-incremented. Last time i saw my colleague (also C# developer) are not using it at all - no id at all. I found lot of issues when there is no id with pk and ai - hard to delete records etc... Our last conversation was also about performance. He said for performance is better to not use Id with auto increment pk for big data. I would like to get your feedback what's the fact?

Let's categorize things into 3 buckets:

Case 1: No natural PK.  Then do have an AI.  Soner or later, you will find that having it makes maintenance easier.  Example: Deleting or modifying a specific row due to a data error.  That is, do not depend on the hidden PK that InnoDB will generate.

Case 2: There is a natural PK and you need an index by that column(s).  Bingo!  A PK is an index, so you can get the performance benefit of the index inherent in a PK.  Plus you avoid the space, clutter, etc, of the AI.

Case 3: Natural PK possible, but indexing it is not needed.  Now we are in a gray area.  Flip a coin.  Or, since a VARCHAR is slightly slower for indexing, pick the INT AI.

Case 4: (Yeah, I started with 3 buckets.)  If the table is PARTITIONed, a lot of additional considerations come into play.  I won't go into them in this Q&A.

Let me provide an example of Case 2 -- a many-to-many mapping table.

A lot of people build essentially

CREATE TABLE A_to_B_mapping
    id INT AUTO_INCREMENT,       -- not used
    id_a,  -- link to table A
    id_b,  -- link to table B
    PRIMARY KEY(id),
    INDEX(id_a),  -- for joining from `A` to `B`
    INDEX(id_b)   -- if going the other way
)

But this would be much better:

CREATE TABLE A_to_B_mapping
    -- no AUTO_INCREMENT,      -- saving space
    id_a,  -- link to table A
    id_b,  -- link to table B
    PRIMARY KEY(id_a, id_b), -- PK, covering, unique, and for joining from `A` to `B`
    INDEX(id_b, id_a)   -- if going the other way, now 'covering'
) ENGINE=InnoDB;        -- so PK is 'clustered'

A "Covering" index is an INDEX that has all the columns needed anywhere in the SELECT.  A non-covering index must reach into the data BTree (after reaching into the index BTree) to get the rest of the columns.  That is, "covering" is more efficient.

Note:  Even if the ids are VARCHAR, the benefit of 'covering' outweighs the string-vs-int argument.

More on many-to-many:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Another Case 2 example:  wp_postmeta is inefficient .  That applies to other EAV schemas, as well.

I find that 2/3 of the time, a 'natural' PK can be made from a column (or combination of columns).


How to access row value from UPDATE sub query function? I would need to use UPDATE like this:

update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
  'SELECT gid as id, source, target, walk_cost as cost FROM ways',
  offer.vertice_id,
  array(select vertice_id from osm_points where super_type = 'bank'),
  false,false)
join osm_points on id2 = vertice_id
where cost != -1
and osm_points.super_type = 'bank'
order by cost
limit 1)t ;

but I get following:

  ERROR:  invalid reference to FROM-clause entry for table "offer"

  HINT:  There is an entry for table "offer", but it cannot be referenced from this part of the query.

Thank you in advance

When using a query of this form: UPDATE a SET columns=values FROM b WHERE ..., the b subquery is evaluated independently of a, and it's the WHERE block at the end of the query that is supposed to join the rows of a and b.

The query in the question attempts to join inside the subquery rather than in a WHERE clause at the uppermost level (in fact it doesn't have such a WHERE clause), but it's not allowed and that's what the error message indicates.

The SQL-standard way of updating each row with a correlated subquery would be:

UPDATE a SET (col1,col2) =
   (SELECT val1,val2 FROM b WHERE expression-needing-values-from-a)

but in older versions of PostgreSQL (9.4 or before), the manual explicitly tells that this form is not implemented. Starting with 9.5, it is implemented.

The typical way to emulate it with postgres 9.4 is to use a copy of table a in the subquery and join back to the updated table in the upper level's WHERE clause, like this:

 UPDATE a SET (col1,col2)=(s.col1,s.col2)
  FROM (SELECT a2.ctid,col1,col2 AS ctid_of_row FROM b, a AS a2 WHERE...) AS s
   WHERE s.ctid_of_row = a.ctid;

knowing that ctid is the system pseudo-column that indicates the physical location of the row version within its table. Alternatively, the primary key can be used if there is one.

In your case, the transformation is not obvious because your subquery is meant to produce only one row with its ORDER BY... LIMIT 1 at the end. It should be redesigned to produce all the target rows in a single resultset, presumably (verticeid,cost,gid) where cost and gid are determined per offer.verticeid.


How to multiply rows for a column that contains negative and zero values? I'm trying to get the Product of all rows for a specific column in a grouped by query.  Most examples I've found point me towards combining exp, sum and log

exp(sum(log([Column A])))

The problem I'm having is that the column contains some zeros for values and thus I'm getting this error when zeros get passed to the log function:

  An invalid floating point operation occurred.

I thought I could work around this by using a case expression, but that just doesn't work the way I would think it should, as it seems to evaluate all cases...

select
  Name,
  Product = case
    when min([Value]) = 0 then 0
    when min([Value]) <> 0 then exp(sum(log(I))) -- trying to get the product of all rows in this column
  end
 from ids
 group by Name

SqlFiddle

Given the following result set:

Id  Name  Value
_________________________________
1   a     1
2   a     2
3   b     0
4   b     1

I would expect to get the following rows:

Name  Product
_____________
a     2
b     0

So in summary...
How do you multiply rows in a column that can contain negative or zero valued numbers?

The magic of NULLIF seems to do the trick for the test case in your question. Since you used a different example than in your SQL Fiddle, I don't know if that's what you want there too.

CREATE TABLE dbo.Ids
(
    Id INT NOT NULL IDENTITY(1, 1),
    Value INT,
    Name NVARCHAR(3)
);
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'a', 1 );
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'a', 2 );
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'b', 0 );
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'b', 1 );

SELECT   Name,
         CASE WHEN MIN(Value) = 0 THEN 0
              WHEN MIN(Value) > 0 THEN EXP(SUM(LOG(NULLIF(Value, 0)))) -- trying to get the product of all rows in this column
         END AS Product
FROM     Ids
GROUP BY Name;

Returns:

Name    Product
a       2
b       0

If you need a more general solution that handles negative numbers and other edge cases, see for example The Product Aggregate in T-SQL Versus the CLR by Scott Burkow. One T-SQL construction from that article is:

EXP(SUM(LOG(NULLIF(ABS([Value]), 0))))
*
IIF(SUM(IIF([Value] = 0, 1, NULL)) > 0, 0, 1)
*
IIF(SUM(IIF([Value] < 0, 1, 0)) % 2 = 1, -1, 1)

As to why your original CASE expression did not work as expected, from the documentation for CASE (Transact-SQL) (emphasis added):

  You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.