Create 7 day rolling average in PostGIS for each feature I have a timeseries dataset that looks like this:

uid | geom     | date       | count |
-------------------------------------
1   | FeatureA | 2016-02-01 | 1     |
2   | FeatureA | 2016-02-02 | 2     |
3   | FeatureA | 2016-02-03 | 3     |
4   | FeatureA | 2016-02-04 | 4     |
5   | FeatureA | 2016-02-05 | 5     |
6   | FeatureA | 2016-02-06 | 9     |
7   | FeatureA | 2016-02-07 | 11    |
8   | FeatureA | 2016-02-08 | 15    |
9   | FeatureA | 2016-02-09 | 17    |
10  | FeatureA | 2016-02-10 | 20    |
11  | FeatureB | 2016-02-01 | 2     |
12  | FeatureB | 2016-02-02 | 2     |
13  | FeatureB | 2016-02-03 | 8     |
14  | FeatureB | 2016-02-04 | 4     |
15  | FeatureB | 2016-02-05 | 5     |
16  | FeatureB | 2016-02-06 | 15    |
17  | FeatureB | 2016-02-07 | 11    |
18  | FeatureB | 2016-02-08 | 15    |
19  | FeatureB | 2016-02-09 | 19    |
20  | FeatureB | 2016-02-10 | 25    |

I would like to calculate the 7 day rolling average for each feature in the dataset (~2000 features). To calculate a 7 day rolling average in Postgres windows could be used as described here.

The following code almost works:

SELECT geom,date,count,
       AVG(count)
            OVER(PARTITION BY geom ORDER BY geom, date ROWS BETWEEN CURRENT ROW AND 7 Following) AS rolling_avg_count
FROM features;

This gives the following output:

 geom    | date       | count | rolling_avg_count
--------------------------------------------------
FeatureA | 2016-02-01 | 1     | 6.25
FeatureA | 2016-02-02 | 2     | 8.25
FeatureA | 2016-02-03 | 3     | 10.5
FeatureA | 2016-02-04 | 4     | 11.57
FeatureA | 2016-02-05 | 5     | 12.83
FeatureA | 2016-02-06 | 9     | 14.4
FeatureA | 2016-02-07 | 11    | 15.75
FeatureA | 2016-02-08 | 15    | 17.33
FeatureA | 2016-02-09 | 17    | 18.5
FeatureA | 2016-02-10 | 20    | 20
FeatureB | 2016-02-01 | 2     | 7.75
FeatureB | 2016-02-02 | 2     | 9.875
FeatureB | 2016-02-03 | 8     | 12.75
FeatureB | 2016-02-04 | 4     | 13.43
FeatureB | 2016-02-05 | 5     | 15
FeatureB | 2016-02-06 | 15    | 17
FeatureB | 2016-02-07 | 11    | 17.5
FeatureB | 2016-02-08 | 15    | 19.67
FeatureB | 2016-02-10 | 25    | 25

However, the output continues calculating averages up to the end of the partition. For example, uid 10 would have a rolling average of 20 (calculated using only one record). I'd like the calculation to stop when there a fewer than 7 following rows.

Ideally the output would look something like this:

 geom    | date       | count | rolling_avg_count
--------------------------------------------------
FeatureA | 2016-02-01 | 1     | 6.25
FeatureA | 2016-02-02 | 2     | 8.25
FeatureA | 2016-02-03 | 3     | 10.5
FeatureA | 2016-02-04 | 4     | 11.57
FeatureA | 2016-02-05 | 5     |
FeatureA | 2016-02-06 | 9     |
FeatureA | 2016-02-07 | 11    |
FeatureA | 2016-02-08 | 15    |
FeatureA | 2016-02-09 | 17    |
FeatureA | 2016-02-10 | 20    |
FeatureB | 2016-02-01 | 2     | 7.75
FeatureB | 2016-02-02 | 2     | 9.875
FeatureB | 2016-02-03 | 8     | 12.75
FeatureB | 2016-02-04 | 4     | 13.43
FeatureB | 2016-02-05 | 5     |
FeatureB | 2016-02-06 | 15    |
FeatureB | 2016-02-07 | 11    |
FeatureB | 2016-02-08 | 15    |
FeatureB | 2016-02-10 | 25    |

This produces the desired result - after adjusting off-by-1 errors ②:

SELECT geom, date, count
     , CASE WHEN rn < 7 THEN NULL  -- ②
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , AVG(count) OVER (PARTITION BY geom
                           ORDER BY date -- ①
                           ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING -- ②
                          ) AS rolling_avg_count
        , row_number() OVER (PARTITION BY geom ORDER BY date DESC) AS rn
   FROM   features
   ) sub;

db<>fiddle here

① With PARTITION BY geom, you don't need geom in ORDER BY.

② You had off-by-one error(s):

  I would like to calculate the 7 day rolling average

But you show a calculation for the 8 day rolling average (1 + 7).

  I'd like the calculation to stop when there a fewer than 7 following rows.

But you show a result for only 6 following rows (displayed values 11.57 and 13.43).

I made it an actual 7 day average, stopping when there are fewer than 6 following rows.

Performance optimization

The above adds an additional sort to the query plan, due to the descending sort order in the second window function.

This alternative query avoids that with an additional count():

SELECT geom, date, count
     , CASE WHEN rn < 6 THEN NULL -- ③
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , avg(count)   OVER (PARTITION BY geom
                             ORDER BY date
                             ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING
                            ) AS rolling_avg_count
        , count(*)     OVER (PARTITION BY geom)
        - row_number() OVER (PARTITION BY geom ORDER BY date) AS rn
   FROM   features
   ) sub;

db<>fiddle here

A bit more verbose, adding an additional window function. But I expect it to perform better.

③ Adjusted to 0-based row number. (Cheaper than adding + 1.)


Log Shipping vs Data Replication in SQL Server 2012 Just wonder if anyone has tried to use Log Shipping on SQL Server 2012 collecting data from SQL Server 2008R2 and SQL Server 2005 databases? I heard it would be an easier option to implement compared to database replication.

Trying to run a project. Not sure to use Database Replication or Log Shipping. This is just a central database for reporting purposes only.

If you are using SQL Server 2012 as reporting, then you can use logshipping and configure your secondary as standby - that will allow users to read data from secondary when logs are not being restored. You can delay the restore of log files on secondary as per your needs.

Note that from lower to higher version, you are doing a one way. You wont be able to restore the sql server 2012 databases to sql server 2005 or 2008R2 easily (if you failed over).

Database transactional replication wont be supported as your subscriber (reporting server) would be on a higher version than publisher (sql server 2005 and 2008R2). See my answer here.

Also refer to SQL Server Many-to-One replication


Is TEXT to VARCHAR(MAX) an implicit conversion? I am copying TEXT columns to another table where the destination column is VARCHAR(MAX).

It appears that this is an implicit conversion and that I do not need to write an explicit CAST for it.

Is this correct?

As detailed in the excellent Microsoft Documentation, conversions from text to varchar can indeed be implicit.


Adding a domain user in SQL Server Management Studio Before I ask my question, let me mention that my SQL Server is installed on a member server of a domain (it belongs to the domain but is not an AD server).

I am having trouble adding a network user in SQL Server Management Studio.

First I tried adding user by right-clicking security and clicking new -> login. I wanted to add a network user, so i clicked locations.  But only the name of the local server was showing. I was unable to browse my domain users (at that time I was logged in through a network user on my SQL Server machine).

So I tried another approach.  I tried to add a network user through Transact-SQL as follows:

create login [domain\user] from windows;

But I got an error message saying - Windows NT user or group not found - even though the user existed on my domain server.

What am I doing wrong?

For me it worked to generate a script with SSMS.

USE [master]
GO
CREATE LOGIN [domain\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [role] ADD MEMBER [domian\user]
GO


Row vs Columnar DB for queries with multiple OR statements I understand columnar databases are great for speed queries where you don't use many fields to lookup against, but what if you only used OR statements?
For instance, I want all the records that have (Val A, Col A) OR (Val B, Col B) OR (Val C, Col C) OR...(Val N, Col N)
I hope I'm asking that clearly.
Edit:
The query OR statement would look A = 1 OR B = 3 OR C = 6 ... OR N = 7
And the reason I'm doing this is I would have a row/record where each col/field is encrypted and I'd like all the records that have a match with any of the fields for a clustering exercise. This query could run 100s of times a second.

The examples in this answer are written from a SQL Server point of view. To repeat back the problem, you want fast queries when the WHERE clause is a series of OR statements. Queries will filter against 4 to 20 different columns and you don't know the columns ahead of time. The first query might look like this:
SELECT COUNT(*)
FROM #Q273599
WHERE ID1 = 1 OR ID2 = 2 OR ID4 = 4 OR ID5 = 5;

and the second query might look like this:
SELECT COUNT(*)
FROM #Q273599
WHERE ID1 = 1 OR ID2 = 2 OR ID8 = 8 OR ID9 = 9 OR ID10 = 10;

This is still a difficult problem for relational databases depending on the size of the table and the required query response times. The fastest method will likely be to define a single column index on every column and to use an RDBMS with a query optimizer that is able to find an index union plan. Creating an index on every column may be impractical from a storage, capacity limit, or DML overhead point of view.
Very generally, it is fair to say that columnar storage will be better for this type of query than row store. Microsoft lists a similar problem (end users searching by hundreds of different filters on a real estate website) as a good case study for the effectiveness of columnstore. I think it's as simple as if you're going to scan the whole table anyway, you might as well scan a smaller table due to the typically better compression that columnar storage provides compared to rowstore. Not needing all of the columns from the table will of course make columnar more attractive compared to row store.
If you're really concerned about performance I recommend mocking up some sample data and trying things out. For the table and query below, I ended up with a 4 second response time for a row store query, a 1 second response time for columnstore, and a 13 ms response time when all columns were indexed. This is just an example to illustrate the general point. Your data is an important part of the question.
CREATE TABLE #Q273599 (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID3 BIGINT NOT NULL,
    ID4 BIGINT NOT NULL,
    ID5 BIGINT NOT NULL,
    ID6 BIGINT NOT NULL,
    ID7 BIGINT NOT NULL,
    ID8 BIGINT NOT NULL,
    ID9 BIGINT NOT NULL,
    ID10 BIGINT NOT NULL,
    PADDING CHAR(500) NOT NULL
);

INSERT INTO #Q273599 WITH (TABLOCK)
SELECT q.RN, RN, RN, RN, RN, RN, RN, RN, RN, RN, ''
FROM
(
    SELECT TOP (25000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) q;

SELECT COUNT_BIG(*)
FROM #Q273599
WHERE ID1 = 1 OR ID2 = 2 OR ID4 = 4 OR ID5 = 5 OR ID6 = 6 OR ID7 = 7 OR ID9 = 9 OR ID10 = 9999999999999
OPTION (MAXDOP 1);


Is there a MySQL engine or trick to avoid writing so many temp tables to disk? I've got a web application connected to a database running MySQL.

Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. This means that I'm getting lots of temporary tables being created on disk (this behavior is described here https://dba.stackexchange.com/a/17679/16724) and this results in pretty crappy performance.

Is there by any chance an easy solution to this problem? Perhaps some fork or other pluggable engine? I'm actually running the Percona fork of MySQL (XtraDB), but it's the same problem with vanilla InnoDB. I'm frankly considering switching to Postgres.

Thanks

You need to properly tune all connection-based settings as you pointed out from that link.
If you are looking for something quick and dirty, I have just the thing:
Back on Nov 30, 2012, I wrote a nice post about mapping temp tables to RAM, even disk-based temp tables : Is it bad to create many mysql temporary tables simultaneously?
Give it a Try !!!
UPDATE 2013-01-03 22:14 EDT
In terms of the BLOBs, my condolences. Notwithstanding, I have good news. I have a very quick-and-dirty stunt you can do once you set up the RAM disk.
Ready ???
Set the following values in
tmp_table_size = 1K
max_heap_table_size = 1K

That's not a misprint. That's not a typo. I said set tmp_table_size and max_heap_table_size to 1K. You are probably saying "Why set those values so low?"
Those values represent the largest in-memory temp table allowed before transferring the temp table to disk. If you have a RAM disk, then temp tables leap right into the RAM disk as quickly as possible. As the MySQL Documentation says on max_heap_table_size:

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.


Speed impact of using varchar in SQL Server 2008 In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any memory/performance gain from changing 1-3 varchar(5) columns to char(5) columns on a table with

a) say 2.000.000 rows
b) if any of the varchar columns are part of indexes / the primary keys

You are confusing text/varchar(max) (LOB types) with varchar(n)/char(n)

LOB types will still be stored in-row/out-of-row based on size, UPDATE, sp_tableoption etc
varchar(n)/char(n)  have always been "in-row"

Otherwise, if you use char(1000) not varchar(1000) and the average data length is, say, 42 this is bad design and a lot of wasted space.


What is the best solution to implement to replicate a database, with a purging process is in place I am planning to implement a solution solution to create a separate database for reporting purposes (read only).  I thought of implementing transactional replication to solve this problem, however I have a data purge process that runs every week.  This means that any data deleted will be replicated too.

I need help to design the best solution, how do I create a separate database instance for reporting.  I want the primary database to keep the data purging in place, but I want a separate db system that holds all the data.

Need ideas!  Please help!

If I understand you correctly the data is purged from the publisher database on a weekly basis - correct?
In this case I would set up replication to a subscriber and then transfer the data from the subscriber to a data warehouse, as Max Vernon suggested.

Hope this helps

Yomet


Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs).  I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those databases up.  When I go to restore them into the MySQL 5.7 server, do I need to run an upgrade on those databases to bring them "up to speed"?

The mysql_upgrade command seems to be related to all database within an instance, and I will be moving dbs one at a time into the server that has had dbs created from scratch there, as well as other dbs that have been moved to it from the previous version (5.1).

Some things, such as microsecond in TIMESTAMP, will automatically be available because the CREATE TABLE on 5.7 will use the new format.  innodb_file_per_table has a new default, so the tables will be laid out differently.  That should not be a problem.

mysql_upgrade is for converting the mysql tables and for ALTERing to accommodate microseconds, etc, in an existing mysql instance where you are updating the software, not where you are loading the data.

Coming forward from 4.0 can cause troubles with changed syntax, the addition of CHARACTER SET, etc.  But 5.1 is not that much different for what you are doing than 5.7.

There have been lots of changes/additions to mysql, information_schema, and performance_schema.  So, be sure not to dump and reload them.

Use pt-show-grants if you need to carry GRANTs forward.  Then check the results.

Do you have specific examples of things that concern you?


How can I display multiple column records in the same row? I want to display all related information in to the same row column. Currently my output and query are the following:

Trader
Items

Prapor
Weapons

Prapor
Wearables

Prapor
Weapon Modifications

Prapor
Health and Buffs

Therapist
Weapons

Therapist
Wearables

Therapist
Weapon Modifications

Therapist
Health and Buffs

select NPC_Traders.Nickname as Trader, Classes.Class as Items
from NPC_Traders, Classes
where Items IN (
           select npc.Tradeables
           from NPC_Traders npc
           where npc.Tradeables LIKE '%1%' OR
                 npc.Tradeables LIKE '%2%' OR
                 npc.Tradeables LIKE '%3%' OR
                                npc.Tradeables LIKE '%4%');

I want to display the information such as:

Trader
Items

Prapor
Weapons, Wearables, Weapon Modifications, Health and Buffs

Therapist
Weapons, Wearables, Weapon Modifications, Health and Buffs

Is there a technique I can use to solve this issue?

You can apply the GROUP_CONCAT function on top of your existing query like so:
SELECT Trader, GROUP_CONCAT(Items, ',') AS Items
FROM
(
    select NPC_Traders.Nickname as Trader, Classes.Class as Items
    from NPC_Traders, Classes
    where Items IN (
               select npc.Tradeables
               from NPC_Traders npc
               where npc.Tradeables LIKE '%1%' OR
                     npc.Tradeables LIKE '%2%' OR
                     npc.Tradeables LIKE '%3%' OR
                                    npc.Tradeables LIKE '%4%')
) AS Subquery


pgbench-tools failed authentication I'm trying to configure pgbench-tools and went to run it per the README and got this error

-bash-4.2$ ./newset 'Initial Config'
psql: FATAL:  Ident authentication failed for user "postgres"
psql: FATAL:  Ident authentication failed for user "postgres"

I looked at my pg_hba.conf file and local is set to trust, so not sure where the error is coming, I made sure the pgbench-tools/config file is set for user postgres. Trying to figure out where this problem is coming from and haven't seen anywhere on pgbench-tools repository that shines any light.

I strongly believe that only the "local" record is set to trust.
try something like that:

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

this should work for all kinds of local connections.


SQL Server full database backup without a job I am trying to work out where I can modify a schedule for a backup job that is running. The SQL Server was setup by a team before it was handed to us.
We have a set of maintenance jobs in our SQL Server Agent which I can modify, but looking through the server logs there is another full backup that runs, backing everything up into a blob storage.
The issue is, I cannot find where this is being scheduled. There is no job that I can see in the Agent that is executing this backup and there is no maintenance plan in the management folder either.
Is there something I am missing? Or any way for me to find what is executing this backup?

A backup command can be run from just about anywhere and scheduled using any kind of scheduling tool. So, while SQL Agent is the most common method, it's not the only one. To determine what's happening when this backup runs, I'd suggest creating an Extended Events session to capture the queries. You'll be able to see the login that is being used. You may, depending on the connection settings, also be able to see the application making the call. At the very least, you can see the machine within your network that the call is coming from.
Because Extended Events can return a lot of data, I'd suggest turning it on only for the time that the calls are being made based on the log information you have. To search through the data, you can use the Live Data Explorer window within SSMS. I have a blog post on getting started with that tool that might help.


changing domain name for Mongodb primary Im trying to initialize my replica set sp that the primary node has a specific domain:port pairing. what I want is:

  "vagrant-ubuntu-trusty-64:27022"

but what I get is:

  "localhost:27022"

This is the command I use to start the mongo instance:

  sudo mongod --port 27022 --dbpath /db/config/data --configsvr --replSet config

This is what I use to access the mongo instance:

  mongo --port 27022

I tried to modify my host file to add the domain name to the default domain of 127.0.0.1, but that didn't work.

127.0.0.1 localhost
127.0.0.1 vagrant-ubuntu-trusty-64
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

How do I get the specific "vagrant-ubuntu-trusty-64:27022" pairing when I initialize the mongo instance?

To add replica set members with hostname, pass the host name when initializing the replica set.

rs.initiate(
    {
        _id: "config",
        version: 1,
        members: [
        {_id: 0, host: "vagrant-ubuntu-trusty-64:27022"}
        ]
    }
)

  _id --> is your replica set name

Or if you want to edit the existing configuration follow the below steps

var conf = rs.conf()

conf.members[0].host="vagrant-ubuntu-trusty-64:27022"

rs.reconfig(conf)

  If you have more than one member in your replica set, all members
  should refer a hostname. Which means all members should be localhost or all members should be hostnames


Import asp.net / SQL Server 2012 website database to local SQL Server 2014 express I have website developed in asp.net and used SQL Server 2012 for database, my task to make website responsive, I have decided make changes locally first, I have whole source through ftp, and downloaded database backup from control panel.

My problem is that when I try to restore downloaded db backup file, it keeps showing me error message "No backupset selected to be restored", please check attached screen. I am new to ASP.NET / SQL Server 2012, can any one help me to solve this issue ?

This could be caused by any number of issues.

To help get to the bottom of this I would, In SSMS open a new query window and run the following command

RESTORE DATABASE [DatabaseNameHere]
FROM DISK='Path to the backup file here'

e.g. if my database was called AdventureWorks and the backup was located in c:\Temp\Adventureworks.bak the TSQL would be.

RESTORE DATABASE [AdventureWorks]
FROM DISK='c:\Temp\Adventureworks.bak'

The error produced will give you a much better indication as to what the issue is.


Are there standard SQL functions with side effects? Do the SQL standards define functions with side-effects?

For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like

SELECT myfunction(params...);

I've seen these sometimes, but I am just curious if the SQL standards do the same.

* This is not a question about PostgreSQL specifically. I'm only using the side-effects examples I see in PostgreSQL.

You have a few different questions in here.

Q: What are ANSI standard SQL functions?

ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that's one heck of a dry, boring read.

Q: Do ANSI standard SQL functions change data in the database?

No. You can use them to change data - for example, I can say:

INSERT INTO dbo.MyReport SELECT MAX(SalespersonRevenue) FROM dbo.Sales

But by themselves, just the use of a AVG, COUNT, MIN, MAX, etc shouldn't change data permanently inside your database.

Q: Does the ANSI standard allow me to write my own functions?

Yes, but the exact implementation varies from vendor to vendor. The functions you write may conform to the ANSI language standard, but what you do inside your function can be horrifically awful, like creating side effects.

When discussing intended behavior, it's possible to get a cross-platform answer.
When discussing side effects, it is not.

Q: Can I create my own function to write data?

Why sure, if you're creative. I'm a Microsoft SQL Server guy, so I'm going to focus on that platform. Books Online's function page says:

  User-defined functions cannot be used to perform actions that modify the database state.

To which I say:

  You're not my real dad.

So here's how I'd break the rules. Warning: very bad ideas follow.

In your function, query a new table specially created for this evil purpose, and then create something that watches the table for select statements, and then fires an action (Extended Events, auditing, or a Profiler trace). You can hook together a Rube Goldberg sort of contraption to perform work based on those select statements.
In the function, call CLR code - heck, you can even call a web service. That web service could very well push data back into your own database.
In the function, call xp_cmdshell and do something through the command prompt. (HT @AaronBertrand in the comments.)

All of these examples have huge drawbacks in the form of performance and transactional consistency. You just asked if it could theoretically be done, and the answer there is yes. I wouldn't ever use either of those in my own code - I'd step back and ask, "What's the business goal I'm trying to achieve here, and is there a way I can do it to achieve performance and transactional consistency?" If you'd like specific advice on those, I'd ask a separate Stack question with specifics.


Finding the most commonly used non-stop words in a column Simply put, I have a Postgres column containing ordinary prose and would like to determine the x most commonly used words ("word" being a group of characters delimited by a space, but not being stop word) across all rows.

I've found two solutions that nearly hit the mark:

SELECT *
FROM   ts_stat($$SELECT to_tsvector('english', title) FROM item$$)
ORDER  BY ndoc DESC
LIMIT  50;

This is great, except it returns word stems.

SELECT   UNNEST(string_to_array(title, ' ')) AS word, COUNT(*) AS ct
FROM     item
GROUP    BY 1
ORDER    BY 2 DESC
LIMIT    50;

This one returns the full words, but includes stop words.

For the sake of simplicity: stop words are supposed to be found on TABLE stop_words (lowercase_stopword text PRIMARY KEY).

Can anyone help me over the line?

Your first query was pretty close. To remove the unwanted stemming, create a text search configuration with a simple dictionary that does not do it.
I suggest to use a separate schema for text search objects, but that's totally optional:
CREATE SCHEMA ts;
GRANT USAGE ON SCHEMA ts TO public;
COMMENT ON SCHEMA ts IS 'text search objects';

CREATE TEXT SEARCH DICTIONARY ts.english_simple_dict (
    TEMPLATE = pg_catalog.simple
  , STOPWORDS = english
);

CREATE TEXT SEARCH CONFIGURATION ts.english_simple (COPY = simple);
ALTER  TEXT SEARCH CONFIGURATION ts.english_simple
   ALTER MAPPING FOR asciiword WITH ts.english_simple_dict;  -- 1, 'Word, all ASCII'

Then your query works, and very fast, too:
SELECT *
FROM   ts_stat($$SELECT to_tsvector('ts.english_simple', title) FROM item$$)
ORDER  BY ndoc DESC
LIMIT  50;

dbfiddle here
This operates with lower case words without stemming and doesn't break for non-ASCII letters.
Backgroud
Read the chapter Simple Dictionary in the manual.
The exact definition of a "word" is a tricky matter. The default text search parser (currently it's the only one) identifies 23 different types of tokens. See:
SELECT * FROM ts_token_type('default');

Built-in text search configurations map most of those to (built-in) dictionaries. Mappings for the english config:
SELECT tt.*, m.mapdict::regdictionary AS dictionary
FROM   pg_ts_config_map m
LEFT   JOIN   ts_token_type(3722) tt ON tt.tokid = m.maptokentype
WHERE  mapcfg = 'english'::regconfig  --  'ts.english_simple'::regconfig
ORDER  BY tt.tokid;

The demo above creates a new config based on the simple config, and since all English stop words are of type 'asciiword', we only need to map this type to remove stop words, no stemming or anything else.


How to create a database user for oracle 11g on windows 8? I am new to oracle database and therefore is trying to follow the office guide. (link to the guide) However, it seems that the guide is for Windows 7, so in step 2 - creating database user, it says:

  Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g
  Express Edition, and then Run SQL Command Line.

And in windows 8, there is not start button. I try to search the menu using keyword oracle but nothing with similar name is found. I tried to go to the Program Files directly but also no similar file found. So, how should I create a database user on Windows 8?

In Windows 8 use shortcut to start command line option (Windows Key+R). It will open "Run" terminal. Then type keyword "cmd". It will start your command line then type "sqlplus" it will start sqlplus for interacting with your Oracle database. Use your SYS user with login credential for entering in sqlplus. After login into sqlplus, create your user using commands.


Is .NET Framework 3.5 still needed with a SQL Server 2017 installation to utilize Database Mail? I'm guessing .NET Framework 3.5 is still needed to use Database Mail with SQL Server 2017? As I had to include this on my SQL Server 2016 installs on Server 2016 not that long ago.

I was unable to find a definitive answer via the web, surprisingly, maybe it goes unsaid.

No, it is not required for SQL Server 2017.  The issue with Database Mail not working was fixed in 2016 SP1 CU2, and 2016 RTM CU2:
FIX: SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed or stops working after applying SQL Server update

I know of several SQL Server 2016 and 2017 systems that are installed without .NET 3.51, and Database Mail is working fine.

Note that .NET Framework 3.5 was not included in Hardware and Software Requirements for Installing SQL Server, and that the SQL Server 2016 and SQL Server 2017 view of this page is the same.  The Database Mail issue was considered a defect in SQL Server 2016 and was fixed accordingly.


Is SERVICE_NAME forced to be unique per logical database within a network? Would it have sense the opposite? I like to know your opinion on these two sentences to see if I understood correctly the concept.

Put RAC configuration aside, I am talking about logical databases, I understand RAC is like a group of hosts serving same logical database by means of analogous instances.

I am talking about the uniqueness of SERVICE_NAME in a network. I also understand that 2 SERVICE_NAME cant point one database, but I would like to know whether two different DATABASES with their unique DB_NAME and SID may and are likely to be useful in some way if they expose same SERVICE_NAME.

The same SERVICE_NAME can run in multiple instances with a RAC, and in a physical standby database, those services also exist.  In fact, you'd need them to be running in order for failover to work.

Service name from a uniqueness perspective only matters in terms of being unique within a particular listener's purview; it's how the listener routes connections.  What would happen if multiple unique databases served by the listener each registered the same service?  How would it choose between/among them?


Execution Plans Query I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard code query. Is there any way to do this without using the GUI?

Use:

set showplan_xml on;
go
Your query;
go
set showplan_xml off;
go

This will give the XML, and you should be able to see the Estimates in there.


mysql : remove row if parent id match in primary key columan I don't know that what should be the title of the question.

I have two table which schema define as below:

Table 1 (organization_master):

CREATE TABLE `organization_master` (  `organization_id` int(11) NOT NULL,  `parent_organization_id` int(11) DEFAULT NULL,  `organization_name` varchar(150) NOT NULL,  `c_user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `organization_master` (`organization_id`, `parent_organization_id`, `organization_name`, `c_user_id`) VALUES
(119, NULL, 'Patel-Apps', 220),
(183, 119, 'Patel-Apps sub 1', 220),
(184, 119, 'Patel-Apps sub 2', 220),
(250, 247, 'Patel-Apps2', 222);
ALTER TABLE `organization_master`  ADD PRIMARY KEY (`organization_id`),  ADD KEY `organization_name` (`organization_name`),  ADD KEY `c_user_id` (`c_user_id`);
ALTER TABLE `organization_master`  MODIFY `organization_id` int(11) NOT NULL AUTO_INCREMENT;

Table 2 (organization_assigned_user) :

CREATE TABLE `organization_assigned_user` (  `organization_assigned_user_id` int(11) NOT NULL,  `organization_id` int(11) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `organization_assigned_user` (`organization_assigned_user_id`, `organization_id`, `user_id`) VALUES (29, 250, 219),(30, 250, 220);
ALTER TABLE `organization_assigned_user`  ADD PRIMARY KEY (`organization_assigned_user_id`),  ADD KEY `user_id` (`user_id`),  ADD KEY `organization_id` (`organization_id`);
ALTER TABLE `organization_assigned_user`  MODIFY `organization_assigned_user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

When I run below query:

SELECT    om.organization_id,
          om.parent_organization_id,
          om.organization_name
FROM      `organization_master` AS om
LEFT JOIN organization_assigned_user AS oau
ON        om.organization_id = oau.organization_id
WHERE     om.c_user_id =220
OR        oau.user_id =220
GROUP BY  om.organization_id;

It give me output as below:

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
183             119                     Patel-Apps sub 1
184             119                     Patel-Apps sub 2
250             247                     Patel-Apps2

Now, my question is that I don't want the rows which parent_organization_id exists in organization_id column. i.e 199 parent_organization_id  exists in organization_id. so my out put should be like like below:

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
250             247                     Patel-Apps2

Try this:

SELECT a.organization_id,
    a.parent_organization_id,
    a.organization_name
FROM (
    SELECT om.organization_id ,
            om.parent_organization_id,
            om.organization_name
    FROM `organization_master` AS om
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id
    WHERE om.c_user_id =220 OR oau.user_id =220
    GROUP BY om.organization_id
) a
WHERE a.parent_organization_id NOT IN
(
    SELECT om.organization_id
    FROM `organization_master` AS om
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id
    WHERE om.c_user_id =220 OR oau.user_id =220
    GROUP BY om.organization_id
)
OR a.parent_organization_id IS NULL

If this works for you, consider storing results of your main SELECT (used twice in my reply) in a TEMP table and joining on that for performance purposes.


Count with where clause Is there a way to have WHERE clause in COUNT ? Or how to count with some clause ?

select
  a
  b,
  count(c where t1.u='UAE') as c1
from t1

I am using MS SQL Server 2014

No, the syntax you have is not valid, it can be corrected by the use of a CASE expression.
(and I guess you have a GROUP BY a, b as you'd get an error otherwise).

select
  a
  b,
  count(case when t1.u = 'UAE' then c else null end) as c1
from t1
group by a, b ;

Note that the ELSE NULL is redundant and can be removed as that is the default ELSE behaviour anyway:

select
  a
  b,
  count(case when t1.u = 'UAE' then c end) as c1
from t1
group by a, b ;

There is a (SQL Standard) FILTER syntax that is close to your attempt but SQL Server has not yet implemented it:

select
  a
  b,
  count(c) filter (where t1.u = 'UAE') as c1
from t1
group by a, b ;


How do I configure multi master replication on WAMP? I want to configure mysql multi master replication, but I've not seen any comprehensive article or tutorial on how to do that on WAMP. I will be grateful if some will point  me to a tutorial, or better still show me how to achieve that.

Whether you have Server configured with WAMP or as a Dedicated DB, the concepts for setting up multimaster replication is identical.

Before I go on, let me clarify the phrase multimaster replication.

True multimaster replication would have a Slave with Multiple Masters. It is not possible at present. MySQL 5.6 is approaching this. I think MariaDB as well. What most people call multimaster replication is, in reality, Master/Master (circular) replication.

Master/Master Replication is nothing more that an interleaving of the Master/Slave paradigm between two DB servers. This is what I will answer.

Back on Feb 06, 2012, I wrote a script to create a Slave from a Given Master ( How to setup replication(Master/slave) in MySQL 5.5.20? ). The script is Linux-centric. Since you said WAMP, you should apply the concepts for the Windows environment. I will do my best to translate the concepts:

Scenario

WDB is the WAMP Server (IP 10.1.2.20)
DDB is the Dedicated Windows DB Server (IP 10.1.2.30)

STEP #1

Edit my.ini on WDB and make sure it has

[mysqld]
server-id=1
log-slave-updates
log-bin=mysql-bin
relay-log=relay-bin

If it does not have these, add these to my.ini and then run this

C:\> net stop mysql
C:\> net start mysql

STEP #2

Edit my.ini on DDB and make sure it has

[mysqld]
server-id=2
log-slave-updates
log-bin=mysql-bin
relay-log=relay-bin

If it does not have these, add these to my.ini and then run this

C:\> net stop mysql
C:\> net start mysql

STEP #3

Please open C:\MasterSlaveSetup.bat it in notepad.

set MYSQL_MASTER_HOST=10.1.2.20
set MYSQL_SLAVE_HOST=10.1.2.30
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set MYSQL_MASTER_CONN=-h%MYSQL_MASTER_HOST} %MYSQL_CONN%
set MYSQL_SLAVE_CONN=-h%MYSQL_SLAVE_HOST} $MYSQL_CONN%

set MYSQLDUMP_OPTIONS=--master-data=1
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --single-transaction
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --routines
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --triggers
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --flush-privileges
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --all-databases

mysql ${MYSQL_MASTER_CONN} -AN -e"GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"

set RELOAD_FILE=C:\MySQLData.sql
echo STOP SLAVE; > %RELOAD_FILE%
echo CHANGE MASTER TO master_host='%MYSQL_MASTER_HOST%'," >> %RELOAD_FILE%
echo master_port=3306,             >> %RELOAD_FILE%
echo master_user='repluser',       >> %RELOAD_FILE%
echo master_password='replpass',   >> %RELOAD_FILE%
echo master_log_file='dummy-file', >> %RELOAD_FILE%
echo master_log_pos=1;             >> %RELOAD_FILE%
mysqldump %MYSQL_MASTER_CONN% %MYSQLDUMP_OPTIONS% >> %RELOAD_FILE%
echo "START SLAVE;" >> %RELOAD_FILE%

mysql %MYSQL_SLAVE_CONN% -A < %RELOAD_FILE%

STEP #4

Run C:\MasterSlaveSetup.bat

STEP #5

C:\> copy C:\MasterSlaveSetup.bat C:\MasterSlaveSetup2.bat

STEP #6

Edit C:\MasterSlaveSetup2.bat to reverse the IP Addresses as follows:

set MYSQL_MASTER_HOST=10.1.2.30
set MYSQL_SLAVE_HOST=10.1.2.20

STEP #7

Run C:\MasterSlaveSetup.bat

When done, you should have Circular Replication fully set up. To test it, login to each MySQL instance and run SHOW SLAVE STATUS\G. You should see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.80.136
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 277892198
               Relay_Log_File: relay-bin.000058
                Relay_Log_Pos: 37535484
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes  <<------
            Slave_SQL_Running: Yes  <<------
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 277892198
              Relay_Log_Space: 277892637
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

If you see Slave_IO_Running=Yes and Slave_SQL_Running=Yes on both DB Servers, CONGRATULATIONS !!! You did it.

CAVEAT

The Dedicated DB Server does not have to a Windows Server. It could be a Linux server.

To help you understand MySQL Replication more, here are links to my past answers on this. Some of them have answers from others that were accepted. Please check these out and enjoy.

Feb 23, 2011 : MySQL Replication - Introduce new Slave to replication
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
Feb 15, 2012 : MySQL replication to slave having VPN connection to Master
Apr 10, 2012 : Slave settings not working
May 07, 2012 : Setting Circular Replication in mysql


Variable numbers of parameters in a SqlClr UDF or Stored Proc Lets say I have the following signature for a CLR Stored Proc or UDF:

[Public]
public static void RaisError(
    string message,
    short severity,
    short state,
    params object[] args)

The generated DDL is

CREATE PROCEDURE [dbo].[RaisError]
    @message [nvarchar](4000),
    @severity [smallint],
    @state [smallint],
    @args /* Error: Unsupported type. */
AS EXTERNAL NAME [SqlSaturdayClr].[StoredProcedures].[RaisError];

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does?

From this codeproject article it seems that extended stored procs intrinsically have a varariable number of variables and you have to enforce variable length in the code. Is my only option to use an extended stored procedure?

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does?

No, there's no built-in way to do this.

  Is my only option to use an extended stored procedure?

You should avoid writing an extended stored procedure, since these have been deprecated since SQL Server 2005 and generally cause more problems than they solve. There is no really good way to simulate the optional parameter behaviour of things like sp_executesql with a SQLCLR procedure or function.

Various workarounds can be suitable depending on the situation. The most often recommended is a T-SQL procedure or function wrapper, though I have also seen people experiment with passing a SQLCLR type (containing an array of values, for example) as well. The T-SQL equivalent to that would be a table-valued parameter.


How to configure DBeaver to save files without a BOM for UTF files? I am looking for the equivalent setting as the PGADMIN setting Write BOM for UTF files which will cause saved files to be saved with/without the BOM.

I've found settings in DBeaver when exporting data, but nothing that would configure new (or existing) files to be saved without the BOM.

How do I configure DBeaver to save files without a BOM for UTF files/

Using sed

You can always cut out three bytes (the bom) with sed

sed -i '1 s/^\xef\xbb\xbf//' *.txt

That'll actually do it in place.


Update Local Table With Data From Linked Server Using Open Query This is the syntax I am trying to use, but I get an error of

  Incorrect syntax near #

I have also tried it with an actual table and not a temp table and still not succesful.  Here is my syntax, what should I do in order to be able to update a table on my SQL Server 2008 installation with data from a linked connection to postgresql?

Create Table #Test (field1 varchar(100), field2 varchar(100))

DECLARE @salesname nvarchar(500) = N'''Sam Wise''';
DECLARE @d1 nvarchar(100) = N'05/01/2016';
DECLARE @d2 nvarchar(100) = N'05/31/2016';
DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(LinkedServer,
''UPDATE #Test
SET field1 = a.salesname SalesName
,field2 = b.salesTotal TotalSales
from sales a
inner join empinfo b
on a.empid = b.empid
WHERE a.salesname IN ('+ REPLACE(@salesname, '''', '''''') + N')
AND iv.jbinvoicedate BETWEEN ('+ CHAR(39) + CHAR(39)
+ CAST(@d1 AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@d2 AS NVARCHAR(500)) + CHAR(39)
+ CHAR(39) + N')''
);
';

EXEC sp_executesql @sql;

So OPENQUERY() is basically saying "Open this query on the linked server" - It's the preferred method for doing a select when able for me. Instead of saying something like

SELECT col1, col2
FROM LinkedServerName.DatabaseName.Schema.Table
WHERE SomeValue = Something Local Here

it does the filtering and joining at the linked server. This avoids you having to bring data back to just filter it out, etc with the four part query aproach.

The problem, though, is that update is trying to run on the destination. It's basically a pass-through query, so you are trying to pass that update through.

So you'll need to do the update locally, but can join to the open query to join to the data on the other side. For more you can see the syntax guide here with some notes.


making a TSQL statement that has variables to be dynamic I am having a real hard time trying to convert a TSQL statement (which be a store procedure) into a Dynamic SQL. Basically I must be able to pass the schema name as a parameter.

Here is my code:

DECLARE @query AS varchar(max)
DECLARE @schemaName AS varchar(5) = 'wl03' -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE) --The 11th month from the current month
DECLARE @plus12Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE) --The 12th month from the current month

DROP TABLE IF EXISTS #12thMonthExpectedActivity

-- Add rows for existing month into a temporary table
-- The code below returns an error: The data types varchar and date are incompatible in the add operator.
SET @query = 'SELECT ' + @plus12Month + 'AS period, genusId, subjectId, waitingStageId, value, ' + GETDATE() + ' AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName + '].[ExpectedActivity]
WHERE period in(' + @plus11Month + ')'
-- EXEC(@query) will go here once the above is working

/* This original code works just fine!
SELECT @plus12Month AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated>' AS savedBy INTO #12thMonthExpectedActivity FROM [wl03].[ExpectedActivity]
  WHERE period in(@plus11Month)
*/

The problem I have is defining the @query variable. I keep getting the same error:

  Msg 402, Level 16, State 1, Line 9 The data types varchar and date are
  incompatible in the add operator.

The code does work fine as long as I don't try to save it in a query. Any ideas? Thanks!

Your dates must be strings too, but in a format that is unambiguous (e.g. date format 112):

DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE), 112) --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE), 112) --The 12th month from the current month

You will need to convert your GETDATE() as well, where you have it in the string. Watch out for quotation marks, and casting back to DATE as well. Here's a proposed solution:

DECLARE @query AS VARCHAR(MAX);
DECLARE @schemaName AS VARCHAR(5) = 'wl03'; -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 11, 0) AS DATE), 112); --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 12, 0) AS DATE), 112); --The 12th month from the current month
DROP TABLE IF EXISTS #12thMonthExpectedActivity;

-- Add rows for existing month into a temporary table
SET @query
    = 'SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('''
      + CONVERT(CHAR(8), GETDATE(), 112)
      + ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName
      + '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')';

PRINT @query;

Read more about CAST and CONVERT with Books Online.


Weird SQL Server instance crash on casting to numeric While working with C# Entity Framework I noticed a crash of my SQL Server instance.

I was able to track it down to this statement:

SELECT * FROM dbo.[TestTable]
where mpnr in (1099059904,
1038139906,
1048119902,
1045119902,
1002109903,
1117109910,
1111149902,
1063149902,
1117159902,
1116109904,
1105079905,
1012079906,
1129129904,
1103059905,
1065059905,
1091059906,
1110149904,
1129149903,
1083029905,
1080139904,
1076109903,
1010019902,
1058019902,
1060019903,
1053019902,
1030089902,
1018149902,
1077149902,
1010109901,
1011109901,
1000119902,
1023049903,
1107119909,
1108119909,
1106119909)

The table looks like this:

CREATE TABLE dbo.[TestTable]([MPNR] [numeric](9, 0) NOT NULL)

The crash occurs every time I start the query. If I reduce the number of values within the IN clause, it works. (It returns no rows, of course.)

I'm aware that the values in the IN clause are 10-digit numbers and the column has only 9-digits, but that should not lead to a crash of the whole SQL Server instance.

My SQL Server's version is 2008 R2 on a Windows Server 2003 32-bit.

Is this a known Bug?
Is there a Patch for SQL Server?

I was able to repro on 2008 R1 SP3 10.00.5512 but installing the latest CU (14) fixed it.

Reviewing the bugs fixed in the intervening versions it looks as though you need to upgrade to a build that includes the following fix.

Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008 or in SQL Server 2012

As you are on 2008 R2 you will need at least CU 9 for SP1 or CU 5 for SP2.

The description of symptoms is somewhat brief but mentions mismatched datatypes

  When you run a query that contains many constant values in an IN
  clause in Microsoft SQL Server 2008, Microsoft SQL Server 2012 or in
  Microsoft SQL Server 2008 R2, an access violation might occur.

  Note For the issue to occur, the constants in the IN clause cannot
  match exactly with the column data type.

It doesn't define "many". From the testing I did I suspect this may mean "20 or more" as this seems to be the cut off point between two different methods of estimating cardinality.

The crash was happening inside a couple of methods called by CScaOp_In::FCalcSelectivity() with names such as LoadHistogramFromXVariantArray() and CInMemHistogram::FJoin() -> WalkHistograms().

For 19 or fewer distinct in list items these methods weren't getting called at all. A similar SQL Sever 2000 bug also mentions this cut off point as significant.

Populating a test table with 100,000 rows of random test data with values between 0 and 1047 and a histogram starting as follows

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
|            0 |          0 |     104 |                   0 | 1              |
|            8 |        672 |     118 |                   7 | 96             |
|           13 |        350 |     118 |                   4 | 87.5           |
|           18 |        395 |     107 |                   4 | 98.75          |
|           23 |        384 |      86 |                   4 | 96             |
|           28 |        371 |      85 |                   4 | 92.75          |
+--------------+------------+---------+---------------------+----------------+

The query

SELECT * FROM dbo.[TestTable]
where mpnr in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)
option (maxdop 1)

Shows estimated rows of 1856.

This is exactly what would be expected by getting the estimated rows for the 19 equality predicates individually and adding them together.

+-------+----------------+-------+
| 1-7   | AVG_RANGE_ROWS | 96    |
| 8     | EQ_ROWS        | 118   |
| 9-12  | AVG_RANGE_ROWS | 87.5  |
| 13    | EQ_ROWS        | 118   |
| 14-17 | AVG_RANGE_ROWS | 98.75 |
| 18    | EQ_ROWS        | 107   |
| 19    | AVG_RANGE_ROWS | 96    |
+-------+----------------+-------+

7*96 + 118 + 4*87.5 + 118 + 4*98.75 + 107 + 1*96 = 1856

The formula no longer works after 20 is added to the in list (Estimated rows 1902.75 rather than the 1952 that adding another 96 to the total would generate).

BETWEEN seems to use yet another method of calculating cardinality estimates.

where mpnr BETWEEN 1 AND 20 estimates only 1829.6 rows. I've no idea how that is derived from the histogram shown.


Occasional error when running sp_BlitzFirst I have set up sp_BlitzFirst to run as a job (for the PowerBI Dashboard). Occasionally, when it runs, it gets the following error:

Msg 8152, Sev 16, State 13, Line 2980 : String or binary data would be truncated. [SQLSTATE 22001]

This is actually happening in the sp_BlitzCache procedure at this line:

INSERT #stored_proc_info ( SPID, SqlHandle, QueryHash, variable_name, variable_datatype, compile_time_value, proc_name )
SELECT vi.SPID, vi.SqlHandle, vi.QueryHash, vi.variable_name, vi.variable_datatype, vi.compile_time_value, vi.proc_name
FROM #variable_info AS vi
OPTION ( RECOMPILE );

How can I resolve this? Thanks.

Darrell

You'll get that error when one of the columns in the select won't fit into the destination table.

Tracking it down can take some time, but it's just like kindergaten - try and fit the shapes in the holes.

If you compare #Variable_info to #stored_proc_info, you will probably find that one of the columns in #variable_info is larger than its corresponding column in #stored_proc_info.

I can't find #variable_info in my version of the sp_blitzcache, so I can't tell which columns don't match.

Often, I track it down by running a query like this:

SELECT len(vi.SPID) as spid, len(vi.SqlHandle) as handle, len(vi.QueryHash as hash), len(vi.variable_name) as variable_name,
len(vi.variable_datatype) as data_type, len(vi.compile_time_value) as compile_time, len(vi.proc_name) as proc_name
FROM #variable_info AS vi;

and looking for values that are too big.


Why does postgresql differentiate between named and numeric time zone notations? Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ?

Assume Europe/Prague timezone, which is currently +02:00 (DST); time of writing is 2018-08-25.

As you can check e.g. via google:

Also, in my postgres installation I can query it:

SELECT utc_offset FROM pg_timezone_names where name = 'Europe/Prague';

Which also yields 02:00:00.

Then, why on earth, do I get two different UTC timestamps when running following two queries?

The following yields 2018-08-25T20:00:00.000Z (2018-08-25 18:00:00-02):

SET TIME ZONE '+02:00'; -- with or without the plus
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

The following yields 2018-08-25T16:00:00.000Z (2018-08-25 18:00:00+02), which is correct:

SET TIME ZONE 'Europe/Prague';
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

Why does postgres differentiate those two notations?

Because, as documented, one uses POSIX and other uses ISO-8601.

  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.


What are the various Informix column data types? I am writing a query where I need to know if a column is of the "BLOB" type of column. According to this documentation  41 is the number for "BLOB" types of columns, in general. However, when I query the syscolumns table I see that columns, which store BLOB data, actually have values of 297, in my case. That page linked above does not even have number "297" anywhere on its page. I found another page, titled "Data Type Constants" but here again, number "297" is not even present anywhere on the page.
It appears that documentation's list of data types is not complete. Is there a (more) complete list of column types in Informix?

The values in the coltype column of the syscolumns system catalog table are defined primarily in the Informix ESQL/C header sqltypes.h.  This header is used throughout the Informix server code.
The values in the coltype column are a mixture of an 8-bit (unsigned) integer in the low-order bits, and various flag values in the high-order bits.  In particular, when a column is defined with the NOT NULL qualifier, the 0x100 bit is set — which corresponds to 'add 256'.  There are other flag bits which you are less likely to see.
The value 4118 (decimal) shown in another answer corresponds to hex 0x1016; the 16 (aka 22 decimal) corresponds to SQLROW, and the 0x1000 (4096 decimal) bit corresponds to #define SQLNAMED 0x1000 /* Named row type vs row type */.  The type is (as noted in the other answer) a 'named row type'.
Consider a table:
CREATE TABLE bool_check
(
    b1      BOOLEAN NOT NULL,
    b2      BOOLEAN
);

The values in the sqltype column of syscolumns are:

b1 = 297 = 256 + 41
b2 = 41

These correspond to SQLUDTFIXED (type 41).  The type SQLBOOL is marked 'used by FE [front end], … not real major types in BE [back end, meaning database server]'.  The collength is 1 for both.
Relevant segments of the header include:
SQL Types:
#define SQLCHAR         0
#define SQLSMINT        1
#define SQLINT          2
#define SQLFLOAT        3
#define SQLSMFLOAT      4
#define SQLDECIMAL      5
#define SQLSERIAL       6
#define SQLDATE         7
#define SQLMONEY        8
#define SQLNULL         9
#define SQLDTIME        10
#define SQLBYTES        11
#define SQLTEXT         12
#define SQLVCHAR        13
#define SQLINTERVAL     14
#define SQLNCHAR        15
#define SQLNVCHAR       16
#define SQLINT8         17
#define SQLSERIAL8      18
#define SQLSET          19
#define SQLMULTISET     20
#define SQLLIST         21
#define SQLROW          22
#define SQLCOLLECTION   23
#define SQLROWREF       24
/*
 * Note: SQLXXX values from 25 through 39 are reserved to avoid collision
 *       with reserved PTXXX values in that same range. See p_types_t.h
 *
 * REFSER8: create tab with ref: referenced serial 8 rsam counter
 *      this is essentially a SERIAL8, but is an additional rsam counter
 *      this type only lives in the system catalogs and when read from
 *      disk is converted to SQLSERIAL8 with CD_REFSER8 set in ddcol_t
 *      ddc_flags we must distinguish from SERIAL8 to allow both
 *      counters in one tab
 *
 * SQLSTREAM: Is a synonym for SQLUDTFIXED used by CDR (Enterprise
 *      Replication) code
 */
#define SQLUDTVAR       40
#define SQLUDTFIXED     41
#define SQLSTREAM       SQLUDTFIXED
#define SQLREFSER8      42

/* These types are used by FE, they are not real major types in BE */
#define SQLLVARCHAR     43
#define SQLSENDRECV     44
#define SQLBOOL         45
#define SQLIMPEXP       46
#define SQLIMPEXPBIN    47

/* This type is used by the UDR code to track default parameters,
   it is not a real major type in BE */
#define SQLUDRDEFAULT   48
#define SQLUNKNOWN      51
#define SQLBIGINT       52
#define SQLBIGSERIAL    53
#define SQLMAXTYPES     54

#define SQLLABEL        SQLINT

Flags:
#define SQLNONULL       0x0100  /* disallow nulls       */
/* a bit to show that the value is from a host variable */
#define SQLHOST         0x0200  /* Value is from host var. */
#define SQLNETFLT       0x0400  /* float-to-decimal for networked backend */
#define SQLDISTINCT     0x0800  /* distinct bit         */
#define SQLNAMED        0x1000  /* Named row type vs row type */
#define SQLDLVARCHAR    0x2000  /* Distinct of lvarchar */
#define SQLDBOOLEAN     0x4000  /* Distinct of boolean */
#define SQLCLIENTCOLL   0x8000  /* Collection is processed on client */

/* we are overloading SQLDBOOLEAN for use with row types */
#define SQLVARROWTYPE   0x4000  /* varlen row type */

There are also 'C-ISAM types' with numbers 100 to 125 and names such a CCHARTYPE and CDECIMALTYPE.  They are not of immediate concern here.  There are 524 lines in the header file (at least in the version I looked at). Of those, 74 are blank, 315 contain code, and the remaining lines are pure comment lines.  AFAIK, the SQLREFSER8 type is stillborn; it does not exist outside this file.
The types BLOB NOT NULL and CLOB NOT NULL are both encoded in coltype as 297 (41 + 256 — the same a BOOLEAN NOT NULL), or SQLUDTFIXED with collength of 72 (as opposed to 1 for BOOLEAN NOT NULL).  The fixed-length data is a descriptor that provides all the details about where the BLOB or CLOB value is actually stored.


How to Join a table to a group_contact in mySql I have this query

select concat('[',group_concat(inn.str),']') into string4  from (
        select concat(
            '{"idmm":',customer.id,
            ',"cd":"',customer.code,
            '","nm":"',customer.name,

            '"," Order":"',

            '","des":"', customer.description,
            '"}' )as a_string
        from
            customertable customer

    )

How do I join the Order table to have the order_name joined to the string?
In the middle of the above string you see '"," Order":"', right? I want to join the Order table to pull the order_name as the customertable has order_id and concatenate to the string.

The customertable has columns

id int(10)
order_id int(10)
code char(6)
name varchar(45)
description varchar(500)

and Ordertable has columns

order_id int(10)
order_name varchar(500)

FROM Customers c
JOIN Orders o USING(order_id)


Improving the queries in a view to query custom types I'll spare you the details, but I've got a situation where I'm going to be creating and managing a lot of custom types. (Postgres 11.4, ultimate deployment under RDS.) It looks like there isn't a system view to make reviewing types simple. Starting from some very helpful threads here, I bashed together a flattened view that lists each type's name, owner, schema, and attributes. I'm sure there's a way to get the same output with a simpler set of queries...but I don't know what they are. So I'm posting the code here in hopes that someone can improve it. And, if that doesn't happen, the current version may prove helpful to someone else eventually.

-- View: data.custom_types
DROP VIEW IF EXISTS data.custom_types;

CREATE OR REPLACE VIEW data.custom_types AS

with type_info as (
   select t.typrelid as type_oid,
          t.typowner::regrole::text as type_owner, -- ::text to make for easy searching
          n.nspname as type_schema,
          t.typname as type_name

     from pg_type t
left join pg_catalog.pg_namespace n on n.oid = t.typnamespace

    where (select c.relkind = 'c'
             from pg_catalog.pg_class c
            where c.oid = t.typrelid) and
                  not exists (select 1
                              from pg_catalog.pg_type el
                             where el.oid = t.typelem and
                                    el.typarray = t.oid) and
                  n.nspname not in ('pg_catalog', 'information_schema')
 )

-- Get the position, name, and type details of a custom type.
select type_info.type_owner,
       type_info.type_schema,
       type_info.type_name,
       pg_attribute.attnum as attribute_number,
       pg_attribute.attname as attribute_name,
       pg_type.typname as attribute_type

  from pg_attribute
  join pg_type   on (pg_attribute.atttypid = pg_type.oid)
  join pg_class  on (pg_attribute.attrelid = pg_class.oid)
  join type_info on (pg_attribute.attrelid = type_info.type_oid);

Note that this version of the query is deliberately not picking up custom types created by an extension. That's what I need here, but it other situations call for getting all of the types.

Then you can query or group by any of the columns. Just to show an example:

select * from custom_types where type_name = 'state_v1' order by attribute_number;

type_owner    type_schema    type_name    attribute_number  attribute_name    attribute_type
user_change_structure    api    state_v1    1               name              citext
user_change_structure    api    state_v1    2               abbr              citext
user_change_structure    api    state_v1    3               population        int8
user_change_structure    api    state_v1    4               total_sq_miles    float4
user_change_structure    api    state_v1    5               percent_land      float4
user_change_structure    api    state_v1    6               statehood_year    int2

It's also handy for GROUP BY and ARRAY_AGG queries.

 select type_schema,
        type_name,
        type_owner,
        count(*) as attribute_count,
        array_agg(attribute_name order by attribute_number) as attribute_names

   from custom_types
  where type_name like 'state_v%'

group by type_schema,
          type_name,
         type_owner

 order by type_name;

type_schema type_name   type_owner  attribute_count attribute_names
api state_v1    user_change_structure   6   {name,abbr,population,total_sq_miles,percent_land,statehood_year}
api state_v2    user_change_structure   8   {id,name,abbr,population,total_sq_miles,percent_land,statehood_year,capital}

Four comments:

You should mention that you are only querying for composite types.
You don't need to join with pg_namespace, you can just cast the schema's oid to regnamespace.
You could turn the subselect into a join for potentially better performance and readability.
The NOT EXISTS clause shouldn't be in the subselect, but in the main query.


How to disable starting MySQL service on Windows7 startup? When I turn on my desktop (Windows 7 SP1), I often find 2 or 3 GB of memory is already occupied by MySQL processes. I don't care about how much memory a database requires, if it actually runs some queries, but I do, if it's an app I even don't start myself yet. For that matter, I barely use MySQL barring when I develop some web apps in my local environments.

So is it possible to stop this gluttony? I tried to remove MySQL from my startup group viamsconfig, but couldn't find its name over there.

Here is extra info.

mysql  Ver 14.14 Distrib 5.6.15, for Win64 (x86_64)
MySQL Server 5.6
MySQL Workbench 6.0

In Control panel open Administrative tools then double click Services.  Scroll down to MySQL and double click to open the properties.  Change the Startup type to Manual.


We converted a database table to be in-memory and the corresponding memory-optimized filegroup takes 1GB on the disk SQL Server 2014 SP2.  As the titles says, we have converted one of our database tables to be in-memory.  After we did this, the corresponding memory-optimized filegroup takes up 1GB on the disk, but on a larger server it's up to 4GB).  I suspect it has to do with the number of CPUs.

The table is EMPTY!  The structure of the table is nothing special, something like this:

Id  int
StatusId    int
ReviewRequired  bit
SourceDate  datetime
UserID  int
InsertDate  datetime
UpdateDate  datetime

Why does it take up so much space on the disk?  I looked, but I couldn't find any way to control the size of the memory-optimized files created.

Here is T-SQL Script used for creating in-memory table:

    CREATE TABLE [dbo].[MyTable]
(      [Id] [INT] NOT NULL,
       [StatusId] [INT] NOT NULL,
       [Reviewed] [BIT] NOT NULL,
       [CreateDate] [DATETIME] NOT NULL,
       [RequestID] [INT] NULL,
       [InsertDate] [DATETIME] NULL,
       [UpdateDate] [DATETIME] NULL,
CONSTRAINT [PK_Id] PRIMARY KEY NONCLUSTERED HASH
(
       [Id]
)WITH ( BUCKET_COUNT = 16),
INDEX [IX_StatusId] NONCLUSTERED HASH
(
       [StatusId]
)WITH ( BUCKET_COUNT = 64)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

I would like to close the loop on my own question.  We worked with Microsoft support on this and the bottom line is : "it's by design".  I strongly believe that this is a flawed design and should be changed.  The Microsoft support engineer opened a feature change request for this.  In addition I created a "MS Connect" item about this.  Please up-vote it if you agree with the request:
https://connect.microsoft.com/SQLServer/feedback/details/3116296/enabling-in-memory-feature-for-databases-takes-up-huge-amount-of-disk-space-for-checkpoint-file-pairs-cfps-in-cases-when-there-are-many-databases-on-a-sql-server-instance


Cannot Start/Stop Services using SSMS 2014 (but 2008R2 works) In SSMS 2014, when I connect to a server in Object Explorer using my network credentials (I am a domain admin), I can do pretty much everything I would expect as a DBA. But I cannot start/stop/restart services. For instance, if I right-click SQL Server Agent,  the options to Start, Stop, and Restart are greyed out.

Using SSMS 2008R2, from the same client machine using the same credentials connecting to the same server, the options are available and functional when I right-click.

This is true for all servers in our environment.

What do I need to configure in order to be able to manipulate SQL services in SSMS 2014?

I can't reproduce that problem here, but my first suspicion any time a menu option disappears or becomes disabled is permissions.

Since you've ruled out that your user can perform these features through other tools, then the difference must be the tool acting on your behalf. Right-click the SSMS shortcut (in some operating systems you'll need Shift+right-click) and choose Run as administrator.


How are foreign keys resolved when creating tables in batch? I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline version of the database I have exported the CREATE TABLE statement for each table into it's own sql file.

I plan to combine the contents of each file into a single batch when initializing the database.

However, each file contains foreign key constraints within the CREATE TABLE statement.

I am wondering if the RDMS (in this case MSSQL and Sybase) is going to be able to handle the batch? Will it be able to create the tables and then create the constraints?

Or is it going to fail when it comes across the first table where the table containing the candidate key has not been created yet?

I've read about batch execution in MSSQL, and my understanding is the database will create a execution plan for the batch. Will the database be able to reorder my create table statements into the right order?

My database contains 366 tables and it would be difficult to order the CREATE TABLE statements in the correct order.

the batch will fail.

do all the table creates first, then do all the foreign key constraints AFTER all the tables are created.

OR

create the tables with constraints in the correct ORDER so that constraints don't fail.


SQL: Query to insert records into the table if there is not even one record with certain condition I have a table train_statuses.
With the schema as below:
CREATE TABLE public.train_statuses (
    id uuid NOT NULL,
    status text NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

Indexes:
    "train_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
    "idx_train_statuses_id_updated_at" btree (id, updated_at)

This table can have multiple entries for the same id.
The status can be one among these values : 'cancelled', 'queued','executed', 'failed', 'succeeded'
Now I need to write a query that inserts entries into train_statuses for all ids that has their status only as either queued or executed with status as 'cancelled'
I came up with this query
WITH ts AS (
        SELECT
            DISTINCT ON (ts.id) ts.id, ts.status
        FROM train_statuses ts
        INNER JOIN train_statuses t ON ts.id = t.id
        WHERE
            ts.status IN ('queued','executed')
            AND ts.status NOT IN ('failed', 'succeeded','cancelled')
    )
    INSERT INTO train_statuses (id, updated_at, status) SELECT id, now(), 'cancelled' FROM ts

This technically works.
But I feel there would be a more optimal way to get this done.
Joining train_statuses again with itself doesn't seem that great since the table size is around 150GB and we don't have index on status.
Please go through and let me know if there is a better way to get the desired result.
Thanks
Note: If there is any better title for this question please add an edit suggestion.

Removed superfluous joins and criteria
WITH ts AS (
        SELECT ts.id
         FROM train_statuses ts
         WHERE ts.status IN ('queued','executed')
        EXCEPT
        SELECT ts.id
         FROM train_statuses ts
         WHERE ts.status Not IN ('queued','executed')
)
    INSERT INTO train_statuses (id, updated_at, status) SELECT id, now(), 'cancelled' FROM ts


DBCC CHECKDB WITH DATA_PURITY gives out of range error I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error:

Msg 2570, Level 16, State 3, Line 2
Page (1:19558), slot 13 in object ID 181575685, index ID 1, partition ID 293374720802816, alloc unit ID 11899744092160 (type "In-row data"). Column "NumberOfShares" value is out of range for data type "numeric".  Update column to a legal value.

The column NumberOfShares is a numeric (19,6) data type. If I run the following

select max (NumberOfShares) from AUDIT_Table
select min (NumberOfShares) from AUDIT_Table

I get:

22678647.839110
-1845953000.000000

These values are inside the bounds of a numeric (19,6) so I'm not sure why the DBCC check fails. Any ideas to find out why it fails? Do I need to use DBCC PAGE? How would you troubleshoot this?

Thanks,
Mark.

Use DBCC PAGE:

DBCC TRACE(3604);
DBCC PAGE('dbname', 1, 19558, 3);

and check the slot 13.


Changing Collation with a High Availability server How do I change a DB Collation without removing the DB from high availability group? Can I just take it offline and change it? or I really do need to take it out of high availability group 1st then set to single user then alter then reset it back to multiuser and back to high availability? will it cause an issue with the partner database?

Sorry I forgot the specs. I accidentally choose a case sensitive collation type. and I want to change it to Case insensitive type. but this DB is on the high availability always on group.

Thank you

I have to remove the DB from High Availability Group(Always on), change the DB to single user, change the collation then change it back to multiuser,back to the high availability group. The good thing is this database is new and it only have 5 tables otherwise it will be more complicated. I was just hoping I don't have to remove it from the group.


Recommended plan for 0min RTO and RPO I have a Windows Server 2008R2 Server, running SQL Server 2008R2 SP2. The server currently only does a full backup once daily. The server's operational hours are between 8am and 5pm. The server's new RTO is 0mins and it's RPO is 0mins during operational hours. What is the best option in achieving this? I have currently thought of an AlwaysOn Availability Cluster. As for backups I have thought of Transactional Log backups every 15mins, Differential backups every 2 hours, and keeping the Full backup every day. Is my plan sufficient?

Also with an AlwaysOn Availability Cluster, do I just need 1 additional server to be a replica or do I really need a Primary Replica and Secondary Replica? (still very confused about AlwaysOn...)

If I implement AlwaysOn, do I really need to backup each server?

If I implement AlwaysOn, do I really need to use a SAN?

Are there any performance impacts doing backups doing business hours?

You could implement database mirroring.  Synchronous database mirroring provides a mechanism to ensure all writes on the primary mirrored databases are sent to the secondary machine prior to being committed on the primary.  This is designed to provide an RPO as close to zero as practically possible.

In the event the primary goes down for any reason, the secondary mirror will become the primary and resume serving clients once it completes the recovery process for the database in question.  Recovery can take a very short amount of time assuming you ensure the redo queue is being processed reliably.


How to fill GROUP_CONCAT() with 0's when JOIN data missing I'm using MySQL 8.0's date generation and am joining in data when/where available.  This technique allows me to ensure this query returns zero values.  This works perfectly when I'm pulling data for a single stat.  Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.

Here's the current output:

[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21

My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:

[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0

        WITH RECURSIVE dates (date) AS
        (
          SELECT :startingDate
          UNION ALL
          SELECT  date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
        )
        SELECT
            COALESCE(daily_stats.date, dates.date) AS label,
            GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
        FROM dates
        LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
        GROUP BY label;

I'm unsure how to accomplish this without doing a UNION on several select queries.  Is there a more efficient approach?

My table:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Both date and stat may be absent. So you must generate not date array, but (date, stat) pairs array:

WITH RECURSIVE dates (date) AS
(
  SELECT '2019-01-24'
  UNION ALL
  SELECT  date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB('2019-01-30', INTERVAL 1 DAY)
),
stats (stat) AS (
  SELECT DISTINCT stat
  FROM daily_stats
  WHERE stat IN(132, 120, 111)
),
dates_stats (date, stat) AS
(
  SELECT date, stat
  FROM dates, stats
)
SELECT
    dates_stats.date AS label,
    GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(dates_stats.stat, 132, 120, 111)) AS value
FROM dates_stats
LEFT JOIN daily_stats ON daily_stats.stat IN(132, 120, 111)
                     AND daily_stats.date = dates_stats.date
                     AND daily_stats.stat = dates_stats.stat
GROUP BY label;

fiddle

Or maybe ever

stats (stat) AS ( SELECT 132 stat
                  UNION ALL
                  SELECT 120
                  UNION ALL
                  SELECT 111
),


What is the proper way of designing database with dynamic fields in MySQL + PHP? I need help on designing a proper structure for a database table with dynamic fields. I am using MySql with PHP and the system I'm currently working on (inventory system) requires a table that accepts user-generated data categories. I'm really not good with this so any help will be greatly appreciated.

This is what I have right now (I know it's in really bad design please help)

Inventories can have different types (e.g Food, Clothes, etc) and details are dynamic based on what will be supplied by user (e.g Size, Brand, etc).

I have inventories_tbl:

+-------------------+
| inventories_tbl   |
+-------------------+
| id                |
| item_name         |
| inventory_type_id |
| description       |
| cost              |
| price             |
| quantity          |
| created_at        |
| updated_at        |
+-------------------+

then I have the inventory_types_tbl:

+--------------------+
|inventory_types_tbl |
+--------------------+
| id                 |
| name               | (e.g Food, Beverages, Clothing,etc)
| display_name       |
| created_at         |
| updated_at         |
+--------------------+

here comes the problematic part, I need to store dynamic inventory details according to what type of inventory type the item is. (e.g Food = 'expiration date', etc then if in Beverages = 'net volume', 'alcohol content', etc)

So I created a inventory_attributes_tbl:

    +-------------------+
 | inventory_attributes_tbl |
    +-------------------+
    | id                |
    | name              | (e.g size, weight, model number, serial number etc)
    | inventory_type_id |
    | field_name        |
    | created_at        |
    | updated_at        |
    +-------------------+

Then I have the inventory_attributes_values_tbl where I store the value of the attributes of a given inventory:

    +-------------------------+
  inventory_attributes_values_tbl
    +-------------------------+
    | id                      |
    | inventory_attributes_id |
    | inventory_id            |
    | value                   |
    | created_at              |
    | updated_at              |
    +-------------------------+

This setup makes the item delete/update so complicated and I also need to create a reports later on about based on the dynamic details of an item like graphs and this setup makes the query so slow and actually made the server to hang on querying just 10k+ of results. Any opinions are appreciated. Thanks in advance.

Its possible to use type JSON

https://dev.mysql.com/doc/refman/8.0/en/json.html

this negates the need for a table of custom fields, however if you need to do join/look ups on this data, it will be difficult.


Cross database reference select access with disabled login for DB user We have two databases (DB1, DB2) with the following view structure.

DB1:

T1, T2, T3, ..... Tn (Tables)
V1 (View consuming {T1, T2, T3 ..... Tn })

Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1

USE DB2;
CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.

I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.

USE DB1;

CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

CREATE USER testUser FOR LOGIN testUser;

GRANT SELECT ON dbo.V1 To testUser;

USE DB2;

CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.

This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?

What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.

If we don't grant select access to V1, server throws permission error:

  The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.

Make sure DB_CHAINING is ON.
Map TestUser Login to DB1 and DB2.
Grant SELECT on V2.
Don't grant SELECT on V1.

See Ownership Chains in the documentation.

Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.

TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.

Double-check that DB_CHAINING is ON by executing:

SELECT name, is_db_chaining_on
FROM sys.databases;

If it's not the case, set it with:

ALTER DATABASE DBX SET DB_CHAINING ON

...and make sure both views have the same owner.

You can try following code to validate that it's working:

Connect as sysadmin and create Databases DB1 and DB2

USE master
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO

in DB1:

Create a sample table TB1

Insert a value in TB1 table (this is only because I dont like empty    result set)

Create a sample view selecting all rows in TB1 table

USE DB1
GO
CREATE TABLE TB1 (id int);
GO
INSERT INTO TB1 VALUES(42);
GO
CREATE VIEW V1 AS SELECT * FROM TB1;
GO

in DB2:

Create a sample view V2 selecting all rows in DB1.dbo.V1 view

USE DB2
GO
CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
GO

Create TestUser Login

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

in DB1:

Create TestUser User mapped to TestUser Login

USE [DB1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

in DB2:

Create TestUser User mapped to TestUser Login

Grant SELECT on V2 view to TestUser user

USE [DB2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
GRANT SELECT ON V2 TO TestUser;

Connect with TestUser Login

In DB2:

Try to select from V2

USE DB2
GO
SELECT * FROM V2;

This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.

Connect as sysadmin

Enable DB_CHAINING on DB1

USE master
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO

Enable DB_CHAINING on DB2

ALTER DATABASE DB2 SET DB_CHAINING ON;
GO

Connect with TestUser Login

In DB2:

Try to Select From V2 again, that time it should succeed

USE DB2
GO
SELECT * FROM V2;

In DB1:

Try to Select From V1

USE DB1
GO
SELECT * FROM V1;
GO

As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.


SELECT from Clustered Columnstore results in LOB Reads I am performing a SELECT from the following Clustered Columnstore Index which contains 5.2 Billion Records. When I review the output of STATISTICS IO, I am seeing LOB Logical reads.

My question is why is the query performing LOB Logical Reads when none of the data types in the table are LOB values?

I am working within a SQL Server 2014 SP3 Enterprise Edition environment.

Table Definition:

CREATE TABLE [dbo].[ColumnstoreTableA](
    [RollupID] [bigint] IDENTITY(1,1) NOT NULL,
    [DT_DIMID] [int] NOT NULL,
    [TC_DIMID] [int] NOT NULL,
    [C_DIMID] [int] NOT NULL,
    [CD_DIMID] [int] NOT NULL,
    [AC_DIMID] [int] NOT NULL,
    [UA_DIMID] [int] NOT NULL,
    [UL_DIMID] [int] NOT NULL,
    [JP_DIMID] [int] NOT NULL,
    [RTT_DIMID] [int] NOT NULL,
    [RA] [int] NOT NULL,
    [Impressions] [int] NOT NULL,
    [Clicks] [int] NOT NULL,
    [Conversions] [int] NOT NULL,
    [Sends] [int] NOT NULL,
    [CreationDate] [datetime2](0) NOT NULL DEFAULT GETDATE(),
    [ModifyDate] [datetime2](0) NOT NULL DEFAULT GETDATE(),
    [Z_DIMID] [int] NOT NULL,
    [R_Actual] [money] NULL,
    [JP_DIMID2] [int] NULL
) ON [PRIMARY]

SELECT Statement:

SELECT  [T].[Impressions]
FROM    [dbo].[ColumnstoreTableA] AS [T]

STATISTICS IO Output:

  Table 'ColumnstoreTableA'. Scan count 5, logical reads 127, physical
  reads 0, read-ahead reads 0, lob logical reads 3719, lob physical
  reads 0, lob read-ahead reads 0.

Multiple sources appear to indicate Columnstore Indexes store its data as LOB data, which is happening in this case.  My understanding and take-away is that it should always be expected for LOB reads to occur when using Columnstore indexes.

What's the anatomy of a Columnstore index?

How to use Columnstore indexes in SQL Server

Inside the SQL Server 2012 Columnstore Indexes

SELECT      COUNT([partition_number]) AS [Number of Partitions],
            SUM([in_row_used_page_count]) AS [SUM_in_row_used_page_count],
            SUM([lob_used_page_count]) AS [SUM_lob_used_page_count],
            SUM([used_page_count]) AS [SUM_used_page_count],
            SUM([row_count]) AS [SUM_row_count]
FROM        [sys].[dm_db_partition_stats]
WHERE       [partition_id] IN   (
                                    SELECT  [p].[partition_id]
                                    FROM    [sys].[partitions] AS [p]
                                    INNER JOIN  [sys].[objects] AS [o] ON [o].[object_id] = [p].[object_id]
                                    WHERE   [o].[object_id] = 971254615
                                );

Results:

Number of Partitions SUM_in_row_used_page_count SUM_lob_used_page_count SUM_used_page_count  SUM_row_count
-------------------- -------------------------- ----------------------- -------------------- --------------------
847                  0                          5630558                 5630558             5175958971


SQL 2008 RTM Temp Tables Today I implemented a monitoring solution on our SQL server using Cacti and the first thing i noticed is the growth in the number of temporary tables, in just 3 hours on our test system we had an additional 300+ temp tables in the temp DB. I also noticed that SQL Server's memory growth over these 3 hours was huge - 15 GB utilized after 3 hrs.

There were only 3-4 users testing on the test system and about 5 connections open to the application when I ran a SP_WHO.

What can be causing this? I did a query on the objects in the temp DB and all the names are similar to this #XXXXXXXXX, I read somewhere that these are from the Table Variable (Declare @myTable...)

Is there any way to check why we have a growth in the number of temp tables & what is causing it? Any help at this stage would be greatly appreciated.

I am going to be installing the service packs tomorrow for 2008, hopefully that solves the problem.

Temp table usage is going to be totally up to your applications which have their databases on the SQL Server, or that are using the SQL Server.  For example the monitoring application itself could be using dozens of temp tables while it gathers up data.

As for the memory usage, SQL Server will by default use up as much memory as it needs to.  Every time is needs to modify data it loads that data into memory and then leaves that data in memory until the SQL Server needs the memory for something else.  In other words high memory usage isn't something that needs to be worried about as it is totally normal.  That said you'll want to cap the amount of memory that the SQL Server has access to so that it doesn't use all the memory on the server.


Insert as many rows as count on other table returns Hello I have three tables in my MySQL-DB

Project:
id | name
1  | project1
2  | project2

KPI:
id | name
1  | build_status
2  | satisfaction

ProjectKPIData:
id | kpi_id | project_id | value
1  | 1      | 1          | 2
2  | 1      | 2          | 0
3  | 2      | 1          | 1
4  | 2      | 2          | 1

Now I am trying to add a trigger to the Project-table. If a new project gets added it should insert as many rows into ProjectKPIData as there are KPI's and also insert the id of them. So after I add a new project the table ProjectKPIData should look like this (the project id 3 is for the newly created project):

ProjectKPIData:
id | kpi_id | project_id | value
1  | 1      | 1          | 2
2  | 1      | 2          | 0
3  | 2      | 1          | 1
4  | 2      | 2          | 1
5  | 1      | 3          | 0
6  | 2      | 3          | 0

Is there a specific MySQL query I can acomplish this? Im pretty new to MySQL-Databases and I couldn't find a way of doing this while searching online.

Thanks for the help.

You can use this query.

INSERT INTO ProjectKPIData (`kpi_id`,`project_id`,`value`) SELECT k.id,NEW.id,0 FROM KPI k;


SQL Agent Job or SQL Alert for Long Running Queries Am trying to create either an Agent Job that runs scheduled every 60 minutes or a SQL Alert that fires off a mail everytime a query is detected that runs longer than 1 hour. The below is the basis of the query which I have:
SELECT
    r.session_id as [SPID]
    ,r.[start_time] as [Start Time]
    , T.[text] as [Query]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.[sql_handle]) T
where
 DATEDIFF(second, r.[start_time], getdate()) > 3600
GO

Would it be more efficient to use an IF Statement, i.e if rows are returned then send mail, if so what is the best way of creating this. Or use a SQL Alert condition that would alert for Long Running Transactions > 3600 seconds and send via operator. If I have go via the Alert would I need an agent job with the Above SQL in. I have tried this (via SQL Alert) but the alert does not seem to trigger.
Any help is much apprecaited.
Have tried to include IF Statement but getting Syntax error (Incorrect syntax near the keyword 'into'.), note made it 60 seconds for testing.
IF (SELECT
'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text), CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))
INTO #LongRunningQuery
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 60) > 0

begin

exec msdb.dbo.sp_send_dbmail

@profile_name = 'Test Alerts',

@recipients = 'test@test.com',

@subject = 'Warning',

@query = 'select * from #LongRunningQuery'

DROP TABLE #LongRunningQuery
end

Any help is much apprecaited.

You can't use the INTO keyword inside of an IF clause, so the syntax error you're currently getting is due to trying to insert the results into a temp table as part of the IF clause.
There's no performance benefit stuffing the query inside the IF clause, so for better readability I would run the query first then check the if results exist like so:
-- Get any long running queries
DROP TABLE IF EXISTS #LongRunningQuery
SELECT
    'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text) AS EmailBody,
    CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) AS Runtime
INTO #LongRunningQuery
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
    AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 60

-- Send email when long run queries found
IF EXISTS (SELECT 1 FROM #LongRunningQuery)
BEGIN

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Alerts', @recipients = 'test@test.com', @subject = 'Warning', @query = 'select * from #LongRunningQuery'

END


Auto_Increment After deleted data from the table How can I reset the auto_increment of a field? I want it to start counting from 1 again after I delete all the data.

ALTER TABLE tablename AUTO_INCREMENT = 1;

See also the same question on stackoverflow.