Primary key auto increment best practices Apologies if something similar has already been asked, I wasn't sure of the best term to search for to get what I was after, so please let me know if there is already a bunch of other related questions and I'll close it!

To the problem!

I have a MySQL database table that has an auto incrementing primary key, the rows in this table get removed and re-added in stages automatically once a day via a cron job php script on a web server.

This script removes and re-adds about 110,000 rows a day, and therefore the primary key number increases by the same number each day. It's currently on about 4 million.

With my limited knowledge of databases, I'm not sure if this is bad practice or not. Years down the line this number is going to be huge! Can it actually get too big? It's currently a BIGINT field type.

I don't have any programmatic need to have this unique primary key - I won't ever be searching based on this number, however it is the only unique Identifier per row.

I can't just reset the index because the whole table is not truncated - only part of it per product category.

What's the best (most efficient) way to handle this scenario?

You're using a BIGINT which is 8 bytes. The maximum value you can store in a BIGINT is 18446744073709551615 if unsigned. At your rate of insertion of approximately 100 thousand rows per day, it will take you 459445680541 years to overflow.

In short, you have nothing to worry about.

In fact, you should consider changing this field to INT which is 4 bytes, with maximum value of 4294967295, which you can use for approximately 110 years before overflowing.


Will "bridge" storage engines work with MariaDB Galera Cluster? Galera states that it only supports InnoDB. The reason is quite clear, and it is related with the way synchronous replication is implemented in Galera.

However, some storage engines are just "bridges", or they do (almost) nothing. Can anyone tell me which of these storage engines can realiably be used in Galera?

OQGRAPH (referencing an InnoDB table)
Mroonga (referencing an InnoDB table)
CONNECT (TABLE_TYPE = PROXY, referencing InnoDB table)
CassandraSE
SphinxSE
BLACKHOLE
SEQUENCE

Galera replicates DDL statements so other storage engines that act as a proxy to InnoDB tables or external systems should work normally.  The tables aren't really replicated but they would be created on each node when they are created on any node.

OQGRAPH - I saw you asking about it on the MariaDB site and as the consensus is there, theoretically it should work since the data is stored in InnoDB tables and would be replicated but it's not a common use case and you'll need to do some testing on your own.
Mroonga - same as OQGRAPH
CONNECT - This should be the same as OQGRAPH.
CassandraSE and SphinxSE - since the only information these engines use from MySQL is created with DDL statements, they should work fine.
BLACKHOLE - These are just normal tables that don't have any data to replicate so there shouldn't be any problems.
SEQUENCE - The SEQUENCE engine doesn't create any tables and is triggered by the names of tables in a query so these should work fine anywhere as long as the plugin is loaded on the server that is being queried.


Availability Group database stuck in Not Synchronizing / Recovery Pending mode While upgrading the storage in a SQL Server 2014 SP1 (12.0.4422.0) instance we ran in to an issue where two of the databases would not start on the secondary after restarting SQL Server. The server had been offline for a few hours while we installed new (larger) SSDs and copied the data files over to the new volume. When we restarted SQL Server all but two of the databases started synchronizing again. The other two were displayed in SSMS as Not Synchronizing / Recovery Pending.

Having had a similar Not Synchronizing / In Recovery issue before, I checked the status under the Availability Groups -> Availability Databases section but they displayed a red X:

and even trying to Suspend Data Movement generated an error message:

  Failed to suspend data movement in database 'StackExchange.Bycycles.Meta', which resides on the availability replica 'ny-sql03' in the availability group 'SENetwork_AG'. (Microsoft.SqlServer.Smo)

  Additional Information:
  An exception occurred while executing a transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  Database 'StackExchange.Bycycles.Meta' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. (Microsoft Sql Server, Error: 945)

I checked and the files existed and did not have any permission issues. I also checked the SQL Server logs in SSMS under Management, but did not see anything about pending recovery or any issues with the two databases.

Searching for help I found two different articles that said the databases would need to be restored.

Is there any way to resume data replication on a secondary when a database is stuck in Recovery Pending?

Since the server had been offline for a while we thought it may have gone outside the recovery window of the primary. We decided to try applying the latest transaction logs on the database to see if that would kick-start the recovery process:

-- Remove database from Availability Group:
Alter Database [StackExchange.Bicycles.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Bicycles.Meta] FROM DISK = '\\ny-back01\backups\SQL\_Trans\SENetwork_AG\StackExchange.Bicycles.Meta\StackExchange.Bicycles.Meta_LOG_20160217_033201.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_AG];
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR RESUME;

Afer running the above on the secondary server for both databases they were able to start synchronizing again.

UPDATE: We had a similar issue where after a Manual AG Failover one of the databases on the new primary replica was stuck in Not Synchronizing mode (switched to Not Synchronizing / Recovery Pending after restarting SQL Server), and the above steps worked to resolve that issue as well.


Stopping a running compaction process I need to run compaction on a production CouchDB instance, that may not have had compaction run on it before.

If compaction puts too much additional load on the CouchDB instance, is it possible to safely kill the compaction process? If so, how?

During compaction, a new .couch file is written. So what you would do, when you interrupt compaction is making the file useless. The only way to stop compaction (iirc) is to stop CouchDB. Not a good idea.

Generally, compaction is a background process. The database reads are not affected by it - also for heavy read! If you have heavy write processes, compaction may never end because writing and updating a doc is faster than compaction.

You could consider scheduling compaction to a time slot, where you know that the database is not on 100% capacity. You do that in local.ini like this:

; configuration for automatic compaction.
; see: http://docs.couchdb.org/en/latest/config/compaction.html
[compactions]
_default = [{db_fragmentation, "70%"}, {view_fragmentation, "60%"}, {from, "02:00"}, {to, "05:00"}]

This means, run automatic compaction only during 2 and 5 in the night and only, when the fragmentation of the database is greater than or equals 70% and the view fragmentation is greater than or equals 60%

Further info can be found here: http://docs.couchdb.org/en/latest/maintenance/compaction.html

If you have questions you are very welcome to join the dev@couchdb.apache.org mailinglist or join IRC at #couchdb-dev or #couchdb at irc.freenode.net


Scripting the copy of SQL Server production database to test environment We are introducing continuous integration on our project and we decided to automate the refresh of our test database instance with fresh production data.

I'm currently looking for the best way to go in order to avoid any production disruptions or slow downs.

I first though about using backup/restore command from a command line with the sqlcmd utility...

But it seems it's problematic because it solely generates a .bak file although this file is still "linked" with .mdf/.ldf files. So when restoring it in our test environment these .mdf/.ldf files are not found.

Then I went on specifying a 'WITH MOVE' options in the restore command in order to specify the location of the .mdf/.ldf file over a network share but I think the production server locks these file and prevent any read on them from the test server.

So basically, do you know a straightforward (and efficient) way to dump a database to a single file to easily load it in our test environment ?
Of course without disrupting the production environment.

Edit:

Both production and test SQL Servers version are 12.0.2000.

But I'm first testing loading from production to a local development SQL Server Express (also v12.0.2000)

But it seems it's problematic because it solely generates a .bak file
  although this file is still "linked" with .mdf/.ldf files. So when
  restoring it in our test environment these .mdf/.ldf files are not
  found. Then I went on specifying a 'WITH MOVE' options in the restore
  command in order to specify the location of the .mdf/.ldf file over a
  network share but I think the production server locks these file and
  prevent any read on them from the test server.

This whole statement does not really make sense. A backup file is not "linked" to the mdf/ldf files, it contains the files of that database in order to recreate them when you restore it. When you go to restore them on another server you add WITH MOVE and specify the location you want the mdf/ldf files to be stored at on that server. They do not link back to the original location where the backup was taken from, at all.

Using backups is the most common method for refreshing a development environment. You would generally base current you can keep the data in development (if needed) on how your backups are done in production (e.g. full/diff/log or just full/diff).


Identify Queries causing High Average Wait time (SQL Server 2008) Recently we started to receive many alerts from SCOM : Alert: SQL 2008 DB Average Wait Time is too high.

When reviewed the graph for this counter, we found that the alert is always ranging between 1,000 (ms)  and  2,500  (ms) .

is there a specific way\query that can identify which queries are causing this locking which lead to the high wait time ?

Thanks
Osama Waly

You can probably find simpler queries to give you that information, but if you want something more robust, I'd suggest sp_BlitzCache. Full disclosure, I'm one of the authors. It's totally free, open source, and you can get it here.

How does it help you?

You can use the @SortOrder parameter to look for queries either with the most total duration accumulated in your plan cache:

EXEC sp_BlitzCache @SortOrder = 'duration'

Or you can look by long average duration:

EXEC sp_BlitzCache @SortOrder = 'avg duration'

That will average our duration over executions to find the longest running by average.

You'll get back a ton of information about each query, including the query plan. If you have any questions about the results, you can ask them here or in the GitHub repo.

Hope this helps!


how to get the date of a file using xp_cmdshell? So far I've this code

CREATE TABLE #tmp (strData VARCHAR(1000))
INSERT INTO #tmp EXEC xp_cmdshell 'dir C:\Users\Giba\Desktop\Folder\cmdshell1\*.txt'
SELECT * FROM #tmp --WHERE strData LIKE '09/25/2018'
DROP TABLE #tmp

but I want to select only date of the .txt files and compare them with current date. Can someone help to select only the date?

This will depend on your regional settings, language, and potentially other settings.

...
WHERE TRY_CONVERT(date, LEFT(strData,10)) = CONVERT(date, GETDATE());

To avoid anything else from entering the #temp table in the first place, you can use forfiles, which allows you to specify a pattern (eliminating all the extra junk dir adds), specify a date (in this case any file with a modified date >= today), and also only return certain properties of the file (like the date), e.g.:

DECLARE @cmd varchar(4000) = 'forfiles /p C:\...\cmdshell1\ /m *.txt '
  + '/d +' + CONVERT(char(10), getdate(), 101) + ' '
  + '/c "cmd /c echo @fdate"';

INSERT #tmp(strData) EXEC master.sys.xp_cmdshell @cmd;

SELECT * FROM #tmp WHERE strData IS NOT NULL;

Results:

strData
----------
09/25/2018
09/25/2018
...

Your question says you only want the date value output, but that doesn't seem to make a lot of sense to me, since you already know the date, it will be the same on every single row, and all that really tells you is a count (so why not just run a count?). Here's how to pull out the file name with a similar technique (the for /f bit is to eliminate quotes around the @file output, as borrowed from this answer):

DECLARE @d char(10) = CONVERT(char(10), getdate(), 101),
  @cmd varchar(4000) = 'for /f %i in (''forfiles '
    + '/p C:\...\cmdshell1\ '
    + '/m *.txt '
    + '/d +' + @d + ' '
    + '/c "cmd /c echo @file"'') do @echo %~i';

INSERT #tmp(strData) EXEC master.sys.xp_cmdshell @cmd;

SELECT strData = @d + ' ' + strData
FROM #tmp WHERE strData IS NOT NULL;

Results:

strData
--------------------------------
09/25/2018 file1.txt
09/25/2018 file2.txt
...

But I have to agree with Kin, this is much better suited for PowerShell no matter what you're actually looking for and whether it makes sense. xp_cmdshell was turned off by default starting with SQL Server 2005, and that was done for a reason. Let SQL Server focus on the data, and PowerShell and other things deal with the OS layer.


STRTOMEMBER function throws error about finding a dimension I'm really stumped here...trying to create some year-to-date measures for a Cube, with NOW() specifying the current month and day (minus a two-day lag), with year being specified by the current member of the Year value on the hierarchy - and using STRTOMEMBER to paste together these bits of the year into a reference to a specific member of the dimension that can be cross-joined with measures to get annual value to-date.

This code works just fine when using STRTOMEMBER to specify the range...it lists every day between the first and the tenth, and the revenue for each day:

SELECT [Measures].[Revenue USD] ON COLUMNS,
{
STRTOMEMBER('[Time Dimension].[Year-Qtr-Month-Date].[Date].&[2017-01-01T00:00:00]') :
STRTOMEMBER('[Time Dimension].[Year-Qtr-Month-Date].[Date].&[2017-01-10T00:00:00]')
}  ON ROWS
FROM [CurrentCube]
WHERE [Time Dimension].[Year].&[2017]

But then the following does not work, it gives the error:

  "The dimension '[Time Dimenson]' was not found in the cube when the string, [Time Dimenson].[Year-Qtr-Month-Date].[Date].&[2017-01-07T00:00:00], was parsed."

I realize the date doesn't quite match up with the same range, but otherwise an identical string is being constructed. It makes no sense to me that the former would work and the latter wouldn't. Tried putting single quotes around it, too, which then complains that STRTOMEMBER expects a member expression but that a string or numeric expression was used. Makes no sense to me that the first option would work but the second one doesn't. Can't find much online to explain this error message, either.

SELECT [Measures].[Revenue USD] ON COLUMNS,
{
STRTOMEMBER(FORMAT(CSTR("[Time Dimenson].[Year-Qtr-Month-Date].[Date].&[" + [Time Dimension].[Year].CURRENTMEMBER.MEMBER_CAPTION + "-" + RIGHT("0" + FORMAT(CSTR(MONTH(NOW()-5))),2) + "-"  + RIGHT("0" + FORMAT(CSTR(DAY(NOW()-5))),2) + "T00:00:00]"))) :
STRTOMEMBER(FORMAT(CSTR("[Time Dimenson].[Year-Qtr-Month-Date].[Date].&[" + [Time Dimension].[Year].CURRENTMEMBER.MEMBER_CAPTION + "-" + RIGHT("0" + FORMAT(CSTR(MONTH(NOW()-2))),2) + "-"  + RIGHT("0" + FORMAT(CSTR(DAY(NOW()-2))),2) + "T00:00:00]")))
}  ON ROWS
FROM [DataInsight]
WHERE [Time Dimension].[Year].&[2017]

Spotted the error - it was a typo..."Time Dimenson" has the word "dimension" misspelled. The error in this case was between the keyboard and the chair.


I am designing a database for a game and the only option I can see a multiple table solution. Is there an option that I'm not seeing? I searched but could find a similar situation, if I missed it please let me know. In this game there are approx 14,000 items which have their own table. The players will want to build multiple collections of these items. The collections would generally range from 20 to 100 different items in varying quantities, but could have hundreds of different items. The problem comes with the storage of these collections. I can't create a collections tables with each row a collection because the number of fields varies so much.
I only see two solutions:

Create a collections table with a BLOB data field and store the data as a JSON object.
Give each collection its own table with an item ID field and a quantity field. Create a collection list table with the user id and the corresponding table name.

Is there a better option? If not, I am leaning toward option two so I can easily run statistic queries. Due to the number of tables that could exist would it benefit me to keep the collections in a database separate from my other tables?

You want to store an undefinied number of items in a list, for this you need a new table or cleaner two tables.

you already have the first table called items, each has an item_id.

Then one table holds all the references to the collections, so best case only a new collection_id for every of the collection.

And the important one is the MxN relation table between items and collections, let's call it collection_items, holding in each row one item_id and one collection_id.

If you want to have multiple same items in one collection, and only want to know how many, add a count field.

But if the relations need to be distinguishable, add a row for every relation and an extra collection_item_id.


Can I upgrade SSIS 2008 packages directly to SSIS 2017 SSISDB? I'm planning a mifgration of a SQL Server 2008 instance running the DB engine and SSIS.

The SSIS is deployed to the file system.

Am I able to copy those SSIS packages to a directory on my new SQL Server 2017 machine, open them in SSDT 2019 and then deploy to SSISDB?

Or is there some other intermediate step or process I should follow?

Maybe.

SSIS packages as a general rule are forward compatible - if you run a 2005 package using the SQL Server 2019 binary of dtexec.exe the first step it will do is an in-memory upgrade of the package to a 2019 equivalent version. Once the package exits, the upgraded package disappears (in-memory only) and all is well and good.

My experience is that there are two cases where the auto upgrade fails: third party components and Script Components - typically source components but async transformations are equally likely candidates. 2008 and 2012 had changes in the signature of the pipeline/scripting stuff that the upgrade wizard couldn't always auto figure out. I never figured out precisely what would throw it out of kilter but something would. Generally speaking, opening the package up in the newer version of SSDT and then double clicking the script component would force it fix itself and worse case, it'd tell you what was off once you were in there.

The important thing to know moving the SSIS project from 2008 to 2012+ is that the wizard will encourage you to move to the "new" project deployment model. Currently, you're in the package deployment model. The package deployment model allows all the packages to work at their own pace - no dependencies on any other package (at least enforced in code). You're welcome to use whatever logging you wish, same with your configuration approach and package protection levels.

But, your question specifically asks about moving to SSISDB so yeah, you get to tackle those questions. With the Project deployment model, all the packages in an SSDT project need to be in harmony.

Oh and a while I've not tested since the release candidate era, they introduced  Incremental Package deployment to the SSISDB with 2017.

Fire up ISDeploymentWizard (C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe)
Change the deployment model from Project Deployment to Package Deployment
Select the package(s) of interest
Connect to your server and create the deployment folder and then create a new project to hold them
Click Next, Click Deploy your packages and cross your fingers


How do I preserve the leading zeros when I insert a number into this table? I have inserted two records into a table.

create table num(id int)
insert into num values(0023)
insert into num values(23)
select * from num

When I query them, they all display as 23. It means that SQL Server ignores the leading 0s. What's the mechanism behind that? How can I have SQL Server return the values as I inserted them (i.e. 0023 and 23)?

0023 is not a number. It's a string. 23 is the number. SQL Server is able to recognize that those extra zeros are not needed to define the number, so it ignores them. If you want to dispaly a value as 0023 to the application, I'd suggest doing the formatting on the application side. That way the number stored in SQL Server is still a number if you want do addition, aggregation, or other calculations. If you really have to store it as '0023', you need to convert it to a character field; char, varchar, nvarchar, nchar.


Output getting truncated in phpMyAdmin I am writing a drop statement (in phpMyAdmin) to get rid of some unneeded tables in my database. If I run this query against our live database I am not seeing the full drop statement (it is getting truncated after 50 characters). If I run it against a copy of that database I get the full statement returned.

Any ideas why the behaviour would be different?

SET GROUP_CONCAT_MAX_LEN=10000;
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
AS statement FROM information_schema.tables
WHERE table_schema = 'dbname' AND table_name LIKE 'prefix%';

Turns out it was phpMyAdmin - needed to show full texts.

Just above the SQL output there is an 'Options' link. Click that and you will see Partial texts/Full texts radio buttons.

Note, that when I went back to get the screenshot it was still set to Full Texts so I assume there is a cookie being set which may explain why it was different in one database to the other.


SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date I've been battling for hours to add a date to a CSV name:
For brevity, Im just taking the first character of the date here.

In SQL Command, this works:

sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd%date:~1,1%.csv"

But when I try to use that exact same code inside the step of a job, the filename is

sqlcmd_%date

I cant run xp_commandShell, is there a way to get the dynamic date working in SQLCmd please?
I've read through so many articles, eg.

Try Agent Tokens instead, eg

sqlcmd -S .\sql2012 -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "c:\temp\sqlcmd$(ESCAPE_NONE(DATE)).csv"

This worked for me.  Just a reminder, bcp is a more appropriate tool for this kind of extract; you won't have the header and "row(s) affected" problems which you're not dealing with here.


Can Oracle be installed on Windows without being an administrator? Can the oracle software be installed on windows without having administrator privileges?

No, but there are workarounds

One workaround is to run a virtual machine that has Oracle and only Oracle installed. this has the significant benefit of insuring that the many kernel tweaks required of oracle are constrained within one instance.

Consider trying deployment within: VMWare Thinapp, VMWare Player (maybe), and I believe VirtualBox offers a user-land install, based on the documentation here.


Error 18456 trying to connect Trying to connect to SQL Server (SQL Server 2008 R2) installed on Windows Server 2008 R2 results in the following error message (according to ERROLOG):

  Error: 18456, Severity: 14, State: 38.

Does anybody have an idea what could be the problem?

This typically means that the database specified in the connection string, in the SSMS dialog's options tab, or the user's default database is offline or otherwise not accessible (it could be restoring, single_user mode, offline, autoclose, etc).

What database are you trying to connect to? Check its status in sys.databases:

SELECT state_desc, user_access_desc, is_auto_close_on, is_in_standby
  FROM sys.databases
  WHERE name = N'database_name';

If you are not specifying a database, then check the default database for the login:

SELECT default_database_name
  FROM sys.server_principals
  WHERE name = N'login_name';


Does Compatibility Level allow unsupported functions? I've got a SQL Server 2016 instance with a database on it, running with a compatibility level of 2008. However, the following dummy proc creates, and executes just fine.

CREATE PROC usp_TestFormat
AS
BEGIN
    SELECT FORMAT(CAST('10/25/2018' AS DATE), 'MMM') AS Month;
END;

However FORMAT() is a 2012+ function, so when I tried to take a stored proc I'd written in above database, or the test proc above even, and deploy it to our production database, that is running on a 2008r2 box, it fails, as it doesn't recognize the function. It was my understanding that compatibility levels would prevent this type of behavior, as I'm now having to go through and re-work the code that uses the format function. Is this a bug, or am I not understanding Compatibility levels as much as I thought?

Is this a bug, or am I not understanding Compatibility levels as much as I thought?

Not a bug.  The Compatibility Levels are intended solely to make upgrading easier.  IE to prevent errors or changes in behavior when you upgrade to a newer version of SQL Server.

After you upgrade to a new version of SQL Server, you might want to take advantage of new features and capabilities without changing the Database Compatibility Level, so where possible, new TSQL language features are not blocked by your lower Compatibility Level.


How to import long strings at different column positions with SSIS? Our provider stores our survey data in the following format

Columnnames for each survey

Example:

|v1         |v2|v3|t1
|CompanyName|v1|v2|v3|t1|

Data for each person

One dataset for survey1

|1        |2|1|I work in the metal industry

One dataset for survey2

|MyCompany|4|2|4|I work as a programmer

For each survey I get an identifier to consume the rest api and get the data in json. That is the way I know which data belong to which columninformation.

RestRequest("/datasources/{identifier}/columns", Method.GET)
RestRequest("/datasources/{identifier}/data", Method.GET)

I consume this with SSIS and my problem is that there are up to 400 columns and there are columns like t1 that can be at any position and hold up to 5000 character strings (t1 is an open question). Within SSIS I could now define each column as string with 5000 in length and set all columns in my stagging table to varchar(max) but this would have huge consequences to the performence. That is why I would like to ask if there is another way to handle this problem.

I would not assume it will have huge consequences to performance. Handling this is what Varchar(Max) is for and it's very unlikely to have any impact.

Regarding your specific question about alternatives however, the only one is to use sparse column sets. This allows you to pack a lot of potential but usually null columns into one actual XML column. Then they only get stored inside if they are actually used. They can be selected from directly using their column name, though if you Select * then you only get the raw XML column.

Microsoft documentation is here: https://msdn.microsoft.com/en-AU/library/cc280521.aspx


What is the correct result for this query? I came across this puzzle in the comments here

CREATE TABLE r (b INT);

SELECT 1 FROM r HAVING 1=1;

SQL Server and PostgreSQL return 1 row.

MySQL and Oracle return zero rows.

Which is correct? Or are both equally valid?

Per the standard:

SELECT 1 FROM r HAVING 1=1

means

SELECT 1 FROM r GROUP BY () HAVING 1=1

Citation ISO/IEC 9075-2:2011 7.10 Syntax Rule 1  (Part of the definition of the HAVING clause):

  Let HC be the <having clause>. Let TE be the <table expression> that
  immediately contains HC. If TE does not immediately contain a
  <group by clause>, then “GROUP BY ()” is implicit. Let T be the
  descriptor of the table defined by the <group by clause> GBC immediately
  contained in TE and let R be the result of GBC.

Ok so that much is pretty clear.

Assertion: 1=1 is true search condition. I will provide no citation for this.

Now

SELECT 1 FROM r GROUP BY () HAVING 1=1

is equivlent to

SELECT 1 FROM r GROUP BY ()

Citation ISO/IEC 9075-2:2011 7.10 General Rule 1:

  The <search condition> is evaluated for each group
  of R. The result of the <having clause> is a grouped
  table of those groups of R for which the result of the
  <search condition> is True.

Logic: Since the search condition is always true, the result is R, which is the result of the group by expression.

The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY CLAUSE)

  1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>.

  2) Case:

  a) If there are no grouping columns, then the result of
  the <group by clause> is the grouped table consisting of T as its only group.

Thus we can conclude that

FROM r GROUP BY ()

results in a grouped table, consisting of one group, with zero rows (since R is empty).

An excerpt from the General Rules of 7.12, which defines a Query Specification (a.k.a a SELECT statement):

  1) Case:

  a) If T is not a grouped table, then [...]

  b) If T is a grouped table, then

  Case:

  i) If T has 0 (zero) groups, then let TEMP be an empty table.

  ii) If T has one or more groups, then each <value expression> is applied
  to each group of T yielding a table TEMP of M rows, where M is the
  number of groups in T. The i-th column of TEMP contains the values
  derived by the evaluation of the i-th <value expression>. [...]

  2) Case:

  a) If the <set quantifier> DISTINCT is not specified, then the
  result of the <query specification> is TEMP.

Therefore since the table has one group, it must have one result row.

Thus

SELECT 1 FROM r HAVING 1=1

should return a 1 row result set.

Q.E.D.


What firewall ports should be opened for central management server Within my environment we currently have a MSSQL installation which serves as the Master in a Multiserver Agent environment (MSX). Due to this server being within a tightly controlled network the necessary ports for MSX functionality have not been opened.

As shown in the image, when I try to enlist a target (TSX) to the master (MSX) I am given an error that the RFC server is not available, I am also unable to deploy multi-server jobs to targets.

What ports should I request to be opened from targets to the master and vice versa?

When you attempt to enlist the Target (TSX) to the Master (MSX), the step that is failing is "Make sure the Agent service for '{MSX Server}' is running." This step simply checks to see that the SQL Server Agent is running on the MSX Server.

You can instead try to use msdb.dbo.sp_msx_enlist (documented here), which is run on the TSX to enlist it on the MSX. This may provide a more helpful error message, or it may even succeed.

If it succeeds, you can query msdb.dbo.systargetservers on the MSX to confirm that the server has been successfully enrolled, and is polling for instructions to download. Next, you can poll msdb.dbo.sysdownloadlist to see those instructions, and see if they are being consumed correctly by the TSX. There is an error_message column which should include download errors. Note, errors being logged here would be dependent on the TSX successfully connecting to the MSX--so your TSX would need to be able to connect to the MSX's DB Engine port (1433 by default) in order to even write the error messages.


BCP utility:“Copy direction must be either 'in', 'out' or 'format'” I moved a SQL Agent job from a SQL Server 2008 R2 instance to a 2016 instance. The job called a PowerShell script which in turn called bcp.exe. After moving the job, I receive this error:

  Copy direction must be either 'in', 'out' or 'format'.

The command did not change, only the location of the job and version of the instance.

bcp "SELECT * FROM [dbo].[Table]" queryout "\\filerepository\ftp\text_file.txt" -T -c -t"|" -S"ServerName" -d"MyDB"

Also, I ran this locally using the 2012 bcp.exe and received the same error. When I compare the books online article for the bcp utility, I do not see any changes which would affect the syntax of this command.

I would appreciate any advice.

Updated: The PowerShell Script

$ErrorActionPreference = "stop"
$serverInstance = "Server"
$databaseName = "DB"

# construct query
$query = @"
SELECT *
FROM [dbo].[Table]
"@

$query = $query -replace "`n|`r|`t"," "

# output results
$extractPath = "\\filerepository\ftp\"
$fileName = "CTBRI_$(Get-Date -Format "yyyy-MM-dd")_text_file.txt"
$path = Join-Path $extractPath $fileName;

$cmd = "`"$query`" queryout `"$path`" -T -c -t`"|`" -S`"$serverInstance`" -d`"$databaseName`""

bcp $cmd

if($LASTEXITCODE -ne 0)
{
    throw "bcp did not exit with a success code of 0. Hopefully there are more detailed messages above."
}

Same version of Powershell on the servers/vms hosting SQL Server 2008R2, 2012, and 2016?
A parsing error that removes the double quotes around the query would result in this error.


How do I identify the source of the 17886 errors ("server will drop the connection") in my SQL errorlog? I have a SQL 2012 server that is throwing the following error every 5 minutes or so:

Error: 17886, Severity: 20, State: 1.
The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

I alert on all Sev 20 errors, so I can't just ignore this.
The good news is that I have some leads on how to fix it (seems like a connection string issue):

At DBA.SE: How to troubleshoot “client driver has sent multiple requests while the session is in single-user mode”
At SO: Error 17886 - The server will drop the connection

In MS forums: Event ID: 17886 Source: MSSQLSERVER (MSSQL Server 2008R2)

The bad news is that I have no idea which application is causing the errors!
The server has over 50 databases, from a wide variety of commercial vendors, contractors, or in-house developers. The error contains no details about which spid or login or hostname or database caused the problem.
I've tried looking at active spids (using sp_WhoIsActive) when the error is occurring, but there is too much activity on the server for anything to stand out (nor do I know what I'm looking for). Same thing with running SQL profiler - without knowing how to filter, I'm just seeing the firehose of data the server handles on an ongoing basis.
I am trying to narrow it down by figuring out which application teams have made recent changes, but that hasn't panned out so far.
Any ideas?

Try to use
select * from sys.dm_os_ring_buffers

to get more details about source clients.
Try filter type RING_BUFFER_CONNECTIVITY.


Constrain input values I am working on limiting input in certain fields in my Postgres database, and I have a working solution using ENUM like so:

CREATE TYPE label AS ENUM ('foo','bar');

However, I was browsing around and looking at alternative ways of doing this using:

constraints (eg. CHECK (type IN ('foo','bar')) )
triggers (slow, from what I've read)
foreign keys

What are the advantages/disadvantages of using either of these methods, would any of these be more advisable? I am not necessarily asking which one is the 'best' method, only what kind of issues may arise from using these, ie. I know that using triggers might be quite slow compared to the other methods, but perhaps could deal with some more complex input conditions.

Maybe not all the differences, but most important in my opinion :

enum vs text/varchar + check constraint.

enum values are internally stored as integer numbers, so different storage requirements
ordering : order of enum values corresponds order in which values were entering during creation type, so for enum ('small', 'big') small < big;  for text column  'big'<'small'

enum vs foreign key

performance-wise they should behave very similar, but you can bypass foreign key constraint by, for instance, disabling all triggers or changing session replication role
there is a bigger difference from data model perspective. If something has attributes, needs to be tracked, likely to be modified in either way, it should be stored as a separate entity in its own table. If it's just an attribute , e.g. color in many cases, then it's fine to store it as a enumerated type.

Triggers are just not the right tool to implement check constraints. They add complexity, hide logic, add maintenance overhead.  In some cases they are "necessary evil" , but they definitely shouldn't be used just to do what check constraint can do.


Error Message Saying "Invalid Object Name" I have been following a SQL course for the past few weeks and I never have had this problem: my database is connected but I get the error message saying "Invalid Object Name". Please see picture.

Your SQL Query is in the wrong database. You are trying to select from the AdventureWorksLT2012 database, but you're connected to the Master database.

If you look at the top left of your window, you will see a bar at the bottom of the ribbon that contains a drop-down list. In it, you can see "master" is the current database.

There are three ways to resolve this in your queries. After you do so, the red lines under your table and column names should all vanish, as they're warning you that the tables and columns don't exist in the current selected database.

Change the drop-down to the correct database. This will fix your query window for that window and that window only, but is the most common solution.
Change your queries to reference the database. This would be queries like Select * from AdventureWorksLT2012.SalesLT.SalesOrderDetail for example. Setting the DB name in your query means it will no longer matter which database your current context is.
Start the query off with a USE [AdventureWorksLT2012]; Go; statement. This will switch your context to the correct database. After running the query, the screen will show this database in the drop down list I mentioned in option 1.


How to make datapump apply directly to the destination schema I have a schema on a server that returns and half I need to replicate it to another server, so I'll la and do an expdp on server1 sending the file to server2 and do an impdp. Solve the problem.

However, I wanted to know if I can not do the expdp and generate the file, but do an impdp directly on the destination without generating the file.

That would save a lot of time.

Original question on SoPT - Como fazer datapump aplicar diretamente no schema destino
Translated by translate.google.com.

Using Network Link Import to Move Data

  When the Import NETWORK_LINK parameter is used to specify a network
  link for an import operation, SQL is used to move the data using an
  INSERT SELECT statement. The SELECT clause retrieves the data from the
  remote database over the network link. The INSERT clause uses SQL to
  insert the data into the target database. There are no dump files
  involved.

NETWORK_LINK


SQL Server 2008 R2 Memory Pressure My SQL Server has a weird problem. It seems it happens with the same matter. It always restarts after running a period of time(about one month long). And I used dbcc memorystatus to check the problem. I found MEMORYCLERK_SQLOPTIMIZER singlepage allocator ocuuping a lot of memory. I guess it could be why my SQL server restarted.

So I used sp_WhoIsActive to find which query script holds memory. After I collect some data, I don't know how to distinguish the relationship between sp_WhoIsActive result and  dbcc memorystatus.

Could you help me and give me some hints to solve the problem?

The following is some information about my SQL Server:

Server: CPU: intel E5645*2, RAM 64GB, HD: 10krpm SAS
SQL Server: 2008r2 with failover cluster feature enabled

max memory:52GB,
instance:1
No of db: 1700(Don't be surprised. There are 1700 dbs in my SQL Server.)

The attach file can be downloaded here.

After unzipping, there are 3 files.

att1.txt - The status before server is going to restart.
att2.txt - The status that the server has run for two weeks after restarting.
whoisactive.bak - The result for sp_WhoIsActive.

You can restore it in the SQL server2012.

My SQL Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Finally I turned on optimize for ad hoc workloads, but it didn't work. I am not sure what happened. Now I restart service when optimizer memory almost holds up all the memory. And all the memory is released.

Well, you can check how often your plans are reused. After that, you may want to decide to switch to optimize for ad-hoc workloads. Those flag will only save a plan-stub instead of a full plan and will do a fast compile of the stub if there is a query which uses the same stub. Those option isn't enabled by default (due to the fact that it's a newer Serverfeature).

Take a look at this query for example:

DECLARE @AdHocSizeInMB DECIMAL(14, 2)
    ,@TotalSizeInMB DECIMAL(14, 2)
    ,@ObjType NVARCHAR(34)

SELECT @AdHocSizeInMB = SUM(CAST((
                CASE
                    WHEN usecounts = 1
                        AND LOWER(objtype) = 'adhoc'
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(14, 2))) / 1048576
    ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
FROM sys.dm_exec_cached_plans

SELECT 'SQL Server Configuration' AS GROUP_TYPE
    ,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
    ,' ' + CASE
        WHEN @AdHocSizeInMB > 200
            OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
            THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
        ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
        END + ' ' AS RECOMMENDATIONS

It should give you an overview if you would benefit from it or not.

You can enable it using this:

sp_configure N'show advanced options',1
GO
reconfigure
GO
sp_configure N'optimize for ad hoc workloads',1
GO
sp_configure N'show advanced options',1
GO
reconfigure
GO

Hopefully this will help you.


Disable MySQL replication on rds I want set up replication between an Amazon RDS MySQL DB instance and a MySQL  instance that is external to Amazon RDS.

but  Slave_IO_Running: Connecting
how to solve this problem?

I have already done the following commands.

mysql>CALL mysql.rds_set_external_master ('192.168.xx.xxx', 3306,
    'repl2', '111111', 'mysql-bin-changelog.000003', 598, 0);

mysql> CALL mysql.rds_start_replication;

+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.01 sec)

The easy way to disable replication is to run this on the RDS slave

CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;

If Slave_IO_Running is Connecting, then there is one or both of the issues

The grants for the repl2 user is wrong
firewall issue on port 3306.

If the issue is the grants, drop the repl2 and add it back with

DROP USER 'repl2'@'%';
CREATE USER 'repl2'@'%' IDENTIFIED BY '111111';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl2'@'%';

I'll leave the FW/SecurityGroup stuff to you.

Once these are cleared up, run the CALL mysql.rds_set_external_master with the proper private IP. Please don't use 192.168.x.x.


store country name or country id I am designing a database layout for a site. Agents registering on the site will have to enter their address including the country. Later I will build a dashboard where you would be able to search/sort by fields (including country field) and produce various reports (which includes country as well).

Is it better to store country name directly in a table or have a separate table with countries and only store the id of a country?

It's better to have a separate table. That way your data will be normalized.

In your specific case you most likely want a list of countries the user can select from (eg. it's not a free form field). By having it be a separate table and referencing the country id you can setup a foreign key constraint to your countries table. This way your data will always be in sync as user's will not be able to enter a country that does not exist.


Rebuild or Reorganize index I am using SQL server 2008 R2.
The DB is normally used from 9 am to 9 pm and the maximum db size is 10 GB.
Whether rebuild index offline causes any problem, if it is done during midnight (when no one is using db)?
or reorganize is enough for index maintenance?

First question - are you using SSD or hard disks. If you are using SSD you probably don't need to do index maintenance at all (maybe once or twice a year) as doing a random read has no cost disadvantage over doing a sequential read.
That said if your database is not in use a rebuild ought to be perfectly fine, be aware that the transaction log should be sized to be equal in size to the largest table as the rebuild will be done in a single transaction.


How to order by COUNT(*) of unique entries in GROUP BY? I search through a tag_map as
SELECT ArticleID,COUNT(*) AS C FROM WordMap
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
)
GROUP BY ArticleID
ORDER BY C DESC

I want to get article ids in which all words (here word1 and word2) appear first.
The above query returns in order of the number (e.g., if word1 appears 100 times).
NOTE: WordMap table is huge, and this is the reason I went for sub-query rather than JOIN.

I answer this question because @ypercubeᵀᴹ was reluctant to do so, and I thought it might help others.
The tricky part is to use COUNT(DISTINCT WordID) instead of COUNT(*), as the latter does not consider the repetition.
As suggested by @ypercubeᵀᴹ, the ultimate solution is to use COUNT(DISTINCT WordID) and COUNT(*) together to have a perfect order considering the number of unique words and total occurrence.
The query looks like
SELECT ArticleID,COUNT(*) AS C, COUNT(DISTINCT WordID) AS D FROM WordMap
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
)
GROUP BY ArticleID
ORDER BY D DESC, C DESC


Postgresql pg_hba.conf changes are ignored or not loaded Using Postgresql 9.3 on Ubuntu 14.04.4 LTS

I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely.

My current configuration looks like so:

host all postgres <remote-ip>/32 trust
asdf asdf asdf
1 1
1-1-1-1-

This is, of course, a completely bogus config file.  However, when I run

sudo service postgresql restart
sudo service postgresql reload

Both commands succeed with status [ OK ] - there are no parse errors.  I have also disabled all entries that start with local and yet I am still able to login as the postgres user.

Any attempts to log into the server remotely from the  host
using:

psql -c 'SELECT version()' -h pg -U postgres

Result in:

psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

I am clearly not trying to use password authentication.

EDIT:

Running SHOW hba_file; as the postgres user yeilds

                hba_file
--------------------------------------
 /etc/postgresql/9.3/main/pg_hba.conf
(1 row)

Edit 2:

Checking the logs shows that the parse errors were happening.  I have cleaned out the bogus lines, leaving

host all postgres <remote-ip>/32 trust

I am still getting a password prompt for some reason.  There is nothing in the log after a failed login.

Figured it out.  This was a long shot, but it may help someone who has exhausted the normal avenues or is not used to postgres.

The server I was working on had another instance running through puppet, and so the config I was modifying was not for the instance I was connecting to.

run find / -name 'postgresql.conf' to see if there are any other locations where you might have a postgres instance.


Dynamic Pivot Table syntax error What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   GROUP_CONCAT(DISTINCT
    ->     CONCAT(
    ->       'IF(q.label = ''',
    ->       label,
    ->       ''', 1, 2) AS ',
    ->       label
    ->    )
    ->   ) INTO @sql
    -> FROM question;
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
    '>       FROM answer a
    '>       INNER JOIN question q ON questionId = q.id
    '>       GROUP BY a.id');
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax ... near ' q.id

Why does the first one pass but the second one blows up?

Adding PREPARE stmt FROM @sql; on the GROUP_CONCAT query gives me this:

You have an error ... near 'IF(q.label = 'Q1', 1, 2) AS Q1,IF(q.label = 'Q2', 1, 2) AS Q2,IF(q.label = '', 1' at line 1

I'm not expecting the label to be empty on the last if, but I don't see how that would blow up the last query.

The query without the dynamic part returns this:

+----+-------+----+
| id | label | id |
+----+-------+----+
|  1 | Q1    |  1 |
|  2 | Q2    |  1 |
|  3 | Q1    |  1 |
|  4 | Q2    |  1 |
+----+-------+----+

This is the correct form of the if clause

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'IF(q.label = "',
       label,
       '", 1, 2) AS ',
       label
    )
   ) INTO @sql
    FROM question;
SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
       FROM answer a
       INNER JOIN question q ON questionId = q.id
       GROUP BY a.id');
PREPARE stmt FROM @sql;

But still you should always write the result of
    SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'IF(q.label = "',
       label,
       '", 1, 2) AS ',
       label
    )
   ) INTO @sql
    FROM question;
 SELECT @sql;


Creating same unique constraint with different names In MySQL 5.7

alter table mydb.roles add constraint uk1 unique (role_name);
alter table mydb.roles add constraint uk2 unique (role_name);

The 2nd alter command threw me a warning stating:

  Duplicate index 'uk2' defined on the table 'world_x.roles'. This is
  deprecated and will be disallowed in a future release.

When I tried the same in Postgres(10.x), it didn't warn me.

So, what would the SQL standard be in this scenario?

It can be result very confusing mixing names constraints and slow down your queries because they have the same name

maybe this results helpful

  CREATE ONLY ONE CONSTRAINT INDEX AND INCLUDE THOSE NECESSARY COLUMNS

Example

ALTER TABLE users ADD CONSTRAINT findUser UNIQUE (nameUser,emailUser);

No double constraint and no warning message and you keep the SQL rules alive

  Remember SQL is an standard but not implemented at the same time for
  everyone with the same rules

As you can see at the following link, in fact these scenario is a bug

https://bugs.mysql.com/bug.php?id=37520

and finally recognized in: https://bugs.mysql.com/bug.php?id=8565

and finally fixed in MySQL Server 5.6.7


How do I generate a date series in PostgreSQL? If you're looking to generate a time series, see this question

Let's say that I want to generate a series of dates between two dates.
 I see the function generate_series provides only

Function                                    Argument Type                         Return Type                                                               Description
generate_series(start, stop, step interval) timestamp or timestamp with time zone setof timestamp or setof timestamp with time zone (same as argument type) Generate a series of values, from start to stop with a step size of step

So how would I go about doing this?

You can use generate_series for this, but be sure to explicitly cast the arguments to "timestamp without time zone" otherwise they will default to "timestamp with timezone". PostgreSQL overloads generate_series for both inputs.

Problems with timestamp with timezone

You can see the drawback here.

SET timezone = 'America/Santiago';
SELECT generate_series(date '2016-08-15', date '2016-08-15', '1 day');
SELECT generate_series(date '2016-08-14', date '2016-08-15', '1 day');

Both of the above return the same amount of days. You can see it again here.

SET timezone = 'America/Sao_Paulo';
SELECT generate_series(date '2016-10-16', date '2016-10-17', '1 day');
SELECT generate_series(date '2016-10-17', date '2016-10-17', '1 day');

The above shows two ranges of one day.

The reason for this behavior is that these timezones have their "DST boundary at midnight, rather than a more sensible time in the small hours"

So what does it look like to "do it right",

SELECT generate_series(
  timestamp without time zone '2016-10-16',
  timestamp without time zone '2016-10-17',
  '1 day'
);

Now you can cast to date..

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-10-16',
  timestamp without time zone '2016-10-17',
  '1 day'
) AS gs(d);

This question and answer was inspired by a conversation with RhodiumToad on IRC (irc://irc.freenode.net/#postgresql). He altered me to this issue and provided the solution.

Update: two potential fixes

Option 1: generate_series(date,date,interval)

Playing around, I discovered I could perhaps save the need to explicitly cast to timestamp without time zone by overload for generate_series(date,date,interval)

Here is my function,

CREATE FUNCTION generate_series( t1 date, t2 date, i interval )
RETURNS setof date
AS $$
  SELECT d::date
  FROM generate_series(
    t1::timestamp without time zone,
    t2::timestamp without time zone,
    i
  )
    AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Now I can rerun the test case above and it's no longer fishy. These two both return the same thing,

SET timezone = 'America/Santiago';
SELECT d::date
FROM generate_series(date '2016-08-15', date '2016-08-15', '1 day')
  AS gs(d);

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-08-15',
  timestamp without time zone '2016-08-15',
  '1 day'
)
  AS gs(d);

As do these two,

SELECT d::date
FROM generate_series(date '2016-08-14', date '2016-08-15', '1 day')
  AS gs(d);

SELECT d::date
FROM generate_series(
  timestamp without time zone '2016-08-14',
  timestamp without time zone '2016-08-15',
  '1 day'
)
  AS gs(d);

Option 2: generate_series(date,date,int)

Another option is to create a new function generate_series(date,date,int) however you can't have both for the reasons mentioned here. So pick one of these,

generate_series(date,date,interval)
generate_series(date,date,int)

If you want the second option, try this one:

CREATE FUNCTION generate_series( t1 date, t2 date, i int )
RETURNS setof date
AS $$
  SELECT d::date
  FROM generate_series(
    t1::timestamp without time zone,
    t2::timestamp without time zone,
    i * interval '1 day'
  )
    AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Caveats

With review on irc, there are some problems with those ideas,

  < johto> generate_series(date, date, unknown) already works today. when you don't break it outright with the int version (e.g. generate_series(date, date, '1 day')) you change the return type from timestamptz to date. (date, date, interval) would break fewer cases, but you'd still change the output type. (it's also not obvious what should happen with  (date, date, '1 hour')  that currently "works" just fine)


How should I organize my mdf, log and tempdb on a 3 physical disk server Windows Server 2012, 48Gb ram, SQL 2016 Standard with:

1 disk SAS for Windows and SQL binary (C: )
2 SSD disk for data (D: and E: )

Talking about performance, what is the better way to put tempdb, mdf and log of my OLTP Database (about 50Gb, about 100 simultaneous users)?

Maybe:

C: - OS and sql binary
D: - MyDB and Tempdb data file
E: - MyDB and TempDB log file

(D: and E: are physical disk, i don't think it's useful for performance creating more Logical partition to divide objects, or not?)

It's the "better" way?

It really depends on your workload but with only two drives you're going to have to make some concessions.  Ideally you want to have tempdb, data and log files all on their own drives.  There's a lot of nasty things going on in tempDB and you don't want that affecting your production databases.

As I said, you should consider your workload, if tempDB is made to work particularly hard or you've got a database that has few write operations then you might find that you're better off putting tempDB on it's own drive and bundling the data and log files together.  In any other situation I'd put tempDB on the same disk as the data files.


How to reference only affected rows in AFTER UPDATE trigger I have this table:

And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number or step_name column.

I started creating it by

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN
    UPDATE [dbo].[app_bread_crumbs]
    SET last_updated_by = SYSTEM_USER,
    last_updated_on = GETDATE()
END

But this will update all rows at once. I'm trying to figure out how to specify to only update last_updated_on and last_updated_by by the trigger for the specific row where updates are being made.

For example if they update:

UPDATE [dbo].[app_bread_crumbs]
    SET step_name = 'DAMAGE' WHERE step_number = 1

Only first row should be updated by the trigger

Use the inserted table, which is a special table available inside triggers containing the rows that will be updated/inserted into the table.

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN
    UPDATE [dbo].[app_bread_crumbs]
    SET last_updated_by = SYSTEM_USER,
    last_updated_on = GETDATE()
    FROM dbo.app_bread_crumbs abc
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.id = abc.id);
END


How do I add an alias to the table SELECT MAX(COUNT), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count
    FROM `library`.`lms_book_details`
    GROUP BY supplier_id
) GROUP BY A.id;

While executing the query, MySQL gives an error:

  Every Derived table must have its own alias.

How do I add an alias to this?

Try like

SELECT MAX(A.count_supplier), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count_supplier
    FROM library.lms_book_details
    GROUP BY supplier_id
) A GROUP BY A.id;

Alias inner COUNT(*) as something different than COUNT as it is good practice to not use it as alias , As i have used count_supplier.


Basic Partition Switching Question I'm new to partition switching and have used this basic syntax previously:

TRUNCATE TABLE TARGET

ALTER TABLE SOURCE SWITCH TO TARGET

I just came across an SSIS package where there is this syntax within a loop:

TRUNCATE TABLE TARGET

ALTER TABLE SOURCE
SWITCH PARTITION $PARTITION.MONTHFN(@MONTH) TO TARGET PARTITION $PARTITION.MONTHFN(@MONTH)

If all the partitions are being switched to the target table, is it necessary to do the switching by partition? Is this only because the source is already partitioned?

This syntax for ALTER TABLE SWITCH requires you to specify a single partition.

ALTER TABLE [ database_name . [schema_name ] . | schema_name. ] source_table_name
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ]
}
[;]

I found several articles that show different ways to automate the switching of all partitions - this is just one example

DECLARE @Partitions TABLE (PartitionId int PRIMARY KEY CLUSTERED);
DECLARE @PartitionId INT;

INSERT @Partitions(PartitionId)
    SELECT
        prv.boundary_id PartitionId
    FROM sys.partition_functions AS pf
    INNER JOIN sys.partition_range_values prv ON prv.function_id=pf.function_id
    WHERE (pf.name=N'PartitionFunctionName');

WHILE EXISTS (SELECT NULL FROM @Partitions)
    BEGIN

        SELECT TOP 1 @PartitionId = PartitionId FROM @Partitions;

        ALTER TABLE MS_PROD SWITCH PARTITION @PartitionId TO MS_Stage PARTITION @PartitionId;

        RAISERROR('Switched PartitionId %d to Stage',0,1,@PartitionId) WITH NOWAIT;

        DELETE @Partitions WHERE PartitionId = @PartitionId;

    END


Do I need to back up the log file drive when using VSS snapshots in simple mode? We're currently using AppAssure to back up the SQL servers (2012/2014). It uses VSS snapshots. All databases are in simple recovery mode. Do I need to bother backing up the drive that contains the ldf files or is it enough to just back up the mdf files and let the log files recreate during a full restore?

SQL Writer Service explains how VSS can be used to take application consistent snapshot backups of the database files directly. It says it does not support log backups, which is why I asked this question.

Snapshot Backups are a feature of SQL server that allow hot backups without doing traditional dumps.

Couple thoughts on this one:
Do you have more than one database per-SQL instance?  Are they all "owned" by the same application?  If you've got multiple DBs for multiple apps, can your backup solution only restore ONE database (both the MDF and LDF) or will you have to restore all the DBs?

As Kris G pointed out, how do you KNOW your VSS snapshots are restorable to a working SQL DB?  Have you test restored them somewhere?

Lastly, to cover your question, it's a bad idea to not backup the transaction log.  Even in Simple Recovery, you can have "in flight" transactions that haven't gotten to the point of being written into the MDF yet.  Not having the Trans Log when you restore one of these will make getting the DB back online and functional a much more difficult task.

Frankly, I'd take the safe way out, turn off the VSS snapshots of your SQL data and log drives, set up a backup process using the native SQL backup command on a set schedule to a location that is covered by the VSS.  More work for you, a bit more work for whoever's in charge of the VSS snapshots, but you'll be much happier if / when you have to restore a database down the road.


Getting new and updated data with an archive column I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers where SQL Server 2008 Standard Edition is installed. CDC, therefore cannot be used as it is an Enterprise feature. The plan is this:

Archive column, smallint - 0=new (default constraint), 1=archived, 2=updated (trigger)
Default constraint Archive=0
Update trigger Archive=2
Index on archive, timestamp

The SSIS package will then get all records where Archive=0, insert those into the destination then get all records where Archive=2 and update them in the destination.

What I need help with is the setting of the Archive column to 1 after the SSIS procedure is done writing new or updated rows. What would be the most efficient way of setting all of the rows processed without touching any rows that might have been inserted or updated after the initial select?

Since you are using SSIS and capturing the changed rows, you should have SSIS capture the PK values for the rows that are being processed. The last step of the package should be to do a targeted update on the source table, setting the [Archive] field to 1 based on those PK values.

And if not already done, the update trigger needs to exclude those changes from setting the [Archive] field to 2. You will probably need something along the lines of:

UPDATE tab
SET    tab.[Archive] = 2
FROM   dbo.MyTable tab
INNER JOIN INSERTED ...
INNER JOIN DELETED ...
WHERE INSERTED.Archive = DELETED.Archive
AND   DELETED.Archive IN (0, 1) -- Add 3 to this list; see below for why

BUT, then you need to deal with the scenario where a row gets update during the ETL process. You still want to ETL that change the next time around. But if the ETL process is running it will change the [Archive] field to 1 at the end and then there is no record of that row being changed. So then you kinda need another value to indicate "Being ETLed", maybe 3. You can then, similar to what @billinkc suggested in a comment on the question, do an UPDATE with the OUTPUT clause as follows:

UPDATE TOP (@BatchSize) tab
SET    tab.[Archive] = 3
OUTPUT INSERTED.*
FROM   dbo.MyTable tab
WHERE  tab.[Archive] IN (0, 2)
ORDER BY tab.[Archive] ASC;

Then, in the final step of the SSIS package, the targeted update should be on both the PK values AND [Archive] = 3. And add 3 to the IN list in the trigger update query above.  This way, only records that have not changed during the ETL process will get updated to 1, while records that have been changed during the ETL will appear as 2 and will get picked up again the next time around with their modified values.

Of course, you might also want to create a filtered index on the [Archive] field to support the getting of records marked as 0, 2, or 3. Hence:

CREATE INDEX ... WHERE [Archive] IN (0, 2, 3);

You want to grab the records marked as 3 because, assuming this is single-threaded per field server, records marked as 3 represent stuck records due to a prior process that failed. This is also why you don't want to mark the records as 1 in the UPDATE...OUTPUT when you initially get them; if the process fails then they will be marked as already archived and not picked up again.

That being said, you might be better off using a queue table instead of a status field. You could create a simple table with just a few fields:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTablePKfield  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

Then the trigger becomes a simple INSERT, UPDATE trigger that just does:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.PKfield
  FROM   INSERTED ins;

Then the SSIS process gets the values via:

SELECT TOP (@BatchSize) etl.QueueID, tab.field1, tab.field2...
FROM   dbo.MyTableEtlQueue etl
INNER JOIN dbo.MyTable tab
        ON tab.PKfield = etl.MyTablePKfield
ORDER BY etl.QueueID ASC;

And the end of the SSIS process does a simple delete against the [MyTableEtlQueue] table based on the [QueueID] values it pulled in at the beginning.

In this model, you don't need the filtered index, or status column on a large table, which reduces contention on, and size of, that already large table. You don't need to worry about records in transit, whether in terms of updates or process failures.

EDIT:
Given the new information of the source table not having any uniqueness, that requires only a minor change to this queue table method: instead of tracking only the PKfield (which apparently doesn't exist ;-), track all of the fields (or at least all that need to be ETLed).

So the queue table changes to be:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTableField1  INT NOT NULL,
MyTableField2  INT NOT NULL,
...
MyTableFieldN  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

And the trigger changes to be:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.MyTableField1, ins.MyTableField2, ..., ins.MyTableField1N
  FROM   INSERTED ins;

Then the SSIS process changes to be (note the lack of JOIN to the source table now):

SELECT TOP (@BatchSize) etl.QueueID, etl.MyTableField1, etl.MyTableField2, ...
FROM   dbo.MyTableEtlQueue etl
ORDER BY etl.QueueID ASC;

This queue table method isolates the source table (and its processing) from the audit/ETL process (much like CDC does). This allows you app to interact with the source table with no issues, and no new column which takes up more space on the datapage, and no potentially long running transaction holding onto the source table while the ETL processes.


How to set identity_insert to on in SQL Server I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created an 'admin' account and gave it permissions to do anything (to the best of my knowledge).

Whenever I run the command under either account I get the result of '0 rows affected', then I am unable to run the query to insert the rows I wish to insert with the following error:

  Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Additionally I've tried to include the schema name in the query like:

set identity_insert mySchema.table_name on;

but I get the following error:

  Cannot find the object "mySchema.table_name" because it does not exist or you do not have permissions.

Also of note, my project looks like:

localhost\SQLEXPRESS
-Databases
--System Databases
--mySchema

Does anyone know a solution to this problem?

You are using the right syntax:

SET IDENTITY_INSERT dbo.sometable ON;

However, a schema in SQL Server is not the same as a database.

The default schema for objects is dbo as in my example. You do not need to specify the database name if you are connected to it, but you could like this:

SET IDENTITY_INSERT DbName.dbo.sometable ON;

There seems to be no way to get the current setting as it is session-scoped and not persisted.

If you run this query in your database it returns the fully qualified names for all tables in there:

SELECT
    QUOTENAME(DB_NAME())+'.'+
    QUOTENAME(OBJECT_SCHEMA_NAME(object_id))+'.'+
    QUOTENAME(OBJECT_NAME(object_id)) AS FullTableName
FROM sys.tables;

SET IDENTITY_INSERT ON... must be run in the same session as the insert.


Make SQL Server SSDT Database Project Reference Different in Publish Profile In a SSDT Database project, I know how to publish the Self-Database to different locations. I can change eg, the Customer database name in the Publish profile.

Example
In:

Dev Environment: will publish profile to CustomerDevDB
QA Environment: will publish profile to CustomerQADB
Prod Environment: will publish profile to CustomerProdDB

(This is legacy system, I did not design it like this)

Now in our Customer Database, I have stored procedures which point to a Product database reference.
How do I change my Project reference to be variable for each Publish Profile?

Dev Environment: will publish to CustomerDevDB and will point to ProductDev

QA Environment: will publish to CustomerQADB and will point to ProductQA

Prod Environment: will publish to CustomerProd and will point to ProductProd

I am trying to test around with Project DB Reference, and SQL Command variables.
* I can point the Database Reference to different databases names,  however I cannot change the reference in the publish profile yet (which is what I am really looking for).

If someone can print screen images of how to do this, it would be helpful. Thanks

I'm not sure how to do this in the UI, however, the publish profiles are xml files, and SQL Command variables and their values can be stored within them.  In this specific case, you'd want to specify the value of the $(ProductVariable) like so for your ProductDev deployment:

  <ItemGroup>
    <SqlCmdVariable Include="ProductVariable">
      <Value>ProductDev</Value>
    </SqlCmdVariable>
  </ItemGroup>

You'd add that ItemGroup to your publish profile after the </Property Group> before the </Project>


What is a online atomic mode backup I am reading the SAP guide administrator of BO, and I am a little confused about some term they use regardless how to perform online or hot backups.

Using the same words

  For hot backups, use the database vendor's backup tools in online atomic mode

It is the first time ever that I heard about online atomic backups.

And what is the difference between these kind of backups and normal backups?? While the guide is talking about making offline backups they do not use the term online atomic mode so I think administrator should perform a normal backup (Normal for me)

Could anyone make some light please??

Thanks in advance

Active database files are constantly changing. Therefore, trying to simply make a copy of an active database file will not result in a valid backup file, but will generally result in a corrupted file; the data written at the start of the file will not necessarily be consistent with the data written later in the file.

So, most database systems have their own internal backup methods, to ensure that a backup file accurately represents the data as of a given moment in time. This backup would be atomic in the database sense - even if it takes hours to write out the file, the contents will be consistent with when the backup started, as though no changes were made to the file for all that time. However, changes are still happening to the file (hence, the online part of the description).

The only other way to get a consistent database backup file is to shut down the database. If the database is not active (not online, in other words), then no changes are happening, and the OS can copy the file without risk of corruption. This would be an offline backup.

Note that there are other differences between these two backup methods. An offline backup is normally going to be a simple copy of the database files; in theory, restoring the database would be as simple as replacing the current files with the backup files. An online atomic backup is usually stored in a different format, and has to be restored by running commands in the DBMS.


Function to convert first names to a specific format I'm trying to figure out a way to show a name properly.
Let's say I have 2 variables:

@Firstname = 'Marc-Oliver'
@Lastname = 'Saint-Onge'

I would like to have name shown as

M.-O. St.Onge
Or at least M.-O. Saint-Onge

I know it's probably weird for you guys to understand this, as there are not a lot of similar situations in English, but in French this is much more familiar.
How could this be possible?
Update
In fact, there are many other possibilities for first name such as 'Pier-Luc', 'Marc-André', 'Marc-Antoine', etc. This is really familiar here in Quebec, in the French language. Forget about last name, I was thinking about it and I figured that it is much more specific so I won't touch it at all. I'll keep it as Saint-Onge and not St. Onge.

Quick and dirty, but I'm assuming you'll be putting this into a more formalizing procedure for spitting out names.

DECLARE @Firstname nvarchar(50)
DECLARE @Lastname nvarchar(50)

SET @Firstname =  'Marc-Oliver'
SET @Lastname = 'Saint-Onge'

SELECT @FirstName, @Lastname

IF CHARINDEX('-', @FirstName) <> 0
    SELECT SUBSTRING(@Firstname, 1, 1) + '.' +
    SUBSTRING(@Firstname, CHARINDEX('-', @Firstname), 2) + '. ' + @Lastname
ELSE
    SELECT @Firstname + ' ' + @Lastname

Which produces

Marc-Oliver     Saint-Onge
--------------------------------------------------------
M.-O. Saint-Onge

Edit:

I had never really messed around with pattern matching like this before, so it's been interesting. I believe this should work for hyphens, periods, or spaces in the first name. I tested with 'Marc Oliver', 'Marc.Oliver' and 'Marc-Oliver' all of which returned 'M.-O.' for the first name.

IF @Firstname LIKE '%[-. ]%'
    SELECT SUBSTRING(@Firstname, 1, 1) + '.-' +
    SUBSTRING(@Firstname, PATINDEX('%[-. ]%', @Firstname) + 1, 1) + '. ' + @Lastname
ELSE
    SELECT @Firstname + ' ' + @Lastname


How do I get the execution plan for a view? I have a schema with a number of views.  I need to check the execution plans to make sure the appropriate indexes are in place and being used.

How do I do this?

I'd rather not have to copy and paste the output from show create view <viewname> into explain, especially as some of the views are built on top of other views and this would be quite a pain.

Use the information_schema.views table
This will generate the EXPLAIN for all views
mysql -uroot -p -AN -e"select concat('explain ',view_definition) from information_schema.views" > /root/ExplainViews.sql

This will generate the EXPLAIN for all views in the mydb database
mysql -uroot -p -AN -e"select concat('explain ',view_definition) from information_schema.views where table_schema = 'mydb'" > /root/ExplainViews.sql

Give it a Try !!!
UPDATE 2012-03-22 11:30 EDT
@MattFenwick, your answer is a whole lot simpler than mine. Here is an example I tried out on my PC running MySQL 5.5.12. I ran EXPLAIN on both the SELECT version from your answer and the EXPLAIN generated from my answer:
mysql> explain select * from bigjoin;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | k     | index  | NULL          | PRIMARY | 4       | NULL          |   14 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.k.id_key |    1 | Using index |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    4 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql> explain select `a`.`id_key` AS `id_key1`,`b`.`id_key` AS `id_key2` from ((`test`.`idlist` `k` left join `test`.`id_key_table` `a` on((`k`.`id_key` = `a`.`id_key`))) left join `test`.`new_keys_to_load` `b` on((`k`.`id_key` = `b`.`id_key`)));
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | k     | index  | NULL          | PRIMARY | 4       | NULL          |   14 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.k.id_key |    1 | Using index |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    4 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql>

They both produced the same EXPLAIN plan. I will change my answer to implement your way. You get a +1 from me although it's +2 for simplicity. You should go ahead and accept your own answer on this one.
Here is an interesting factoid about VIEWs in MySQL : A view is represented in two places in the information_schema database

information_schema.views : table_schema,table_name identify, and view_definition
information_schema.tables : table_schema,table_name where ENGINE is NULL

This will generate the EXPLAIN for all views
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.tables WHERE engine IS NULL" > /root/ExplainViews.sql

or
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.views" > /root/ExplainViews.sql

This will generate the EXPLAIN for all views in the mydb database
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.tables WHERE table_schema='mydb' AND engine IS NULL;" > /root/ExplainViews.sql

or
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.views WHERE table_schema='mydb';" > /root/ExplainViews.sql


.pcppass not working I run pgpool-II as postgres user so I can replicate exactly what I did in one sequence here in this post.

-bash-4.2$ echo "pgpool:$(pg_md5 test)" > /etc/pgpool-II-11/pcp.conf
-bash-4.2$ cat /etc/pgpool-II-11/pcp.conf
pgpool:098f6bcd4621d373cade4e832627b4f6
<Restart pgpool-II-11 service>
-bash-4.2$ echo "cia-db-vip:9898:pgpool:$(pg_md5 test)" > .pcppass
-bash-4.2$ cat .pcppass
cia-db-vip:9898:pgpool:098f6bcd4621d373cade4e832627b4f6
-bash-4.2$ pcp_watchdog_info -h cia-db-vip -p 9898 -U pgpool -w 0
FATAL:  authentication failed for user "pgpool"
DETAIL:  username and/or password does not match

-bash-4.2$ pcp_watchdog_info -h cia-db-vip -p 9898 -U pgpool 0
Password: <I Enter the password test>
2 YES cia-db-3:9999 Linux cia-db-3 cia-db-3

cia-db-3:9999 Linux cia-db-3 cia-db-3 9999 9000 4 MASTER
-bash-4.2$ ls -l .pcppass /etc/pgpool-II-11/pcp.conf
-rw-------. 1 postgres postgres 40 Jul 30 11:22 /etc/pgpool-II-11/pcp.conf
-rw-------. 1 postgres postgres 56 Jul 30 11:23 .pcppass

So only if I enter the password does it work. I've also tried hitting enter without a password.

I tried checking strace -ff pcp_watchdog_info and it starts to read the file .pcppass but I can only see the first line. Have no idea if it continues reading through the file or closes it.

stat("/var/lib/pgsql/.pcppass", {st_mode=S_IFREG|0600, st_size=56, ...}) = 0
open("/var/lib/pgsql/.pcppass", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0600, st_size=56, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f581f3d6000
read(4, "cia-db-vip:9898:pgpool:098f6bcd4"..., 4096) = 56
close(4)                                = 0
munmap(0x7f581f3d6000, 4096)            = 0
write(3, "M\0\0\0\4", 5)                = 5
select(4, [3], NULL, [3], NULL)         = 1 (in [3])
read(3, "m\0\0\0\10\205^\v\341", 1024)  = 9
write(3, "R\0\0\0,pgpool\000381d652cc9ef7891d4cb"..., 45) = 45
select(4, [3], NULL, [3], NULL)         = 1 (in [3])
read(3, "E\0\0\0xSFATAL\0CXX000\0Mauthenticati"..., 1024) = 121
close(3)                                = 0
write(2, "FATAL:  authentication failed fo"..., 98FATAL:  authentication failed for user "pgpool"
DETAIL:  username and/or password does not match

I also tried resetting the postgres user pgpool password to test, including in /etc/pgpool-II-11/pool_passwd but I doubt it matters since I can actually use pcp commands if I enter the password. So it seems that user is separate from the postgres users.

Edit: Also tried temporarily setting SElinux to permissive but it did not help.

The password is not supposed to be pg_md5 encoded in the .pcppass file, only in the pcp.conf file.


Triggers lost when changing table property in SQL Server Management Studio designer We found a bug in the SQL Server Management Studio. When you change any table property that requires the table to be dropped and re-created, all your triggers are gone. It must be done within the designer. The designer needs to have "Prevent saving changes that require re-creation" option disabled. In general, every time such a change occur the table is copied, dropped and recreated with the new set of properties. Unfortunately, the process deletes your triggers.

We checked every version of SQL SMS we have, and this are: 2008, 2012, 2014 and 2016. The bug is fixed only in the 2016 version. It might be important, that we used to have Microsoft SQL Server 2000 (32 bit) database and upgraded it to Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64).

We found the bug thanks to our database documentation. We saw there are no triggers in the table and our first thought was that there were none from the very beginning. A quick look at the documentation was enough to see that we are wrong. We recreate the process in our database. You can watch in on the video below:

https://www.youtube.com/watch?v=1VWrPBOC5rA

Did you encounter it in any other SSMS version and if yes, then on which database version?

Do you know any workaround for it?

You shouldn't be doing those changes in the GUI anyway but in T-SQL it's the only way you can reliably publish changes from dev > test > prod and it's the only way you can have source control on your database structure.

That being said, this happens when you change things that require the table to be recreated and is not a bug but documented behaviour.

Please see Error message when you try to save a table in SQL Server: "Saving changes is not permitted" which clearly states (emphasis mine):

  This problem occurs when the Prevent saving changes that require the
  table re-creation option is enabled, and you make one or more of the
  following changes to the table:

  You change the Allow Nulls setting for a column.
  You reorder columns in the table.
  You change the column data type.
  You add a new column.

  When you change a table so that you alter the metadata structure of
  the table, and then you save the table, the table must be re-created
  based on these changes. This may result in the loss of metadata and in
  a direct loss of data during the re-creation of the table. If you
  enable the Prevent saving changes that require the table re-creation
  option in the Designer section of the SQL Server Management Studio
  (SSMS) Options window, you receive the error message that is mentioned
  in the "Symptoms" section.

The work-around in that article is exactly what you should be doing:

  To work around this problem, use Transact-SQL statements to make the
  changes to the metadata structure of a table.


tuning postgresql for large amounts of ram I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm etc).

On the one server, I am running sql server 2005, on the second server postgresql 9.1. The difference in performance b/n these 2 servers is staggering, it's so bad on postgresql that I'm regretting my initial "let's use postgresql instead of paying for the sql server license" speech to my boss. We're talking differences of 30 seconds vs 15 mins for the same command, and it's not just this one command, it's any query or command I throw at it. They both have pretty much the same data (records were inserted in different order), and both databases have the exact same structure / indexes etc.

But I'm hoping it's just a matter of performance tuning. The thing is, sql server is pretty much using all 32 gigs of ram on the server, whereas postgresl is using nothing, definitely less than a gig though I haven't actually figured it out in fine detail.

How do I get postgresql to use 20+ gigs of ram? These servers were built specifically for this database stuff, so any ram not in use by the database and supporting processes is wasted in my opinion.

There are many tweakable constants, initialised via postgres.conf. The most important ones are:

max_connections: the number of concurrent sessions
work_mem : the maximal amount of memory to be used for intermediate results such as hash tables, and for sorting
shared_buffers the amount of memory dedicated to 'pinned' buffer space.
effective_cache_size the amount of memory assumed to be used by the OS's LRU buffers.
random_page_cost : an estimate for the relative cost of disk seeks.

max_connections should not be set higher than needed, connections cost resources even when idle; in most cases a connection would spend more time waiting inside than waiting outside. (at the price of concurrency) A nice rule-of-thumb formula is "number of spindles+number of processors+X"

work_mem is tricky: is can be applied to every subquery, so a query with 5 HASHJOINS might cost 5*work_mem. And for worst-case scenarios, you should also think of multiple sessions consuming this amount (again a reason to keep max_connections low).

shared_buffers is (IMHO) overrated. Normally it is advised to set it to about 1/4...1/2 of all available "free" memory, but I tend to keep it low, and set effective_cache_size to all available "free" memory.

random_page_cost is the cost for a seek+read on the disk. It is relative to the sequential_disk_cost, which is 1. The default (4) for random_page_cost is set too high for modern machines and network storage, normally it can be lowered to between 2 and 1.x. For SSD disks yould even set it to 1.0, since seeking is almost for free on SSDs.


How can I search for all points with a specific range (radius) of another point? The syntax I use to query for polygons in range of other Points

SELECT  *
FROM    dbo.myTable
INNER JOIN
    dbo.myPOI ON myTable.geog.STIntersects(myPOI.geog) = 1

But, to query points of interests, I use a radius and I'm using this.

SELECT  *
FROM    dbo.myTable
INNER JOIN
    dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius

Is there a better way to write this? Can the queries be improved?

Can the queries themselves be improved?

...
dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius

Yes. That's going to be slow. You could massively improve that query by buffering the point and finding the intersects:

ON myTable.geog.STIntersects(myPOI.geog.STBuffer(PP.Point_Radius)) = 1

You can make this even better in your case because the radius is provided on the row, rather than in the query. You can store the geometery returned by STBuffer on the table instead of the point. If you ever want to to find the center point (which you'll probably never need), you can use the GIS function STCentroid.

Other implementations

As a side note, this type of query is done with ST_DWithin in PostGIS and will run on the index.


Are there RDBMS which can count rows fast? If I use query this in MySQL:

SELECT * FROM table WHERE some_col=something ORDER BY some_col LIMIT 10000, 10

It will scan 10010 rows, because it is not able to find row number 10000 without it. Are there RDBMS that can perform such tasks faster, straight getting to 10000 row without scanning all 10000 (with WHERE clauses like in the example above)?

Most RDBMS mostly use BTrees for indexes. These hold no summary information internally about the fan-out or total number of descendents. So the only way to get to a desired offset within the data is to start at the beginning a step through.

If you were able to explicitly define a sequencing column within your table the RDBMS may be able to use an index on it to more quickly get to your offset (no guarantees; which indexes the optimiser chooses to use is a complex topic). The query would then become

SELECT * FROM table
WHERE some_col = something
AND sequence_col > 10000        -- may get an index seek because of this, maybe not
ORDER BY sequence_col LIMIT 10  -- ordering by the new column

There are many risks associated with this, including

maintaining separate sequence columns for each desired sort order
concurrent changes in the data while a read is in progress
holes appearing in the sequence

Usually you are much better off simply indexing the existing business-required columns in an appropriate way and implementing pagination. @Vladimir has given a link for this.


How to edit current query without executing it? I am aware of using \e in psql to edit the query in the buffer.
But what if you are currently creating the query for the first time and want to jump to the editor?
Or better, you go through history to a previous, expensive, query and want to add some edits before.
A nasty trick could be going to the first char of the line an add a comment (/*) and the same at the end, but something like C-xC-e in the shell will be great.

Don't write a trailing semicolon. If you go through the history, remove the trailing semicolon. That will put the query in the buffer, and you can edit it with \e.


Increment a column in table based on another table I have 2 table in my database.

Table #1

----------------------------
| name | id | overdue_days |
----------------------------

Table #2

-------------
| name | id |
-------------

This is to maintain a fine collection database where overdue_days in table #1 corresponds to how many days the fine submission by a particular id pending is done.

Table #2 receives every day the names of defaulters for that day.

My intention is to compare table #1 and table #2 and

Increment the overdue days for ids which are present in table #2 and are already present in table #1.
If a particular id is present in table #1 but not in table #2 , then set its overdue days to 0.
If an id is present in table #2 but not in table #1, insert a row for it with overdue days as value 1.

My desired result would be updated table #1 with the above conditions.

I can think of one way to use three separate join queries and cover each of the cases.

update T1
set overdue_days = overdue_days + 1
from table#1 T1
inner join table#2 T2
on T1.id = T2.id;

Is there an optimised way to do the above?

For MySQL only.

If the id field in the table1 is primary key or has unique constraint, the solution can consist of 2 queries:

INSERT IGNORE INTO table1 (name, id, overdue_days)
SELECT name, id, 0
FROM table2;

UPDATE table1
LEFT JOIN table2 ON table1.id=table2.id
SET table1.overdue_days = CASE WHEN table2.id IS NULL
                               THEN 0
                               ELSE table1.overdue_days+1
                          END;

If record present in table1 only, it is not affected by Q1 and overdue_days is set to 0 by Q2.
If record present in both tables, it is not affected by Q1 and overdue_days is incremented by Q2.
If record present in table2 only, it is inserted by Q1 with overdue_days = 0, and then is incremented by Q2.

For PostgreSQL.

  PostgreSQl is not my favorite DBMS, some mistakes possible

INSERT INTO table1 (name, id, overdue_days)
SELECT name, id, 1
FROM table2
ON CONFLICT DO UPDATE SET overdue_days = overdue_days+1;

UPDATE table1
SET overdue_days = 0
WHERE NOT EXISTS (SELECT 1
                  FROM table2
                  WHERE table1.id = table2.id);

If record present in table1 only, it is not affected by Q1 and overdue_days is set to 0 by Q2.
If record present in both tables, it's overdue_days is incremented by Q1 and is not affected by Q2.
If record present in table2 only, it is inserted into table1 by Q1 with overdue_days = 1, which is not affected by Q2.


Port SQL Server data to MySQL without Workbench When using MySQL Workbench to migrate data from SQL Server to MySQL I ran into a bug that it won't escape apostrophes. In the interim I would like to still port that data over. Is there any simple way to do this?

I've exported my data into a file with INSERT statements but they don't match the syntax of MySQL (I'm new to MySQL) so I don't know if it would work, neither does the file contain semicolons at the end of each statement.

Thanks in advance.

Update

So, far it looks like the easiest way to do it is to export the data into flat files and go through each table and make sure to also generate a SSIS file that you can use to automate future exports. Like so:

 DTExec /File MyExportedSissFileForOneTable.dtsx

And then use PowerShell or some other scripting language to do all the different files you need to update. Then import the files with MySQL - which I haven't figured out yet.

In retrospect it is probably nicer to just roll my own converter since MySQL uses snake case instead of title case like SQL Server.

So, what I'm going to do is use a PowerShell script like so:

$tables = "FirstTable", "SecondTable"
function script {
    param(
        [System.String] $table
    )
    "set nocount on; select * from dbo."+$table
}

$tables |
% {
   $path = "C:\Data\"+$_+".txt"
   $script = script $_
   sqlcmd -S MyServer -d MyDatabase -W -h -1 -E -Q $script -s `"`t`" -o $path
}

This will export all the data from my SQL Server database and put them in files and then I will import those files into MySQL. Which I haven't quite figured out yet. I let the MySQL Workbench do the initial CREATE TABLE statements and used Vim to make everything snake case.