Access: How to Combine Data from Two Rows in a Query I have a base dataset formatted like the one below.

Sex1    | Sex2    | Number|
F       |         | 5     |
M       |         | 8     |
M       | F       | 5     |
F       | F       | 3     |
M       | F       | 2     |
F       | M       | 1     |
F       | F       | 9     |
M       | M       | 3     |

I'd like to use SQL to create a query that produces totals from the price by Gender composition like so:

Sex | Total Number
F   |    5
M   |    8
MF  |    8
FF  |    12
MM  |    3

However, I'm struggling to find a way to pull the FM and MF categories into the same row. The code I have that leaves them distinct is of the form

SELECT Sex1 & "" & Sex2 AS Sex, Sum(Number) AS [Total Number]
FROM TableName
GROUP BY Sex1 & "" & Sex2

Is there a simple way to combine the rows?

You could use a CASE expression or the IIf() function to first order the two values and then group by them:

with CASE expression:

SELECT CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END
       & CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END,
         CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END ;

with IIf() function:

SELECT IIf(Sex1 < Sex2, Sex1, Sex2) & IIf(Sex1 < Sex2, Sex2, Sex1) AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY IIf(Sex1 < Sex2, Sex1, Sex2),
         IIf(Sex1 < Sex2, Sex2, Sex1) ;

You can try using CONCAT() function in MSSQL to get 2 columns in one.

SELECT CONCAT(Sex1,Sex2) AS Sex, Sum(Number) AS Total_Number FROM db.test GROUP BY concat(Sex1,Sex2);


Getting distinct list of records based on the MAX of a column I have a status table (tblTestActionStatus) which has three columns

ID_TestAction:  references some test action
ID_Status:   a look up table reference for the variety of possible statuses for the action
StatusDateTime:  a datetime field which logs the exact time a status update was made for the test action.

So while a test action is happening, periodic status updates are made.

I am interested to know the current status which would be the last status update of a test action as of the moment I run the query.
For example, test action # 100 has gotten 4 status changes so far.  The first was 1, then 2, then 3, and most recently 4.  And test action # 101 has gotten 3 status updates so far.

So I would like to write a query that returns for me all the columns in the table but for only the most recent StatusDateTimes.

I attached a pic which shows the table contents and the rows I would like to see coming back from the query highlighted.

This answer was originally posted by the OP as an edit to the question; it is now reposted as a Community Wiki answer.

I was able to find an answer with some more searching in the archive.

https://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records

So the SQL I created from that post which works in my case is as follows...

SELECT tblTestActionStatus.ID_TestAction, StatusDateTime, ID_Status
FROM tblTestActionStatus
INNER JOIN
    (
        Select MAX(StatusDateTime) as LatestDate, ID_TestAction
        FROM tblTestActionStatus
        Group By ID_TestAction
    ) SubMax
on tblTestActionStatus.StatusDateTime = SubMax.LatestDate
and tblTestActionStatus.ID_TestAction = SubMax.ID_TestAction

This query give the same result also:

SELECT tblTestActionStatus.ID_TestAction as 'ID_TestAction__',
       tblTestActionStatus.ID_Status as 'ID_Status__',
       tblTestActionStatus.StatusDateTime as 'StatusDateTime__'
FROM   tblTestActionStatus
WHERE  tblTestActionStatus.StatusDateTime IN
  (SELECT MAX( tblTestActionStatus.StatusDateTime )
          FROM tblTestActionStatus
   GROUP BY tblTestActionStatus.ID_TestAction)


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

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

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

Maybe:

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

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

It's the "better" way?

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

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

This sort of question would be better asked on the database administrators channel but Brent Ozar has some good advice https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/


Deleting duplicates with group by and count What is the fastest method to convert the following query:

SELECT COUNT(*) as c FROM tbl_fields
WHERE fieldnotes IS NULL
GROUP BY fieldno,fieldserial,id,fielddate,fieldsid
HAVING COUNT(*) > 1;

... into one that will delete duplicated records? The table does not have any primary keys and contains several million entries.

According to your query, you have fieldno,fieldserial,id,fielddate,fieldsid as a rule for uniqueness in the GROUP BY clause.
You can try this :
CREATE TABLE tbl_fields_unique LIKE tbl_fields;
ALTER TABLE tbl_fields_unique
ADD UNIQUE KEY unq (fieldno,fieldserial,id,fielddate,fieldsid);
INSERT IGNORE INTO tbl_fields_unique
SELECT * FROM tbl_fields;
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

This will filter rows with duplicate fieldno,fieldserial,id,fielddate,fieldsid fields. Look over the new table. Once you are satisfied with the contents of the tbl_fields_unique table, do this:
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

Give it a Try !!!

CREATE TABLE friends_copy LIKE friends;

insert into friends_copy(name,dob)
select name,dob from friends group by name,dob having count(*)>=1


How do I migrate data from a SQL Server DB to MongoDB? I wish to migrate a large SQL Server Database (having a million tables) to MongoDB. I am not able to understand how to convert the complex Joins, indexes and keys in SQL Server to MongoDB which has over a million tables and huge data?

Joins you either pre-compute and store the denormalised data in the document. Or the application implements a join algorithm for runtime.

Indexes can be defined for whichever values inside the documents you'd like to have them. The documentation adequately  describes the options. As currently, they help reads but have a write overhead.

Keys: there are no keys. Each row has a unique identifier which you can assign or let the system generate a value. This would correspond to a surrogate primary key. Foreign keys, uniqueness, check constraints, assertions etc. are the appapplication's responsibility.

The first step to migrating from SQL to NoSQL is to denormalize your schema since MongoDB does not support joins. You should be able to recreate the indices and keys in Mongo using JSON format. Also keep in mind that MongoDB does not support transactions.

You can check out this tool to help you move data between SQL and Mongo.
https://github.com/anlek/mongify


Converting a varchar date into a date (dd/mm/yy) I have a CSV file that has dates written like this: 250909,240909 and they get stored in a SQL Server database as varchars.

How do I get it to convert them into dates so that it gets displayed like this: 25/09/09, using the dd/mm/yyyy format?

At the moment I can get it to convert, but it converts it like this: 2025/09/09, meaning it is using the yyyy/mm/dd format which is wrong, seeing that the first column is the day and second the month and third the year.

I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order.  (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)

Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.

http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT

I changed the month to 8 to make it easier to double check.  Using the CONVERT style option 3, you can do the following:

DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809';

-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
     SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);

-- Using the DATE
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];

The results of the last two selects are:

DefaultFormat
-------------
2009-08-25

DD/MM/YY
--------
25/08/09

To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)

Likewise when displaying the DATE as you desire, you need to use STYLE 3

You can use this method to convert your date to this format : dd/mm/yyyy.

CONVERT(varchar(10), CAST("datefield" as date), 103) as dd/mm/yyyy


Can every SPARQL query be expressed as a GraphQL query? The question is really formulated in the title :-)

I am working on an RDF project but I want to make it as easy as possible to interact with the API. Therefor GraphQL seems the most likely.

UPDATE:
Got an answer on Quora.

We are currently working on a GraphQL library to query a SPARQL Endpoint according to an OWL schema. It will be the main API access point for the DATAtourisme, french government project to handle and share touristic open data.

Contact me to tell me more about your project, i will tell you if the library can fit your needs.


Getting the "Database does not exist" error message Hoping someone can help out here. I've searched for an answer on my own as much as I could in the areas that I understand regarding SQL Server administration. Something which I'm fairly new at. ( Under 2 years experience )

  Msg 911, Level 16, State 1, Line 1
  Database 'dbname' does not exist. Make sure that the name is entered correctly.

The issue is when running this script (error above) ( script posted below ) it stops and says it's not able to hit a particular database as if its still connected. However, this database which was from a migration from old server to new and was renamed (all areas including files) no longer exists anywhere. I do not see it in any T-SQL outputs or any reports and the MDF / LDF files are long gone. However, the script still hangs and thinks this DB should be part of this instance.

In searching, I've come across various posts stating a possible permission issue and to check the master DB for residual information all of which have not panned out with any solution. I have full access under my active directory login for this instance and have the SA codes as well. Same error when running as that.

Any help would be appreciated, hopefully, its something simple and I'm just missing something obvious.

Thank you all for your help, it's appreciated.

DECLARE @CurrentDB sysname
DECLARE curDatabase CURSOR FAST_FORWARD FOR
SELECT name FROM master.sys.databases
--SELECT * FROM master.sys.databases
WHERE name not in ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @CurrentDB
WHILE ( @@FETCH_STATUS = 0)
BEGIN
--print @CurrentDB
--
declare @sql1 varchar(100)
select @sql1 = 'USE ' + @CurrentDB + ';' + '

'

--EXEC sp_sqlexec @sql1
--print @sql1

declare @sql2 varchar(max)
select @sql2 =
-- Ensure a USE <databasename> statement has been executed first.
'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'

declare @sql varchar(max)
select @sql = @sql1 + @sql2

EXEC sp_sqlexec @Sql

--print @sql

FETCH NEXT FROM curDatabase INTO @CurrentDB
END -- curDatabase WHILE loop
CLOSE curDatabase
DEALLOCATE curDatabase

Using your script, I hit the same error with a database called ಠ_ಠ.

I've made some changes to handle Unicode strings properly, and quote any odd characters in database names.

Ultimately, the problem was with your use of sp_sqlexec. When I changed it to sp_executesql, the script worked fine.

DECLARE @CurrentDB NVARCHAR(128);
DECLARE curDatabase CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   master.sys.databases
    --SELECT * FROM master.sys.databases
    WHERE  name NOT IN ( 'master', 'tempdb', 'msdb', 'model', 'AdventureWorks', 'AdventureWorksDW' );
OPEN curDatabase;
FETCH NEXT FROM curDatabase
INTO @CurrentDB;
WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        --print @CurrentDB
        --
        DECLARE @sql1 NVARCHAR(MAX) = N'';
        SELECT @sql1 = @sql1 + N'USE ' + QUOTENAME(@CurrentDB) + N';

'       ;

        --EXEC sp_sqlexec @sql1
        --print @sql1

        DECLARE @sql2 NVARCHAR(MAX) = N'';
        SET @sql2
            = @sql2
              +
        -- Ensure a USE <databasename> statement has been executed first.
        N'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE 1 = 0;

select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid
from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'       ;

        --PRINT @sql2

        DECLARE @sql NVARCHAR(MAX) = N'';
        SET @sql = @sql + @sql1 + @sql2;

        PRINT @sql;
        EXEC sys.sp_executesql @sql;

        SET @sql = N'';
        SET @sql1 = N'';
        SET @sql2 = N'';

        FETCH NEXT FROM curDatabase
        INTO @CurrentDB;
    END; -- curDatabase WHILE loop
CLOSE curDatabase;
DEALLOCATE curDatabase;

Also, I'd strongly recommend against writing maintenance scripts like this from scratch. Ola Hallengren writes amazing free ones.

I faced such problem when running a script in SQL Server Management Studio to do some stuff on my Local SQL Server. I noticed that the Query Window that I was running the script in was connected to a Remote SQL Server not my local machine.


what is so special about MongoDB indexing? I was reading the MongoDB documentation, where it lists Indexing as an special feature. But after reading the doc, I still can't see what it is that makes MongoDB index special. Anyone can explain?

there is absolutely nothing special about it. absolutely zero; nothing.
if you want REALLY advanced indexing -> use PostgreSQL.


How to DROP multiple tables from databases? I have situation where someone mistakenly execute script for creating tables under wrong database.

Let's say we have database DB1 which is some test database, and we have to create other database DB2. Script for creating tables is intended for DB2, but someone execute it under DB1.

Now my database DB1 has its own correct tables, and other incorrect tables. I know how to write a select query to view them, and I know that all wrongly created tables were created on a specific date, let's say 2009-12-12. But the tables are related with foreign keys, and I can't just drop them in the order that they appear, I must first to remove all foreign keys and then drop all of the tables.

Can someone suggest how to do that?

I can do it with cursor, but that is not elegant solution.

Since you only have to do it once, forget the fancy batch job and just get on with each task.  Assuming you know the table names, you just do this sort of thing.

For each table with foreign keys, drop the foreign key constraint on that field.  Then depending on your requirement, you can add records to the table that should have the data and add a new foreign key, or, simply delete those records.  Then you can drop the table.

Much of this work can be done with SSMS so you might not have to write that many queries.

DELETE t1,t2
FROM table1 AS t1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

like this way youu can delete multiple tables

see this enter link description here link


Slow queries on billions-rows-table // index used Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with.

My project is about collecting data from devices (up to 1000 or more devices), where every device is sending one data block every second, which makes about 3 million rows per hour.

Currently I've got one big table where I store the incoming data of every device:

CREATE TABLE data_block(
    id bigserial
    timestamp timestamp
    mac bigint
)

Because there are several types of data a data block can (or can not) include, there are other tables which reference the data_block table.

CREATE TABLE dataA(
    data_block_id bigserial
    data

    CONSTRAINT fkey FOREIGN KEY (data_block_id) REFERENCES data_block(id);
);
CREATE TABLE dataB(...);
CREATE TABLE dataC(...);
CREATE INDEX index_dataA_block_id ON dataA (data_block_id DESC);
...

It is possible that in one data_block there is 3x dataA, 1x dataB, but no dataC.

The data will be kept for some weeks, so I'm going to have ~5 billion rows in this table. At the moment, I have ~600 million rows in the table and my queries take a really long time. So I decided to make an index over timestamp and mac, because my select statements always query over time and often also over time+mac.

CREATE INDEX index_ts_mac ON data_block (timestamp DESC, mac);

...but my queries still take ages. For example, I queried data for one day and one mac:

SELECT * FROM data_block
WHERE timestamp>'2014-09-15'
AND timestamp<'2014-09-17'
AND mac=123456789

Index Scan using index_ts_mac on data_block  (cost=0.57..957307.24 rows=315409 width=32) (actual time=39.849..334534.972 rows=285857 loops=1)
  Index Cond: ((timestamp > '2014-09-14 00:00:00'::timestamp without time zone) AND (timestamp < '2014-09-16 00:00:00'::timestamp without time zone) AND (mac = 123456789))
Total runtime: 334642.078 ms

I did a full vacuum before query run. Is there an elegant way to solve such a problem with big tables to do an query <10sec?

I read about partitioning, but this won't work with my dataA, dataB, dataC references to data_block_id right? If it would work somehow, should I make partitions over time or over mac?

I changed my index to the other direction. First MAC, then timestamp, and it gains a lot of performance.

CREATE INDEX index_mac_ts ON data_block (mac, timestamp DESC);

But still, queries take >30sec. Especially when I do a LEFT JOIN with my data tables. Here is an EXPLAIN ANALYZE of the query with the new index:

EXPLAIN ANALYZE SELECT * FROM data_block WHERE mac = 123456789 AND timestamp < '2014-10-05 00:00:00' AND timestamp > '2014-10-04 00:00:00'

Bitmap Heap Scan on data_block  (cost=1514.57..89137.07 rows=58667 width=28) (actual time=2420.842..32353.678 rows=51342 loops=1)
  Recheck Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on index_mac_ts  (cost=0.00..1499.90 rows=58667 width=0) (actual time=2399.291..2399.291 rows=51342 loops=1)
        Index Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
Total runtime: 32360.620 ms

Unfortunately my hardware is strictly limited. I'm using an Intel i3-2100 @3.10Ghz, 4GB RAM. My current settings are as following:

default_statistics_target = 100
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 512MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 2GB
max_connections = 20
random_page_cost = 2

This may reflect my MS SQL bias, but I'd try clustering the table by timestamp.  If you're frequently pulling data for a specific time span, this will help because the data will be physically stored contiguously.  The system can seek to the start point, scan to the end of the range, and be done.  If you're querying for a specific hour, that's just 3,600,000 records.

If your query (which is...?) is for a specific machine, Postgres will then need to filter out 99.9% of those 3.6 M records.  If this one-in-a-thousand filter is more selective than a typical date range fitler, you should use the more selective mac field as the first component of your index.  It may still be worth clustering.

If that still doesn't do it, I'd partition by the same field you're indexing, either timestamp or mac.

You didn't give the data types.  Are they appropriate to the data?  Storing dates as text will needlessly bloat your table, for example.

I worked on an application that had billions of readings from electric meters and executed most queries in well under 10 seconds.

Our environment was different.  Microsoft SQL Server on a server class machine (4 cores, 24 GB memory).   Any chance to upgrade to a server?

One big issue is that ingesting the readings one at a time had a big performance  impact on the database.  Writing data required locks and queries would wait.   Can you do inserts in batches?

With your schema, you'll have 4 very large tables.  It'll be important that all your joins use indexes on both tables.  A table scan will take forever.  Is it feasible to merge them to 1 table with null able fields?


How to copy MongoDB database from server to local machine? Or just may be dump database on to the folder on the server and then scp it to local machine? I'm trying to use mongodump from /usr/bin, but nothing good comes out of it, for instance:
mongodump --ssl -u root -p password --authenticationDatabase name -d databaseName -o /database
-->Failed: error connecting to db server: no reachable servers
if:
mongodump -u root -p password --authenticationDatabase name -d databaseName -o /database
-->Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed.
tried to do --host=ip.ad.re.ss:27017
tried to do --host=host-name:27017
--host=127.0.0.1:27017
--host=web-address:27017
tried to switch off mongodb...
Tried to copy files manually, but end up with the empty database on the local machine...
tried to use: mongo --> db.copydatabase(from, to, name)
Nothing is happening positive... How to use mongodump? Possible to use any other way. Only through terminal, ubuntu setup doesn't have any gnu.

Basically what is missing from your mongodump command is... "" round your --host -value.
--host="host-name:27017"

Check similar question here

As you said - more errorless method:

connect to server and create dump there (like this,
mongodump -u root -p password --authenticationDatabase admin -d databaseName -o /database)
scp data to the local machine

Based on your error:

you are doing something wrong in params (did you use --authenticationDatabase admin)?
you are doing something wrong with regarding place (server, network)

Basically, your variant should work, just need to adjust the params for sure.


MySQL Workbench Loses Saved Connections I've been using MySQL Workbench as my go-to management application for quite some time. However, recently (namely, in my most recent install), it will every so often lose the saved connections I've put in. It doesn't happen every time, but sometimes, when I open it up, there won't be any saved connections and I have to re-enter them.

Does anyone know of a way to fix this? The only thing I've been able to find are the release notes, which claim to have fixed this issue over a year ago (ironically, I never had an issue with that until recently).

I'm currently running Lubuntu 11.04 64bit.

Did you get the very, very latest?

The latest stable build (here) is 5.1.19.  However, the bug fix has been put in place for 5.2.21 (per here).

The absolute latest version available is 5.2.34, which should have the bug fix.  However, that's not the latest stable version; it's just the latest generally available (GA) version.  (It's available here, for what it's worth.)

Edit

Since you have the latest version (with the alleged bug fix), I would report this as a new bug.

My only advice... Try to figure out if there is an exact sequence of steps that you can perform to make this happen every time.  Then give them all the information you can (including the version number you're using).

I'm using 5.2.45 and have recently experienced this bug on multiple installations. Stored connections do not persist. Previously, I did not experience this in a past version.


Capture all queries that use hints How can I capture or list all queries that use hints and have executed since the last instance restart?

I am using SQL Server 2005 Standard Edition.

At my prodding @buckley already provided a query from which you can derive information. I'll add that you can capture snapshots of this DMV periodically so that you capture things even if they have been pushed out of the cache since their last use. I won't get into how to maintain an archive of DMV data here; maybe a good blog post in the making.

Once you have the data (or just looking at the current plans that are cached), some common hints you can derive from the XML returned by sys.dm_exec_query_plan.query_plan:

    <QueryPlan      NonParallelPlanReason="MaxDOPSetToOne"
    ---- this query used OPTION (MAXDOP 1) ^^^^^^^^^^^^^^
    ---- or perhaps was forced using server maxdop settings or resource governor

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="1" NoExpandHint="0">
    ---- this query used WITH (FORCESCAN) ------------^

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1">
    ---- this query used WITH (NOEXPAND) ------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="1" ...>
    ---- this query used WITH (FORCESEEK) ------------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1"
    ---- this query uses WITH (FORCESEEK(index(col))) ----------^-------------^

    <IndexScan      Ordered="0" ForcedIndex="1" ForceScan="0" NoExpandHint="0">
    ---- this query used WITH (INDEX = ...) -^

I am NOT an XML guy so I have no clue how to tell you to extract that information dynamically. (More on this below.)

I can't see any way to determine whether isolation has been set (at the batch level or using things like WITH (NOLOCK)) - you might have to:

capture those in the act by polling sys.dm_exec_requests (though transaction_isolation_level really only tells you about things like SET TRANSACTION ISOLATION LEVEL, not table-level hints, since you can apply NOLOCK only to some tables in the query), or
rely on parsing of the query text itself from sys.dm_exec_sql_text (noting that this can yield false positives if, say, NOLOCK is commented out, or you have a table name like dbo.SubmitNoLocks).

I also can't see any difference in the plan for query hints like OPTION (FORCE ORDER) or OPTION (FAST n), so likewise these may also need to rely on parsing the query text as opposed to relying on the plan XML.

EDIT

Since you can get a non-XML version of the plan from an alternate DMF, sys.dm_exec_text_query_plan, you don't necessarily have to become an XML guru to perform this parsing. For example, the following will capture all query plans in the cache that used WITH (INDEX...), WITH FORCESEEK[(...)], WITH FORCESCAN, WITH (NOEXPAND) or WITH (NOLOCK):

SELECT t.[text]--, qp.query_plan
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE
 t.[text] NOT LIKE '%dm_exec%' -- to keep this query out of result
 AND
 (
  qp.query_plan LIKE '%ForceSeek="1"%'
  OR qp.query_plan LIKE '%Forcescan="1"%'
  OR qp.query_plan LIKE '%NoExpandHint="1"%'
  OR qp.query_plan LIKE '%ForcedIndex="1"%'
  OR t.[text] LIKE '%NOLOCK%'
 )
 -- t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;

As identified earlier, this could produce false positives for queries like this:

SELECT * FROM dbo.table /* WITH (NOLOCK) */
SELECT * FROM dbo.RenoLocksmiths;

But outside of parsing the query text for NOLOCK, for all the plan attributes - since the XML gets entitized and characters like quotes get escaped - it will not return false positives for queries like this:

SELECT * FROM dbo.table /* Forceseek="1" */
SELECT * FROM dbo.[WITH (FORCESEEK)];

Using the sys.dm_exec_query_plan statement you can get the information you are looking for.


How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production.

So I would just like to make all collations which are utf8_bin to utf8mb4_bin.

I believe there is no performance loss/gain with this change other than a bit of storage overhead.

From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:

For each database:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

For each table:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

For each column:

ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

(Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a VARCHAR column.)

Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8.

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this.

I made a script which does this more or less automatically:

<?php
/**
 * Requires php >= 5.5
 *
 * Use this script to convert utf-8 data in utf-8 mysql tables stored via latin1 connection
 * This is a PHP port from: https://gist.github.com/njvack/6113127
 *
 * BACKUP YOUR DATABASE BEFORE YOU RUN THIS SCRIPT!
 *
 * Once the script ran over your databases, change your database connection charset to utf8:
 *
 * $dsn = 'mysql:host=localhost;port=3306;charset=utf8';
 *
 * DON'T RUN THIS SCRIPT MORE THAN ONCE!
 *
 * @author hollodotme
 *
 * @author derclops since 2019-07-01
 *
 *         I have taken the liberty to adapt this script to also do the following:
 *
 *         - convert the database to utf8mb4
 *         - convert all tables to utf8mb4
 *         - actually then also convert the data to utf8mb4
 *
 */

header('Content-Type: text/plain; charset=utf-8');

$dsn      = 'mysql:host=localhost;port=3306;charset=utf8';
$user     = 'root';
$password = 'root';
$options  = [
    \PDO::ATTR_CURSOR                   => \PDO::CURSOR_FWDONLY,
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    \PDO::MYSQL_ATTR_INIT_COMMAND       => "SET CHARACTER SET latin1",
];

$dbManager = new \PDO( $dsn, $user, $password, $options );

$databasesToConvert = [ 'database1',/** database3, ... */ ];
$typesToConvert     = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext' ];

foreach ( $databasesToConvert as $database )
{
    echo $database, ":\n";
    echo str_repeat( '=', strlen( $database ) + 1 ), "\n";

    $dbManager->exec( "USE `{$database}`" );

    echo "converting database to correct locale too ... \n";

    $dbManager->exec("ALTER DATABASE `{$database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci");

    $tablesStatement = $dbManager->query( "SHOW TABLES" );
    while ( ($table = $tablesStatement->fetchColumn()) )
    {
        echo "Table: {$table}:\n";
        echo str_repeat( '-', strlen( $table ) + 8 ), "\n";

        $columnsToConvert = [ ];

        $columsStatement = $dbManager->query( "DESCRIBE `{$table}`" );

        while ( ($tableInfo = $columsStatement->fetch( \PDO::FETCH_ASSOC )) )
        {
            $column = $tableInfo['Field'];
            echo ' * ' . $column . ': ' . $tableInfo['Type'];

            $type = preg_replace( "#\(\d+\)#", '', $tableInfo['Type'] );

            if ( in_array( $type, $typesToConvert ) )
            {
                echo " => must be converted\n";

                $columnsToConvert[] = $column;
            }
            else
            {
                echo " => not relevant\n";
            }
        }

        //convert table also!!!
        $convert = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

        echo "\n", $convert, "\n";
        $dbManager->exec( $convert );
        $databaseErrors = $dbManager->errorInfo();
        if( !empty($databaseErrors[1]) ){
            echo "\n !!!!!!!!!!!!!!!!! ERROR OCCURED ".print_r($databaseErrors, true)." \n";
            exit;
        }

        if ( !empty($columnsToConvert) )
        {
            $converts = array_map(
                function ( $column )
                {
                    //return "`{$column}` = IFNULL(CONVERT(CAST(CONVERT(`{$column}` USING latin1) AS binary) USING utf8mb4),`{$column}`)";
                    return "`{$column}` = CONVERT(BINARY(CONVERT(`{$column}` USING latin1)) USING utf8mb4)";
                },
                $columnsToConvert
            );

            $query = "UPDATE IGNORE `{$table}` SET " . join( ', ', $converts );

            //alternative
            // UPDATE feedback SET reply = CONVERT(BINARY(CONVERT(reply USING latin1)) USING utf8mb4) WHERE feedback_id = 15015;

            echo "\n", $query, "\n";

            $dbManager->exec( $query );

            $databaseErrors = $dbManager->errorInfo();
            if( !empty($databaseErrors[1]) ){
                echo "\n !!!!!!!!!!!!!!!!! ERROR OCCURED ".print_r($databaseErrors, true)." \n";
                exit;
            }
        }

        echo "\n--\n";
    }

    echo "\n";
}


Improve query speed for large, wide table I have created a medical claims database using PostgreSQL 9.4.1 and pgAdmin III. There is one table with ~61 million rows and 103 columns. The table has the structure given below. It is static and will not be updated. (Most columns are omitted here to save space.)

    CREATE TABLE claims
   (
     spending double precision,
     releaseid bigint NOT NULL,
     priceinclude bytea,
     costsharing double precision,
     hcciprofode bytea,
     memberlinkeid bigint,
     age integer,
     orgid integer,
     gender character(1),
     membercity character varying(22),
     memberstate character(2),
     zip character varying(5),
     principaldiagnosis character varying(6),
     CONSTRAINT claims_pkid PRIMARY KEY (releaseid)
   )
   WITH (
     OIDS=FALSE
   );

Indexes:
"claims_pkid" PRIMARY KEY, btree (releaseid)
"claims_age_idx" btree (age)
"claims_memberlinkeid_idx" btree (memberlinkeid)
"claims_principaldiagnosis_idx" btree (principaldiagnosis)
"claims_serviceprovider_idx" btree (serviceprovider)
"claims_zip_idx" btree (zip)

The indexes shown above are the only ones I've added so far. I am running the database using Windows 7 with the following hardware:

64-bit Windows 7
32.0 GB RAM
Intel Core i7-4770 CPU @ 3.40 GHz

My main question involves what I can do to improve query speed without buying a dedicated server. I am trying to demonstrate a proof-of-concept to my supervisors to show them that SQL would be faster than the statistical package the currently use, STATA. I have optimized the default settings of PostgreSQL as follows:

shared_buffers=8GB
effective_cache_size=24GB
work_mem=1GB
maintenance_work_mem=1GB
checkpoint_segments=128
checkpoint_completion=0.9
wal_buffers=-1
default_stat_target=500

However, the queries are still slow. For example, the following query

EXPLAIN ANALYZE VERBOSE
SELECT * FROM claims
WHERE gender='F'
AND age BETWEEN 16 AND 20
LIMIT 100000;

returns this:

Limit  (cost=0.00..226622.43 rows=100000 width=427) (actual time=0.029..9509.028 rows=100000 loops=1)
Output: spending, releaseid, priceinclude, costsharing, hcciprofcode, htype,
hcciotpcode, drgmpr, medsurg, typecode, claimcat, memberlinkeid, age, orgid,
insurancetype, gender, membercity, memberstate, zip, admissiondate, serviceprovider,
servicenpi, ser (...)"
->  Seq Scan on public.claims  (cost=0.00..4778859.76 rows=2108732 width=427)
(actual time=0.028..9497.420 rows=100000 loops=1)"
Output: spending, releaseid, priceinclude, costsharing,
hcciprofcode, htype, hcciotpcode, drgmpr, medsurg,
typecode, claimcat, memberlinkeid, age, orgid,
insurancetype, gender, membercity, memberstate,
zip, admissiondate, serviceprovider, servicenp (...)"
Filter: ((claims.age >= 16) AND (claims.age <= 20) AND (claims.gender = 'F'::bpchar))"
"Rows Removed by Filter: 3194024"
"Planning time: 0.190 ms"
"Execution time: 9519.455 ms"

Should I be creating a new view or a smaller table to access frequently used columns? I have never created a database this large so I am quite perplexed as to what to do!

UPDATE: My goal is to be able to query this database in a reasonable amount of time for exploratory data analysis. 15 seconds for queries is reasonable; 5 minutes for queries on the whole table is not. I am not sure how best to deal with this large table in order to achieve that goal, which is what I would like help with. (Apologies if this is a little vague.)

For your example query:
         SELECT * FROM claims
         WHERE gender='F'
         AND age BETWEEN 16 AND 20
         LIMIT 100000;
try to add index on age.
But I suggest to use "SQL SERVER PROFILER" and "Database Engine Tuning Adviser" to monitor your queries, and then add suitable indexes, and make suitable changes.


How to create a correct entity-relationship diagram (ERD) about a video game profits scenario I'm starting to build my application database, and I'm really new to the this world, so I have a lot of doubts about my ERD and if its entity types and relationships are ok.

This is an application that calculates how much me and my friends profit in a video game and saves all the data so we can check it later. I'll explain it a bit so you can understand the ERD.

We want to save this data about every Hunt:

Where the HUNT was (RESPAWN)
How many people we were (1 to 4)
The date of the HUNT
How much everyone WASTE (Knight, Druid, Paladin, Sorcerer and the
Total)
The loot value
The HUNT balance
How many profit we did (each one of us)
The amount of money everyone have to recieve (paying waste + profit
-> TRANSFER)
And if the HUNT is already paid or not

I'm already doing all the math and saving this data in a .txt file for the moment, but I want to take the next step and build a decent database, and to make sure the following diagram is ok:

Are some relationships wrong? Do you think I should add more entity types, properties, etc.?

A question I have is what is the significance of the _EK/_ED columns? I can assume they are separate columns for each person in your party? If that is so, this design would have a lot of NULL values for whenever there isn't 4 people in a hunt, and will outright prevent accounting for a 5 person hunt. I hesitate to suggest a more flexible design until I can better understand what exactly each column is for. I would be happy to expand on what you have if you could post some sample data, such as from a hunt or two, along with a quick explanation of where the information is coming from.

I would also review the datatypes chosen. SMALLINT is fine for key id fields, but it would be best to choose a DATE or DATETIME for your hunt date, instead of varchar. The Paid column has two issues that I see. Including a question mark is not encouraged, instead keep it to alpha-numeric and maybe underscores. If the only two options for that field are Yes/No, then it can be a bit datatype. That way you only store 0 for no, and 1 for yes. That saves a lot of space compared to varchars, and prevents you from having "Yes","YES", and "yes", which are all seen as different depending on your chosen software.


Switching values in a column with one update statement You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one update query to resolve this.

This question was asked at a recent interview I had, and I'm going into more interviews that may have similar questions so I wanted to get an idea of how to handle this.

You want to use a CASE expression of some type.

In SQL Server the code would look like this:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  WHEN gender = 'W' THEN 'M'
                  ELSE gender END

Edit:   As stated in the comments (and some of the other answers) the ELSE isn't necessary if you put a WHERE clause on the statement.

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  WHEN gender = 'W' THEN 'M' END
WHERE gender IN ('M','W')

This avoids unnecessary updates.  The important thing in either case is to remember that there are options other than M & W (NULL for example) and you don't want to put in mistaken information.  For example:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  ELSE 'M' END

This would replace any NULLs (or other possible genders) as 'M' which would be incorrect.

A couple of other options would be

/*Simple form of CASE rather than Searched form*/
UPDATE TableName
SET    gender = CASE gender
                  WHEN 'M' THEN 'W'
                  WHEN 'W' THEN 'M'
                END
WHERE  gender IN ( 'M', 'W' );

And a more concise

/*For SQL Server 2012+*/
UPDATE TableName
SET    gender = IIF(gender = 'M', 'W', 'M')
WHERE  gender IN ( 'M', 'W' );

You could perform this update using a case expression.

UPDATE names_table
   SET names_table.gender = ( CASE
                                  WHEN names_table.gender = 'M'
                                    THEN 'W'
                                  ELSE
                                      names_table.gender = 'M'
                              END)

I'd suggest running your update statement within a transaction and adding a simple query such as:

SELECT n.gender, *
FROM names_table

in order to check the results you'll be getting. Performing the transaction with a rollback and switching it to a commit when your results line up with what you're expecting.


Using Row_Number to find consecutive row count I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row

If my data looks like this

724
727
728
733
735
737
743
747
749

the resulting data with a consecutive row count column would look like this

724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
748 1

I've done it using a looping function but I'm trying to figure out using a cte. Here is a sample of my latest attempt

DECLARE @d TABLE ( signal INT )
INSERT  INTO @d
        SELECT  724
        UNION
        SELECT  727
        UNION
        SELECT  728
        UNION
        SELECT  729
        UNION
        SELECT  735
        UNION
        SELECT  737
        UNION
        SELECT  743
        UNION
        SELECT  744
        UNION
        SELECT  748 ;
WITH    a AS ( SELECT   signal,
                        ROW_NUMBER() OVER ( ORDER BY signal ) AS marker
               FROM     @d
             ) ,
        b AS ( SELECT   a1.signal,
                        CASE ( a1.signal - a2.signal )
                          WHEN 1 THEN 1
                          ELSE 0
                        END consecutiveMarker
               FROM     a a1
                        INNER JOIN a a2 ON a2.marker = a1.marker - 1
             )
    SELECT  *
    FROM    b

Produces these  results

signal  consecutiveMarker
727 0
728 1
729 1
735 0
737 0
743 0
744 1
748 0

The first obvious issue is missing the first signal in a series. Barring that, I thought I could then pass this to another cte with a row_number partitioning on the consecutiveMarker. That didn't work because it partitioned it as one  partition. I couldn't find a way to indicate to the partitioning method that one series is separate from the next

Any help is appreciated.

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number

WITH DATA(C) AS
(
SELECT 724 UNION ALL
SELECT 727 UNION ALL
SELECT 728 UNION ALL
SELECT 729 UNION ALL
SELECT 735 UNION ALL
SELECT 737 UNION ALL
SELECT 743 UNION ALL
SELECT 744 UNION ALL
SELECT 747 UNION ALL
SELECT 749
), T1 AS
(
SELECT C,
       C - ROW_NUMBER() OVER (ORDER BY C) AS Grp
FROM DATA)
SELECT C,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY C) AS Consecutive
FROM T1

Returns

C           Consecutive
----------- --------------------
724         1
727         1
728         2
729         3
735         1
737         1
743         1
744         2
747         1
749         1

As usual with such problems, it is very easy to accomplish in Java or C++ or C#.

If you really need to do it in the database, you can use an RDBMS with fast cursors, such as Oracle, write a simple cursor, and enjoy fast performance without having to write anything complex.

If you need to do it in T-SQL, and you cannot change database design, Itzik Ben-Gan has written up several solutions in "MVP Deep Dives vol 1", and some new solutions using OLAP functions in his new book about window functions in SQL 2012.

Alternatively, you can add another column consecutiveMarker to your table, and store precalculated values in it. We can use constraints to ensure that pre-calculated data is always valid. If anyone is interested, I can explain how.


Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script.

INSERT INTO params (extension, type, subtype, paramvalue)
VALUES ('12345', 'FRNR1', 'FORWARD', '24268')
ON DUPLICATE KEY UPDATE paramvalue = VALUES(paramvalue);

The purpose is that params_exceptions contains records which prevents certain records in params to be updated with the new data (or using the data in params_exceptions afterwards for that record).

I've been messing around a bit and I was thinking of combining WHERE NOT EXISTS within the ON DUPLICATE KEY UPDATE? Of course all of the above is possible by using sequential statements, but it's possible that the script would run at the same time and I can't use transactions for now.

I was thinking of the following statement, but I can't seem to figure it out:

INSERT INTO params (extension, type, subtype, paramvalue)
SELECT *
FROM (SELECT '12345', 'TEST', 'TEST2', '24268') AS tmp
WHERE NOT EXISTS (
    SELECT 1, extension
    FROM params_exceptions t
    WHERE extension = '12345' AND
          type = 'FRNR1' AND
          subtype = 'FORWARD'
) LIMIT 1
ON DUPLICATE KEY UPDATE subtype=subtype, paramvalue=t.extension;

Can someone give me some advice here?

The problem in your query is this part:

ON DUPLICATE KEY UPDATE
    --
    paramvalue = t.extension ;

The alias t is not visible in that part of the query.

However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345')  are the same, then you you can use the VALUES (extension) in the UPDATE part:

Several other improvements can be made:

no need for a derived table (tmp), you can use the internal dual table.
the UPDATE subtype = subtype is redundant
the inetger values (eg. 12345) need not to be passed quoted. They are not string literals, they are integer values.

The statement becomes:

INSERT INTO params
  (extension, type, subtype, paramvalue)
SELECT 1234567, 'TEST', 'TEST2', '24268'
FROM dual
WHERE NOT EXISTS
  ( SELECT 1
    FROM params_exceptions t
    WHERE extension = 1234567
      AND type = 'FRNR1'
      AND subtype = 'FORWARD'
  )
ON DUPLICATE KEY UPDATE
  paramvalue = VALUES (extension) ;

I ended up creating the following trigger:

DECLARE paramvalue_ VARCHAR(50);
DECLARE msg VARCHAR(255);

CREATE TEMPORARY TABLE IF NOT EXISTS params_temp(extension integer, type varchar(50), subtype varchar(50), paramvalue varchar(50), active integer, PRIMARY KEY (`extension`,`type`,`subtype`), UNIQUE KEY `extension_type` (`extension`,`type`));
DELETE FROM params_temp;

INSERT INTO params_temp(extension, type, subtype, paramvalue, active) VALUES (NEW.extension, NEW.type, NEW.subtype, NEW.paramvalue, NEW.active);

INSERT INTO params_temp(extension, type, subtype, paramvalue, active)
SELECT extension, type, subtype, paramvalue, active FROM params_exceptions WHERE extension = NEW.extension AND subtype = NEW.subtype AND type = NEW.type
ON DUPLICATE KEY UPDATE paramvalue=params_exceptions.paramvalue;

SELECT paramvalue INTO paramvalue_ FROM params_exceptions WHERE extension = NEW.extension AND subtype = NEW.subtype AND type = NEW.type;
IF paramvalue_ != "" THEN
IF OLD.paramvalue != paramvalue_ THEN
  SET NEW.paramvalue = paramvalue_;
ELSE
    SET msg = "Update overriden.";
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
END IF;


How to get sum of last previous X days of everyday? Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday.

For example my graph will include

Sum of Day - 60 to 30
Sum of Day - 59 to 29
Sum of Day - 58 to 28
Sum of Day - 57 to 27

......

Sum of Day - 33 to 3
Sum of Day - 32 to 2
Sum of Day - 31 to 1

So that it will give me 4 results ( Day 8 to 4, Days 7 to 3, Day 6 to 2, Day 5 to 1 ). It will be sum of number_of_sales

CREATE TABLE sales (
  sales_id          int  PRIMARY KEY,
  sales_create_date timestamp,
  number_of_sales   int
);

INSERT INTO sales (sales_id, sales_create_date,number_of_sales)
VALUES
  (0, '2015-05-02 05:30:40',100),
  (1, '2015-05-02 00:00:00',200),
  (2, '2015-05-03 00:00:00',300),
  (3, '2015-05-03 00:00:00',400),
  (4, '2015-05-05 00:00:00',500),
  (5, '2015-05-06 00:00:00',600),
  (6, '2015-05-07 00:00:00',700),
  (7, '2015-05-08 00:00:00',800),
  (8, '2015-05-08 00:00:00',900);

This is the final result that I need from this query

In my real case, I will get sales of last 60 days, and it will return me 30 records but summing up previous 30 days every day. In example I gave you rather than analyzing 60 days, I analyzed 8 days, which will product me 4 entries.
Plase don't spend time by getting the date of last entry. Just think the start date is today always.

Here is what I think you want,

SELECT days.d::date, sum(sales.number_of_sales)
FROM (
        SELECT min(sales_create_date::date), max(sales_create_date::date)
        FROM sales
) AS minmax
CROSS JOIN LATERAL generate_series(minmax.min, minmax.max, '2 days')
        AS days(d)
LEFT OUTER JOIN sales
        ON sales_create_date >= days.d
        AND sales_create_date < (days.d + '2 days')
GROUP BY days.d;

Notice you have to put the window date twice in the above.

What we're doing is,

Selecting min and max dates for the table
Generating the start for the range on the window
Joining back to sales with the range start and the end interval
Grouping and selecting the count(*) in the window.

Result,

     d      | sum
------------+------
 2015-05-02 | 1000
 2015-05-04 |  500
 2015-05-08 | 1700
 2015-05-06 | 1300
(4 rows)

I just learnt that what I want is moving average in statistics.
So that I googled articles and found that there are nearly built in functions for these operations.

Something like

SELECT
       SUM(ad.number_of_sales)
            OVER(ORDER BY ad.sales_create_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS avg_downloads
FROM sales ad
;

I just drop the link of the article since it has a lot of goodies.

https://www.compose.com/articles/metrics-maven-calculating-a-moving-average-in-postgresql/


What is difference between Oracle Apps and Oracle Application Server? I am very new to oracle, could anybody tell me what Oracle Apps and Oracle Application Server are.

Oracle Applications means software that helps you runs your enterprise businesses such as Accounting, Humans Resource, or Financials. But the Oracle Applications Server is the technology that, mostly installs on the server, integrates others software to work together. It can be called Middleware.

The first example is like Peoplesoft or SAP ERP. The latter is like Apache HTTP Server, Tomcat, Glassfish.

Oracle Application server = it is a platform to build any application(web based)
oracle applications = it is used to run the application


I'm concerned that we may not have a clear picture of how a group is defined.  I only say this because, depending on some unstated conditions, the dates above will either form one giant single group, or 3 groups where one group dominates the set.

Missing grouping conditions?

1) Does this 15 day rule cascade?  If a record Y starts 10 days after another record X, and then there is another record Z started 10 days after that, then does this form one group of three records X,Y,Z, or two groups each containing two records X,Y and Y,Z? I made the assumption that the 15 day rules cascades to form larger groups.

2) Are the dates inclusive?  For example, if one record has a start date and then a dead date many months later, do all days within that range get merged into the group? I treat both possibilities in my quick analysis below.

Potential Groupings

So, if we begin with id 7714, we see that the start date is 1/27.  Clearly, the next entry 7882 starting on 1/28 falls in this group. Notice however that 7882 ends on 5/15, so anything which starts within 15 days of 5/15 must be added to the group.

Thus, 19690through 21210 get added to the group, which via cascading leads to 21918 being subsequently added to the group.  The cascading has consumed nearly all entries in the set. Call this GROUP A.

If, however, the grouping is date inclusive as well, all entries from 13190 up to 17124 must also belong to GROUP A, and now all ids are in a single group.

If the dates from GROUP A aren't inclusive, but actually strictly adhere to the '15 day after' rule with cascading, then instead you would have a second group composed of 13190 through 14020, and a third group with a single entry, 17124.

Essentially, my question is, do any of these match your intended grouping, or is there some other information we're missing in the group definition? I'm sorry for such a long-winded answer, but it doesn't appear that your tentative requested output meets your grouping definition.

With clarifications, I'm sure that we can sort this problem out.


"Unrelated" INSERT and UPDATE blocking each other Repro scenario:

CREATE TABLE test (
  ID int IDENTITY(1,1),
  mykey nvarchar(255) NOT NULL,
  exp_date datetime,
PRIMARY KEY (ID));
GO

CREATE INDEX not_expired_keys ON test (exp_date, mykey);

INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);

I start transaction 1:

-- add key B
BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);
...

and then execute transaction 2 in parallel:

-- expire key A
BEGIN TRANSACTION;
UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking
ROLLBACK;

As it turns out, transaction 1's uncommitted INSERT blocks transaction 2's UPDATE, even though they affect disjoint sets of rows (mykey = 'B' vs. mykey = 'A').

Observations:

The blocking also occurs on the lowest transaction isolation level READ UNCOMMITTED.
The blocking goes away if I put a unique index on mykey. Unfortunately, I cannot do that, since key names can be reused once a key has expired.

My questions:

(Out of curiosity:) Why do these statements block each other even on the READ UNCOMMITTED level?
Is there an easy and reliable way to make them not block each other?

Lets take a look on the execution plans.

1st query - Insert

BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);

And its execution plan

We see that sql server is doing Clustered Index Insert operation.

Now lets take a look on update

BEGIN TRANSACTION;
UPDATE test
SET exp_date = GETDATE()
WHERE exp_date IS NULL AND mykey = 'A' -- <-- Blocking

And its execution plan

SQL Server scans the Clustered Index of the table, and put U lock on it, even if it can choose another index to find the matching rows. The reason is, because we have only 1 row in the table, and SQL Server Optimizer find easier to scan the Clustered Index instead of searching data in the non-clustered index.

But what if we force the sql server to use non-clustered index?

BEGIN TRANSACTION;
UPDATE test
SET exp_date = GETDATE()
FROM test WITH(INDEX = not_expired_keys)
WHERE exp_date IS NULL AND mykey = 'A' -- <-- No Blocking!!!

And its execution plan

I think if we put more rows in the table SQL Server will choose the non-clustered index for finding the rows that must be updated, and there will be no blocking.

This is essentially a bug in SQL server. Rather than implement predicate locks, MS took the cheap way out of blocking a range on one of the indexes used to select the rows from the table. If it didn't use an index (or if there's no index) it will block a range on the table instead.

Yeah forcing the index will cause it to lock on the index instead; however it always takes page locks. Page locks are bad for lock hierarchies; unrelated rows that happen to be on the same index page or table page will be blocked. There is no way around this with two writing statements. If one of your statements is reading, WITH(ROWLOCK) on the reader will bypass.

I opened a case with MS that lasted two months before they outright refused to fix it.


Completely uninstall Oracle server on Linux I have searched lot in Google for how to uninstall Oracle on Linux, but I can't figure it out.

I think that I need to reinstall the server because I get a lot of errors when I try to startup the server.

Does any one know how to completely remove all data belong to an Oracle server?

Oracle provides extensive documentation about how its database software can be uninstalled:

Removing Oracle Database Software - 11.2 - this is the new method for uninstallation, using the deinstall tool. This includes instructions on how to download the tool if your install failed and you can't use it directly from there.
Removing Oracle Database Software - 11.1 - this has step-by-step, part by part clean removal instructions for the various parts of the product you could have installed. These methods still work on 11gR2.

If there's anything of any value in the databases on that machine, a full backup is a must. Ideally make that data available on other database(s) on a separate machine before you start uninstalling anything – that way you're sure you won't actually be losing data even if you re-image the OS. (Re-installing the OS is sometimes the most efficient removal tool if you have good procedures/tooling for that.)

But if you're just having issues starting a database, a re-install isn't usually the right thing to do. Fixing those errors is usually much more productive – you'll actually learn something about how the software works along the way, and that's valuable.
To get help with that, if your searches in the Web and the Oracle support site have not helped so far, you could post a question here that includes:

The exact error message you get when you try starting up your database (or whatever action it is you're currently stuck with)
The relevant logs from your alert.log file (located usually in $ORACLE_BASE/diag/rdbms/<lowercase db name>/<SID/trace/alert_<SID>.log)
What changes you made since the last time the database worked
Anything else that you have noticed looks off

The more drastic option for uninstallation is simply to stop all services, and remove (delete) all the files that were installed. Be very careful if you go that route, as usual - don't nuke unrelated files. And make sure you have backups you know you can restore if there's anything useful in the database(s) you have installed.
The main directories/files that need cleaning up are:

$ORACLE_BASE and anything under it
$ORACLE_HOME and anything under it (you might have several)
/etc/ora* - this is the last thing to delete. /etc/oratab contains a list of your Oracle homes, so use it as a reference.
Scan your system for files belonging to the Oracle owner and dba group and remove what is now unnecessary.

Another good reference to uninstall oracle: http://www.dba-oracle.com/t_uninstall_software.htm


dangers of using SHRINKFILE in sql server 2008 I'm trying to shrink the file size of a massive log file and was wondering how I estimate the size of the log file and what the dangers in running this are

If you use the full or bulk logged recovery model,
I advise that you take a look at this post for shrinking the log file. First perform a full database backup as switching the recovery models will break the log chain. Also chose a reasonable initial size for the log file, and generally speaking I use an increment in MB rather than percents.

What is your database's recovery model?
If FULL you can Shrink(Back up with no log). if recovery model is simple, SQL manage the transaction log file.

You can shrink log file successfully once the transaction log was commit(Full recovery model).
As far as i know, i have concern once your transaction log file get auto grow again, it might takes time to reserve space. You might get problem IO overhead.


Correcting a database that has had indexes and statistics created on it willy-nilly I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and statistics for said individual cases with no concern for the impact on the DB overall.
How do I remedy this situation?

I myself am not a DBA but am interested in doing this properly -

Should I drop all indexes and statistics and start over?
Is there a tool out there that can look at the entirety of SQL operations and recommend which indexes and stats to delete, update or create.
Any recommended standard procedures for this situation, I can't imagine I am unique in encountering this scenario

I'm on SQL Server 2012

Consider following the STOP pattern that's used in first aid training. Stop, think, observe and plan. That is, don't rush into action. Start by gathering some information, draft an action plan and only then start doing things. If there's an electricity accident, one doesn't touch the victim before cutting power. Likewise, one shouldn't start dropping indexes without knowing what use those would be.

Is the system working well enough? How can you tell? That is, what's the definition of "well enough?"

Do you have a set of business rules like "Report X must be ready within Y minutes" and "Daily ETL must be finished by 0600 as cube update starts at 0610." Maybe there's something like "Database backup must be finished at 0200, as disk-to-tape backup starts at 0300." Be aware there might be some ad-hoc rules like "CFO needs $stuff NOW" that are usually not documented at all.

If you don't have any business constraints, you need to discuss with your boss, other users and business about such constraints.

If you've got a set of rules, that's great news. Now, see if the system respects those limits at the moment.

After you know the rules for the system, maybe it would be a great idea to do some basic health check. Brent Ozar's sp_Blitz is widely used free tool. There's warnings if backups are not handled properly, lack of recent integrity checks, configuration issues and a lot more.

you can search for missing and unused indexes
There are a couple of dynamic views in SQL that you can use!

Here you can find some scripts that can help you

Missing Indexes

Unused indexes


new Neo4j install: "No authorization header supplied" even after editing config file I just installed Neo4j today to play around on my Ubuntu EC2 instance, and I'm having two problems. First, the ubuntu install did not create a neo4j command so I'm not able to issue commands from the cli. Second, when I try this sample command to add a node to the database, I get the error

curl -H "Accept: application/json; charset=UTF-8" -H "Content-Type: application/json" -X POST http://localhost:7474/db/data/cypher -d '{
  "query" : "CREATE (n:Person { name : {name} }) RETURN n",
  "params" : {
    "name" : "Foo"
  }
}'

I get this error:

{
  "errors" : [ {
    "message" : "No authorization header supplied.",
    "code" : "Neo.ClientError.Security.AuthorizationFailed"
  } ]

I read the docs here, and I modified the config file per these instructions:
http://neo4j.com/docs/stable/rest-api-security.html

I updated the config file so that it should not require authentication:

dbms.security.auth_enabled=false

But still my curl requests to my localhost Neo4j server are blocked. All I can think is maybe neo4j needs a restart, but since I have no neo4j command, I can't use neo4j restart. I logged into the neo4j shell, but in the shell's list of commands there is no restart.

So my questions are:

(1) How can I restart neo4j?

(2) Where is my neo4j command/how can I create one following a standard ubuntu install?

(3) How could I create a username/password if I wanted to keep the access authentication in place?

Thanks.

You restart Neo4j by calling /path/to/neo/bin/neo4j restart
or by  /etc/init.d/neo4j restart if you installed it as a service.

The neo4j startup script is in the same path as the shell.

You can restart the Neo4J service using:

sudo service neo4j-service restart

from the shell.


Difference between candidate key & primary key What is the differences between candidate key and primary key?

I think this post does a pretty good job of explaining the difference.

To summarize:

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key.

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as a unique key in the database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as a Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

Candidate keys can take null values whereas primary keys can never be null. That is also one of the significant differences.


How can I make only *one* user controlled database How can I system admin let it be me, but another users and admins must be cannot access my database.
My database located on customer server. I deleted all sql users, there's only sa user. This persons don't know my sa password. But this persons created sa similar users, and can access my database. I want block this users to my db.

You can only really hope to log this and raise alerts.  Users who can administer the hardware can always stop the service and bring a database up in single user mode etc.  They can give themselves elevated permissions etc.

It's a common requirement to segregate administrative duties so that server admins cannot see confidential files or confidential data.

However, because every administrative role can typically elevate their rights or create other users, requiring users to use least privilege accounts for their daily work and auditing what is done with elevated accounts is really the only way to deal with it.

Ensure there are no unwanted groups like BUILT-IN\ADMINISTRATOR granted rights to the server & therefore your database, as that is a common backdoor for your windows Admin to get into your databases.
Explicitly deny the user access to your database

use [PrivateDatabaseName];
revoke connect from guest;
revoke connect from user1;
revoke connect from user2;


Is there a reason to store currency amounts as fractions instead of decimals? I've been reviewing GnuCash's DB design and I found that they are storing amounts as two 64-bit integers, the numerator and denominator of a fraction.

Is there some advantage from a DB perspective to storing financial data this way?  I always learned (in MS SQL Server) to use decimal(18,4) for currencies values.

There's no advantage from a DBMS perspective, but it may allow better data management, if you are dealing with some unusual form of currency.  For example, if you had a currency where the basic unit is the pound, and the pound is composed of 20 shillings, and each shilling is composed of 12 pennies, it would sometimes be impossible to represent an exact number of pennies as a decimal fraction of some number of pounds.

If penny amounts were represented as approximations, round off error would begin to creep in when arithmetic operations were performed.

Such a currency may seem unbelievably quirky, but this is the way British currency worked when Elizabeth II ascended to the throne.  There may be currencies in the world today that still work in a similar way.

This is explained in detail in this post (here related to java development, but the goal is to explain why not to use float or double values for representing money):
https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency


Trying to find the last time that a value has changed I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.

Records are inserted into this table periodically. The ID will always stay the same but occasionally the value will change.

How can I write a query that will give me the ID plus the most recent time the value has changed? Note: the value will always increase.

From this sample data:

  Create Table Taco
 (  Taco_ID int,
    Taco_value int,
    Taco_date datetime)

Insert INTO Taco
Values (1, 1, '2012-07-01 00:00:01'),
        (1, 1, '2012-07-01 00:00:02'),
        (1, 1, '2012-07-01 00:00:03'),
        (1, 1, '2012-07-01 00:00:04'),
        (1, 2, '2012-07-01 00:00:05'),
        (1, 2, '2012-07-01 00:00:06'),
        (1, 2, '2012-07-01 00:00:07'),
        (1, 2, '2012-07-01 00:00:08')

The result should be:

Taco_ID      Taco_date
1            2012-07-01 00:00:05

(Because 00:05 was the last time Taco_Value changed.)

These two queries rely on the assumption that Taco_value always increases over time.
;WITH x AS
(
  SELECT Taco_ID, Taco_date,
    dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
    qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
  FROM dbo.Taco
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
  FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y
WHERE rn = 1;

An alternative with fewer window function madness:
;WITH x AS
(
  SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

Examples at SQLfiddle

Update
For those keeping track, there was contention over what happens if Taco_value could ever repeat. If it could go from 1 to 2 and then back to 1 for any given Taco_ID, the queries will not work. Here is a solution for that case, even if it isn't quite the gaps & islands technique that someone like Itzik Ben-Gan may be able to dream up, and even if it isn't relevant for the OP's scenario - it may be relevant to a future reader. It's a little more complex, and I also added an additional variable - a Taco_ID that only ever has one Taco_value.
If you want to include the first row for any ID where value didn't change at all in the entire set:
;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT
  main.Taco_ID,
  Taco_date = MIN(CASE
    WHEN main.Taco_value = rest.Taco_value
    THEN rest.Taco_date ELSE main.Taco_date
  END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

If you want to exclude those rows, it's a bit more complex, but still minor changes:
;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT
  main.Taco_ID,
  Taco_date = MIN(
  CASE
    WHEN main.Taco_value = rest.Taco_value
    THEN rest.Taco_date ELSE main.Taco_date
  END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause *****
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

Updated SQLfiddle examples

Could this be as simple as the following?

       SELECT taco_id, MAX(
             CASE
                 WHEN taco_value <> MAX(taco_value)
                 THEN taco_date
                 ELSE null
             END) AS last_change_date

Given that taco_value always increases?

p.s. I am quite beginner SQL myself, however, learning slowly but surely.


SQL Server 2008/R2 recovery model Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the Simple Recovery model.

Quite often and for certain practical reasons many databases are created using SSMS. However mistakes can be made and the operator can forget to specify the Simple Recovery model. This leads to a "suprise" a few days later when the box is struggling with disk space due to three or four 60GB log files that have never been truncated.

I can make the Simple Recovery model the default setting for new databases by configuring the recovery model on the model database. However, is this recommended, if I do this could it come back and bite me in any way in the future?

I see one of three options here:

1) you can have a templated script to create databases that explicitly includes the recovery model.

2) you can set the model database to simple and not have to worry about this.

3) you can hope everybody remembers, which seems like what you are doing. (not recommended)

I would personally go with number two. That's what the model database is there for.

The safe bet is putting your DB in full mode, but then you have the log growth issue. Now there are some options:

Put a limit on the max size of your log file. This will affect operations once this limit is hit. The benefit is that it will prevent scenarios where the disk runs out of space and then you will have bigger issues.
Create alerts that fire once your log file grow above a watermark and then manage it.
Schedule log shrink jobs. Log shriking hurts the performance of your DB. I would not suggest it.

Being the DBA you should use all options that helps with a recovery. It also depends on your SLA with the business.

All that being said, I manage a few DBs in simple mode. This is because of disclaimers outlined in the SLA. The business decided on not spending on drives for the log files (you can take a horse to the water, but you can't make it drink). The business manages the backup, restore, and DR. There was a DR, and the money lost was greater than what it would have cost in extra disk space.


How to link my Azure VM running SQL server to my on-premise domain or Active Directory? I'm kinda new to cloud/azure. I have set up a VM running SQL Server using default templates and settings like dynamic IP and name etc.

Can I connect it to my on premise domain or AD?
What are the exact steps and prerequisites for it?

Yes you can.  You'll need to have a site to site VPN between Azure and your environment.

Once that's one you've got a couple of options.

You can change the DNS settings on the vNet to point to your office domain controllers.
Add domain controllers to Azure (with static IPs), then change the vNet to use the Azure DCs as for authentication.

Once one of those is done, reboot your VM and you should be able to add it to your domain. We do this for clients all the time, and we've done it with our
lab, and it works perfectly.

The product Azure Active Directory isn't needed to do this at all.

As mentioned by in the comment by SqlWorldWide, syncing your on-premise AD to Azure AD is a good option. One of the benefits of doing this is that when there is an interruption in the connectivity between your on-prem environment and Azure, you will still be able to authenticate against Azure AD.

Another option we have implemented at a few clients is creating a permanent VPN between your Azure environment and on-prem environment using this guide: Create a Site-to-Site connection in the Azure portal. This allows you to treat the VPN as if it was just another server in your on-prem environment. The challenge with this approach is working with your networking team to set up your organisation's firewall to establish the IPSec VPN.


sql server database sharding - what to do with common data / non sharded data We have a very large scale enterprise level database.  As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very heavy and continues to grow heavier the larger the company grows.  sql proc optimization has been performed and hardware has already been scaled up to a very high level.

We are looking to shard the database now to ensure that we can handle company growth and future loads.

We have decided what particular data should be sharded. It is a subset of our database which is highly utilized.

However, my question is regarding the non sharded data which is common/universal. An example of data like this may be an  Inventory table for instance or possibly an Employee table, user table etc .

I see two options to handle this common/universal data:

1) design 1 - Place the common/universal data in an external database. All writes will occur here. This data will then be replicated down to each shard allowing each shard to read this data and inner join to this data in t-sql procs.

2) design 2 - Give each shard its own copy of all common/universal data.  Let each shard write locally to these tables and utilize sql merge replication to update/sync this data on all other shards.

concerns about design #1

1) Transactional issues: If you have a situation in which you must write or update data in a shard and then write/update  a common/universal table in 1 stored proc for instance, you will no longer be able to do this easily.  The data now exists on seperate sql instances and databases.   You may need to involve MS DTS to see if you can wrap these writes into a transaction since they are in a separate database. Performance is a concern here and possible rewrites may be involved for procs that write to sharded and common data.

2)a loss of referential integrity.  Not possible to do cross database referential integrity.

3) Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards.

4). increased database trips.  Like #1 above, when you run into a situation in which you must update sharded data and common data you are going to make multiple round trips to accomplish this since the data is now in separate databases.  Some network latency here but I am not worried about this issue as much as the above 3.

concerns about design #2

In design #2 each shard gets its own instance of all common/universal data. This means that all code that joins to or updates common data continues to work/run just like it does today. There is very little recoding/rewriting needed from the development team. However, this design completely depends on merge replication to keep data in sync across all shards.  the dbas are highly skilled and are very concerned that merge replication may not be able to handle this and should merge replication fail, that recovery from this failure is not great and could impact us very negatively.

I am curious to know if anyone has gone with design option #2. I am also curious to know if i am overlooking a 3rd or 4th design option that I do not see.

thank you in advance.

Your question focused on this:

  However, my question is regarding the non sharded data which is
  common/universal. An example of data like this may be an Inventory
  table for instance or possibly an Employee table, user table etc .

When you're doing sharding, and you have data that all of the shards need to see, you have to classify that data with a few attributes:

Does it change frequently? In your examples, you listed Inventory, Employee, and User. Typically inventory changes very fast, but the Employees records only change periodically (say, a few hundred updates per day).

How much delay can each shard tolerate? Even though the Inventory may constantly be changing, you can typically tolerate a large amount of delay (minutes or even hours) on a table like that. If you're selling unique items with a very limited quantity that you can never restock (think original artworks), then you don't shard that data at all - you only query the original database. However, in most online stores, you're not selling out of every item every day, and you're going to restock things quickly anyway, so you don't really need up-to-the-millisecond counts of inventory. In fact, in most cases, you only need an In-Stock flag that's either 0 or 1, and a central process updates that flag. That way, you don't have to push every up/down bump of item count out to every shard. Employee or User data, on the other hand, may need to be pushed out to every shard nearly instantaneously.

Will you be joining from the sharded tables to the non-sharded ones? Ideally, the answer here is no - you should make two separate queries to get the data, and then join them on the app side. This gets a lot harder from an app perspective, but it gives you the capability to get the freshest data from each source.

Is this original data, or copied? Another way to think of this question: what do you need to back up, and how frequently? Typically in a high-volume sharding environment, you want the backups to be as fast and as small as possible. (After all, you need to protect each node, and you want all of the shards to fail over to DR at the same point in time - not have some shards with newer data than others.) This means the sharded data and the non-sharded data should be in completely separate databases - even if they're on the same server. I may need constant transaction log backups of my sharded (original) data, but I may not need to back up the non-sharded data at all. It's probably easier for me to just refresh my Employees or Users table from the single source of truth rather than back it up on every shard. If all of my data is in a single database, though, I lose that capability.

Now, about your concerns:

"Transactional issues...you will no longer be able to do this easily." Correct. In sharded scenarios, throw the concept of a transaction out the window. It gets worse, too - for the sharded data, you could have one shard up and online, and another shard down temporarily due to a cluster instance failover or restart. You need to plan for failure of any part of the system, at any time.

"Not possible to do cross database referential integrity." Correct. When you split a single table out across multiple servers, you're putting your big boy pants on and telling the database server that you're taking over for tough tasks like point-in-time backups, relationships between tables, and combining data from multiple sources. It's on you and your code now.

"Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards." Correct here as well. There's no easy button for this, but once you've built this into the app, you're able to scale like crazy. I'd argue that the easier way to do this is to split the app's connections by reads.

"increased database trips." - Yes, if you break the data into multiple servers, the app is going to have to reach out to the network more. The key is to implement caching as well so that some of this data can be stored in lower-cost, higher-throughput, lock-free systems. The fastest query is the one you never make.

I've also laid out more pros and cons to dividing up multi-tenant databases here, such as performance tuning on individual shards, different backup/recovery strategies per shard, and schema deployment challenges.

A possible 3rd option. Using relational sharding (instead of black box sharding), you should be able to shard and distribute your entire database. Because it is built off of a traditional relational data model, the database knows what data is stored on what servers and thus where to find it, so all of your data can be considered 'common/universal'.  Check out dbShards as a possibility to make the entire sharding process easier.


Should I put users in admin DB or in the DB that they should have access to What would the pros / cons be of putting users that should have access to one database in the admin database instead of the database that they should have access to?

Background:

I'm a C# developer. This is the first live project using mongodb I'm working on.

I would like to know from people used to managing mongodb instances how they go about setting up users.

In this case I want three users, one for administration, one "webuser" that the intranet web application which I develop will use to read and write documents in one database and a reader user used when read access is enough. Both the readwrite and the reader user just need acces to one database. The admin user should be admin of the mongoserver. The database will be hosted on the server that hosts the web

in general not just Mongo talking in general given root/sysadmin privileges or adding all users to the admin groups is not recommended due to obvious reasons. And in mongo like advanced databases there are enriched security groups and roles can be created and assign each user as per the requirement. please refer the mongo documentation on creating user roles and assigning privileges.
https://docs.mongodb.com/manual/tutorial/manage-users-and-roles/

If you want those users to access only the one particular database and no other - then you don't have to.

Have a look here: MongoDB security reference


Different results rebuilding an index online and offline I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.

When I run the same rebuild index offline, the average fragmentation is 25%, with 4 fragments and 28 pages.

I think the FILLFACTOR is 90. The database is 77GB.  I'm not a DBA or similar, I'm a C# dev, so I'm not fully familiar with all the terms. There is no activity on this table, this is occurring in our Dev environment.

This is the query, with names redacted.

ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON,
    ONLINE = ON,
    SORT_IN_TEMPDB = ON
);

What could be causing this difference? The same situation occurs on multiple tables.

This is by no means a full answer but may move things along a bit if you were to try something similar and report your results.

I couldn't reproduce them. With the following test table

CREATE TABLE [dbo].[Table]
(
Col BIGINT
)

CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col)

INSERT INTO [dbo].[Table]
SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2

And multiple runs of the following script

USE FragTest;

DECLARE @DbccPage TABLE (
  ParentObject VARCHAR(255),
  Object       VARCHAR(255),
  Field        VARCHAR(255),
  VALUE        VARCHAR(255))

DECLARE @sp_index_info TABLE (
  PageFID         TINYINT,
  PagePID         INT,
  IAMFID          TINYINT,
  IAMPID          INT,
  ObjectID        INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(30),
  PageType        TINYINT,
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     INT,
  PrevPageFID     TINYINT,
  PrevPagePID     INT,
  PRIMARY KEY (PageFID, PagePID));

DECLARE @I INT = 0

WHILE @I < 2
  BEGIN
      DECLARE @Online VARCHAR(3) = CASE
          WHEN @I = 0 THEN 'OFF'
          ELSE 'ON'
        END

      EXEC('ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON,
    ONLINE = ' + @Online + ',
    SORT_IN_TEMPDB = ON
);')

      INSERT INTO @sp_index_info
      EXEC ('DBCC IND ( FragTest, ''[dbo].[Table]'', 2)' );

      ; WITH T
           AS (SELECT *,
                      PagePID - ROW_NUMBER() OVER (PARTITION BY PageType, IndexLevel ORDER BY PagePID) AS Grp
               FROM   @sp_index_info)
      SELECT PageType,
             MIN(PagePID) AS StartPID,
             MAX(PagePID) AS EndPID,
             COUNT(*)     AS [count],
             IndexLevel
      FROM   T
      GROUP  BY Grp,
                PageType,
                IndexLevel
      ORDER  BY PageType DESC,
                StartPID

      DECLARE @DynSQL NVARCHAR(4000)

      SELECT @DynSQL = N'DBCC PAGE (FragTest, ' + LTRIM(PageFID) + ',' + LTRIM(PagePID) + ',3) WITH TABLERESULTS'
      FROM   @sp_index_info
      WHERE  PageType = 10

      INSERT INTO @DbccPage
      EXEC(@DynSQL)

      SELECT VALUE AS SinglePageAllocations
      FROM   @DbccPage
      WHERE  VALUE <> '(0:0)'
             AND Object LIKE '%IAM: Single Page Allocations%'

      SELECT avg_page_space_used_in_percent,
             avg_fragmentation_in_percent,
             fragment_count,
             page_count,
             @Online                                                   AS [Online],
             (SELECT COUNT(*)
              FROM   @DbccPage
              WHERE  VALUE <> '(0:0)'
                     AND Object LIKE '%IAM: Single Page Allocations%') AS SinglePageAllocations
      FROM   sys.dm_db_index_physical_stats(db_id(), object_id('[dbo].[Table]'), 2, NULL, 'DETAILED')
      WHERE  index_level = 0

      DELETE FROM @sp_index_info

      DELETE FROM @DbccPage

      SET @I = @I + 1
  END

I consistently got results like

Online = OFF

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       119         119         1           NULL
2        2328        2351        24          0
2        2352        2352        1           1
2        2384        2392        9           0

SinglePageAllocations
----------------------

(0 row(s) affected)

avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
98.8139362490734               0                            2                    33                   OFF    0

Online = ON

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       115         115         1           NULL
2        114         114         1           0
2        118         118         1           1
2        2416        2449        34          0

SinglePageAllocations
-----------------------
(1:114)
(1:118)

avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
97.4019644180875               2.85714285714286             2                    35                   ON     2

At least in the test I did the differences between the two balanced out fragmentation wise (though similarly to your test I did find that rebuilding the index online led to a higher page count.).

I found that the Online = OFF version always used uniform extents and had zero single page allocations whereas the Online = ON always seemed to put the index root page and first index leaf page in mixed extents.

Putting the first index leaf page in a mixed extent and the rest in contiguous uniform extents causes a fragment count of 2.

The Online = OFF version avoids the fragment caused by the lone index leaf page but the contiguity of the leaf pages is broken by the index root page that shares the same extents and this too has a fragment count of 2.

I was running my test on a newly created database with 1 GB of free space and no concurrent activity. Perhaps the Online = OFF version is more vulnerable to concurrent allocations causing it to be given non contiguous uniform extents.

Fragmentation depends on your file system, locally you may have millions of files that take most disk space and there's a few free spaces on a disk that can be used to store some data. So if whole index data can't be stored in one space it's fragmentating to several parts. Online you may have empty disk that has lot's of unassigned space parts wich can be used to store whole table or index or whatever else