Is deleting secondary files a bad idea? I have inherited a SQL DB that has 16 db files. All in the same filegroup of the 16 3 are set to grow the others are static consuming 1 GB each. Would there be a performance impact if I deleted the superfluous files. Should I do any re-indexing etc. afterwards?

Since this is on hold I'll try and be clearer though I believe the three gentlemen that responded have adequately answered my question. Thank you to them, all three responses have been helpful.

On the clarity issue the bottom line question is this. I have a DB that is made up of 16 files in the primary filegroup. They're on the same drive, in the same folder. 13 of the files make up less than 1% of the DB size. If I, following the proper procedure, delete them would there be likely be much of a performance impact (fragmentation etc.)

Thanks to all for the responses.

There are many considerations when determining the right number of files in a filegroup--Determining the proper number and layout of files in this filegroup is a separate question, but one that should not be ignored. File layouts for a database can be non-trivial, and the topic of adding/removing files from a filegroup raises lots of tangential discussion points.

That being said, I'm going to assume you've already looked at those things and come to the conclusion that you must remove some files.

If you choose to remove data files from a filegroup, the first step is to run DBCC SHRINKFILE(logicalname,EMPTYFILE). (If you don't do this manually, the GUI will do it for you--I recommend against doing this sort of thing via the GUI.) This will evacuate data pages from this file and move them into another file in the filegroup that has room. This is done page-by-page, and is an online operation. However, just like any file-shrinking, it's going to cause all sorts of nasty fragmentation.

You probably want to run index maintenance after you remove the files.

Before you begin, you will  also want to consider which file(s) you want to remain at the end. I would go ahead and pre-grow those files so that you're not trying to autogrow FileA while trying to shrink FileB. Or possibly, I would just create some brand-new files and get rid of the existing 16 files.


DB2 LUW Query Optimization - IXSCAN timerons greater than total timerons Sorry - I feel like this is a newb question, but I can't find anything on it.
In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38.
However, on closer examination of the explain plan for the optimized query, I see one IXSCAN that reports 3,707.97 timerons, while the total is still reported as 38.61!

I'm not super familiar with DB2 LUW, and was wondering how this could be.  Even with parallelism, I would not think we could get much smaller than, say, 1/4 of the largest #. Surely not 1/10th. (?)

This is DB2 LUW 9.5 on AIX.  Very plain-vanilla installation for testing environment. No HA, no partitioning, nothing like that.

Just want to tie this off.
As was suggested in the comments, this simply should not be so. It must have been a bug in the level of DB2 I was using.


SQL Server Pivot Multiple Column and Value I have a sample table attendance look like this :

PERIOD  EMPL_NMBR   EMPL_NAME           DRPT     DAYX    [IN ]    [OUT]
201603  00001       John                QA       01      08:00    17:00
201603  00001       John                QA       02      08:00    17:00
201603  00001       John                QA       03
201603  00001       John                QA       04      08:00    17:00
201603  00001       Susan               SALES    01      08:00    17:00
201603  00001       Susan               SALES    02
201603  00001       Susan               SALES    03      08:00    17:00
201603  00001       Susan               SALES    04

I'd like to display the data from that table like this:

PERIOD     EMPL_NMBR     EMPL_NAME      1           2            3             4
                                   [IN]  [OUT]  [IN]  [OUT]  [IN]  [OUT]   [IN]  [OUT]
201603     00001         John     08:00  17:00  08:00  17:00  -      -     08:00  17:00
201603     00002         Susan    08:00  17:00   -      -    08:00  17:00    -      -

I can successfully pivot it using a single column and single value dynamically, but I get confused when I try to pivot multiple columns and values dynamically.

Can anyone help me?

If you have to do it on the SQL Server side, you can use GROUP BY with a CASE statement:

SELECT PERIOD, EMPL_NMBR, EMPL_NAME
    , IN_1 = MAX(CASE WHEN DAYX = 1 THEN [IN] END)
    , OUT_1 = MAX(CASE WHEN DAYX = 1 THEN [OUT] END)
    , IN_2 = MAX(CASE WHEN DAYX = 2 THEN [IN] END)
    , OUT_2 = MAX(CASE WHEN DAYX = 2 THEN [OUT] END)
    , IN_3 = MAX(CASE WHEN DAYX = 3 THEN [IN] END)
    , OUT_3 = MAX(CASE WHEN DAYX = 3 THEN [OUT] END)
    , IN_4 = MAX(CASE WHEN DAYX = 4 THEN [IN] END)
    , OUT_4 = MAX(CASE WHEN DAYX = 4 THEN [OUT] END)
FROM @data
GROUP BY PERIOD, EMPL_NMBR, EMPL_NAME;


SQL Server Log Shipping: File copy options? I usually use redgate sql backup which has a transaction log copy mechanism built in.

If I roll my own log shipping, what are established ways to move the log file?

I want to emphasize: The way log shipping works in the log shipping feature is a mess. The requirement of a file share, and all the firewall and auth overhead of that are simply over the top.

In my dreams, there would be an automatic way to do log shipping via azure files. Click click click. Think how great this would be:

1) No file shares needed!
2) Your backups and tran logs are automatically backed up in azure cloud (DR step #1 !)
3) The remote can be anywhere
4) I can now easily do one-to-many log shipping, because the point to point architecture of the built in log shipping is side stepped.

If I roll my own log shipping, what are established ways to move the log file?

Why do you want to roll out your own logshipping solution ? Microsoft has put in a lot of effort for giving out-of-box Logshipping from SQL 2005 and up. This is tested by millions of customers since sql 2005, so highly unlikely you will encounter any bugs with it.

You can refer to : Step By Step SQL Server Log Shipping for how to configure native logshipping.

References for Custom Logshipping :

Log shipping and A Control Mechanism
Custom Log Shipping
How to Setup Log Shipping for Multiple Databases

Using Powershell :

Log Shipping via PowerShell Invoke-LSRestore
Your poor man’s SQL Server log shipping-PowerShell version
Secure Log Shipping via SSL FTP
Automating SQL Log Shipping over SFTP (SSH), the simple way


Innobackupex (Xtrabackup) 2.4.8 incremental backups take the same time as full backup We have our operational as well as decision making databases both running on Percona MySQL 5.7.10. The OS is Ubuntu 16.04. On these various database instances we have been using Percona Innobackupex (Xtrabackup) for daily and incremental backups.
   Both of these backup types take the same amount of time to finish. For our Data Warehouse, running on an AWS ec2 m4.2xlarge instance type, which is around 3 TB in size both the full and incremental take around 5-6 hours to finish. Shouldn't the incremental finish faster?

Based on the full backup taken a day before, I've tried testing the incremental backup numerous times and each time I found it to be taking the same time as the full backup.

Any help or pointers to this would of be of great help.

It's kind of expected assuming how incremental backups work.

Xtrabackup knows the last LSN (a point when a full copy was taken).
Xtrabackup reads all pages. Every page has an LSN in its header of the latest modification:

if a page is newer than the last LSN - save it.
if a page is older than the last LSN - skip it.

So, in the end of the day xtrabackup needs to read same amount of pages, whether it's a full or incremental copy.

But. There is an option in Percona Server can track modified pages. In that enabled, Xtrabackup doesn't have to scan whole tablespace and thus incremental backup time can be greatly reduced.


Is there a way to specify which schema the database task "Export Data-tier Application" exports for the other entities besides the tables? I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object types too such as views and stored procedures. Is this possible?

There is an option to include stored procedures in export data, uncheck the option  to exclude stored procedures from exporting data


Mysql fulltext index search returning weird result I have ingredient table. I want all those recipes which have certain ingredients. Below is my table structure.

Table(ingredient) - Applied fulltext index on ingredient column.
------------------------------------------------------
 ingredientID   rcteID  ingredient
    310           1     Mint Leaves
    311           1     Corriender Leaves
    312           1     GreenChili

I am trying to fetch above record below fulltext search query but not getting that record.

SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
      AGAINST ('+Mint Leaves +Corriender Leaves +Greenchili' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID`
IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

Why above query is not working for above record?

When I tried below query it worked. just changed searching text.

SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
      AGAINST ('+Greenchili +Mint Leaves +Corriender Leaves' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID` IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

OUTPUT
--------------------
ingredientID    rcteID
311                1

Don't understand what's going on. Why first query not returning any result and below query returning result?

According to the MySQL Documentation on FULLTEXT Boolean Searching

  "

  A phrase that is enclosed within double quote (“"”) characters matches
  only rows that contain the phrase literally, as it was typed. The
  full-text engine splits the phrase into words and performs a search in
  the FULLTEXT index for the words. Nonword characters need not be
  matched exactly: Phrase searching requires only that matches contain
  exactly the same words as the phrase and in the same order. For
  example, "test phrase" matches "test, phrase".

  If the phrase contains no words that are in the index, the result is
  empty. For example, if all words are either stopwords or shorter than
  the minimum length of indexed words, the result is empty.

  The following examples demonstrate some search strings that use
  boolean full-text operators:

  'apple banana'

  Find rows that contain at least one of the two words.

  '+apple +juice'

  Find rows that contain both words.

  '+apple macintosh'

  Find rows that contain the word “apple”, but rank rows higher if they
  also contain “macintosh”.

  '+apple -macintosh'

  Find rows that contain the word “apple” but not “macintosh”.

  '+apple ~macintosh'

  Find rows that contain the word “apple”, but if the row also contains
  the word “macintosh”, rate it lower than if row does not. This is
  “softer” than a search for '+apple -macintosh', for which the presence
  of “macintosh” causes the row not to be returned at all.

  '+apple +(>turnover

  Find rows that contain the words “apple” and “turnover”, or “apple”
  and “strudel” (in any order), but rank “apple turnover” higher than
  “apple strudel”.

  'apple*'

  Find rows that contain words such as “apple”, “apples”, “applesauce”,
  or “applet”.

  '"some words"'

  Find rows that contain the exact phrase “some words” (for example,
  rows that contain “some words of wisdom” but not “some noise words”).
  Note that the “"” characters that enclose the phrase are operator
  characters that delimit the phrase. They are not the quotation marks
  that enclose the search string itself.

MAIN PROBLEM

You are searching for 3 ingredients
You are querying for 4 words (Mint, Leaves, Corriender, Greenchili)

COURSE OF ACTION

You need to adjust the MATCH clause to query three string tokens. You do that by putting double quotes around "Mint Leaves" and "Corriender Leaves":

SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
      AGAINST ('+Greenchili +"Mint Leaves" +"Corriender Leaves"' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID` IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

GIVE IT A TRY !!!

NOTE: Corriender should be spelled Coriander


Selecting from a view shows the wrong set of columns I'm selecting from a view in SQL Server. If I right-click on it and choose "Select Top n Rows", it runs and shows me 30 columns.  If I open the view in ALTER mode (right-click, Script View As, etc...), highlight the code and run it, I get 50 columns.  I should get 50 columns, because I just added 20 new columns to the table feeding the view, and inside the view I'm using a "SELECT * FROM..." statement.

I've refreshed the Views folder, but that didn't help.

Anyone know why the discrepancy, and how to correct it?

First, stop using SELECT * in your views. I talk about this quite a bit here:

Bad habits to kick : using SELECT * / omitting the column list

Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.:

EXEC sp_refreshview N'dbo.viewname';

If you want to generate a script that refreshes all of the views in the system:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  EXEC sp_refreshsqlmodule N'''
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id];

EXEC sp_executesql @sql;

(You may want to run it twice in case there are circular or out-of-order dependencies.)

But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using SELECT * in your views!

Keeping sysdepends up to date in SQL Server 2008

You may also want to consider adding WITH SCHEMABINDING to the definition of all of your views. This does two things:

Prevents you from using SELECT * in the view. This is A GOOD THING™.
Prevents you from changing underlying tables without knowing exactly which views would be affected by the change. This is also A GOOD THING™.


Query to get the number of errors reported by PostgreSQL Is there any internal table recording the number of errors produced by Postgres?

I was expecting something like:

select
    database, count(*)
from
    pg_errors
where
    type='error' and date > (now() - INTERVAL '1 HOUR')

This is not tracked inside the database.

You could mine the log file for ERROR entries — log_destination = 'csvlog' will make it easier.

One option to process csvlogs (thanks, a_horse_with_no_name!) is to use them with a file_fdw foreign table to access them with SQL from PostgreSQL.


Export a table and zip with password I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party tool that doesn't require a local installation (standalone EXE on a network drive for example).

I tried using sp_OA*, but found this post saying they were buggy, memory-leaky and deprecated features. So I am looking for alternatives, if there are any. Google searches all came back to the same solutions: 7zip, Winrar, or sp_OA*.

I don't want the code, just the name of the features or methods that could help me, so I would know what to search for.

MSSQL2008 and up. Can be a Powershell solution. Not SSIS if possible. For the record, it's to daily update a software with current data, as it doesn't have direct access to the source.

If 7zip is the best/only way to go, I'll have a ton of paperwork to fill in and meetings to attend to justify installing non-database software on a SQL server. :-)

Yes, stay away from the sp_OA* OLE Automation procedures.

If you want  / need to handle this purely within SQL Server then you can do so using SQLCLR, which replaces the sp_OA* OLE Automation procedures. And if you aren't using xp_cmdshell for anything else, then no need to enable it just for this (although to be fair, if using SQL Agent, then a CMD step would be able to execute BCP and/or 7zip without the need for enabling xp_cmdshell).

With SQLCLR you can:

do the export from within T-SQL as well and not need to mess with BCP which is an external process (see FileStream).
zip the file, as a separate step, since. NET has methods to gzip and gunzip (see GZipStream).
encrypt with a password (see RijndaelManaged and AesManaged). While you did specify AES, the MSDN documentation for AesManaged states:

  The AES algorithm is essentially the Rijndael symmetric algorithm with a fixed block size and iteration count. This class functions the same way as the RijndaelManaged class but limits blocks to 128 bits and does not allow feedback modes.

The difference is if you are using SQL Server 2005, 2008, or 2008 R2. Those versions are limited to using .NET 3.5, and the MSDN documentation for AesManaged (for that version) states that it has a Host Protection Attribute (HPA) which means that any assembly using that class has to be marked as PERMISSION_SET = UNSAFE, whereas RijndaelManaged does not have that issue and can be used in an EXTERNAL_ACCESS assembly. However, it seems that the documentation for AesManaged shows that, starting in .NET Framework version 4.0 (SQL Server 2012, 2014, and 2016 use .NET Framework 4.0 and newer--currently up to 4.6), the note about the HPA has been removed. Even if UNSAFE is acceptable, please note that the specific HPA is MayLeakOnAbort, which means "might leak memory if the operation is terminated".

In this setup, the password is part of the encryption, not the compression. Hence you either have a zip file containing an encrypted file, or you have an encrypted file containing the zip file. The former method gives you a file that can be unzipped by anyone but then only read by someone with the correct password. The latter method requires knowing the password to get the compressed file, and also allows for better compression since the compression will be done on a text file  (with a greater likelihood of compressible patterns).

While I have not implemented the encryption algorithms yet, I have coded both the file export (of any random query) and GZip/GZunzip in SQLCLR and can state that they do run efficiently, or at least can be made to be efficient (and can also be made to be inefficient). By this I mean I have used them to extract queries producing 5 GB text files, and GZipping those 5 GB text files, and memory was never adversely affected since both operations are streamed. Please note that the .NET-provided GZipStream class does not support the Zip64 format, and so is limited to 4 GB of source data.

For anyone who is interested in pre-built SQLCLR functions to do the extract and compression (the ones I mentioned above), they are available in the SQL# library (which I am the author of). Please note that:

while there is a Free version of SQL#, the two objects mentioned here, DB_BulkExport and File_GZip, are only available in the Full version.
in order to overcome the 4 GB limitation of the GZipStream class, I had to incorporate a 3rd-party library that does support Zip64.
soon enough there will be functions to handle file encryption and decryption via Rijndael (available for SQL Server 2005 - 2016 at least) and Aes (available for SQL Server 2012 - 2016 at least).


(mysql innodb) Is single update statement with "where" transaction safe? I understand that a single update statement is atomic, the row will be locked during update process. But when there is a "where" cause, is it still atomic?

For example:

table: ABC
-------------
|colA | colB|
-------------
|123  | 0   |

--update 1
update ABC set colA=456, colB=1 where colB=0;

--update 2
update ABC set colA=556, colB=2 where colB=0;

Due to the where cause, the update will first check colB value and find that row in the table, then lock the row for update, right?
If update 1 and update 2 is executed at the same time, is there a chance that both get successfully executed and one of them overwrite the value of another?

Is it better to write as below?

START TRANSACTION;
  update ABC set colA=456, colB=1 where colB=0;
COMMIT;

For sanity, we just assume inno

  If update 1 and update 2 is executed at the same time, is there a chance that both get successfully executed and one of them overwrite the value of another?

No, that's not how MVCC works. And wrapping single statements in transactions doesn't do anything. They're effectively already wrapped in transactions.

The first transaction will read from ABC, lock it with a row-level lock pending update, write a new row with the updated value, and set it as live on the table.

The second transaction will read from ABC, if the row is locked, it'll block. If not, it will lock the row pending update, write a new row with the updated value, and set it to live on the new table. If it is locked, it'll wait until the lock releases and recheck the row.

Interestingly enough, the default isolation level of read committed does not protect from phantom writes. So if both transactions start at the same time, they'll both read from the same snapshot. But, if one txn1 writes the update it'll actually modify the snapshot txn2 is reading from. So while you can't read the modified data (phantom reads), when you try to write the data you're interacting with the same master copy.

Suggestion: Upgrade to a real database, if possible

For more information, read the docs on MariaDB on transaction isolation.


Add If Statement To Stored Procedure I am running an Insert Into statement in my stored procedure that is receiving data from a Table Value Parameter  I just realized I need to 1st verify that the data does not already exist in the table before doing an insert so I am attempting to alter my procedure to this

ALTER PROC [dbo].[CMT]
(@TempTable AS dbo.TableValue_ET READONLY)
AS
BEGIN

if b.[empID] NOT IN (Select [empID] FROM [Habib].[dbo].[SE])
BEGIN
  INSERT INTO [Habib].[dbo].[SE]([empID],[POD],[empNotes],[hiredate],[terminationdate],[terminationtime],[address],[city],[state])
  SELECT
  [empID]
  ,[POD]
  ,[empNotes]
  ,convert(varchar(10), [b].[hiredate], 101)
  ,convert(varchar(10), [b].[terminationdate], 101)
  ,[terminationtime] = LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7))
  ,[address]
  ,[city]
  ,[state]
  FROM @TempTable b
END

END

However, when I attempt to save this updated syntax I get an error of

  Msg 4104, Level 16, State 1, Procedure UpdateShippingExceptions, Line 7
  The multi-part identifier "b.empID" could not be bound.

What must I change so that I can 1st check if the empID already exists in the table before inserting it?

The basic form is:

INSERT dbo.table
(
  cols
)
SELECT
  cols
FROM @tvp AS t
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.basetable AS b WHERE b.key = t.key
);

In your case, that would be:

INSERT INTO [Habib].[dbo].[SE]
(
  [empID],
  [POD],
  [empNotes],
  [hiredate],
  [terminationdate],
  [terminationtime],
  [address],
  [city],
  [state] /* why do people prefer horizontal scrollbars to vertical ones? */
)
SELECT
  [empID],
  [POD],
  [empNotes],
  convert(char(10), [b].[hiredate], 101),
  convert(char(10), [b].[terminationdate], 101),
  LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7)),
  [address],
  [city],
  [state]
FROM @TempTable AS b
WHERE NOT EXISTS
(
  SELECT 1
    FROM [Habib].[dbo].[SE] AS se
    WHERE se.empID = b.empID
);

One thing you need to find out, though, is if users enter existing empID values here and expect to update existing employees with new information for the other columns... if so, you're throwing their updates away without telling them.


JOIN to read first table and get possible relationship in the second table Consider a main table as

CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB

and a second table for relationship as

CREATE TABLE group_members
(
relation_id int(11) NOT NULL AUTO_INCREMENT,
group_id int(11),
user_id int(11),
FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
PRIMARY KEY(relationsh_id)
) ENGINE=InnoDB

How to get a full list of all groups with an additional column indicating that if a given user is member of each group or not. This means that we will have a WHERE clause indicating the corresponding user_id, and the additional column will show 0 or 1, depending that this user_id has a connection to each group or not.

Based on your updated question:

SELECT group_title, IF(user_id IS NULL, 0, 1)
FROM groups
LEFT JOIN (SELECT user_id, group_id
          FROM group_members WHERE user_id=1)gMembers USING(group_id)

sqlfiddle


You might have to check the last character of each line

Instead of this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'
INTO TABLE players
FIELDS TERMINATED BY ','
ENCLOSED BY '^'
LINES TERMINATED BY '\n';

Try this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'
INTO TABLE players
FIELDS TERMINATED BY ','
ENCLOSED BY '^'
LINES TERMINATED BY '\r\n';


Copy data from one table into other on some condition I have two table as follows.

Table : city

city_id  city_name   state_id
  1       Cachar       1
  2      Darrang       1
  3       Nicobar      1

Table 2 : locality

pincode  Address       city
110020   abc           Nicobar
110021   abcd          Cachar
110024   abcd          Cachar
110023   abc           Nicobar

in table city i have 1430 rows , containing different cities in India.
And in table locality each city contains near about 100 pincode .

What i want to do ?
i want to replace each city(column) in table : locality with its corresponding city_id in table : city .

Question : How can i do this ? Is there any fast way to this ? I don't want to use any programming language ie. php ,java  . Is there any procedure , looping in Mysql ?

Result looks like ie.

Table 2 : locality

pincode  Address       city
110020   abc             3
110021   abcd            1
110024   abcd            1
110023   abc             3

ALTER TABLE locality ADD COLUMN city_id ...;  -- add new column
UPDATE locality
  JOIN city  ON city.city_name = locality.city
   SET locality.city_id = city.city_id;  -- set new column
ALTER TABLE locality DROP COLUMN city;   -- cleanup by removing old column


Understand responsibilities of database and backend Still learning, but there is something I can't understand.
Some features of MySQL seem (to me) to flow on the responsibilities of the backend.
In particular, I'm talking about data validation (excluding relations) for example NULL/UNIQUE.
Now, I'd like to know why this duplicate? Is it a second layer? More in general, in a good application, do I have to count on the errors raised by the DB as an organic part of the application, or should the application be designed to avoid those errors and only count on them as an extrema ratio?

An RDBMS is part of the backend, only in  rare cases such as access, you can have both.
Even so the GUI should always (try to) prevent errors in the database, and should control the input of its validity.
Errors happen, should not directly shown to the user. But as you have to debug it, you should show helpful ids or text, that could help fixing things.
For a qualified system you have to go a step further and tell the user how he can fix the problem, for example be editing his input in a correct way.
Check constraints help to debug your code, especially in the beginning, when the GUI is only starting, so no problem will be missed.
Foriegn keys, triggers and events help the database maintaining or do things that the user has no influence over, since the RDBMS is  a background process (nearly always running on a different machine to the end-user's), that shouldn't come to light.


What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics? We will get several thousands products in our database. The problem is that one product can have several different names e.g. "iPad 16GByte UMTS schwarz" or "iPad 16GB 3G black" and so on. But they are the same product. We would like to combine them together for an exact analysis (like how many products in which city in a specific period of time). What would be the best approach? What are the best experts for this?

I'm not sure how you would do this with a nosql database like mongodb but in a typical data warehouse I would do this with the dimension table hierarchy.

Product_ID   Full_Product_Nm            Product_Lvl1       Product_Lvl2
----------   ---------------            --------------     ------------
1            iPad 16GByte UMTS schwarz  iPad               iPad 16gb
2            iPad 16GB 3G black         iPad               iPad 16gb
3            iPad 64GByte UMTS schwarz  iPad               iPad 64gb
4            iPad 64GB 3G black         iPad               iPad 64gb

Then I could join my fact table to the dimension and pull out the hierarchy level that I would like to report on.

So in your example I could pull out the Product_Lvl1 to get iPad but if I wanted to compare sales/inventory of 64gb compared to the 16gb iPad I could do that using the Product_Lvl2.


How to retain the current value of a sequence when copying a database in SQL Server I have a template database that contains tables with some data I'd like to have always when starting a new project. The idea is to copy the template when starting a new project. I use sequences for getting id column values (in code) for data rows in tables.

The problem is this: when I copy the database using SQL Server Management Studio's Tasks -> Copy Database, and select Use the SQL Management Object method as the transfer method, the sequence values seem to reset to source database's sequence's "reset value" instead of retaining the sequences current value.

Is there any way to retain the current value of sequence automatically when copying the database? Now when I add some data to the template database, I basically have to every time update related table's reset value, or otherwise the data and the current value of sequence will be off in the copied database.

I'm using SQL Server Management Studio 2012 and the server version is 12.0.4237 (so SQL Server 2014).

You can get the current seed values by running:

SELECT
   s.name as [schema],
   t.name as [table],
   IDENT_CURRENT( s.name + '.' +t.name )  NextIdentValue,
   'DBCC CHECKIDENT ('+char(39)+s.name+'.' +t.name+char(39)+',RESEED,'+ CAST(IDENT_CURRENT( s.name + '.' +t.name ) as varchar(50))+ '); ' as cmdSQL
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

Run the cmdSQL script column on the target system.

Depending on your requirements, you can store the output to a table/file and then execute the synchronization script after restore.


Help troubleshooting mysql deadlock We have a query, which is trying to update a row, placing a user at the end of a queue.

To do this, it needs to grab the MAX(initialPositon) and set this row's initialPosition to MAX(initialPosition)+1

Due to the mysql restrictions on updating the same table you are reading from, we've designed this using a join

It's important this query is atomic, because it could be hit under heavy concurrency and we don't want the position to be duplicated

Here's the query:

UPDATE ListingQueue LQ
            JOIN (
                SELECT
                    (MAX(initialPosition) + 1) AS maxpos,
                    listingid
                FROM
                    ListingQueue
                WHERE
                    ListingQueue.listingid = 945877
                    FOR UPDATE
            ) AS LQ2 ON LQ2.listingid = LQ.listingid
            SET LQ.initialPosition = LQ2.maxpos,
                LQ.stage = 'waitingroom',
                LQ.stageLastUpdate = NOW()
            WHERE
                LQ.token = '6HMQ2JPcMTxUcJquw9SFH9ZQ76@CRbBf6p'
            AND LQ.listingid = 945877

This works fine, under normal conditions. When we hit it with concurrency, we get deadlocks:

**Deadlock found when trying to get lock; try restarting transaction**

Any suggestions on how to avoid this lock?

Table Structures:

  CREATE TABLE `ListingQueue` (
 `token` varchar(100) NOT NULL,
`listingid` int(11) unsigned NOT NULL,
`initialPosition` int(11) unsigned DEFAULT NULL,
 `stage` enum('waitingroom','inqueue','eventpage','frontofqueue') DEFAULT     'waitingroom',
 `stageLastUpdate` datetime DEFAULT NULL,
 `enteredQueue` datetime DEFAULT NULL,
 PRIMARY KEY (`token`,`listingid`),
 KEY `currentPos` (`listingid`,`initialPosition`,`stage`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is an ugly solution that may introduce other issues.  It is offered as a means to eliminate the lock you are experiencing.  Auto_increment is preferable to managing numbers in this manner.

The number could be managed in its own table incremented as needed.  This table could be locked at no detriment to the main table.  When write locked, other sessions are prevented from using the table, the locking session can then increment the number (and store it in a variable - something meaningful like @next_position) then releasing the lock immediately after the update.  The number is then used in your main query - no join, simply "LQ.initialPosition = @next_position".

You may still have contention on the position number table - but these locks shouldn't be held too long - only during the incrementation to ensure only one process increments the number at a time.

The locking scheme could be eliminated, but this would require knowing the current number first which would then be tested in the update's where clause to eliminate race conditions with other sessions.  A loop will be necessary to try again if the update doesn't work (another session made the update first).  This complicates the use of the table but reduces the potential for trouble.


Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian After upgrading my Debian development box I have both PostgreSQL 9.1 and 9.4 on my system.  I want to have 9.4 serve my data and get rid of the outdated version.

AFAICS, it is important to use a working directory where the postgres user can write, and configure the old and new services in a way which allows them to run simultaneously (using different ports).  Thus, this is what I tried:

# cd /tmp/
# sudo -u postgres /usr/lib/postgresql/9.4/bin/pg_upgrade \
--check \
--retain \
--old-bindir=/usr/lib/postgresql/9.1/bin/ \
--new-bindir=/usr/lib/postgresql/9.4/bin/ \
--old-datadir=/var/lib/postgresql/9.1/main \
--new-datadir=/var/lib/postgresql/9.4/main \
--old-options /etc/postgresql/9.1/main/postgresql.conf \
--new-options=/etc/postgresql/9.4/main/postgresql.conf \
--old-port=5433 \
--new-port=5432

This failed (because of postmasters serving the running services).

The --old-port and --new-port options mirror the settings in the configuration files; thus, both services are able to run at the same time.  I stopped them both and tried again:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5433"?

could not connect to old postmaster started with the command:
"/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b /etc/postgresql/9.1/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/tmp'" start
Failure, exiting

The log file pg_upgrade_server.log contains:

-----------------------------------------------------------------
  pg_upgrade run on Mon Jul 18 11:46:33 2016
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b /etc/postgresql/9.1/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: invalid argument: "/etc/postgresql/9.1/main/postgresql.conf"
Try "postgres --help" for more information.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

As far as pg_ctl --help tells me, there is no -b option; the file /etc/postgresql/9.1/main/postgresql.conf does exist.

What is going on here, and how can I get the upgrade done?

For the records, I gave up trying this method and did it via pg_dumpall instead.

Here is my earlier question in the unix area (with some more detail), and of course the PostgreSQL docs about upgrading where this is still the first method described (and obviously prefered).


Piplelined Result Set over Database Link Alternatives Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there.  I can think of a few, but I'd rather not lead the question.  I am particularly interested in alternatives that work when the function would accept more than one parameter and return more than one row of data.

Hoo boy - that's not going to be simple, as far as I know.

A few things I guess you could try:

Drop a view on top and stream those results across the link. The problem is going to be passing parameters though...
Have a routine across the link that you can pass parameters to that dumps your pipelined results to a table that you can then stream across the link. Then perhaps have a cleanup function that nukes the table when you're done with it (but beware concurrency...)
Re-architect? Instead of pipelining rows, you could return a collection of records (not sure how that translates across the link, though -- never tried it). Alternatively, a method whereby you could call it several times, each time returning the values of a row in a record. Neither is particular elegant. (You'd have to have some sort of start/next function at least, if not more.)

Pipelining is such a useful feature that it is a shame it doesn't translate across the db-link, though. Maybe Oracle will, one day, see fit to fill that particular gap. Otherwise, though, I fear you've got your work cut out for you.


What's the relationship between Documents, XSDs and Collections? I have a number of XML Schema Definitions describing business objects. It's unclear to me what the correct way to deploy these is if my intention is to make typed xml columns. The name Xml Schema Collection implies that more than one Xsd Document definition can exist in a collection, but any example I look at shows a Collection as the specific constraint on the column, not a document within it.

Do I create a schema collection called 'BusinessObjects' and create individual Xsd Documents inside it? Do I make one Collection for each Document?

Yes, you are allowed to create multiple schema namespaces within a collection (in the Examples section of CREATE XML SCHEMA COLLECTION, see B. Specifying multiple schema namespaces in a schema collection).

But you can only associate a single XML Schema Collection with an XML field or variable. So whatever schema definitions you might put into a collection should all be relevant to the XML documents that you are going to store in the XML field or variable that will be assigned that XML SCHEMA COLLECTION.

If you will have different XML fields for different types of entities, then I would think those should have their own XML SCHEMA COLLECTIONs.

For more information, please see:

XML Schema Collections
Requirements and Limitations for XML Schema Collections on the Server


Get timing on individual queries in Anonymous code block I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON gives me the execution time for the whole block. Any idea how to get the time of JUST the last query? The beef is that I don't want the previous queries to interfere with the timing it gives me. Example below:

set echo on;
set feedback on;
set timing on;
set serveroutput on;

declare
  pid varchar(20);
  aid varchar(20);
  bob char(1);
begin

  select st.some_id, st.person_id into aid, pid
    from some_table st
      join person p on st.person_id = p.person_id
    where p.name not like '%bob%' and rownum = 1;

  -- get timing of another query here

end;

When I run this it gives me the total execution time of the code block:

Elapsed: 00:00:00.045

Save sysdate or systimestamp into a pl/sql variable just before the query then use dbms_output.put_line to output the difference afterwards.


Physical only checkdb is failing, but full one is completed successfully I am executing checkdb with physical_only option and it fails with multiple errors like below:

  Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1557580587,
  index ID 1, partition ID 72057594088456192, alloc unit ID
  72057594177454080 (type In-row data). The off-row data node at page
  (1:13282192), slot 3, text ID 6370769698816 is referenced by page
  (0:0), slot 0, but was not seen in the scan. Msg 8965, Level 16, State
  1, Line 1 Table error: Object ID 1557580587, index ID 1, partition ID
  72057594088456192, alloc unit ID 72057594177454080 (type In-row data).
  The off-row data node at page (1:13282192), slot 5, text ID
  6370769764352 is referenced by page (0:0), slot 0, but was not seen in
  the scan. CHECKDB found 0 allocation errors and 5255 consistency
  errors in table 'TableX' (object ID
  1557580587). CHECKDB found 0 allocation errors and 5255 consistency
  errors in database 'DatabaseX'. repair_allow_data_loss is the minimum
  repair level for the errors found by DBCC CHECKDB (DWH_LAND).

However full checkdb is successfull:

  CHECKDB found 0 allocation errors and 0 consistency errors in database
  'DatabaseX'. DBCC execution completed. If DBCC printed error messages,
  contact your system administrator.

TableX has around 200 000 rows and has clustered columnstore index on it.
 We are using following version of SQL Server:
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4

Should I be worried?

Yes, this a defect in SQL Server 2017 up to CU14. There was a similar issue with nonclustered columnstore indexes in SQL Server 2016.

It's detailed in this video: CHECKDB Bug With SQL Server 2017 And ColumnStore Indexes

A more portable repro of the issue is this:

SELECT *, CONVERT(NVARCHAR(MAX), m.text + m.text + m.text + m.text + m.text) AS texty
INTO dbo.corrupt
FROM sys.messages AS m;

CREATE CLUSTERED COLUMNSTORE INDEX cx_corrupt ON dbo.corrupt;

DBCC CHECKDB WITH PHYSICAL_ONLY;

DROP TABLE dbo.corrupt;


Postgresql - Internal Array handling - array type or pointer type I have a question, about the internal handling of array columns in Postgres.
Does the engine handle it as a classical array with memory alloc or is it somehow a pointer list.

It would be interesting to know - I have a few questions to answer myself concerning performance... :)

Arrays are defined in src/includes/utils/array.h and src/backend/utils/adt/arrayfuncs.c and the other array* files in src/backend/utils/adt/.

An array is a VARLENA (see src/backend/utils/adt/varlena.c) which is a palloc()'d type, a scoped-storage wrapper around malloc(). Arrays are block-allocated data, not linked lists.


What's the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields? I've just had some database backup problems on a website I run containing some important lead data. Unfortunately, I've now lost one of the key MySQL tables, which means my data has lost its relationship and is now hard to read and manipulate in Excel. Luckily, I did manage to keep 1 table which contains the important values. I now just need to re-format it for Excel to make it easier to work with.

Currently when I export from the DB to CSV the data looks like this:

id,lead_id,field_number,value
1,1,1,Mickey
2,1,2,Mouse
3,2,1,Minnie
4,2,2,Mouse

I would like to re-format this data into the following format:

id,lead_id,field_1,field_2
1,1,Mickey,Mouse
2,2,Minnie Mouse

I hope that makes sense, I wasn't sure how else to explain this question.

This will generate a CSV per lead_id in order of field_number.

SELECT lead_id, lead_id, GROUP_CONCAT(value)
FROM test_table
GROUP BY lead_id
ORDER BY field_number
INTO OUTFILE '/tmp/result.txt'
  FELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

There are 2 lead_id columns to match your output. MySQL doesn't have ROW_NUMBER() to generate an arbitrary sequence.

I have found that the following works better to order the fields properly:

SELECT lead_id,
    GROUP_CONCAT(value
        ORDER BY field_number)
    FROM exp_wp_rg_lead_detail
    GROUP BY lead_id


How does IDENTITY_INSERT affect concurrency? I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support.

Under certain circumstances, it archives and incomplete post from the posting queue table to the posting archive table. I need to move these archived results back into the queue.

The queue ID is an identity column and I would like to keep it the same.

The question is, if I do identity_insert on/insert/identity_insert off, what can I expect with respect to concurrency with processes that create the queue entries and expect the identity column to be generated automatically?

Any pointers on the best way to demonstrate such behavior would be greatly appreciated as well.

Setting IDENTITY_INSERT ON on its own does not eliminate concurrency - this does not place any exclusive locks on the table, only a brief schema stability (Sch-S) lock.

So what could theoretically happen, under the default behavior, is you could do this in session 1:

BEGIN TRANSACTION;

-- 1
SET IDENTITY_INSERT dbo.tablename ON;

-- 2
INSERT dbo.tablename(id, etc) VALUES(100, 'foo'); -- next identity is now 101

-- 3
INSERT dbo.tablename(id, etc) VALUES(101, 'foo'); -- next identity is now 102

-- 4
SET IDENTITY_INSERT dbo.tablename OFF;

COMMIT TRANSACTION;

In another session, you can insert rows into the table at points 1, 2, 3 or 4. This may seem like a good thing, except what happens for any insert that happens between 2 and 3, is that the auto-generated value triggered by another session is based on the results of statement 2 - so it will generate a 101, and then statement 3 will fail with a primary key violation. This is pretty simple to set up and test yourself with some WAITFORs:

-- session 1
-- DROP TABLE dbo.what;
CREATE TABLE dbo.what(id INT IDENTITY PRIMARY KEY);
GO
BEGIN TRANSACTION;

SET IDENTITY_INSERT dbo.what ON;

INSERT dbo.what(id) VALUES(32);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(33);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(34);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(35);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(36);

SET IDENTITY_INSERT dbo.what OFF;

COMMIT TRANSACTION;

Once that batch has started, start this batch in another window:

-- session 2
INSERT dbo.what DEFAULT VALUES;
WAITFOR DELAY '00:00:01';
GO 20

Session 2 should only ever insert values from 1-20, right? Except that because the underlying identity has been updated by your manual inserts session 1, at some point session 2 will pick up where session 1 left off, and insert 32, or 33, or 34 etc. It will be allowed to do this, but then session 1 will fail on the next insert with a PK violation (which one wins may just be a matter of timing).

One way to workaround this is to invoke a TABLOCK on the first insert:

INSERT dbo.what WITH (TABLOCK) (id) VALUES(32);

This will block any other users trying to insert (or do anything, really) with this table until you are done moving those archived rows back. This throttles concurrency, sure, but this is the way you want blocking to work. And hopefully this isn't something that's happening at such a frequent rate where you're blocking other people all the time.

A couple of other workarounds:

stop caring about the IDENTITY value generated. Who cares? Use a UNIQUEIDENTIFIER (maybe generated in a separate table with an IDENTITY as a surrogate) if the original value is very important.
change the archive process to use a "soft delete" where something is marked as archived initially and the archival isn't made permanent until some later date. Then whatever process is trying to move them back can simply perform a direct update and fix the soft delete flag.


Designing Simple Schema for Disaggregation of Demand Forecast I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:

I have a parent-child hierarchy of products (example, Raw Material > Work in Progress > End Product).

Orders are placed at each level.
Number of orders shall be viewable in weekly buckets for the next 6 months.
Demand forecast can be done for each product level.
Demand forecast for any week within next 6 months can be done today.
Demand forecast is done for weekly buckets, for the next 6 months.

Demand Forecast is usually done at the higher level in hierarchy (Raw Material or Work in Progress level) It has to be disaggregated to a lower level (End Product).

There are 2 ways in which demand forecast can be disaggregated from a higher level to lower level:

User specifies percentage distribution for end product. Say, there's a forecast of 1000 for Work In Progress.. and user says I want 40% for End Product 1 and 60% for End Product 2 in bucket 10.. Then for 10th week (Sunday to Saturday) from now, forecast value for End Product 1 would be 400 and, for End Product 2 would be 600.
User says, just disaggregate according to orders placed against end products in Bucket 5, and orders in bucket 5 for End Product 1 and 2 are 200 and 800 respectively, then forecast value for EP1 would be ((200/1000) * 100)% and for EP2 would be ((800/1000) * 100)% of forecast for 'Work in Progress'.

Forecast shall be viewable in weekly buckets for the next 6 months and the ideal format should be:

product name | bucket number | week start date | week end date | forecast value | created_on

PRODUCT_HIERARCHY table could look like this:

id  |   name                |   parent_id
__________________________________________
1   |   raw material        |   (null)
2   |   work in progress    |   1
3   |   end product 1       |   2
4   |   end product 2       |   2

ORDERS table might look like this:

id | prod_id | order_date | delivery_date | delivered_date

where,

prod_id is foreign key that references id of PRODUCT_HIERARCHY table,

How to store forecast?
What would be a good basic schema for such a requirement?

My idea to select orders for 26 weekly buckets is:

SELECT
    COUNT(*) TOTAL_ORDERS,
    WIDTH_BUCKET(
        delivery_date,
        SYSDATE,
        ADD_MONTHS(sysdate, 6),
        TO_NUMBER( TO_CHAR(SYSDATE,'DD-MON-YYYY') - TO_CHAR(ADD_MONTHS(sysdate, 6),'DD-MON-YYYY') ) / 7
    ) BUCKET_NO
FROM
    orders_table
WHERE
    delivery_date BETWEEN SYSDATE AND ADD_MONTHS(sysdate, 6);

But this will give weekly buckets starting from today irrespective of the day. How can I convert them to Sunday to Saturday weeks in Oracle?

Please help designing this database structure.

(will be using Oracle 11g)

Okay, so here's the data model I came up with.

PRODUCT -- to store product information and maintain parent-child hierarchy

id  NUMBER  "Primary Key Not Null"
level_code  VARCHAR2    Not Null
name    VARCHAR2    Not Null
description VARCHAR2
parent_id   NUMBER  Foreign Key references PRODUCT(id)

ORDERS -- to store orders for products

id  NUMBER  "Primary Key Not Null"
prod_id     NUMBER  "Foreign Key references PRODUCT(id) Not Null"
order_type  VARCHAR2    "Not Null Default 'Default'"
order_qty   NUMBER  Not Null
order_date  NUMBER  Foreign Key references DATE_INFO(date_key)
delivery_date   NUMBER  "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"

FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)

id  NUMBER  "Primary Key Not Null"
product_id  NUMBER  "Foreign Key references PRODUCT(id) Not Null"
forecast_value  NUMBER  Not Null
week    NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"

DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level

id  NUMBER  "Primary Key Not Null"
parent_product_id   NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
child_product_id    NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
method  VARCHAR2    Not Null
from_week   NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution    NUMBER  Not Null

DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls

date_key    NUMBER  "Primary Key
Not Null"
full_date   DATE    Not Null
week_begin_date DATE    Not Null
week_end_date   DATE    Not Null

As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function

CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
  RETURN DATE
IS
  week_start_date DATE;
BEGIN
  SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
  INTO week_start_date FROM dual;
  RETURN week_start_date;
END;


Inaccurate count when using multiple count functions in PostgreSQL PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.890

I'm relatively new to SQL and am learning a lot of about writing queries as in my current work role. I encountered this problem that puzzles me. If I do a count of subscriptions that are paid with a null churn date, I get a count of 1279. This is the correct number. But once I add the count of customers that installed shopify, this count jumps to 1835. Why would this happen? Can anybody help me out? Thanks!

select
    count (s.account_id)
  , count (case when c.type ilike 'shopifychannel' and s.status = 'paid'
                then c.created_at else null end) as installed_shopify
from subscriptions s
join channels c on s.account_id = c.account_id
where s.status = 'paid'
  and s.churned_at is null

Count s.id will count the total number of customers who have s.status = 'paid'
Count case when 'shopifychannel' will count the total number of customers who have paid and installed a shopify channel. These are the outputs I am attempting to get.

I suppose you have a problem with your channels table that may return more than one row per s.account_id. I propose this query :

SELECT
    COUNT (DISTINCT s.account_id),
    COUNT (DISTINCT CONCAT(s.account_id, c.type)) AS installed_shopify
FROM subscriptions s
LEFT JOIN channels c ON s.account_id = c.account_id AND c.type ILIKE 'shopifychannel'
WHERE s.status = 'paid'
  AND s.churned_at IS NULL

Again, you must be sure that there is only (zero or one) row in channels for each account_id having the condition c.type ilike 'shopifychannel'.

The LEFT JOIN assure you don't miss a row in your primary table subscriptions. The condition c.type ILIKE 'shopifychannel' is in the LEFT JOIN because if you put it in the WHERE clause, you'll also lose subscriptions lines that don't have installed Shopify.


Appropriate database for web analytics? I am working on the web analytics it is similar to google analytics,  Currently i am using the mongoDB.
Problem using MongoDB
Writing and reading is performed at the same time so after some point writing is bit slow. So i am looking for appropriate database which is suitable for following requirement.
Requirements

Open Source

Can adapt large volume of data.

Better performance while aggregating the data.

Time based aggregation.

Real time processing.

Driver support for node.js and java.

Database should be scalable with node.js and java
 Saving the data using java.

 Fetching the data using node.js

I think you are looking for DBs that are designed for analytics.
May be you should check these solutions:.
some data storage solutions specific to realtime analytics

Druid
Spark
Cassandra
Hbase
hypertable
accumulo

reference:

http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis
http://relistan.com/cassandra-vs-mongo/


Drill Down Functionality for Data Entry in Database I am building a database that houses financial statement data. Within the database and to maintain the relational model and data integrity the fields are standardized. However, I do not want to lose the supporting data that the standardized data represents. For example, an income statement from one entity may be more detailed than another entity.

Company A
Depreciation = 100
Taxes = 50
Other Expenses = 300
Total Expenses = 450 * This is what is stored in the relational model

Company B
Total Expenses = 400

If I wanted Total Expenses to be the standardized field how could I capture within that field the detailed data that supports that total expense figure?

Many Thanks!

One of my favorite approaches to problems like these in the context of data warehousing usually takes the form of an agile design practice, where the granularity of a system does not need to always be "rolled up" from the lowest level right away, but can be, as stated in the title of the topic "drilled down" into, as the finer granularity details become available.

Assuming you have some table Expense with columns Expense_PK, CompanyName and TotalExpenses,  I would simply create a new table, ExpenseDetail, containing columns ExpenseDetail_PK, Expense_FK, Taxes and OtherExpenses, adding a detail record when one was available.

The nuance of doing this is to be stalwart in maintaining the detail record properly.  Should the nature of OtherExpenses be revealed to be solely comprised of Adjustments, Dues and Assessments, it is often far better to extend the detail record with the additional atomic columns for each of the now determinant fees than to add another OtherExpensesDetail table to the mix.  On the other hand, in Canada anyway, the Taxes column would pose a more than reasonable opportunity for a TaxesDetail table, defined TaxesDetail_PK, ExpenseDetail_FK, GST, PST, so that Company A could provide additional tax breakdowns in the form of Federal and Provincial taxes, where as theoretical Company C could simply be allocated the aggregated Taxes value in the detail record if that was all that was available or were operating in a province that only applied a singular harmonized sales tax ( HST ).

That said, I feel there are many ways of going about problems like these, but this is at least one example of something that is quite versatile and works quite well for what it is.


pgAdmin 4 v4 does not prompt to save on page refresh (I can't seem to find this issue anywhere else despite being a seemingly common issue)

Typically when I'm using the Query Editor I press F5 to run my query. With pgAdmin being a browser application, this can be an issue since F5 also refreshes web pages. In pgAdmin 4 v3, it would prompt to save my file if it tried to refresh the page, so it wasn't a big deal. It doesn't do that in version 4, so I have to save frequently and, when I forget, I just lose my progress with no warning.

How come pgAdmin 4 doesn't prompt to save? Is there a way to turn this back on? Is there a way I can reach out to the developers and request this be a feature, if it isn't already?

Thank you in advance.

Version pgAdmin 4 v4.2

There is a setting: File -> Preferences -> Browser -> Display -> Confirm on close or refresh

It worked fine.

I've created an issue here which is rejected: https://redmine.postgresql.org/issues/3993#change-15078

In pgAdmin 4 v4.1:
File --> Preferences --> Query Tool --> Options --> Prompt to save unsaved query changes?
Didn't work for me so I've updated to 4.2


How to create two additional columns in a results set to act as axis for a heatmap I have a set of results from a WSUS report that consists of computername and the status level of patching on that computer.

I have been asked to display this as a heatmap e.g each computer is a single cell with a color assigned depending on the patch status (e.g. failed, waiting for reboot, downloaded etc) and to do this I need some columns to use as axis. Heatmaps needs 3 columns (x,y and heat) I only have the heat at the moment.

I can do this with cursors iterating over each row incrementing the counters for two columns and inserting the row plus these two values into a temp table and then return that but it is not efficient.

Is there a better way of doing this?

Ultimately I am after

Computer Name | Heat | X | Y
Comp1         | 4    | 1 | 1
Comp2         | 4    | 1 | 2
Comp3         | 4    | 2 | 1
Comp4         | 4    | 2 | 2

Where X & Y are the generated columns (i.e. not in the data returned from the query)

I am using SQL Server Express 2014 for dev but the production target will probably be 2012 or 2014 full version.

Answer originally left by the question author in a comment

Final solution was:

SELECT
    ...,
    (((ROW_NUMBER() OVER (ORDER BY (SELECT 1))) -1 ) / @cnt) + 1 AS X,
    (((ROW_NUMBER() OVER (ORDER BY (SELECT 1))) -1 ) % @cnt) + 1 AS X


SQL Server 2012: Generate Scripts from command line I am running SQL Server 2012.

The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts.

Is there a way to automate that via command line somehow?

I want to create a script that includes the schema and data of the entire data base.

Tools like ScriptDB and sqlpubwiz.exe all seem to target SQL Server 2005.
What about SQL Server 2012?

Best is to use Powershell - if you are going to use it frequently. You can refer to Automated Script-generation with Powershell and SMO.

Also, SQL Server PowerShell Extensions (SQLPSX) are of great value when working with Powershell. All the modules are having help files e.g. Get-SqlScripter.

Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path D:\scripts\script.sql
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlView | Get-SqlScripter

For third party tools, highly recommend to check out (there are many third party tools out there, but below ones I have used and they are great):

Redgate's SQL Compare and Data Compare
SSMS Tools Pack by Mladen Prajdić


Applying Single Table Inheritance From that StackOverflow's question:

Summarizing:

My CRUDs are: customers, employees and branches.
Customers and Employees are associated with one Person, one Individual (fields related to the person itself) and one User (for login purposes). So Individuals and Users are always related to one Customer or Employee person.
Branches are associated with one Person and one Company (fields related to the corporate person). So Companys are always related to one Branch person. They didn't have an user because they're affiliated to the company that this database stands for - it's their employees who can authenticate into the app.
An Employee belongs to one Branch. A Branch has zero or many Employees. (yeah, diagram is incorrect, sorry!)
Currently I'm using a single bit to diff Employee role - I've employees with operational and administrative rights. Roles permissions goes in the code itself, and here you can realize that customers also have their own role... Any suggestions to implement a better (and simple) way to manage that roles for both customers and employees?

How should I change that wrong 3NF structure to achieve a well designed single table inheritance for a better use of MVC pattern?

Update

Ok, I tried to simplify it a bit, and now I accomplished this new structure:

Can I still improve it? How?

Unless I'm misunderstanding your requirements, the Person, Individual, Company, and User tables could be combined into a single record since the relationships between these tables are all one-to-one.  Unless this is some kind of trick question...

One could go so far as to say all the tables that involve one-to-one relationships could be combined into a single table since that would eliminate the duplicate foreign keys, thereby saving space.  Having said that you'd really have to be hard pressed for space to worry about saving that small an amount of space.

It looks to me like you need to revisit your design spec with an eye for simplification.


Permissions for runtime user on all objects in schema (including future objects) In PostgreSQL 10 - when using an "owner user" (login role owning a schema and all tables, not used by the application at runtime) and a "runtime" user I can GRANT Select/Update/Delete permissions on all existing tables in the schema to the runtime user:

GRANT SELECT ON ALL TABLES IN SCHEMA owner TO runtime;

But this only applies to existing objects and not new tables which might get created later on.

Is there a way to avoid granting on all new objects by way of inheriting roles or schema permissions or similar?

Yes. The key word is DEFAULT PRIVILEGES.

ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA owner
GRANT SELECT ON TABLES TO runtime;

Grants the specifies privileges for all specified objects created in the future, by the specified role to the other specified role.

I specified the target_role (owner_user) explicitly to avoid ambiguity. Else, quoting the manual:

  If FOR ROLE is omitted, the current role is assumed.

Related:

Grant all on a specific schema in the db to a group role in PostgreSQL
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

And don't forget access to sequences if you have any serial columns. The first linked answer has instructions.


How does SQL bind rows in filestream-enabled table with their files Every BLOB stored in a filestream-Enabled table actually stored in a physical file, How can I know the actual filename for a specific row in that table.
For example, I have the Images table which is filestream-enabled, it have a filestream column called Data its type : VARBINARY(MAX).
Here is an example of that table that have 19 rows and also the filestream folder contains 19 rows :

Is it stored in some system table, or there is direct relationship between the "ROWGUIDCOL" and its name ?

Just as caveat it is best not to disturb the physical files involved with FILESTREAM for a database.

Below are a few good links on the architecture:
Paul Randal White Paper - FILESTREAM Storage
Paul Randal Blog Post - FILESTREAM directory structure

As quoted from Paul's blog post above:

  The FILESTREAM file names are actually the log-sequence number from the database transaction log at the time the files were created. You can correlate these by looking at the data with DBCC PAGE, but first finding the allocated pages using sp_AllocationMetadata (see this blog post).

Note the stored procedure he mentions is an undocumented one that he provides some additional information to in the blog post he linked.


Distinct value across multiple columns I have a table like this,

CREATE TABLE foo (
    id integer,
  col1 integer,
  col2 integer,
  col3 integer,
  col4 integer,
  col5 integer
);
INSERT INTO foo (id, col1, col2, col3, col4, col5)
VALUES
    (322, NULL, 3, 1, 2, 2),
    (323, 1, 3, 1, 2, 2),
    (324, 1, 1, 1, 2, 2),
    (325, 3, 3, 3, 3, NULL);

I need to write a query such that I need to get

ID, concatinated string of unique value from the columns without null

My desired output should be

ID, UniqVal
322, "3,1,2"
323, "1,3,2"
324, "1,2"
325, "3"

Note: The version is MySQL 5.5

Select each column along with the ID where the column IS NOT NULL. Use UNION to combine the result sets. This will already eliminate duplicates. Then GROUP BY the ID and use the group_concat() aggregation function to build your list.

SELECT id,
       group_concat(col
                    SEPARATOR ',') uniqval
       FROM (SELECT id,
                    col1 col
                    FROM elbat
                    WHERE col1 IS NOT NULL
             UNION
             SELECT id,
                    col2 col
                    FROM elbat
                    WHERE col2 IS NOT NULL
             UNION
             SELECT id,
                    col3 col
                    FROM elbat
                    WHERE col3 IS NOT NULL
             UNION
             SELECT id,
                    col4 col
                    FROM elbat
                    WHERE col4 IS NOT NULL
             UNION
             SELECT id,
                    col5 col
                    FROM elbat
                    WHERE col5 IS NOT NULL) x
       GROUP BY id;

SQL Fiddle


Is there a way to access temporary tables of other sessions in postgres? I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis tell me: ERROR: cannot access temporary tables of other sessions when trying to query the data. I tried changing the permissions of the schema and table, but this didn't seem to help, even though I'm accessing the database with the same user as the program itself (at least in dbVis). Is there a setting I can change in my database that allows me to have "root" access to all session in my database?

The lack of access to temporary tables in other sessions is not a matter of permissions, it's a technical limitation of the design. A PostgreSQL backend can't access temporary tables of another backend because none of the usual housekeeping to allow concurrent access is done for temporary tables.

In 9.2 you will want to use an UNLOGGED table instead; this can be visible from other sessions, but retains most of the performance benefits of a temporary table.


SQL Server - Files timestamps unchanged so what is it that's being backed up? Please see the below snapshot, showing the timestamps of all the data files as well as transaction log files for one of the SQL Server instances under my management. This is a production instance.

Today is Dec 27th, 2019. However, as you can see, all the timestamps are showing much older times.
The truth is, I took this snapshot a few days ago on 24th Dec. However, as I checked today, the situation is still unchanged.
The snapshot above was taken from the user databases. I checked the system databases as well, which are residing on a separate drive, and took the snapshot of the files' timestamps, which were as shown below:

These databases are certainly not super active. But, I am certain that there are transactions running all the time. Additionally, I looked at the "Schema Changes History" report for a few of them, and could confirm that our maintenance jobs have been working and rebuilding indexes in as recently as yesterday 26th Dec.
The databases are all in FULL recovery model (although that may not be relevant - just a fact to mention).  The OS is Windows NT 6.0 (= Windows Server 2007) and SQL Server version is 2008 SP3. Both the OS and SQL Serve are due for upgrade next year Q1. Also as is evident from the above snaps, with data files and log files both residing in the same disk drive and folder, the configuration of this server which I have "inherited" is certainly not the best. So, hopefully all will be fixed during the planned upgrade/migration activity which is going to happen in a few months.
Now the question for me is why the timestamps of these files are not getting updated, despite the databases being active anyway? (even manually running of Checkpoint didn't change anything). and then, if the transaction log files are not getting updated, will the backups be good and reliable? Do they really contain the records that they are supposed to?

In answer to your main question ("what is being backed up"), everything that is supposed to be   More seriously, you can't use those time stamps as a sign of changes happening in the database files.  SQL Server doesn't make any guarantees about how the modified date of database files will be changed or updated.

There is an interesting blog post here that attempts to find patterns with when and how the date modified stamp is updated on MDF files:

SQL Server Database File - Date Modified

Their observations line up with your situation I think: normal writes to the data file don't update the date modified stamp.  It looks like growth events (manual or automatic) would update that value, as would restarting the service (or anything else that might "open" or "close" a database - using the autoclose feature, explicitly taking the DB offline, etc).

I expect 19/12/2019 9:52 PM corresponds with a Windows or SQL Server restart, which is how several of the date / times got sync'd up.

However, again, this behavior is undocumented.  Any patterns we notice could change at any time, or have unexpected edge cases.

For SQL Server data files residing on an NTFS file system, this applies:

  The only guarantee about a file time stamp is that the file time is correctly reflected when the handle that makes the change is closed.

Though, to be clear, that's a guarantee provided by Windows.  We can't necessarily know a ton about how SQL Server creates and destroys file handles in every scenario.

David Browne (a Top Microsoft Man) mentioned this in a comment, which is useful to know:

  SQL Server keeps the database files open whenever it’s running, for better performance and protection against other processes changing them. So the attributes don’t get changed on every file write.


Why query_cache_type is disabled by default start from MySQL 5.6? We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the  query_cache_type is defaulted to off start from 5.6.

We enabled it again and see the loading decrease, why this value is being disabled by default start from MySQL 5.6? I cannot see the problem in enabled it.

You need the history of InnoDB to understand why. Here it goes:
WAR STORY
InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the InnoDB Buffer Pool and then crosschecking the Query Cache for the same changes.
PEACE TREATY
Before MySQL 5.0, the query cache was disabled for InnoDB. Now, InnoDB interacts with it. To simplify matters, you can just disable the Query Cache by setting the query_cache_size to 0.
According to the MySQL Documentation on query_cache_time

If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

TERMS OF SURRENDER
Setting query_cache_size to 0 is not a one-size-fits-all solution.
The reason for the war, in the first place, is overhead. InnoDB will always inspect changes. A bigger query cache will make InnoDB work that much harder. Disabling the query cache let's the InnoDB and Query Cache be happy. However, you (the Developer/DBA) might be a casualty of that war by means bad query performance, even with such a peace treaty in place.
Depending on the following

Workload
Frequency of Changes
Frequency of reading the same data

you should set query_cache_size to whatever number you feel increases performance (This being tantamount to starting an underground movement).
EPILOGUE
In case you are wondering where I came up with this war story, please see my old post

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?

Read it carefully because I learned this from Pages 209-215 of High Performance MySQL (2nd Edition)
I have recommended disabling the query cache to others before

Sep 25, 2013 : invalidating query cache entries(key)
Sep 26, 2013 : query cache hit value is not changing in my database
Dec 23, 2013 : MySQL with high CPU and memory usage

NOTE : I realize the question was about the query_cache_type. It does have an effect on the query cache. Disabling the cache squashes InnoDB's dominance over it. Setting the query_cache_type manually simply forces the Developer/DBA to think carefully about the type of queries the query cache will encounter.


Can data changes become visible between a subquery and an outer statement in PostgreSQL? I have a PostgreSQL query that looks like this:

DELETE FROM x WHERE x.id NOT IN (SELECT x_id FROM y WHERE ...);

Is it possible for the DELETE to see rows that weren't previously committed and visible to the SELECT subquery?

My intuition is that since the subquery is part of the overall statement, no data changes should be visible to the DELETE that weren't already visible to the SELECT. Is this correct?

Is it possible for the DELETE to see rows that weren't previously committed and visible to the SELECT subquery?

No, that's not possible.

Postgres always operates at "read committed" isolation level (or higher) and every statement sees a consistent snapshot of the database from the time the statement started. And in this case the statement defining the snapshot is the DELETE statement. The select from the sub-query sees the same snapshot


Could a busy database affect others on the same server? I have a very busy database with lots of reads and writes. I want to create another database on the same server, but I am wondering if that busy database will slow down the performance of the second one.

I'm using SQL Server.

In general I want to understand if a blocking query will be per database or per server instance.

Will a busy database affect others? In general, yes. Within a single instance all databases share buffer pool, worker threads, plan cache and other system resource. There is a single TempDB for the instance to which all queries write (or spill) when needed.

Installing multiple instances on one machine may not prevent this. Likely they will still share CPU cores, network cards and (perhaps) disk spindles. A very busy DB on one instance could adversely affect a DB on another instance that shares these types of resources. Putting the instances in VMs does not avoid this conflict.

It is possible to configure each instances to have dedicated CPU, memory, disk, network cards etc. Then there will be little or no impact of one on the other. By then you have, I would argue, two independent machines residing inside a single case.

So your question about a blocking query comes down to system configuration. If a query in one DB blocks, and it does so while holding lots of resource - say an exceedingly large temp table or very many rows in an open transaction - and that resource is shared with another DB - then another query in a separate DB may not be able to acquire the resource (disk, memory etc) it requires and will fail.

The shared and exclusive locks taken in the normal course of DML, that are active in one DB will not affect what reads and writes can occur in the other DB (in the absence of cross-database queries).


XML query filter by element name I have the following structure with a variable number of elements on the 2nd level under root with unknown names:

<root>
   text a
   text b
</root>

I need a query that delivers the text of a specific element in the 2nd level. the name of the required element is the argument passed in by a user.

I already succeeded in finding it for example for element "A", querying the XML document stored in variable @x:

declare @str varchar(1000) = '<root>text atext b</root>'
declare @x xml = convert(xml, @str)
SELECT xLangStruct.x.value('(A/text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x)

This works for "A", but now I need to replace "A" with a variable or any kind of filtering... So I need anything like that:

SELECT xLangStruct.x.value('(@SEARCHEDELEMENT/text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x)

or

SELECT xLangStruct.x.value('(./text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x) WHERE xLangStruct.x.Elementname = @SEARCHEDELEMENT

While there's no messing with Mikael Eriksson when it comes to XML (he is, after all, my spiritual XML guide), here's an answer specific to your question.

You just have to change the way you're handling the XML a little bit. I also changed the XML a touch to rule out false positives searching for A and B in the node names to text 1 and text 2.

DECLARE @xmltable TABLE (xmlvalue XML)
INSERT @xmltable ( xmlvalue )
    VALUES ( '<root>text 1text 2</root>' )

DECLARE @searchstring VARCHAR(100) = 'A'

SELECT x.*,
xa.c.value('.', 'VARCHAR(100)') AS [weird]
FROM @xmltable AS x
CROSS APPLY x.xmlvalue.nodes('/root/*[local-name()=sql:variable("@searchstring")]') xa (c)

This returns text 1.

DECLARE @xmltable TABLE (xmlvalue XML)
INSERT @xmltable ( xmlvalue )
    VALUES ( '<root>text 1text 2</root>' )

    DECLARE @searchstring VARCHAR(100) = 'B'

    SELECT x.*,
    xa.c.value('.', 'VARCHAR(100)') AS [weird]
    FROM @xmltable AS x
    CROSS APPLY x.xmlvalue.nodes('/root/*[local-name()=sql:variable("@searchstring")]') xa (c)

This returns text 2.


Should I use a compound or surrogate PK in a many-to-many table? I have a database:

DROP TABLE IF EXISTS `books`;

CREATE TABLE `books` (
  `isbn` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) COMMENT 'Books used at this school';

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (
  `class_id` INT(10) NOT NULL AUTO_INCREMENT,
  `teacher_id` SMALLINT(5) NULL DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) COMMENT 'Classes at the school';

DROP TABLE IF EXISTS `b_c`;

CREATE TABLE `b_c` (
  `isbn` VARCHAR(255) NOT NULL,
  `class_id` INT(10) NOT NULL,
  PRIMARY KEY (`isbn`)
) COMMENT 'Books to classes';

ALTER TABLE `b_c` ADD FOREIGN KEY (isbn) REFERENCES `books` (`isbn`)
    ON UPDATE CASCADE;
ALTER TABLE `b_c` ADD FOREIGN KEY (class_id) REFERENCES `classes` (`class_id`)
    ON UPDATE CASCADE;

The issue I'm having is that I would like to normalize data as much as possible (I don't want multiple entries for the same relationship to be entered into the table b_c), but I would like to only store what data is absolutely pertinent.

My first idea to deal with this is to just create a compound primary key for the b_c table consisting of the fields isbn and class_id which would solve the issue of having duplicate relationships in the table, however, I have heard strong opinions on having a unique identifier for every row in a table like this.  The justification for having a unique identifier for every row seems to be that it's useful to be able to specify a specific row, though I don't see a situation in which this would become useful.  Can someone offer an example?

Another criticism I've heard is that using compound PKs in this way can make JOINs extremely taxing.  Can someone comment on the performance of these two different methods?

The question boils down to "Is it worth it to add an id field to the b_c table or is the use of compound PKs enough to properly represent the relationship between the books and classes tables?

If you have any other comments about the design not directly pertaining to the question, I would love to hear them and thank you in advance for you help.

FWIW ISBN is terrible as a primary key. For one, what happens if you get a book you want to put up for pre-order, but the ISBN hasn't been assigned yet? What happens when the ISBN changes (yes, this happens!)? What happens when they change the ISBN format yet again? I would say make that a candidate key but use a surrogate for the PK.

Adding to that the other issues with using strings as keys, and then on top of that a compound key that you require, I just don't understand why you want to fight surrogates when you're getting the suggestion from so many angles. You know that many of the people who are pushing you that way have a lot of experience, right?


Subquery returns more than 1 row I'm executing a query to return the products of a specific sale in Magento database.

It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row when the sale contains more than one product/row.

Query:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "cip") AS cip, /*Select CIP*/

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "cip7") AS cip7, /*Select CIP7*/

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "gtin_13") AS gtin13 /*Select GTIN13*/

FROM    magento_sales_flat_order_item
WHERE   order_id = 1

I do not see why this error occurs, despite that the root select only (without the inner ones) returns 3 rows, and the inner selects returns also 3 rows .

How can I resolve the issue?

My MySQL knowledge is rusty (at best), but subqueries like this can normally be replaced by a JOIN and GROUP BY to achieve the desired result.

Something like this will get you on your way:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,
        MAX(CASE WHEN mea.attribute_code = 'cip' THEN mcpev.value END) AS cip,
        MAX(CASE WHEN mea.attribute_code = 'cip7' THEN mcpev.value END) AS cip7,
        MAX(CASE WHEN mea.attribute_code = 'gtin_13' THEN mcpev.value END) AS gtin13
FROM    magento_sales_flat_order_item msfoi
INNER JOIN magento_catalog_product_entity_varchar mcpev
  ON msfoi.product_id = mcpev.entity_id
INNER JOIN magento_eav_attribute mea
  ON mcpev.attribute_id = mea.attribute_id
WHERE   order_id = 1
GROUP BY name, qty_ordered, item_id, original_price, discount_percent, price, tax_percent


How do you reset the sa password? I lost the sa password on a machine, and when I log in to the machine directly using an account in the admin group, SQL Server Management Studio will not allow me to log in using Windows authentication.

My plan was to simply log into the server, connect via Windows Authentication, and reset sa to use a new password. Since I cant' connect via Windows Authentication, this won't work.

How else can I reset the sa password?

You can follow the steps mentioned in the link below to reset the SA password:

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Steps summarised below:

  Open SQL Server Configuration Manager from Start Menu > Programs > Microsoft SQL Server 20xx > Configuration Tools > relevant to the newest version of SQL Server you have installed (e.g. if you have 2005 and 2012 installed, use the 2012 version). Don't have a Start Menu? On Windows 8's Start screen, start typing SQL Server Con... until it shows up.
  Stop the SQL Server instance you need to recover by right-clicking the instance in SQL Server Services and selecting "Stop"
  Right-click the instance you just stopped, click Properties, and in the “Advanced” tab, in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option (on newer versions, you can go directly to the "Startup Parameters" tab, type "-m" and click Add, without worrying about the syntax, the semi-colon, or anything else).
  Click the “OK” button, and restart the SQL Server Instance
  After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

  The following example adds the account "Buck" in the "CONTOSO" domain to the sysadmin role:

  EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';

  Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server instance one more time.

  NOTE: make sure there is no space between “;” and “-m”, the registry
      parameter parser is sensitive to such typos. You should see an entry
      in the SQL Server ERRORLOG file that says “SQL Server started in
      single-user mode.”

Additional resources:

Connect to SQL Server When System Administrators Are Locked Out
Leveraging Service SIDs to Logon to SQL Server 2012 and SQL Server 2014 Instances with Sysadmin Privileges
Recover access to a SQL Server instance using PsExec

Ultimately, you could always copy the database files to another instance, or even reinstall SQL Server (adding a local account as sysadmin during that process).


TSQL - create a function that takes a multi-row query result as an argument I work with a variety of servers on mostly SQL Server 2014 and earlier.

I have boiler-plate code that can take the results of an arbitrary select statement (returning a list of varchar or the like) and transform them into a single comma separated string. I use this regularly on reports (for e.g. listing the agents associated with a single call).

I'm getting tired of retyping it.

How can I create a function (or stored procedure, or other thing I don't know about) to re-use this code neatly?

Ideal useage would be something like:

select @result = udf_makeCSF( (select [First Name] + [Last Name] from AgentTable agents where ... ) )

I've seen some suggestions about using exec for similar problems and that gives me the stone-cold horrors.

EDIT: To make it clear, I already know how to make a comma-separated list. That's the boiler-plate code which I'm getting tired of copy-pasting everywhere. So please don't get hung up on explaining that, that's a solved problem.

But to extend my example, to make it more concrete for folks:

If I have CallSegments table, like so:

pkey    callid    start time     duration    time to answer    handling agent   caller     ...
1       1         8:00:20        260         12                12               5551234    ...
2       1         8:04:35        380         3                 16               5551234    ...
3       2         9:08:16        512         36                12               5554321    ...
4       3         9:58:42        8           1                 12               5557890    ...

This table represents incoming activity at a call center. Each row is a single segment of a call - A single call may have multiple segments, such as when it is transferred to another agent or another queue, a conference call is started, etc.

This is simplifying the data I'm working with, it better represents the state of affairs halfway through my procedure.

I also have an Agents table, like so

pkey    first name     last name    hire date    ...
12      Alice          Smith        1900-01-01   ...
14      Bob            Jones        2000-12-31   ...

This represents individual call-center agents. The only information I need out of this (for this report) is names of handling agent(s)

I am tasked with producing a report on calls where I assemble the various segments into a single line per call summarizing the lifecycle of the call.

Example desired output would be something like this:

start time     duration    time to answer    handling agents           caller     ...
8:00:20        00:10:40    00:00:15          Alice Smith, Bob Jones    555-1234   ...
9:08:16        00:08:32    00:00:36          Alice Smith               555-4321   ...
9:58:42        00:00:08    00:00:01          Charley Carpenter         555-7890   ...

Collecting the statistics is a group by and a couple of SUM()s. Collecting the handling agents is not.

This is not my exact scenario every time, but it's variations on this depending on report requirements (agent names is common, but also what queues did this call go through, and similar). The similarity is having a column of data that needs to get concatenated with a separator (usually commas) and returned as a single string.

Which feels to me like making this a shared snipped of code to be re-used rather than retyped repeatedly is good. I would write a custom aggregation method in C# and be done with it, but most of the client sites I work at do not allow for this.

Really what you are looking to do is inline concatenation / aggregation. The two easiest methods are:

SQLCLR (works with SQL Server 2005 and newer, but not Azure SQL Database unless it is the new Managed Instance type)
The STRING_AGG aggregate function (works with SQL Server 2017 and newer, and works on Azure SQL Database)

Currently, neither of these options works for you:

Option #1 is prohibited by policy, and (or due to) the fact that the client(s) will soon be migrating to Azure SQL Database which does not support SQLCLR (anymore; it did for about 18 months).
Option #2 is not possible due to being on SQL Server 2014 which does not have that function

This might be a long-shot, but given that you are only prohibited from using SQLCLR by policy, and that they will be migrating to Azure SQL Datbase, you might could do the following:

Short-term (prior to migrating to Azure SQL Database): use a SQLCLR aggregate function
Long-term (upon migrating to Azure SQL Database): swap out the SQLCLR aggregate for STRING_AGG

While this does entail modifying queries upon migration, the modification is a simple replacement. Assuming that all stored procedures, functions, etc are stored in a repository (e.g. Git, SVN, etc), it should be fairly easy to do a mass replacement across multiple files. Then deploy, test, and commit to the repository.

For example (using the SQL# library that I wrote which has Agg_Join in the Free version):

SELECT so.[schema_id],
       COUNT(*) AS [NumObjects],
       SQL#.Agg_Join(DISTINCT RTRIM(so.[type])) AS [Types]
FROM   sys.objects so
GROUP BY so.[schema_id];

SELECT so.[schema_id],
       COUNT(*) AS [NumObjects],
       STRING_AGG(RTRIM(so.[type]), ',')
              WITHIN GROUP (ORDER BY RTRIM(so.[type])) AS [Types]
FROM   sys.objects so
GROUP BY so.[schema_id];

These don't return identical results sets unless you remove the DISTINCT from SQL#.Agg_Join(DISTINCT RTRIM(so.[type])). I kept that in there to show a case where it helps, and to point out that for some reason, DISTINCT is not supported in the STRING_AGG function :(.

If you can do a Regular Expression (RegEx) replace, you can swap out:

SQL#.Agg_Join({stuff})

for:

STRING_AGG({stuff}, ',') [WITHIN GROUP (ORDER BY {stuff})]

with minimal effort. The WITHIN GROUP part is optional (I added it there since the DISTINCT in the SQLCLR version did the ordering). The main point being that the queries are otherwise untouched and should produce the same results, hence not needing a massive retesting effort.

In fact, if you create your own SQLCLR UDA such that it has the second parameter for the delimiter (the SQL# Agg_Join doesn't such that it can work in SQL Server 2005), you could name it "String_Agg". This would reduce the find/replace to be replacing dbo.String_Agg( with String_Agg(.


Can I use table aliases in a MySQL DELETE statement? This does not work:

DELETE FROM topics AS t , posts AS p  USING t INNER JOIN p
WHERE t.id=p.topic_id AND t.id = '5599';

whereas this does:

DELETE FROM topics, posts USING topics INNER JOIN posts
WHERE topics.id=posts.topic_id AND topics.id = '5599';

Is it not valid to use table aliases in a DELETE statement or is there some other way?

My aim is to delete all topics with id 5599 and associated posts with posts.topic_id = 5599

I don't like the USING syntax. The MySQL documentation shows the following as a way to declare which tables to delete from a join, which is almost identical to the SELECT syntax. You can use aliases of course:

DELETE t, p                  -- delete from both tables
                             -- this can be used as well:  t.*, p.*
FROM topics AS t
  JOIN posts AS p
    ON t.id = p.topic_id
WHERE t.id = 5599 ;

Also note that this will delete the topic with id=5599 only if there are (at least one) related posts. If you want to delete it anyway (even when there is no post), use LEFT JOIN instead.

For the record, your (not working) 1st statement is aliasing the tables in the wrong place. This should work:

DELETE FROM t, p
USING topics AS t INNER JOIN posts AS p
WHERE t.id=p.topic_id AND t.id = '5599';

or better, using proper JOIN syntax:

DELETE FROM t, p
USING topics AS t INNER JOIN posts AS p     -- or: LEFT JOIN
   ON t.id = p.topic_id
WHERE t.id = 5599;