texts:
The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels.

If I understand correctly, the external level represents the users' views, the conceptual level is the conceptual diagram (ER model), and the internal level is the relational model.

My problem is that I found in the literature that besides those, there is a fourth level (the lowest one), which is the physical level.

I want to understand what is it concretely? Is it at this point that we define the type of index, access paths, and things related to physical access to data?


texts: Is a single-table relational database similar to a simple NoSQL database?

texts:
One advantage touted for NoSQL databases is the lack of a schema. In the case of a single-table relational database, there are no relationships between multiple tables, and new columns can be easily added to the single table. It seems like it is just as good as a NoSQL database, since there is no need to design a schema. So, is a single-table relational database similar to a simple NoSQL database?

Are there good reasons to use NoSQL databases if single-table relational databases are good enough, given that relational databases like SQLite are better tested and likely more stable?


texts: Using a Linked Server to import data from Excel

texts:
I have read a lot about linked servers being bad when there are multiple hops across servers and distributed joins.

What if I am just using a linked server to import data into SQL Server from an Excel spreadsheet?

What if that's all I am doing and the rest is being all done in SQL Server itself on a single server?

I want to avoid SSIS owing to headaches with multiple transactions and chaining them to each other (which is failing for some reason).


texts: MongoDb: acquireWaitCount and timeAcquiringMicros is missing from system.profile output

texts:
I enabled profiling in a mongodb database since sometimes a query is fast while sometimes the exact same query takes a very long time, despite the execution plan being a IXSCAN. I suspect it is due to locking issues but in the database profiler output acquireWaitCount, timeAcquiringMicros and deadlockCount are missing. How can I find these values and how can I find the query that holds those locks? I am using mongodb 4.2
Here is the output from a database profiler query:
{
  "op": "query",
  "ns": "db.Caratula-221",
  "command": {
    "find": "Caratula-221",
    "filter": {
      "EmpresaId": "33707830439",
      "Carpeta": "VALOR CRITERIO"
    },
    "limit": 1,
    "readConcern": {
      "level": "linearizable"
    },
    "$db": "db",
    "lsid": {
      "id": "a4c0937f-a77c-40c4-ae4a-f14fc8c67042"
    },
    "$clusterTime": {
      "clusterTime": "Timestamp(1598361984, 1)",
      "signature": {
        "hash": "BinData(0,\"AAAAAAAAAAAAAAAAAAAAAAAAAAA=\")",
        "keyId": 0
      }
    }
  },
  "keysExamined": 1,
  "docsExamined": 1,
  "cursorExhausted": true,
  "numYield": 0,
  "nreturned": 1,
  "queryHash": "D282DB09",
  "planCacheKey": "8921BB9F",
  "locks": {
    "ParallelBatchWriterMode": {
      "acquireCount": {
        "r": 2
      }
    },
    "ReplicationStateTransition": {
      "acquireCount": {
        "w": 3
      }
    },
    "Global": {
      "acquireCount": {
        "r": 2,
        "w": 1
      }
    },
    "Database": {
      "acquireCount": {
        "r": 1,
        "w": 1
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 1
      }
    },
    "Mutex": {
      "acquireCount": {
        "r": 1
      }
    },
    "oplog": {
      "acquireCount": {
        "w": 1
      }
    }
  },
  "flowControl": {
    "acquireCount": 1
  },
  "storage": {},
  "responseLength": 2492,
  "protocol": "op_msg",
  "millis": 8383,
  "planSummary": "IXSCAN { EmpresaId: 1, Carpeta: 1 }",
  "ts": "2020-08-25T13:26:32.526Z",
  "client": "192.168.2.78",
  "allUsers": [
    {
      "user": "dbadmin",
      "db": "admin"
    }
  ],
  "user": "dbadmin@admin"
}


texts: Managing SQL Server logins and database users

texts:
I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question about server logins and database users in the following scenario:

We have 30 database users that require the same permissions, each with their own AD accounts.

When setting up SQL server access for these SQL writers what is the most performant approach for giving a group of users with the same permissions access to a server/database?

I appreciate that this could be achieved via several approaches but am keen to learn the performant approach to managing relationships between server logins/database users/database roles.

Thanks :)


texts: How do I temporarily disable sp_send_dbmail?

texts:
I have just taken over responsibility for a SQL 2000 system which made heavy use of xp_sendmail.

We are in the process of upgrading to SQL Server 2008 R2 and I have implemented sp_send_dbmail.

We are currently performing user acceptance testing on the new SQL Server.  The application sends out many emails directly to our customers.  Since our test database is a copy of our live database, there are many valid customer email addresses in our test database.  Is there a way to either temporarily redirect the emails or shut down the Database Mail service, without impacting our testing?


texts: Error querying postgresql table created in pgAdmin

texts:
I have successfully created a table in pgAdmin which generated the code:
CREATE TABLE public."Test3"
(
    "PID" integer,
    "Name" character varying(20),
    PRIMARY KEY ("PID")
)

TABLESPACE pg_default;

ALTER TABLE public."Test3"
    OWNER to postgres;

However when I try to query the table with:
Select * from Test3;

I get the error:
ERROR:  relation "test3" does not exist
LINE 1: Select * from Test3;
                      ^
SQL state: 42P01 Character: 15

Same thing happens if I leave out the TABLESPACE line. Same thing happens if I try to query a table after importing rows from a .csv file. Seems like I must be missing something obvious.
Edit: I am trying to create the table in a database named "ABR"


texts: How to show the related (suggested) keywords?

texts:
We do a program for cataloging images by assigning keywords (tags) to them.

An image has the following keywords "John", "Jane", "Portrait".

Another image has the following keywords "John", "Jane", "Wedding".

When the user tries to catalog the next image and adds the "John" keyword, we want to appear in the 'Suggested' area the keyword "Jane".

How can we extract (using SQL) the suggested keywords based on the most possible combinations?

Note that Images can have more keywords assigned and in 'Suggested' area is best to have more suggestions.

The relevant table is called TagsTree and it has the following creation DDL:

CREATE TABLE TagsTree(ImageID INTEGER NOT NULL, TagID INTEGER NOT NULL, UNIQUE (ImageID, TagID))

...where ImageID is FK to the photo entity and TagID is FK reference to the keyword entity.

Also, any other hints regarding schema etc. are greatly appreciated.

PS: The DB engine is SQLite3, if it matters.


texts: Converting Microsoft SQL Server collation to UTF 8

texts:
I've a Rails-Application, which reads data from a Microsoft SQL Server 2008 R2 Database.

All umlauts are represented by a '?', so I checked the encoding of the database.  The encoding is 'Latin 1'.   When I try to change it, I have a big list of different collations, but UTF-8 is not listed.  The rails application communicates with the database using an ODBC DSN, could this be the source of the problem?

How can I change the database collation to UTF-8?


texts: Updating column based on conditions from another table

texts:
I am updating a column to values based on conditions from another table. I want only the values to be updated for rows where the condition meets. It shouldn't overwrite the rows where the conditions do not meet to null (as other values exists). For e.g.

update i2017_mar_ye a
set    a.status = (select case
                            when b.cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'RE_Others'
                            when b.cvg_cstat in ('B','I','K','N') then 'RE_Lapse'
                            when b.cvg_cstat in ('D') then 'RE_Death'
                            when b.cvg_cstat in ('E','S') then 'RE_Surrender'
                            when b.cvg_cstat in ('H') then 'RE_Rider Claims'
                          end
                   from  i2016_mar b
                   where a.pol_id = b.pol_id
                   and   a.cvg_num = b.cvg_num
                   and   a.cvg_iss_dt <= to_date('31-March-2016')
                   and   a.cvg_cstat in ('1','2','3','4')
                   and   b.cvg_cstat not in ('1','2','3','4')
                   and   a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9'));
commit;

update i2017_mar_ye a
set    a.status = (select case
                            when a.cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'DE_Others'
                            when a.cvg_cstat in ('B','I','K','N') then 'DE_Lapse'
                            when a.cvg_cstat in ('D') then 'DE_Death'
                            when a.cvg_cstat in ('E','S') then 'DE_Surrender'
                            when a.cvg_cstat in ('F') then 'DE_Maturity'
                            when a.cvg_cstat in ('H') then 'DE_Rider Claims'
                          end
                   from  i2016_mar b
                   where rownum = 1
                   and   a.pol_id = b.pol_id
                   and   a.cvg_num = b.cvg_num
                   and   a.cvg_iss_dt <= to_date('31-March-2016')
                   and   b.cvg_cstat in ('1','2','3','4')
                   and   a.cvg_cstat not in ('1','2','3','4')
                   and   a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9'));
commit;

update i2017_mar_ye
set    status = case
                  when cvg_cstat in ('1','2','4') then 'NB_Inforce'
                  when cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'NB_Others'
                  when cvg_cstat in ('B','I','K','N') then 'NB_Lapse'
                  when cvg_cstat in ('D') then 'NB_Death'
                  when cvg_cstat in ('E','S') then 'NB_Surrender'
                  when cvg_cstat in ('F') then 'NB_Maturity'
                  when cvg_cstat in ('H') then 'NB_Rider Claims'
                end
where  cvg_iss_dt > to_date('31-March-2016')
and    cvg_cstat not like 'P%'
and    cvg_cstat not like 'R%'
and    cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9');
commit;

Here only, DE series is updated. RE series gets set to null.


texts:
So I'm doing some research on MSDTC and its role it does or does not play in various types of calls within SQL Server, and it got me thinking on the following questions:

Are there any 3rd party alternatives/replacements for MSDTC (if they exist) & their pros/cons
Does DTC come into play when I simply query a single table (CRUD or Select) via a linked server?
Is MSDTC required for transactional replication?  I know updatable subscriptions require MSDTC, but not clear if non-updatable subscriptions need/use MSDTC.

From googling, I can't seem to find good answers to these questions, so turning to the community here.  Any help in bettering my understanding of MSDTC is greatly appreciated.

Thank you!


texts:
I am keeping data in key/value pairs.  I know how to convert that data into crosstab form using queries like this:

<!-- language: lang-sql -->
SELECT master.tag,
    MAX(CASE WHEN data.key = 1 THEN data.value ELSE NULL END) AS F1,
    MAX(CASE WHEN data.key = 2 THEN data.value ELSE NULL END) AS F2,
    MAX(CASE WHEN data.key = 3 THEN data.value ELSE NULL END) AS F3
FROM master left join data on data.master_id= master.id
GROUP BY master.id

In fact I am using more complicated version with dynamically created query, providing only fields which exist for selected master records.
This solution provides read-only recordset. Now, how to create a similar updatable recordset ?

I see three solutions, but none of these looks good:
1. Use joins like the code below:

SELECT master.tag,
  data1.value AS F1,
  data2.value AS F2,
  data3.value AS F3
FROM master
  inner join data as data_1 on data_1.master_id=master.id and data1.key=1
  inner join data as data_2 on data_2.master_id=master.id and data2.key=2
  inner join data as data_3 on data_3.master_id=master.id and data3.key=3

Performance is terrible at 10 joins and only a few master records.
2.
  Use view (read-only) to export current data along with data table id's and write own table editor.
This option is not practical. A form with custom update handling could be doable and useful but is not sufficient for me / does not cover all user tasks.
3. Create a temporary table with triggers in a SPROC. Edits would be made on this table and all changes would be forwarded to KVP storage by triggers.
This option looks the most practical, but there are issues with table lifetime. I have to use regular tables because true temporary tables can not have triggers. Each user may open more than one table. I have to find a way to detect which tables are no longer needed.

The question is are there any better options than the 3 above?
If not, how to implement option 3 properly?

Bakground:
The backend is MySQL, the frontend is MS Access 2007.
Data is very irregular. There will be 60-150 various keys in KVP table but only 15-30 will be used for a given master row. Data can be grouped in classes and each class has specific set of keys. There can be 1-30 instances of each class. The idea is to let the user work like it was a regular table but limiting rows to one or a few classes and showing only columns (keys) which exist in all classes/rows.


texts: considerations when using triggers on a replicated (target) database

texts:
Our SQL Sever 2008 application database is replicated from Server A to Server B (push replication).  We use the copy, let's call it database_b, on Server B to create reports and run other queries so that our reports do not interfere with the application.  Currently we leverage inefficient views to combine data across multiple tables in database_b so that report writing is simplified for our report writers (who have basic SQL skills).

99.9% of the database activity is INSERTS, so we are exploring a way to replace the inefficient views with tables we can optimize.  Here is a simplified example:

There is an appointment table and a location (lookup) table.  Every time a new appointment is scheduled, a row is added to the appointment table.  Every time this INSERT happens, I want to take that appointment_id and insert it and its corresponding location name (join on location_id from both tables) into a reporting table.

I have accomplished this with a trigger on the appointment table in database_b on Server B.

My question is - are there any particular considerations given that database_b is a replicated copy?  Do I need to worry about a failing trigger mucking up the entire (push) replication process?  Anything else I am missing?

Unfortunately it's difficult to test this in our development environment, so I don't have the opportunity for a lot of trial and error.


texts: Vanilla installation of MySQL on Windows 10 fails, service cannot start, corrupt InnoDB page

texts:
I started with a clean machine (Windows 10, 64bits), no previous installation of MySQL. I downloaded MySQL Installer Web Community 5.7.20.0, and installed the following:

MySQL Server 5.7.20 (x86)
MySQL Workbench 6.3.10 (x64)
MySQL Notifier 1.1.7 (x86)
MySQL Utilities 1.6.5 (x86)
MySQL Shell 1.0.10 (x86)
MySQL Router 2.1.4 (x86)
Connector/NET 6.10.4 (x86)

During the (vanilla) installation, the server doesn't start. Starting MySQL57 in Windows services returns "The MySQL57 service on Local Computer started and the stopped. Some services stop automatically if they are not in use by other services or programs."

I then looked around and one promising solution was to run this:

  mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" --standalone --console

So I did and out came this:

2017-11-25T01:12:00.706665Z 0 [Warning] option 'read_buffer_size': unsigned value 0 adjusted to 8192
2017-11-25T01:12:00.706665Z 0 [Warning] option 'read_rnd_buffer_size': unsigned value 0 adjusted to 1
2017-11-25T01:12:00.706665Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-25T01:12:00.706665Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-11-25T01:12:00.708671Z 0 [Note] mysqld.exe (mysqld 5.7.20-log) starting as process 2968 ...
2017-11-25T01:12:00.712680Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-11-25T01:12:00.713685Z 0 [Note] InnoDB: Uses event mutexes
2017-11-25T01:12:00.713685Z 0 [Note] InnoDB: Memory barrier is not used
2017-11-25T01:12:00.714689Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-11-25T01:12:00.714689Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2017-11-25T01:12:00.715690Z 0 [Note] InnoDB: Number of pools: 1
2017-11-25T01:12:00.715690Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-11-25T01:12:00.718699Z 0 [Note] InnoDB: Initializing buffer pool, total size = 8M, instances = 1, chunk size = 8M
2017-11-25T01:12:00.719701Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-11-25T01:12:00.732507Z 0 [ERROR] InnoDB: Header page consists of zero bytes in datafile: .\ibdata1, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-11-25T01:12:00.733507Z 0 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile '.\ibdata1' could not be found in the doublewrite buffer.
2017-11-25T01:12:00.734515Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
2017-11-25T01:12:00.936694Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-11-25T01:12:00.937698Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-11-25T01:12:00.942571Z 0 [ERROR] Failed to initialize plugins.
2017-11-25T01:12:00.944576Z 0 [ERROR] Aborting

2017-11-25T01:12:00.945578Z 0 [Note] Binlog end
2017-11-25T01:12:00.950583Z 0 [Note] Shutting down plugin 'CSV'
2017-11-25T01:12:00.954598Z 0 [Note] mysqld.exe: Shutdown complete

Of which a few warnings and errors have me looking down a rabbit's hole of possible configuration issues. I don't have any data, I can start over from scratch. How can I fix this header page issue?


texts: Are there any way to stop typing 'go' in sqlcmd?

texts:
If I remember correctly, last time Microsoft Sqlcmd require each command to end with ';' and hit Enter key to execute the command.

However, currently, we need to type the GO each time to execute the command.

So, are there other alternative such that when we hit Enter key after ending the command with ';', it will Execute the command?


texts: pros and cons of partition or not-partition this sql server table that is highly hit?

texts:
I have a table that is highly hit on my database.

this table has 370,370 records.

when I run the following select:

select  [ImageSize] , the_num = count(*)
from  [dbo].[ProductImages]
group by [ImageSize]
order by [ImageSize]

I get this result:

Basically there are only 5 different imageSize objects in that table.

This is the current table definition:

IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL
DROP TABLE [dbo].[ProductImages]
GO
CREATE TABLE [dbo].[ProductImages] (
[ProductImageID]  INT              IDENTITY(1,1)   NOT NULL,
[SeasonItemID]    VARCHAR(5)                       NOT NULL,
[Language]        SMALLINT                         NOT NULL,
[Tier1]           VARCHAR(10)                      NOT NULL,
[Tier2]           VARCHAR(10)                      NOT NULL,
[Gender]          CHAR(1)                          NOT NULL,
[SortOrder]       SMALLINT                         NOT NULL,
[ImageFormat]     VARCHAR(20)                      NOT NULL,
[ImageSize]       VARCHAR(10)                      NOT NULL,
[ImageWidth]      SMALLINT                         NOT NULL,
[ImageHeight]     SMALLINT                         NOT NULL,
[BodenDomainURL]  NVARCHAR(40)                     NOT NULL,
[BodenImageURL]   NVARCHAR(210)                    NOT NULL,
CONSTRAINT   [PK_ProductImages]
PRIMARY KEY NONCLUSTERED ([SeasonItemID] asc,
                          [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc,
                          [SortOrder] asc, [ImageFormat] asc,
                          [ImageSize] asc))

GO

  and here are the definitions of the currently
  1 clustered index,
  4 non clustered  indexes,
  1 filtered index
  and the primary key non clustered defined above:

    CREATE CLUSTERED INDEX
    [CIX_ProductImages__ProductImageID]
               ON [dbo].[ProductImages] ([ProductImageID] asc)

            CREATE NONCLUSTERED INDEX

[IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL]
               ON [dbo].[ProductImages]
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc,
        [ImageSize] asc, [ImageFormat] asc,
        [BodenImageURL] asc)
               INCLUDE ([BodenDomainURL])

            CREATE NONCLUSTERED INDEX

        [IX_ProductImages_ImageFormat_ImageSize_INCL]
               ON [dbo].[ProductImages]
        ([ImageFormat] asc, [ImageSize] asc)
               INCLUDE ([SeasonItemID], [Language],
         [Tier1], [Tier2], [BodenDomainURL],
        [BodenImageURL])

            CREATE NONCLUSTERED INDEX
[IX_ProductImages_Language_Tier1_Tier2_ImageFormat_INCL]
               ON [dbo].[ProductImages]
        ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc,
        [ImageFormat] asc)
               INCLUDE ([ProductImageID], [BodenImageURL])

This is a filtered index:

            CREATE NONCLUSTERED INDEX
[IDX_ProdImages_GetProductListingPageDenormalisedData]
               ON [dbo].[ProductImages]
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc,
        [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc)
               INCLUDE ([BodenImageURL])
               WHERE ([ImageWidth]=(195)
        AND [ImageHeight]=(252)
        AND [ImageFormat]='ProductMain')

Can I expect significative gains by partitioning all these indexes according to the imageSize?

On what factors would it depend mainly (in this case)?

Most queries use the ImageSize as one of the parameter, it is even in the clustered index.

I could modify the indexes and table structure,
this table is mostly used for reading. there is only 1 write a day, and that would not normally affect many records.


texts:
I set up a database with some views. I want to log who accessed which view and when. The target-table looks like this:
[schema], [table], [user], [timestamp]
dbo, access, someuser, 2021-04-13 12:07:47.250

So every time someone opens a view, there should be a new row added with said data. As there aren't that many views, a "quick and dirty" solution would be fine.
Thanks in advance.


texts: Adding second SQL Server to create 2 Node cluster

texts:
I am not a dba. I have general knowledge of SQL Server and T-SQL. Our company currently has a SQL Server 2008 R2 hosting about 30 databases.

I've been asked to research adding a second server for HA/Failover. My question is, should I be considering upgrading the current server to at least 2012, and build the new server the same? I had initially thought about building another 2008 R2 server, but it looks like clustering and HA is much improved in 2012.

Also, not being an expert, is this going to be something a journeyman administrator can accomplish (obviously you can't gauge my skill level) or will we be looking at engaging a consultant? I know that question may be hard to answer.


texts:
I have a long running query (fact table with 100 million rows joining a number of small dim tables then group by) that's spilling to tempdb, even though (after some tweaking) the CE is very close to the actual number of rows, see plan:

Looking for an explanation, I noticed the following memory grant info:

Environment: SQL Server 2012 SP1 Enterprise, server RAM 256 GB, SQL Server max memory 200 GB, buffer pool size 42 GB, workspace max size 156 GB (GrantedMemory = 156 * 25% ~= 38 GB)
Questions

does that mean no matter how good the CE is, the query has no chance of not spilling over? since the query max ram is hard capped at 38 GB
does query optimizer not take max query ram into consideration when building the plan? (forcing a Hash Match aggregate would eliminate the sort step and significantly improve query performance, unfortunately, the actual query is coming from Cognos and we have no control of it)
will increase the 25% cap to close to 100% be a sensible option here? (assuming that the said server access can be controlled to limit the number of concurrent query requests)

Anonymized query plan at Paste The Plan
When forcing a hash match aggregate (instead of a sort + stream aggregate), the query consistently finishes 3 - 4 times quicker. Unfortunately, the actual query is coming from Cognos, and we have no way of changing it.
There is no hash spill in the hash aggregate plan. The query optimizer won't pick hash match aggregate because if I look at the operator cost for hash vs stream aggregate, the CPU cost of hash group is 2 - 3 times higher than doing stream aggregate.
In both stream and hash aggregate, the estimated output rows is exactly the same as the input (~100 million rows).
The query uses a single NC columnstore index, and column stats are all regularly updated.


texts: Real time sync one table from DB (Server A) to DB (Server B)

texts:
need some suggestions on syncing data from one table (which get millions of records on a daily basis) from Server A to another database (different server B) in a real time. Whenever any update or Insert happen in this table we need the change in real time in a table on Server B. At few hours in a day we get millions of records inserted in this table with a very short span as this table is highly transactional.
This table on DB in Server A can be relational and No SQL DB. Haven't finalized it yet.
So far we are considering a data streaming options like Kafka, event hub streaming. But need to understand if these right approaches or if there is another solution that we further explore.


texts:
Very new to SQL on AWS RDS so.

Need to create maintenance jobs on an AWS RDS SQL2016 instance.  I know parts of Ola Hallengren's life saver maintenance script works, Index, stats and integrity.

Picking through Ola's maint script it looks like there is logic that checks for RDS.

For anyone who has done this, how do I install it, do I run the scripts individually and against which DB?


texts: Select distinct values between two values in different field

texts:
I have a table named products like this:

| product_name | start_year | end_year |
|--------------|------------|----------|
| wine         | 2001       | 2012     |
| cheese       | 1999       | 2010     |
| apple        | 2008       | 2015     |

What kind of query do I have to run to have all values between start_year and  end_year where product = 'apple'?

This is the desired results:

| year |
|------|
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |


texts: What is an easy query to fix up historical columns valid_from and valid_to?

texts:
I have a table with typical history rows using valid_from and valid_to dates.  My table might look like this:

| id | object_id | name | valid_from | valid_to
--------------------------------------------------
|  1 | 1000      | Foo  | 2010-07-01 |   null
|  2 | 1000      | Bar  | 2011-12-31 |   null
|  3 | 2000      | Baz  | 2010-08-04 |   null
|  4 | 2000      | Bang | 2013-03-21 |   null

I'd like run a SQL update such that it would update valid_to of a row to next historical valid_from date minus 1 day if possible.  So therefore the table might look like this when the query is done:

| id | object_id | name | valid_from | valid_to
--------------------------------------------------
|  1 | 1000      | Foo  | 2010-07-01 |   2011-12-30
|  2 | 1000      | Bar  | 2011-12-31 |   null
|  3 | 2000      | Baz  | 2010-08-04 |   2013-03-20
|  4 | 2000      | Bang | 2013-03-21 |   null

I've tried a few different variations, but I'm striking out on this one:

select *
from employees as cur
where cur.valid_from < (
    select nx.valid_from from employees as nx
    where
       cur.source_id=nx.source_id
       and nx.valid_from > cur.valid_from
);

And

update employees cur
set cur.valid_to = (
    select nx.valid_from
    from employees nx
    where cur.source_id=nx.source_id
          and nx.valid_from > cur.valid_from
);

Part of the problem with the subquery as I've written it is that it will return multiple rows.  I just need the very first row that occurs after the valid_from of the current row.  And the update statement MySQL complains that cur.valid_from cannot be used in the subquery.

I'm trying to find a query that would be portable between Oracle and MySQL.  Can someone get me on the right track?


texts: how to take postgres server backup skipping some of databases?

texts:
I have PostgreSQL server version 9.5. Contains 20 Databases.

I want take server backup skipping of some 10 databases.

Am using pg_dumpall, but it was taking full server backup.

thanks,


texts:
I am trying to upgrade to SQL Server 2012 from SQL Server 2008 R2 without installing any of the 2008 R2 service packs (1, 2, and 3).

The reason is that, at my work, we ship software with SQL Server Express RTM as a prerequisite and we include the RTM versions with our software.

Now the problem comes when we included SQL Server 2012 in our new release. For customers which are in 2008 R2 RTM, we have to ship the service packs with our software so that they can upgrade to the service pack and then upgrade to the SQL Server 2012. However, the service packs have significantly increased the size of our software package and downloading it is taking so much time for customers.

Is there a way to upgrade to SQL Server 2012 from SQL Server 2008 R2 without installing any of the three service packs?


texts:
I'm using Oracle 11.2 SE on a 16GB Xeon E3 Linux machine as well as 11.2 XE on a 8GB i7 Win8 machine:

SELECT banner FROM v$version:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

and

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE   11.2.0.2.0  Production"
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Both systems suffer from the same problem:
I have a complicated SELECT (120 steps in EXPLAIN PLAN) for a right now pretty empty schema (20 MB in total):

SELECT segment_type, COUNT(*) AS objectcount,
       SUM(bytes / 1024 / 1024) AS MB FROM user_segments GROUP BY segment_type;

LOBINDEX    1   0,0625
LOBSEGMENT  1   2
TABLE       55  6,4375
INDEX       204 12,75

Table stats are gathered with:

DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname => loop_tablesnames,
   estimate_percent=>100, method_opt=>'FOR ALL COLUMNS', cascade=>true, force=>true);

The EXPLAIN PLAN for the statement leading to 120 steps (these are expected) takes approx 7sec on the linux machine and 22sec on the win8 machine.

The installs are basic, no changes to the Oracle configuration have been done.

As my SQLs are generated and do not use parameter binding, almost every statement is hard-parsed leading to the same delay for statement execution. Once no hard parse is needed, the statement executes within milliseconds.

For less complicated statements, the EXPLAIN PLAN is fast.

Is there anything I can do to speed up the EXPLAIN PLAN and the hard parse step (I'd think that the solution for EXPLAIN PLAN will also solve the other)?

Thank you & best regards,
Blama


texts: Reason for having additional "type" table of all possible string values?

texts:
I've been looking over some sample schemas, and here's something I've seen done several times, but being new to MySQL I'm not sure why. Say I've got a table for items:

CREATE TABLE item (
  itemID CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  itemType VARCHAR(255) NOT NULL,
  PRIMARY KEY (itemID),
  CONSTRAINT fk_item_itemType FOREIGN KEY (itemType) REFERENCES itemType(name)
) ENGINE = MyISAM;

Why would you need itemType to be a foreign key to another table that lists all the possible item types? Would it prevent typos if the entered value wasn't in the other table? The other table is this:

CREATE TABLE itemType (
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (name),
) ENGINE = MyISAM;
INSERT INTO itemType (name) VALUES ('gloves');
INSERT INTO itemType (name) VALUES ('boots');
INSERT INTO itemType (name) VALUES ('ring');


texts: SQL Agent using domain user for Jobs with TSQL

texts:
I have tried every option but i still cant find out why my sql agent is using a domain user when executing job steps with TSQL and keep getting the following error:

Executed as user: RESOURCE\UXCOLEBSREP$. Login failed for user 'RESOURCE\UXCOLEBSREP$'. [SQLSTATE 28000] (Error 18456).  The step failed.

I have made sure that the agent is running using this acocunt:
NT SERVICE\SQLAgent$UXB

I have made sure that no run as being used under any job stpes.

Please advice! I'm using sql 2017

Thx


texts: How to prevent pg_dump from generating "SET default_table_access_method=heap;"?

texts:
I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too.
Now I need to reload the file into a PostgreSQL v11 server, and I get a error message:
unrecognized configuration parameter "default_table_access_method"

I guess that this parameter is introduced by PG12, so PG11 can't recognize it, even though I use the 12 version of pg_restore.
Because the file is huge and in binary format, I can NOT edit it to remove the line manually.
I tested that pg_restore test_file -f plain.sql with a test dump file, and it looks like work, and I can remove the line from the plain SQL file.
But the real dump file is about 30GB, the plain SQL file expanded from the binary might be bigger (the working database occupies 300GB disk space).
Is there a way in it, I can cleanly reload it into PG11, only without the line "SET default_table_access_method = heap;"?
In fact, the following all can be ignored, because they are all default values:
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';


texts:
I have a log table which I have to clean up a bit because it is growing very fast.
While deleting some historical data I reached count zero for that condition on which I have deleted the entries. 5 minutes later I check again (hit f5) and there are some old entries (50). I commented every known function in the application which adds records to this table and entries are still popping out.

Using
SELECT sql FROM sys.syscacheobjects where sql like '%Table_name%', I found one insert query but I don't know how or where it is being executed from.
Looking at the where clause of the insert string I think it might be a stored procedure?

values (@0, @1, null, @2, null, null, @3, @4, @5, @6, @7)
select [TableId] from [dbo].[Table] where @@ROWCOUNT > 0


texts:
In our SQL Server DB, we have about some 800+ tables and there are 40 - 50 tables are business critical tables. MIS team needs to generate reports based on those 50 business tables.

Day by day, records are getting huge in those business tables. Daily MIS team is pulling those millions of records directly from Production like select * from Table1 and pushing those records into their environment. Table1 may have 30 million records.

Those 50 tables gets updated frequently. MIS team requires those delta records (update/inserted/delete)

Our Senior DBA advice us to use the below 4th approach. He says replication is a failure model and it will leads I/O problem.

What would be the best solution?

We have few approaches here

Always On
Replication
Mirroring
Introducing new column (LastModifiedDate & creating index) in those 50 tables and pulling those records periodically and populating it to MIS environment.

There will be huge code change for the new column LastModifiedDate approach.

Based on those 50 tables, we have huge number of stored procedures which it has Insert/Update statements. In those stored procedures, we need to do code change for LastModifiedDate.

Since we have SQL Server 2008 R2 only, we cant got for Always On approach. If it is the only approach, then we can approach management to upgrade the 2008 to 2014/2016.

What would be the best solution from the above approaches?

Please let me know if any other approach to do.


texts: Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

texts:
I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but they share very few columns. For example, all posts have user_id, description, email_on_question, and email_on_bid columns, but on top of this will have 10 - 20 unique fields. All in all, I'd say about 8 out 60 columns are shared between post types.

Because of the vast number of unique columns, and because some post types have unique behavior and children, I initially decided to go with Concrete Table Inheritance - each object is represented by a separate table, with shared columns being duplicated on each table:

-- SportsPost -- | -- WorldNewsPost -- | -- TechnologyPost --
user_id            user_id               user_id
description        description           description
team               location              industry

The problem with this approach is that it's very difficult to query and aggregate all posts. I have a dashboard view which lists the top 20 most recently created posts, and in order to retrieve the data for this view I have to execute a separate query on 10 different tables! I execute all 10 queries inside of a transaction to save myself from doing multiple roundtrips, but I'm still concerned that the performance of the query will be very poor as the application scales and new post types are created.

I'm considering switching over to Single Table Inheritance at this point. Instead of executing 10+ queries for the dashboard view, I'll only need to execute one query. Does this sound like a reasonable approach? Are there any drawbacks of STI (other than NULL columns) that I'm not aware of?


texts: Hierarchical permissions in a table stored hierarchy

texts:
Assuming the following database structure (modifiable if need be) ...

I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that allows me to return a row containing the Page and the effective permissions.
I am thinking that the ideal solution may include a function that uses a CTE to perform the recursion needed to evaluate the "effective permissions" for a given page row for the current user.
Background and Implementation details
The above schema represents a start point for a content management system in which users can be granted permissions by being added and removed from roles.
Resources in the system (e.g. pages) are associated with roles to grant the group of users linked to that role the permissions it grants.
The idea is to be able to easily lock down a user by simply having a deny all role and adding the root level page in the tree to that role and then adding the user to that role.
This would allow the permission structure to remain in place when (for example) a contractor working for the company is not available for long periods, this will then also allow for the same granting of their original permissions by simply removing the user from that one role.
Permissions are based on typical ACL type rules that might apply to file system by following these rules.
The CRUD permissions are to be nullable bits so the available values are true, false, not defined where the following is true:

false + anything = false
true + not defined = true
true + true = true
not defined + not defined = not defined

If any of the permissions is false -> false
Else if any is true -> true
Else (all not defined) -> false

In other words you get no permissions on anything unless you are granted them through role membership and a deny rule overrides an allow rule.
The "set" of permissions this applies to is all permissions applied to the tree up to and including the current page, in other words: If a false is in any role applied to any page in the tree to this page then the result is false, but if the whole tree up to here is not defined then the current page contains a true rule the result is true here but would be false for the parent.
I'd like to loosely keep the db structure if possible, also keep in mind that my goal here is to be able to do something like: select * from pages where effective permissions (read = true) and user = ? so any solution should be able to allow me have a queryable set with the effective permissions in them in some way (returning them is optional as long as the criteria can be specified).
Assuming 2 pages exist where 1 is a child of the other and 2 roles exist, one for admin users and 1 for read only users, both are linked to only the root level page I would expect to see something like this as expected output:
Admin user:
Id, Parent, Name, Create, Read, Update, Delete
1,  null,   Root, True  , True, True  , True
2,  1,      Child,True  , True, True  , True

Read only user:
Id, Parent, Name, Create, Read, Update, Delete
1,  null,   Root, False , True, False , False
2,  1,      Child,False , True, False , False

Further discussion around this question can be found in the main site chat room starting here.


texts: can we use a mongodb 3.0 and mongodb 2.6 in same replica set?

texts:
i know that replicas using different storage engines can coexist in a replica set on MongoDB 3.0  but can we add a MongoDB 3.0 replica to a replica set that consists of MongoDB 2.6 instances ?

That would be the easiest way to port to "MongoDB 3.0 + wiredTiger" instances from our MongoDB 2.6 instances


texts: What is controlling SQL Server's page file usage? SQL Server or Windows?

texts:
If Lock Pages In Memory is not enabled and SQL Server data is paged out to the page file does SQL Server decide what data gets paged out? Or is this handled by the OS?

If Windows is handling this does it tell SQL Server which data has been paged out?
It is my understanding that SQL Server knows how much data is in RAM and how much is in the page file.
But does SQL Server know what data pages are put where and does it have any influence on whether a data page is put in one or the other?

Here's some Background:

I am not really trying to solve a problem as such.

My question is more about understanding what's going on inside SQL Server...

I had an issue with an application that had a long queue of jobs/queries that timed out waiting for a SQL Server instance to handle the requests.
I noticed that two SELECT queries on the database in question were briefly blocking an UPDATE and a DELETE query.
After a short while two new SELECTs were blocking two new UPDATE and DELETE queries. This continued.

SQL Server worked as expected - except it was too slow for the application that experienced timeouts.

The SQL Server instance is located on a active/passive cluster with 14 other SQL Server instances.

I worked on this with the application developers.
We discussed allocating more RAM to the instance and while not really thinking it would help I raised the max server memory (MB) setting from 40GB to 55GB.
Within a few seconds all the jobs waiting in line at the application server where executed - the developers were watching them being removed from the queue one by one very quickly.

The server hosting the SQL Server was not at the time set to allow Lock Pages In Memory for the service account, so I was thinking that if the data needed to handle the requests mentioned were placed in the page file and when the extra RAM was allocated the data was moved from the page file to the RAM maybe that could explain why all the jobs finished so fast immediately after the extra RAM was allocated.


texts: How memory allocation happened in SQL Server

texts:
Suppose I have a 32 GB machine, now I have allocated 12GB memory to the maximum server memory for my database application to perform. Now I am running some load simulation which is going to consume the entire 12GB. Now SQL Server is run out of memory when it required more memory, OS is going to assign more memory:

Now here is my thought, the scheduler will check the priority of the task if the task has got the highest priority then, how the memory is going to allocate? if so how much % of the rest available memory will allocate to SQL Server. Is it entire or it will assign periodically and how much memory will allocate?

If the above condition is false then the alternate concept is (note this is what I think), if the task has got the lowest priority then it will wait for its priority to come till the time application will throw out memory exception.

Is the maximum server memory parameter play a role in memory allocation in the SQL server? How important it is for memory allocation and troubleshooting?


texts: After Update Trigger When Multiple Rows To Update

texts:
Is it possible to use a trigger to update multiple rows?  For example, I have an app that will use a Table Value Param & a SQL Stored Proc to dump anywhere between 15 and 20 rows into a table.  Once that data is dumped, I then need a way (I was thinking a trigger) to update the remaining "missing" fields.  I thought this approach may work (it is the valid syntax for my update)

Create TRIGGER [dbo].[UpdateFields]
ON  [dbo].[TFData]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE a
Set
a.DateOfInjury = b.InjurDate
,a.GameNum = c.GameNum
FROM [dbo].[InjuryReport] a
INNER JOIN [dbo].[GameStats] b
ON a.playnum = b.playnum
INNER JOIN [dbo].[GameSched] c
On b.gamenum = c.gamenum

How would I need to code this trigger to update multiple rows?  I assume I would somehow need to join on the virtual table but I am not certain.


texts: Managing CTE execution order under uncertainty

texts:
I am writing a large, multi-step CTE for performance reasons.

In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero.

In a subsequent table, the origin from the previous query is deleted but must be after the previous query is completed.

Finally, rows must be written in place of the deleted rows after the second query above is completed.

In the first two queries, I am using RETURNING to enforce execution order.

In the second query, I'm determining that the first query is completed by this subquery

(SELECT COUNT(*) FROM first_query) >= 0

In the the third query, I'm determining that the second query is completed by this subquery

SELECT EXISTS (SELECT 1 FROM second_query)

Is the subquery to determine that the first query has completed correct?

Is the subquery to determine that the second query, which must return rows, has completed optimal for accuracy, precision, and performance?

Using the above subqueries to enforce execution order is giving duplicate key value violations.

Query subsection

WITH copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = $1::bigint
        RETURNING *
),
main_table_deleted AS (
    DELETE FROM main_table WHERE column_a = $1::bigint
        AND (SELECT COUNT(*) FROM copy_to_other_table) >= 0
        RETURNING *
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = $1::bigint
             AND EXISTS (SELECT 1 FROM main_table_deleted)

It is the final query that is violating the unique constraint.


texts: sp_BlitzIndex -- 'High value" vs 'High Impact'

texts:
Sorry, I may be missing something super-obvious here, but I've googled this and asked some colleagues and am not finding a satisfactory answer.

I've been using sp_BlitzIndex for a few months now along with some of my other regular index tuning tools and have found it to be a super-useful tool to add to my SQL toolbox.

But I do not quite understand the difference between 'High value' and 'High Impact' as in this example below:

'Indexaphobia: High value missing index with High Impact'

At first I thought they meant roughly the same thing -- it was identifying a missing index that would be both highly valuable and have a great impact on query performance.

But that didn't quite make sense to me -- why have two separate terms to indicate what was pretty much the same thing?  So a colleague suggested that the term 'impact' was referring to the impact on db write operations.  So 'high impact' in this case meant the index might cost more than other indexes when it came to updates and deletes.

But I've read some of Brent's comments, and, if I'm understanding him correctly, I don't think my colleague's interpretation is quite right either.

Can someone please enlighten me on this?  I'd really like to understand the difference before testing out some of the suggestions sp_BlitzIndex is making to me.  Is 'high impact' good or bad?  If it's good, then what's the difference between 'high value' and 'high impact'?

Thanks very much in advance!!

Robert


texts: SQL Server 2000 : List and Disable all triggers

texts:
I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the triggers and the Shrinking of the Transaction Log.

Currently the biggest problem I have is the disabling of the Triggers. This would have been super easy in sql 2005 / 2008 as they included a 'Disable Triggers All' command. I have a query to list all of the Triggers although it doesn't list the status of Trigger (this would be a bonus)

SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S2.deltrig = s1.id THEN 'Delete' WHEN S2.instrig = s1.id THEN 'Insert' WHEN S2.updtrig = s1.id THEN 'Update' END 'TriggerType' , 'S1',s1.*,'S2',s2.* FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

Source

The deleting of the records will be plain delete's one for each table, this probably isnt the most efficient way to do it but its the way which was prescribed.

Shrinking the Transaction Log is also sorted, i found an article on using the DBCC on Microsoft support site

Any advise would be really appreciated


texts: mysql - How to repair & backup archive table, also stop growing .ARN extension file?

texts:
I have an MySQL table build with archive storage engine.  The table has over 100k records.

I have got disk full and it turned out to be a large .ARN file from an archive table. When I cleared some space, the file continued to grow until the space was consumed.

Right now the .ARN file is over 16GB and it's growing on every insert. The corresponding .ARZ file is just over 8MB.

I cannot fetch records from archive table, as the table data is corrupted.
When I tried to repair table it says "Incorrect key file for table" which means File system out of space.

But I am left with no disk space, so I have to repair and backup this table on my local machine.

How can I repair archive table, backup data from archive table and free some disk space? Why .ARN file keeps growing on every insert?


texts: SQL Server 2014: missing replication option

texts:
This article details exact steps to set up basic database replication with SQL Server 2014.

It starts with this:

  Right-click the Replication folder, and then click Configure Distribution.

On right clicking my Replication folder (I do believe I installed replication), I only get the following options:

Launch Replication Monitor
Generate Scripts....
Update Replication Passwords...

I can't find any other place in SQL Server Mgmt Studio to get to this section. What am I missing?


texts: Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

texts:
I need to extend my existing table with status column.
I am in doubt how to do that in most appropriate way regarding of future use of that status and performances of existing table.

My table is around 3,7 MIL records, and it is heavy read and write table.
Table represent stock (warehouse) of items (goods). It holds floats for many kind of prices and amounts, primary key is composite by item_id and warehouse_id.

Here is how I see ways to implement adding status of an item.

To add three more columns for statuses, each one column will represent some of statues,
Something like 'allow_sell','allow_orders','allow_returns'
I like this way because it uses BITs, (is it faster than varchar, or integer ?) and easy to understand what each status represents and which combination of statuses are seted on an item. In this way

To add one integer column called status_id and to add one more table statuses_of_item where I can hold description of each status and add statues as many as I wish.

To add one varchar(10) column where status will be written as dresption ('denay_all','allow_all', 'allow_orders_wo_sels' etc)

Have in mind that status is part of business logic and it uses only internal, Representation of statues are not displayed in SIMPLE WAY, statues not shown to users, it is only way how system should handle some situation in different places.

I think that 70% of items inside table will have default (same) status.
My database system is MS Sql Server 2008R2.

How I should design my table to include stautes ?


texts: Corruption in D:\$MFT. How may this progress? I don't think CHECKDB is useful here?

texts:
I have a server that is reporting NTFS errors; one of the error messages implicates d:\$MFT as being possibly corrupt. My SQL data files reside on this drive.

The Master File Table holds information about all files on the disk, so I imagine if the corruption was to occur SQL may not be able to read data from one of the MDFs or NDFs?

I imagine CHECKDB will not be a relevant consistency check, and the only way to resolve this error would be a chkdsk /r?

Any help would be appreciated. We haven't had any corruption reports in the SQL error log yet. I imagine it could get serious depending on what parts of the MFT are corrupted (as previously stated it could affect SQL's ability to read MDFs or NDFs)?

Thanks


texts: Looking for a way to measure trail and error query alternatives

texts:
I would like to tune my query but i found that i can not measure the execution time correctly because SQL is using cache and some other optimizations so every time i'm executing the same query i am getting a different statistics.

I would like to find a away to get the same statistics every time i'm executing the same query.

I tried to run the following before every execution but with no success:

DBCC FreeSystemCache
DBCC FreeProcCache
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS


texts: Bitmask Flags with Lookup Tables Clarification

texts:
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic.

Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records.

Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results.

I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented.

Is this as simple as creating a lookup table that has all possible search conditions?

Example for 3 bits using (a & b) (Edit: Wrong bitwise op)

001,001
001,011
001,101
001,111
010,010
010,011
010,110
011,011
011,111
etc

The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows.

Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated.

Edit: A more concrete example using small data.

  Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all.

Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &).

Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions.


texts:
I have a very complex query (who hasn't :-)). In this query is a JOIN to a table like this one (of course the original has some more columns):

CREATE TABLE dbo.tbl_detail (id bigint PRIMARY key,
                   descr varchar(300),
                   txt varchar(MAX),
                   descr_p varchar(300)
                  );

There are two joins to this table in the query:

LEFT JOIN dbo.tbl_detail td1 ON td1.id = main.detail_id
LEFT JOIN dbo.tbl_detail td2 ON td2.id = main.second_id
The query returns ~200k rows.
td1 outputs all columns
td2 outputs all columns except [txt] (varchar(max))

Problem:

td2 uses an HASH JOIN
but td1 uses an NESTED LOOKUP (over 200k rows; no statistic problem, since the estimates are correct)
when I remove the [txt] column from the td1 output it uses a HASH JOIN too
when I alter the column to VARCHAR(5000) (or smaller) it uses a HASH JOIN
when I alter the column to VARCHAR(8000) it uses the NESTED LOOKUP again
OPTION (HASH JOIN) or LEFT HASH JOIN would work, but produces an extrem slow query plan

Question:
Why does it not always use the HASH JOIN? Is there a length limit (per column or sum of all output columns)?

PS: Microsoft SQL Server 2014 (SP2) Developer Edition


texts: Which MySQL settings are the closest equivalent to RDS' "Queue depth"?

texts:
I'm trying to find out which MySQL status variables can be used to get as close as possible to the RDS value of "Queue depth".

On a test system, I reckon that %pending% variables may be at least a part of it:

mysql> show global status like '%pending%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| Innodb_data_pending_fsyncs   | 18446744073709551615 |
| Innodb_data_pending_reads    | 0                    |
| Innodb_data_pending_writes   | 0                    |
| Innodb_os_log_pending_fsyncs | 0                    |
| Innodb_os_log_pending_writes | 0                    |
+------------------------------+----------------------+

although in this case, Innodb_data_pending_fsyncs is clearly a bogus value.

Assuming I'm on an InnoDB-only setup, which are the values that I can use for this purpose?


texts:
I have a table With currency, paymenttype and invoiceamount. I have to write a query to get currency, paymenttype and total invoiceamount made.

This is pretty simple with group by. But actually I have three payment types - 0, 1, 2. The data in the table is

    Currency   PaymentType Invoice Amount
    Aaa.        0.           100
    Aaa.        1.           200
    Aaa.        1.            50
    Bbb.        0.           150
    Bbb.        1.           100
    Bbb.        2.           100

My query is

Select currency, paymenttype, sum(invoiceamount) as total
from table
group by currency, paymenttype

Result

      Currency  paymenttype total
      Aaa.       0.          100
      Aaa.       1.          250
      Bbb.       0.          150
      Bbb.       1.          100
      Bbb.       2.          100

As Aaa. does not have paymenttype 2 it should also show a row with 0 value like below.

      Aaa.      2.     0

How to do this?


texts: How to AUTOINCREMENT against another column rather than the whole table in sqlite?

texts:
I'm using a table to track messages sent in several different channels.

I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'll use 'id' instead.

Here's an example of the end result that I'm looking for:

┌───────┬────────────┬────┬───────────────────┐
│ rowid │ channel_id │ id │       text        │
├───────┼────────────┼────┼───────────────────┤
│     1 │          1 │  1 │ Hello!            │
│     2 │          1 │  2 │ Message text      │
│     3 │          1 │  3 │ Beep boop         │
│     4 │          2 │  1 │ Different channel │
│     5 │          2 │  2 │ So id has reset   │
└───────┴────────────┴────┴───────────────────┘

I should then be able to INSERT INTO messages(channel, text) and have id fill itself.

Normally I'd use id INTEGER NOT NULL PRIMARY KEY or id INTEGER NOT NULL AUTOINCREMENT. However, I believe that this increments the id per-table, not per-channel.

Is there a way that I can make the id increment only such that it creates unique combinations with another column?


texts:
I'm trying to implement database level audit on some tables for tracking the changes. When I activate the audit I see the results in the audit file as seen below.

UPDATE "MYDB"."dbo"."MyCompany$Mail Setup" WITH (REPEATABLEREAD, ROWLOCK)
SET "Description"=@P1 WHERE ("Code"=@P2) AND ("timestamp"<=@P3)

As seen from the example, I cannot see the actual parameter values.
The application creating this statement is a well-known Microsoft ERP application and we do not have a chance to change the way it creates sql statements. I believe it's using dynamic sql statements to execute queries on the server.

Is there anyway to achieve this goal or am i missing something here?
(I'm trying to use SQL level auditing because ERP application audit has some limitations.)


texts: ActiveX script throwing runtime error inside DTS package

texts:
I am a DBA. We have migrated SQL 2000 databases + DTS packages to SQL 2008. One of the DTS packages runs an ActiveX script to open an Excel file (which has runs macros when you open it).  Coding inside the ActiveX script is:

Function Main()
    Set XlApp = CreateObject("Excel.Application")
    call XlApp.Workbooks.Open("\\server\c$\path.xls",3)
    call XlApp.Workbooks.Open("\\server\c$\path2.xls",3)
    call XlApp.Workbooks.Open("\\server\c$\path3.xls",3)
    set XlApp = nothing
    Main = DTSTaskExecResult_Success
End Function

Sometimes it throws "Active X component has experienced a run time error" without even telling the details about the error and sometimes it runs fine. The DTS package is run using a SQL agent job.
Is there a better and reliable way to do this without using the ActiveX script?

Thanks!


texts: Is there a progress indicator for OPTIMIZE TABLE progress?

texts:
MySQL 5.1.4x (Windows) | Innodb

I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use

OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;

to reduce the data space footprint the empty whitespace is taking up in the filesystem.

My concern is that once I start this command, I won't know how long it's going to take or where it is in the process. Is there anyway I can determine this information? As far as I know, there's no progress indicator.


texts:
I am getting these error messages one after the other every week on Win 2008 and SQL 2008 R2. MAX DOP is set at 1.  Any idea how I can solve this?

Thanks.

  DESCRIPTION:   A fatal error occurred while reading the input stream
  from the network. The session will be terminated (input error: 64,
  output error: 0).  DESCRIPTION:   The client was unable to reuse a
  session with SPID 60, which had been reset for connection pooling. The
  failure ID is 29. This error may have been caused by an earlier
  operation failing. Check the error logs for failed operations
  immediately before this error message.


texts: Messages in SQLCMD script output are not in order

texts:
I am running a batch file that launches a SQLCMD script that creates an Agent job from TFS Release Manager.

The script assigns SQLCMD parameters to variables, lists the variables using selects and displays some feedback in the running of the script throughout the script.

It looks something like this:

SET NOCOUNT ON

DECLARE
    @Var1 NVARCHAR(100) = '$(Var1)'
    ,@Var2 NVARCHAR(100) = '$(Var2)'

SELECT '@Var1' AS VarName,@Var1 AS VarValue
UNION ALL
SELECT '@Var2',@Var2

PRINT 'Do the first thing'
--delete sql agent job

PRINT 'Do the next thing'
--create sql agent job

PRINT 'Do the last thing'
--add steps

Every so often, in the output, I see the PRINT messages in the middle of the SELECT output.

Is there a way to guarantee the order of these messages?


texts: Automated UML2 Class Diagram generation from SQL?

texts:
How can I generate a class diagram from an SQL database or script?  I'll add the functions and set whether the attributes are public, private or virtual once the initial diagram is generated.

I've generated it using PowerDesigner, here is the .sql file: http://pastebin.com/S14JWjKF


texts: Many to Many association table PK as FK in other table

texts:
I have the following tables: school, teacher, school_teacher and student.

school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher.

A student belongs to a teacher of a particular school and a teacher of a school has many students.

My question is whether I should create the foreign key in the student table to point to school_teacher table PK or to the two tables PKs (school, teacher) individually?

I believe it would be best for referential integrity to reference school_teacher to ensure that transaction records are only inserted if there is a relationship between the school and the teacher. In terms of best practices, what is the best option or the best way to address this problem? Is there any other possibility better than the ones I mention?

In general, is there a problem in which the primary key of an association table is a foreign key in another table?

This is simply an example. In a real case a student could have more than one teacher. It is to simplify the example.


texts: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS

texts:
I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from  visual studio or from SQL Server Management Studio, it ran successfully. But when i schedule it using SQL server agent job, an error occur.

  Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.  Source: .Net SqlClient Data Provider

What is this error? Why this happen only when scheduled? How to overcome this problem?


texts: MySQL consumes memory for each portion of read

texts:
I'm using MySQL instance for read only operations (data migration to another DB).

I'm reading several dozens of millions rows from one table using limited portions of data (100 000 rows) using select SQL_NO_CACHE * from TABLE.

After each read portion MySQL consumes about 50-100 MB of RAM, and doesn't free memory.

I have no idea, why it does consumes memory as I read data with SQL_NO_CACHE parameter.
At least after several iterations of reading all memory becomes eaten and server starts dropping incoming connections.

So my question is: how to avoid this constant memory consuming for read-only usage?

UPD If it matters, it is AWS RDS instance of MySQL


texts:
There are several source code formatters for SQL in SSMS.

I have tried a couple and found that are not configurable in terms of what formatting they do (e.g. indenting).

Does anyone know of one that is configurable in terms of how it formats the code?

Thanks.


texts: Efficient predictable ordering in PostgreSQL

texts:
I have a large table of locations. I would like to efficient paginate though the table. I had previously being using an OFFSET approach however the size of the table made that approach unusable. So I am now trying a cursor approach using the location id.

In order to ensure consistent ordering for cases where a user has 2 rows with identical timestamp, I am also ordering by id.

SELECT  *
FROM locations
WHERE
  user_id = 1
ORDER BY timestamp desc, id
LIMIT 100;

However after adding id to the ORDER BY, the query has become slow. It is now doing a Seq Scan which takes ~20 seconds.

QUERY PLAN
Limit  (cost=502534.86..502535.11 rows=100 width=152) (actual
time=22822.113..22822.142 rows=100 loops=1)
  ->  Sort  (cost=502534.86..515512.80 rows=5191175 width=152) (actual time=22822.110..22822.133 rows=100 loops=1)
        Sort Key: ""timestamp"" DESC, id"
        Sort Method: top-N heapsort  Memory: 51kB
    ->  Seq Scan on locations  (cost=0.00..304131.89 rows=5191175 width=152) (actual time=1.603..21284.908 rows=5169237 loops=1)
          Filter: (user_id = 1)
          Rows Removed by Filter: 3048468
Planning time: 0.204 ms
Execution time: 22822.194 ms

Timestamp collisions are edge cases and id is a primary key. So why does the execution plan require a Seq Scan?

For context

SELECT indexdef
FROM pg_indexes
WHERE tablename = 'locations'

results

CREATE UNIQUE INDEX locations_pkey ON locations USING btree (id)
CREATE INDEX index_locations_on_user_id_and_timestamp ON locations USING btree (user_id, "timestamp")
CREATE INDEX index_locations_on_user_id_and_point ON locations USING gist (user_id, point)
CREATE INDEX index_locations_on_user_id ON locations USING btree (user_id)
CREATE INDEX index_locations_on_user_id_and_timestamp ON locations USING btree (user_id, "timestamp")
CREATE INDEX index_locations_on_user_id_and_timestamp_and_id ON locations USING btree (user_id, "timestamp", id)


texts: RETURNING value from SELECT table in INSERT .. SELECT

texts:
I have a table that looks like this:

| column | def  |
| ------ | ---- |
| id     | uuid |
| status | int  |
| data   | text |

I'd like to write a query to duplicate a row (changing only a few columns) and return the old (replaced) data:

INSERT INTO tmp (status, data)
SELECT source.status, :data
FROM table AS source
WHERE id = :id
RETURNING id, source.data

but RETURNING can only access values from the inserted row, is there an alternative to do this?


texts: How do I export Postgres data in "Insert ... on conflict" format instead of "cp" format?

texts:
I’m using Postgres 9.5 on Mac Sierra.  I want to export some records from my local db and import them into a db on a Linux machine (also running PostGres 9.5).  I’m using this command to export data from my local machine …

localhost:myproject davea$ pg_dump -U myproject mydb -a -t table1 -t table2 -t table3 > /tmp/pgdata.sql

The data is exported in a series of copy commands.  Is there a way to export the table data so that the file has a bunch of “INSERT ... ON CONFLICT DO NOTHING;” statements?  There are some duplicates in the origin database and the destination db but I don’t want that to derail the import of the non-duplicate data.


texts: Cannot process cube using SQL agent job in SSMS

texts:
I have an issue where we have a user who has used his own AD account to create and deploy the cube back when he was the sole DBA.

I would like to have an proxy account with a password known internally instead of his own credentials.

For this I have created a new AD account, ProcessCube, which I have given sysadmin rights on the underlying SQL Engine. I have also created a role called Process cube on the SSAS database with read and write rights on every cube and read rights on data sources.

In SSAS I have double clicked the data sources and under Impersonation mode I have replaced the old credentials with the credentials of the newly created AD account ProcessCube.

I have not deployed the cube with the new credentials, but rather let the SQL SSAS command in the SQL agent do the trick, do I need to deploy the cube first?

In the SQL Agent I've made ProcessCube the Owner of the job which is a SQL Server Analysis Server Command.

When I try to execute the SQL agent job I get the error:

The following system error occurred:  The user name or password is incorrect.  Source="Microsoft SQL Server 2016 Analysis Service

I assume I need some more credentials, what more do I need? What have  I missed?

The command I try to execute in the agent is as follows:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
    <KeyErrorLimit>-1</KeyErrorLimit>
    <KeyNotFound>IgnoreError</KeyNotFound>
    <NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>
  </ErrorConfiguration>
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
      <Object>
        <DatabaseID>DataBaseID</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>


texts: Invert a Boolean expression which can return UNKNOWN

texts:
Example
I have a table
ID  myField
------------
 1  someValue
 2  NULL
 3  someOtherValue

and a T-SQL Boolean expression which can evaluate to TRUE, FALSE or (due to SQL's ternary logic) UNKNOWN:
SELECT * FROM myTable WHERE myField = 'someValue'

-- yields record 1

If I want to get all the other records, I cannot simply negate the expression
SELECT * FROM myTable WHERE NOT (myField = 'someValue')

-- yields only record 3

I know how why this happens (ternary logic), and I know how to solve this specific issue.
I know I can just use myField = 'someValue' AND NOT myField IS NULL and I get an "invertible" expression which never yields UNKNOWN:
SELECT * FROM myTable WHERE NOT (myField = 'someValue' AND myField IS NOT NULL)

-- yields records 2 and 3, hooray!

General Case
Now, let's talk about the general case. Let's say instead of myField = 'someValue' I have some complex expression involving lots of fields and conditions, maybe subqueries:
SELECT * FROM myTable WHERE ...some complex Boolean expression...

Is there a generic way to "invert" this expession? Bonus points if it works for subexpressions:
SELECT * FROM myTable
 WHERE ...some expression which stays...
   AND ...some expression which I might want to invert...

I need to support SQL Server 2008-2014, but if there's an elegant solution requiring a newer version than 2008, I'm interested to hear about it too.


texts: How to write a shell script to check the mysql database backup status.

texts:
I have mysql production databases running in main server(Linux) and from this server every night the backup is taken into local server.
Now i want to find a shell script to check if all the databases backup is up to date or not(for an example: i have twenty database in the main server so i want to know all of them have been backed up completely or not) and compare the each database's current size with their previous size, in a sense i want to make sure with through this script that my backup is perfect and if not i will have a track of that occurrence.

Please help me to get a solution of this problem.


texts: How to save result of sp_BlitzWho to a table? An option to save to table is not there compared to other procs like sp_Blitz.


texts:
The query cache hit value is not changing in my database only for some queries.If I write

select * from drugs_info limit 1000;

the query cache hit has changed, but if I execute the following queries for two to three times the query cache hit is not changing. The queries as follows:

select * from drugs_info limit 10000;

select * from drugs_info limit 15000;

like this. I don't know what is the problem.

The table, which contains 64500 rows:

CREATE TABLE drugs_info (
    did int(11) NOT NULL AUTO_INCREMENT,
    brand_name varchar(150) DEFAULT NULL,
    generic varchar(500) DEFAULT NULL,
    tradename varchar(150) DEFAULT NULL,
    manfactured varchar(100) DEFAULT NULL,
    unit varchar(300) DEFAULT NULL,
    type varchar(50) DEFAULT NULL,
    quantity varchar(50) DEFAULT NULL,
    price float DEFAULT NULL,
    PRIMARY KEY (did),
    KEY id1 (brand_name,generic)
)
ENGINE=InnoDB AUTO_INCREMENT=64379 DEFAULT CHARSET=latin1;

and the query:

select * from drugs_info;

and my settings are

mysql> show status like 'qcache%';
+-------------------------+---------+
| Variable_name           |  Value  |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8958376 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

Why is this?


texts:
I've read that a decent backup strategy would be:

1) Full backup weekly
2) Differential backups daily
3) Log backups every 5 minutes.

Now question is how do I write this stored procedure and create a sql job out of it. For eg. For differential backup, it is necessary that full backup must exist. Now when it is Monday 12 AM and it is time for both weekly and differential backup to run. It is possible that differential backup runs before full backup. How do I handle these tricky conditions? Could anybody please share the solution for this?


texts:
I am developing a laboratory database query which compares test results (testResults) to specification conditions (specCond).  I cannot get SQL Server to retrieve the results efficiently.

Schema: test  (1-*, test_id) samp

The execution plan for the query below shows SQL Server retrieves testResults for ALL tests before filtering on test.testName.  I assume this is because of the complexity of my testResults view.

SELECT test_id, stat, testVal
  FROM testResults tr
  JOIN test ON tr.test_id = test.test_id
 WHERE test.testName = 'someTestName'

testResults Option 1: CROSS JOIN

-- testResults_CROSSJOIN
SELECT t.test_id,
    specStat.stat,
    CASE specStat.stat
        WHEN 'MIN'          THEN tMin
        WHEN 'MAX'          THEN tMax
        WHEN 'AVG'          THEN tAvg
        WHEN 'STDEV'            THEN tStdev
        WHEN 'EV'           THEN tMax - tMin
    END AS testVal
FROM (
    SELECT test.test_id,
        MIN(samp.result) AS tMin,
        MAX(samp.result) AS tMax,
        AVG(samp.result) AS tAvg,
        STDEV(samp.result) AS tStdev
    FROM test
    INNER JOIN samp ON test.test_id = samp.test_id

    WHERE (samp.status = 'OK')
    GROUP BY test.test_id
    ) AS t
CROSS JOIN specStat -- Contains stat <= ('MIN', 'MAX', 'AVG', 'STDEV', 'EV')

testResults Option 2: UNPIVOT

select i.test_id, i.stat, i.testVal
from (
        SELECT test.test_id,
            CAST (MIN(samp.result) AS Numeric(18,9)) AS "MIN",
            CAST (MAX(samp.result) AS Numeric(18,9)) AS "MAX",
            CAST (AVG(samp.result) AS Numeric(18,9)) AS "AVG",
            CAST (STDEV(samp.result) AS Numeric(18,9)) AS "STDEV",
            CAST (MAX(samp.result) - MIN(samp.result) AS Numeric(18,9)) AS "RANGE"
        FROM test
        INNER JOIN samp ON test.test_id = samp.test_id
        WHERE samp.status = 'OK'

        GROUP BY test.test_id
) t
unpivot ( testVal for stat IN ("MIN", "MAX", "AVG", "STDEV", "RANGE")) i

join test on i.test_id = test.test_id
where test.testName = 'P1STD'

How can I rewrite my testResults view so that it ONLY retrieves results for the joined test(s)?  (Ideas: query hints, another transformation type, etc)


texts:
I have a very simple table storing the following fields per http request:

id serial
url varchar
date varchar (yyyy-mm-dd)
time varchar (hh:mm)
latency integer

And I am trying to calculate average latency per minute, however if i have 10 requests (9 of 1s and 1 of 100s) then average becomes useless. In order to solve that I was wondering about remove outliers.

So, basically what I want is to calculate the average excluding request which latency is bigger then 99.7%, naturally I want to group those requests per minute in order to follow "average" latency per minute.

My query is as follows:

select mdate,mtime,avg(latency)
from mtable
  join (
    select mdate,mtime,avg(latency) + 3*stddev(latency) as "uband"
    from mtable
    where url like '%ThePartialUrl%'
    group by, mdate, mtime
 ) as t
 on mdate=t.mdate and mtime=t.mtime and latency < t.uband
 where url like '%ThePartialUrl%'
 group by mdate,mtime
 order by mdate, mtime

However it is taking too much time.
Things I've already know/done:

url like requires a full scan regardless of index
mdate and mtime could be one column using datetime value
I have index for almost everything (mdate,mtime,latency)
ajusting work_mem parameters

The table itself has around 100M rows at total.

Looking at the execution plan it seems the nested loop (join) is the one taking most time since the subquery returns 40K rows (1 per minute during a whole month) and it shall be joined around to 10M rows for the specific '%ThePartialUrl%', resulting in 4 a billion loop.

Anything that could help me improve it?

I am using postgres 10.

Thanks and Regards


texts: Postgres: Why does pg_stat_get_dead_tuples return 0 when there is still wasted space?

texts:
Using Postgres 11.1, I have been trying to determine/estimate the amount of 'wasted' space in a table that would be recovered by a VACUUM FULL. My plan was to use pg_stat_get_live_tuples (L), pg_stat_get_dead_tuples (D), and pg_total_relation_size (S), then estimate the wasted space as (D / (L+D)) * S.

However, this does not appear to work. While investigating why not, I did the following setup:

CREATE TABLE sam_silly(txt TEXT);
INSERT INTO sam_silly VALUES('one');
INSERT INTO sam_silly VALUES('two');
INSERT INTO sam_silly VALUES('three');
INSERT INTO sam_silly VALUES('four');
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;

Then I did an initial count of the space according to these functions. The results were as expected (there are a million i.e. 2^20 rows):

select pg_total_relation_size(c.oid) AS size, pg_stat_get_live_tuples(c.oid) AS live, pg_stat_get_dead_tuples(c.oid) AS dead
FROM pg_class c where relname='sam_silly';

47308800, 1048576, 0

I then deleted half the table:

delete from sam_silly where txt like 'one%' or txt like 'three%';

Immediately repeating the query above gave the results I would expect:

47308800, 524288, 524288

Half the data is alive, half dead.

Then I ran 'analyze' and this is where things got weird:

47316992, 524288, 0

Space went up slightly but now there are no dead rows!

I tried 'vacuum' too:

47316992, 524264, 0

Not much change.

Finally, after VACUUM FULL it behaves as I would expect again:

23519232, 524264, 0

Now there are no dead tuples but also the space has been recovered.

The real database has autovacuum on, so it will probably vacuum the tables I'm interested in. But it looks like vacuuming causes it to set the number of dead tuples to 0 even though there is still a lot of 'wasted' space.

Is there a way to find out/estimate how much space is 'wasted' in this situation, when pg_stat_get_dead_tuples is returning zero even though it has not reclaimed the space used by the previously-reported dead tuples?

Exact version in case relevant: 'PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit'


texts: Byte ordering for multibyte characters in SQL Server versus Oracle

texts:
I am currently in the process of migrating data from Oracle to SQL Server and I'm encountering an issue trying to validate the data post-migration.

Environment Details:

Oracle 12 - AL32UTF8 character set
Client - NLS_LANG - WE8MSWIN1252
VARCHAR2 field

SQL Server 2016

Latin1_General_CI_AS collation
NVARCHAR field

I'm using DBMS_CRYPTO.HASH on Oracle to generate a checksum of the whole row, then copying to SQL and using HASHBYTES to generate a checksum of the whole row, which I'm then comparing to validate the data matches.

The checksums match for all rows, except those with multibyte characters.

For example, rows with this character: ◦ do not match in the checksums, even though the data is transferred correctly. When I use DUMP in Oracle or convert to VARBINARY in SQL Server the data matches exactly except for the bytes for this character.

In SQL Server, the bytes are 0xE625 and in Oracle they are 0x25E6.

Why are they ordered differently, and is there a reliable way to convert one to the other to ensure the checksum at the other end matches for strings with multi-byte characters?


texts: Should I create a new table or save "static" data in all the tables?

texts:
I'm using Postgres as the database management system.

Currently, I have  a countries table, which has only four countries, i.e. four rows (might add one or two more countries, i.e. rows, later).

--Table 1: countries

id
country_name

I have many tables, referencing to this countries table.

--Table 2: events
name
country_id
date

--Table 3: servers
server_name
country_id

--Table 4: packages
package_name
country_id

The problem is whenever I want a country name, I wanted to join the countries table with other table and make the query to get the country_name value(s).

As the countries table is "static" with only four country rows, should I create a column named country_name in the tables 2, 3, 4 and save the country value as a string, as this will get rid of the countries table and also will get rid of all Joins in my query?

Kindly advice what is the correct approach when dealing with "static" names.


texts:
I plan on setting up mirroring on a SQL 2012 database which is currently in production. My concern though is how much it will affect the performance of the principal database.

I am using mirroring for disaster recovery and nobody will require access to the mirrored database except in the event of a problem.

I am planning on doing asynchronous mirroring. Is this a good option?


texts: Mysql master/master replication, what is the problems

texts:
I tried to set up Master <-> Master replication on production and some people they tell me that is not a good option.

I want to know why is not a good option and what is the problem that may find? :)

Mysql version: 5.7.23

mysql config:

auto-increment-increment = 2
# 1 Node
auto-increment-offset = 1
# 2 Node
auto-increment-offset = 2


texts: What impact do indexes have on the performance of table modifications?

texts:
I have always wondered what the effect indexes have on table modification in SQL Server. Will modification of a table will be slower with an increased number of indexes applied to that table?

My gut feeling says that this will induce overhead of re-indexing but I could be wrong.


texts:
I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML.

Something about this is causing a Remote Scan. Any ideas on how to go about tuning it?

declare @tab_office as table (id varchar(100))
    declare @doc_filter int
    exec sp_xml_preparedocument @doc_filter output,@office_id
    insert into @tab_office (id) (select office_id from Openxml(@doc_filter,'//filter') with (office_id varchar     (100) 'office_id'))

xml data

'

OOT9

OOT8

OOT5

'


texts: Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

texts:
I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should be in good shape. I can have the procedures handle all the audit logging, wrap things in transactions as needed, etc.

But to do this, I need to deny direct DML statements to everybody on this particular schema - even the sysadmin server role. Is there any simple way to do this without resorting to INSTEAD OF triggers (which wouldn't be quite as bulletproof)? I don't see any syntax like "DENY INSERT, UPDATE, DELETE ON SCHEMA::schemaname TO ALL", and denying permissions to the "public" role doesn't seem to have the desired effect.


texts: SSRS 2008 - How to Total values from an expression

texts:
I'm trying to figure out how to total values from a calculated field.

In the example below, I have the following expression:

=IIF(Fields!Role.Value = "Contractor", Fields!actualwork.Value * 100,
Fields!actualwork.Value * 77)

Now I want to provide a total, but it doesn't show up on the report. Is there something I'm missing?


texts:
I have a table that uses an integer for an id. I need to change this to a String and prefix all entries shorter than 4 with zeroes. The database is MySql. I converted the field to a VARCHAR(6), but not need to convert the data in the column to have it prefixed. I tried the following code, but it did not update any of the rows. Any help as to what I am doing wrong would be appreciated.

UPDATE clients
SET clientId =
    CASE
    WHEN char_length(clientId) = 1 THEN '000' + clientId
    WHEN char_length(clientId) = 2 THEN '00' + clientId
    WHEN char_length(clientId) = 3 THEN '0' + clientId
    ELSE clientId END
WHERE
    char_length(clientId) < 4;


texts: MySQL tables do not show up in the phpMyAdmin and MySQLWorkbench

texts:
I just setup the phpMyAdmin and I have this problem: When I login, I can't see any tables.

I had tried this with MySQLWorkbench and I got the same result.

Any suggestions ?


texts: PHP / MYSQL connection failures via socket under moderate load

texts:
I've done quite a bit of reading before asking this, so let me preface by saying I am not running out of connections, or memory, or cpu, and from what I can tell, I am not running out of file descriptors either.

Here's what PHP throws at me when MySQL is under moderate load:

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11 "Resource temporarily unavailable")

This happens randomly under moderate load - but the more I push, the more frequently php throws this at me. While this is happening I can always connect locally through the console and from PHP through 127.0.0.1 instead of "localhost" which uses the faster unix socket.

Here's a few system variables to weed out the usual problems:

cat /proc/sys/fs/file-max = 4895952
lsof | wc -l = 215778 (during "outages")
Highest usage of available connections: 26% (261/1000)

InnoDB buffer pool / data size: 10.0G/3.7G (plenty o room)

soft nofile 999999
hard nofile 999999

I am actually running MariaDB (Server version: 10.0.17-MariaDB MariaDB Server)

These results are generated both under normal load, and by running mysqlslap during off hours, so, slow queries are not an issue - just high connections.

Any advice? I can report additional settings/data if necessary - mysqltuner.pl says everything is a-ok

and again, the revealing thing here is that connecting via IP works just fine and is fast during these outages - I just can't figure out why.


texts: How does Yelp efficiently calculate distance in the database?

texts:
For example, say I have a table:

Business(BusinessID, Lattitude, Longitude)

All are indexed of course. Also there are 1 million records

Say I want to find businesses closest to 106,5, for example, how would I do so?

If I do

SELECT *
FROM Business
WHERE (Some formula to compute distance here) < 2000

for example, or if I do

SELECT *
FROM Business
TOP 20

In theory the computer will have to compute distance for all biz while in practice only those with lattitude and longitude within a certain range that should be computed.

So how can I do what I want in PhP, or SQL, for example?

I am grateful with the answer so far. I am using mysql and they do not have anything more efficient than the obvious solution. MySQL spatial do not have compute distance function either.


texts: Drop All Columns in a Table that are not Keys

texts:
This may seem like an odd request but let me explain, there may be a tool that I haven't found that will do what I need. I couldn't make SSMS do what I needed

Goal: To deliver a Database Diagram that contains tableName, their PKs and FKs for related tables and their relations for several groups of tables.

What I have done is to create a copy of the DB and plan on removing all columns that are not PK or FK from each table. Which I am not thrilled with doing since it could range from 150 tables to 950 tables.

So I would like either (or both :)) the name of a tool that will diagram as I need or a script that I can use to delete all columns that are not PK or FK from all non-system tables.


texts: How to pass parameter value for ExecuteReader() - powershell

texts:
I am trying to run following powershell script. How Can I pass parameter value for @P0= '2019-11-22' and @P1 = '123456789' during runtime?

$sqlconnection = New-Object System.Data.SqlClient.SqlConnection
$sqlconnection.ConnectionString = "server = Servername;Database=TestDB;Integrated security=true"
$sqlconnection.OPEN()

$sqlCmnd = New-OBJECT SYSTEM.DATA.SqlClient.SqlCommand
$sqlCmnd.Connection = $sqlconnection
$sqlCmnd.CommandText = "select distinct table1.column1, ..........  where table2.column='value' and table2.coulumn=@P0 and table2.column=@P1 order by table1.Column1"

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@P0",[Data.SQLDBType]::varchar, 8000))) | OUT-NULL
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@P1",[Data.SQLDBType]::varchar, 8000))) | OUT-NULL

$sqlCmnd.ExecuteReader()
$sqlconnection.close()


texts:
I have this table

Grade        Lec         Lab
1.5           3           0
1.5           3           0
1.5           2           1

The formula will be like this

1.5 * 3
1.5 * 3
1.5 * (2+1) = 13.5 / 9 = 1.5

13.5 is the total then 9 is the total sum of the lec + lab

What query should I use?

How can i add this to query WHERE Lec + Lab = 15. If lec + lab is 15 above will show but if lower than 15 it will not show then i want to limit the show in 20


texts: MySQL Query to Display Organized by Date


texts: Restoring database from a server that uses data compression

texts:
In our environment our production server is using MS SQL Server 2008 Enterprise Edition.  We have a development and test server that runs MS SQL Server Standard Edition and the table structure between all three environments is exactly the same.

We frequently refresh our dev and test environments from our production database.

I'm looking into turning on data compression in our production environment, but reading this technet article it sounds like we will no longer be able to perform these refreshes.  Am I reading this correctly?

Quote from article:

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions.

Will I be forced to upgrade our dev/test DB to Enterprise Edition to continue to refresh our data?


texts: Is it possible to create a database called C that is a view into all tables in database A + B?

texts:
Using SQL 2005 or 2008, is it possible to create a database called C that is a view into all tables in database A + B ?   Databases A, B, and C  are on the same local server.

I am wanting to be able to create join queries between tables that are in 2 different databases.    Is there another way to do this?