Using PIVOT to convert multiple rows and columns So my current query output is as follows:

I would like to consolidate all of these rows to just one long row like so:

MyID    Customer  relc_rate  relc_consumption   relc_daysofservice   relc_unitmeasure   relc_charge   relc_chargedate
-----   --------  ---------  ----------------   ------------------   ----------------   -----------   ---------------
10016    112992    110W            753                  31                 KWH

and Pretty much repeat that for RGAS, RWTR, etc all across one row. Yes I understand that will be about over 50 columns if I consolidate all the service types into its own column.

Now I was able to get started with getting the service number into the "Type" using this Pivot query. However, I got stuck after that trying to figure out how to get each service type, along with the rate, etc into it's own column.

select *
from
(
 SELECT [Premise]
    , [Premise Address] premise_address
    , [Customer] as customer_no
    , [Service Type]
    , [Service Number]

 FROM UTIL_MAY2019
) premise_table
pivot
(
  sum([Service number])
  for [Service Type] in ([RELC], [RWTR], [RSWR], [RGAR], [STRM], [DELC], [SLCM], [CGAS], [CWTR], [CSWR], [CGAR])
) piv_premise

Here's the DDL for testing:

CREATE TABLE supercharge (
MyID int
 , customer int
 , Servicetype varchar(10)
 , servicerate varchar(10)
 , serviceNumber int
 , consumption int
 , daysofservice int
 , unitofmeasure varchar(10)
 , chargeAmount double
 , chargeDate datetime
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate)

VALUES
(10016, 112992, RELC, '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'),
(10016, 112992, RGAS, '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'),
(10016, 112992, RWTR, '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'),
(10016, 112992, RSWR, '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'),
(10016, 112992, RGAR, '', 500, null, null, '',32.12, '2019-05-08'),
(10016, 112992, STRM, '', 700, null, null, '',2.38, '2019-05-08'),
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'),
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

Well, this solution is not particularly sophisticated (it doesn't use PIVOT), but it would solve your problem.  There are probably smarter ways to address the problem, but here is one method.

The solution selects from a derived unpivoted table (note comment about unpivot original data).  It groups by MyId and customer and uses CASE expressions to derive the individual column names and values.  My example only show two of the servicetype columns broken out into separate columns, but hopefully you'll see the pattern.  (I'm not sure how you handle the rows with blank servicetype).

--demo setup
DROP TABLE IF EXISTS supercharge
GO
CREATE TABLE supercharge (
MyID int
 , customer int
 , Servicetype varchar(10)
 , servicerate varchar(10)
 , serviceNumber int
 , consumption int
 , daysofservice int
 , unitofmeasure varchar(10)
 , chargeAmount decimal(11,2)
 , chargeDate datetime
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate)

VALUES
(10016, 112992, 'RELC', '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'),
(10016, 112992, 'RGAS', '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'),
(10016, 112992, 'RWTR', '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'),
(10016, 112992, 'RSWR', '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'),
(10016, 112992, 'RGAR', '', 500, null, null, '',32.12, '2019-05-08'),
(10016, 112992, 'STRM', '', 700, null, null, '',2.38, '2019-05-08'),
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'),
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

--solution
SELECT MyID
    ,customer
--RELC columns
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'rate'
                THEN value
            END) AS Relc_Rate
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'consumption'
                THEN value
            END) AS Relc_Consumption
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'daysofservice'
                THEN value
            END) AS Relc_DaysOfService
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'unitofmeasure'
                THEN value
            END) AS Relc_UnitOfMeasure
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'chargeamount'
                THEN value
            END) AS Relc_Charge
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'chargedate'
                THEN value
            END) AS Relc_ChargeDate
--RGAS columns
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'rate'
                THEN value
            END) AS Rgas_Rate
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'consumption'
                THEN value
            END) AS Rgas_Consumption
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'daysofservice'
                THEN value
            END) AS Rgas_DaysOfService
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'unitofmeasure'
                THEN value
            END) AS Rgas_UnitOfMeasure
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'chargeamount'
                THEN value
            END) AS Rgas_Charge
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'chargedate'
                THEN value
            END) AS Rgas_ChargeDate

FROM
--unpivot original data
(
select MyID,customer,Servicetype, col, value
from supercharge
cross apply
(
  select 'rate', cast(servicerate as varchar(10)) union all
  select 'consumption', cast(consumption as varchar(10)) union all
  select 'daysofservice', cast(daysofservice as varchar(10)) union all
  select 'unitofmeasure', cast(unitofmeasure as varchar(10)) union all
  select 'chargeamount', cast(chargeAmount as varchar(10)) union all
  select 'chargeDate', convert(varchar(10), chargeDate,121)
) c(col, value)
) d
GROUP BY MyID
    ,customer;

| MyID  | customer | Relc_Rate | Relc_Consumption | Relc_DaysOfService | Relc_UnitOfMeasure | Relc_Charge | Relc_ChargeDate | Rgas_Rate | Rgas_Consumption | Rgas_DaysOfService | Rgas_UnitOfMeasure | Rgas_Charge | Rgas_ChargeDate |
|-------|----------|-----------|------------------|--------------------|--------------------|-------------|-----------------|-----------|------------------|--------------------|--------------------|-------------|-----------------|
| 10016 | 112992   | 110W      | 753              | 31                 | KWH                | 99.92       | 2019-05-08      | 120       | 0                | 31                 | CCF                | 5.31        | 2019-05-08      |


SQL Server 2008, cpu 100% until recreate an index I have a 4000000 lines table (SQL Server 2008). This table has a private key composed of two int columns. One corresponds to a PK in another table of the same database. There is a relation between them. The other to a PK of a table in another database. (I don't know if it is important)

Sometimes, there are the same amount of queries as usualy, but the CPU usage grows to 90-100%. And some stored procedures on this table are too long. At this moment, just SELECT queries run on this table.

If I wait, the CPU usage gets normal after 1 or 2 hours.
But I also can drop and recreate the PK index. And the CPU usage gets normal immediately. So I think the problem is related to this index. Am I wrong ?

But the index is not fragmented. I rebuild it every night in a maintenance plan. I even tried to reorganise it after each big insert or delete operations. I also tried to update its statistics. And I also tried to do nothing at all.

I get this problem randomly every day but at different hours, without any way to reproduce it. So I don't know how I could compare the execution plans.

Thanks,

Loic

EDIT : This table is populated with a SSIS package. It inserts missing lines and delete deprecated lines (no update). The insert is done by fast load within an oledb destination limited to 500 lines per batch. It does a bulk insert.
I tried adding several things at the end of this package including reorganize indexes and update statistics.

The issue does not occure during the execution of this SSIS package. It can be for example 15 minutes after its end. It can occure even if there ar few lines manipulated by the package (50 lines for example).

My gut guess is that the CPU usage dropping after recreating the PK is coincidental, especially since you believe the index is not fragmented. My guess is there's nothing wrong with the index, per se. More likely, it's the lack of indexes to satisfy the particular queries / procs executing against that table.

What you need to do is monitor the server. This is 2008 so you have many tools available to you and you can go about this a couple different ways.

It sounds like this is happening frequently throughout the day, so observing a Profiler trace and sorting on CPU can be the quickest way to identify what queries / procs are hammering CPU at that particular moment. (SQL Trace is the server side version of the Profiler GUI - this might be a better option if you need to trace for an extended period of time, but that is a different conversation.)

Along the same lines and a bit more accurate would be to correlate a Perfmon trace with a Profiler trace and then you can see exactly what was running when your CPU spiked. (You can google that up for instructions.)

Another approach would be to look at the execution DMV's (see link below) and again sort descending on total_worker_time as that relates to CPU cost.

These approaches will narrow down the queries / procs you need to focus on and from there you need to look at the excecution plan and determine if you can add indexes, break up the query into smaller pieces, etc...

Here are some links to get you started:

https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

http://whoisactive.com/


Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)? This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question).

Our legacy ERP was running on SQL Server 2000 EE and now it's on 2008 R2 EE. We're noticing many more blocked commands in the 2008R2 environment. The 2008R2 environment is running with trace flag 4199 and 1119.

I noticed a difference between the MSDN documentation on hints in 2000) vs. 2008R2 where the FAST hint is expanded to note that the result will continue to be processed once the specified n rows had been returned. Is this just a more robust document, is this a change in behavior for this hint through the years, or something different?

Yes this is just improved documentation.

FAST n has always been the n version of FASTFIRSTROW i.e. a request to the optimiser to choose a plan that will return n rows more quickly if a plan can be identified to do so.

It is not equivalent in anyway to requesting the TOP n rows, which is in effect what you would be asking for if the expectation was for query execution to terminate after n had been delivered.


Creating a composite type with "char"? PostgreSQL has a type called char that's stored as an signed 1 byte int

  The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

Can we create composite types with "char"? I can create a table with it...

CREATE TABLE pixel ( r "char", g "char", b "char" );

Internally that creates a type which I can use elsewhere,

CREATE TABLE f ( mypixel pixel );

But, can I create a simple TYPE (no table) from it?

CREATE TYPE pixel ( r "char", g "char", b "char" );
ERROR:  syntax error at or near ""char""
LINE 1: CREATE TYPE pixel ( r "char", g "char", b "char" );

It's just the missing keyword AS:

CREATE TYPE pixel AS (r "char", g "char", b "char");

The data type "char" is a non-standard type, primarily meant for internal purposes. But it's just another type - except for the peculiar spelling of the name including the double-quotes to disambiguate against char.


How can I select data size group by id for every value in a table I hope I can explain exactly what I need to get.
I have a Table that has a specific column of type MEDIUMBLOB.
What I need to do is to determine the size of every value inserted on this column grouped by idMsg and transform this value in MB.
DB Structure is:
CREATE TABLE `sat_msgBlob` (
    `idOwnApp` MEDIUMINT(11) NOT NULL DEFAULT '0',
    `idMsg` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `idVeh` MEDIUMINT(11) NULL DEFAULT NULL,
    `idTypeMsg` MEDIUMINT(11) NULL DEFAULT NULL,
    `idProviderComm` MEDIUMINT(11) NULL DEFAULT NULL,
    `idPayCode` MEDIUMINT(11) NULL DEFAULT NULL,
    `msgInOut` CHAR(1) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `idMsgSender` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `txMsg` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `tmMsg` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `idControl` MEDIUMINT(11) NOT NULL DEFAULT '0',
    `txBlob` MEDIUMBLOB NULL DEFAULT NULL,
    `idUpdUser` MEDIUMINT(11) NULL DEFAULT NULL,
    `tmUpdate` DATETIME NULL DEFAULT NULL,
    `idMission` MEDIUMINT(11) NULL DEFAULT NULL,
    `idEvent` MEDIUMINT(11) NULL DEFAULT NULL,
    `tmPrcsLast` DATETIME NULL DEFAULT NULL,
    `idBlob` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`idMsg`, `tmMsg`) USING BTREE,
    INDEX `X2` (`idVeh`, `idOwnApp`, `tmMsg`) USING BTREE,
    INDEX `m1` (`idMission`, `idEvent`) USING BTREE,
    INDEX `X3` (`idControl`) USING BTREE,
    INDEX `X4` (`tmMsg`) USING BTREE,
    INDEX `X6` (`idBlob`) USING BTREE,
    INDEX `X7` (`tmUpdate`) USING BTREE
)
COLLATE='utf8_general_ci'
AUTO_INCREMENT=971935070
AVG_ROW_LENGTH=8450
MAX_ROWS=4294967295;

The data inserted in this txBlob column look like this
0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C21444F435459504520566D4D4C2053595354454D2022687474703A2F2F7777772E766961747261636B2E726F2F737065632F566D4D4C5F76302E312E6474

I had tried to use this query but I am not sure that the value is correct:
SELECT sys.format_bytes(LENGTH(txBlob)) AS size
FROM sat_msgBlob
WHERE tmMsg BETWEEN '2021-03-01' AND '2021-03-02'
GROUP BY idMsg

It seems that you need in
SELECT idMsg, SUM(LENGTH(txBlob)) AS total_size_in_bytes
FROM sat_msgBlob
WHERE tmMsg BETWEEN '2021-03-01' AND '2021-03-02'
GROUP BY idMsg

Then, after receiving the output into the client you may format it to Mbytes. Or divide the sum in the query to 1048576 and cast to decimal with needed fractional part length.


Postgres Number of Updates/Deletes Since Last Autovacuum Is there a good way to monitor the number of updates/deletes to a table since the last autovacuum? I'm trying to find tables that aren't updated frequently enough to avoid autovacuum (to prevent wraparound).

I was thinking of using n_dead_tup from pg_stat_all_tables, but is this a good idea/is there a better way?

I'm using Postgres 9.6.

Thanks

n_dead_tup in pg_stat_all_tables is the perfect way to monitor that, because that is the value that triggers autovacuum.

From v13 on, you also have to watch n_ins_since_vacuum, because autovacuum is also triggered by inserts there.

Normally, there is no reason to be afraid of anti-wraparound vacuum runs. PostgreSQL 9.6 introduced an optimization that allows such VACUUM runs to skip pages with only frozen tuples.

The exception might be a table that receives almost only inserts. You could potentially get massive anti-wraparound VACUUM runs there. A way to avoid that is to drastically lower autovacuum_freeze_max_age for such tables, so that you get anti-wraparound VACUUM runs there earlier and more often. This problem has been abolished with PostgreSQL v13.


Add linked server from SQL-Server to MySQL: "Data source name not found" (Error:7303) I'm trying to link my SQL-Server 2005 on Windows Server 2008 R2 with MySQL 5.5.40 on a different computer. I have followed this guide:

http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

However, when i try to test the connection under "Linked Servers" as you can see in this screenshot:

i'm getting this error:

The ODBC data source was added as shown in above link as you can see here:

What can be the reason for this? Note that the Server2008 is a 64bit OS, if that matters.

The ODBC Connection needs to be a System DSN. The connection is made as the service account, not you, and so the service account doesn't know about your User DSN.

From the article:

  Select the System DSN tab to configure a data source for the entire
  system.


Get last result of query and set it to variable I am trying to get the last (most recent) record from a query and set it to the value of a declared variable, but it doesn't seem to be working.

DECLARE @PriceID int
SELECT TOP 1 log_id = @PriceID
  FROM price_logs
 WHERE part_id = 45
 ORDER BY log_id DESC

In this instance, the part_id value doesn't matter, it just need to be a value that exists within the database.  When I run this code I am getting a blank value for @PriceID, yet when I check the values in the price_logs table, there is a data row to be collected.  So what is it I am doing wrong here?

Swap the variable and column name so you are setting the value to the variable, not the other way around. How you have it written, you are setting the column name log_id to the value of @PriceID which is currently NULL since you only declared it.

DECLARE @PriceID int
SELECT TOP 1 @PriceID = log_id
  FROM price_logs
 WHERE part_id = 45
 ORDER BY log_id DESC


Primary replica set server goes secondary after secondary fails I have a 2 servers replica set that, after the secondary fails the primary goes into secondary mode while the secondary is in STARTUP2 (recovering). The problem with this is that I can't use the collection stored in that replica set freely, I'm getting errors trying to use the collection:

pymongo.errors.OperationFailure: database error: ReplicaSetMonitor no master found for set: rs2

Sometimes if I restart the mongod instances, the server rs2-1 is the primary for a while, but after some time (while the secondary is recovering) I see this in the logs of rs2-1 (the primary):

Tue May  7 17:43:40.677 [rsHealthPoll] replSet member XXX.XXX.XXX.XXX:27017 is now in state DOWN
Tue May  7 17:43:40.677 [rsMgr] can't see a majority of the set, relinquishing primary
Tue May  7 17:43:40.682 [rsMgr] replSet relinquishing primary state
Tue May  7 17:43:40.682 [rsMgr] replSet SECONDARY
Tue May  7 17:43:40.682 [rsMgr] replSet closing client sockets after relinquishing primary

Is there an easy way to make the primary keep being primary after the secondary fails? Am I doing something wrong?

Thanks in advance!

MongoDB requires a primary to be able to see a majority of the set in order to remain a primary. The majority is defined as being greater than n/2 machines where n is the number of machines in the replica set. So, if n is 1, then one machine constitutes a majority. When n is 2 or 3, 2 machines constitute a majority.

When the secondary is added, the primary now requires to be able to access two machines, itself and the secondary. When the newly added secondary goes down, the primary no longer sees a majority, and steps down.

This scenario is why an arbiter is recommended in addition to the primary and secondary. If you have two machines and one arbiter in the replica set, assuming the arbiter always stays up (which should be true because the arbiter does barely anything), then as long as one other machine is up, the arbiter will ensure that a primary will exist, because the other machine and the arbiter constitute a majority (2 out of 3).


complex mysql constraints over foreign keys It seems rational to me to stop users or bad codes from inserting invalid data, but I don't remember to see this anywhere!

Consider the following tables

How I can make sure an order is always referencing an address that is created by the same user?
Is this kind of constraint usual and recommended? I mean, Do I even have to care about it in the design?

ALTER TABLE orders
  ADD FOREIGN KEY (user_id, address_id)
      REFERENCES address (user_id, id);

A single row then cannot have a user_id and address_id that do not appear on a row together in the address table.

Yes, I would say you want such a constraint.


MySQL: Granting permission for CREATE VIEW I created a new database schema as 'company_security' and populate the database with the table structure of very popular Company-Employee tables which contains DEPARTMENT,DEPENDENT,DEPT_LOCATIONS,EMPLOYEE,PROJECT & WORKS_ON tables. Then I created a new user in a root user session as,

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1'

What I wanted to do is create a view WORKS_ON1(Fname,Lname,Pno) on EMPLOYEE and WORKS_ON from user1. To do this, first I granted necessary permission as root to user1 as follows.

GRANT SELECT ON company_security.WORKS_ON TO 'user1'@'localhost'
GRANT SELECT company_security.EMPLOYEE TO 'user1'@'localhost'
GRANT CREATE VIEW ON company_security.* TO 'user1'@'localhost'

Then I flushed the privileges and this was the output for SHOW GRANTS for 'user1'@'localhost'

Then I tried to create the view from user1 as,

CREATE VIEW WORKS_ON1 AS select Fname,Lname,Pno from EMPLOYEE,WORKS_ON;

but I got an error like this,

ERROR 1142 (42000): CREATE VIEW command denied to user 'user1'@'localhost' for table 'WORKS_ON1'

I have found a similar problem in MySQL forum but the MySQL DOC reference link he mentioned in the solution seems like removed.

So my question is how to solve this problem? even I tried to solve this by granting permission for create view in a global level for user1 but that failed too. Either we cannot grant permission for the WORKS_ON1 view as the error saying because it does not exist in the database already.

MySQL version I'm using: mysql  Ver 14.14 Distrib 5.6.28, for debian-linux-gnu (x86_64) using  EditLine wrapper

This is detailed in When Privilege Changes Take Effect in the MySQL documentation.

  A grant table reload affects privileges for each existing client
  connection as follows:

  Table and column privilege changes take effect with the client's next
  request.

  Database privilege changes take effect the next time the client
  executes a USE db_name statement.

  Note Client applications may cache the database name; thus, this
  effect may not be visible to them without actually changing to a
  different database or flushing the privileges.

  Global privileges and passwords are unaffected for a connected client.
  These changes take effect only for subsequent connections.


Azure SQL Server - Cannot get rid of the sort operator I'm using Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8   Apr  3 2019 13:27:00   Copyright (C) 2019 Microsoft Corporation), Premium tier 4 (500 DTUs).

Query: offer progress has a history of statuses for each offer and I want to get the first row for each offer/status. So, for offer #1 I want the first time it was set to status 1, status 2, etc.

        SELECT OfferProgressId, OfferId, DateCreated, OfferStatusDate, OfferStatusId, Name
        FROM
        (
            SELECT
                op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name,
                ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus
            FROM OfferProgress op
            LEFT JOIN [User] u ON u.UserId = op.UserId
            WHERE
                op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17) AND
                op.DateCreated >= '8/1/2017'
        ) t
        WHERE
            t.FirstStatus=1

Execution plan: https://www.brentozar.com/pastetheplan/?id=rkhP--LcN

My goal is to get rid of the sort operator that has a warning.

My indexes: IX_OfferProgress_DateCreated, IX_OfferProgress_OfferId_OfferStatusId_DateCreated, IX_OfferProgress_OfferStatusId_DateCreated_OfferId_UserId_with_include

I don't know if you are going to be able to eliminate the sort operator, the use of ROW_NUMBER necessitates a sort operation.

But you may be able to reduce the total cost of your query by using something similar.

;WITH CTE_Initial AS
    (
    SELECT op.OfferProgressId
        , ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus
    FROM OfferProgress AS op
    WHERE op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17)
        AND op.DateCreated >= '8/1/2017'
    )
SELECT op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name
FROM CTE_Initial AS I
    INNER JOIN OfferProgres AS op ON op.OfferProgressId = I.OfferProgressId
    LEFT OUTER JOIN [User] AS u ON u.UserID = op.UserID
WHERE I.FirstStatus = 1


Order of maintenance plan tasks Please could you tell me the correct order of maintenance plan operations in SQL Server?

Check integrity
Update statistics
Rebuild indexes

Is correct this order?

There is no point in updating your statistics if you are rebuilding your indexes. The statistics for an index are updated if the index is rebuilt.

Another problem is that the rebuild index job in the Microsoft maintenance plans are not intelligent so they just rebuild all indexes whether they need it or not. Brent Ozar talks about this here http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

The best solution for index maintenance is to schedule a custom script that checks for things like fragmentation levels and then performs the appropriate action. The best script out there to do this can be found here http://ola.hallengren.com/

If you want to stick with the native maintenance plans then I would just drop the update statistics job.


MS SQL Server 2000 ODBC driver for Linux Microsoft has official ODBC drivers for MS SQL Server, for Linux. Great! Nice installation instructions for all the major distros, too.

Unfortunately, their ODBC drivers, going back to the oldest version I can find only support SQL Server 2005 and newer:

  Native Data Access APIs – ODBC and OLE DB – for SQL Server 2005 and later.

I need to connect to a very old SQL Server (2000):

SELECT @@VERSION
→
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
       May  3 2005 23:18:38
       Copyright (c) 1988-2003 Microsoft Corporation
       Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I've confirmed that the v13 driver does not work:

$ sqlcmd -U XXXX -S 10.1.2.3
Password:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : ODBC Driver 13 for SQL Server does not support connections to SQL Server 2000 or earlier versions..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.

—
Is there an official MS SQL ODBC driver that will run on a modern 64-bit Linux and connect to SQL Server 2000?

Or do I need to go to third party drivers like the open-source FreeTDS?

@Dan, this is by design. SQL Server 2005 is not longer supported. Newly released client drivers support SQL Server 2005+.

That said, can you try this?

wget https://gallery.technet.microsoft.com/scriptcenter/SQLCMD-and-BCP-for-Ubuntu-c88a28cc/file/142121/1/Ubuntu%2014.04%20MSFT%20ODBC%20Driver.sh
sudo bash Ubuntu\ 14.04\ MSFT\ ODBC\ Driver.sh
sqlcmd –S yourServerName.database.windows.net – U yourUsername – P yourPassword

These steps install the older version (ODBC Driver v11).


Mysql & multi-tenant db: the best way to organize? I've never completed any multi-tenant project before...

What is the best way to organize multi-tenant db using mysql? and to seperate access between different companies?

sure it can be done in a different ways:

different database for each company
different tables for each  company
one db each table with column tenant_id

i prefer 3rd option.

but: is it possible somehow to limit access between different companies data, for example using mysql schema (or something another) and not using back-end code?

i heard something, that in postgresql its possible using schema...

but is this possible in mysql? (for example tenant1 cannot view data of tenant2)

If you have thousands of tables or thousands of databases, there could be performance problems.
If you let customers connect on their own directly to mysql, then you must control their GRANTs, which can be managed at the database or table level.  (This is one way to 'limit access'.)
If you throw all customers into the same set of tables, then you must provide security via an application layer, and manage logins yourself, not via MySQL's GRANTs.
You could try to use VIEWs, with permissions, to constrain users.  Let users connect to the dataset, but limit them to views only.  Build into the views a way to limit them to tenant_id....  (I have not thought through this approach; it may have fatal flaws.)
A 4th option is to have separate instances of MySQL; each customer has nearly-complete control of his instance.  (You maintain admin control.)
With the 4th option, there are many choices - VMs, Docker, mysql_multi, etc.  Be warned:  those tend to work better on *nix, not Windows.  (In spite of decades of development, Win still lags behind *nix when it comes to being "ready for prime time" in the 'server' arena.)
With the 4th option, CGroups could be used to limit each customer's resource usage.  This way one runaway customer cannot significantly hurt the other customers.
Another consideration:  How easy will it be to "shard"?  That is, have multiple servers, each handling some of your customers.  In particular, what will it take to migrate a customer from one shard to another.  Once you have solved that, you have addressed the questions of how to upgrade the hardware, OS, MySQL version, etc.
Do you provide specific client software (a la WordPress) (SaaS)?  Or are you simply providing a database engine (DaaS)?
Will there be client software on the same server?


Creating a Friendships database that includes a Block Friend option My database teacher asked us to program a clone of Facebook using MySQL as the database management system, so I'm trying to represent a friends relationship in the corresponding database.

So far I have built the following tables by searching everywhere:

CREATE TABLE Users (
    UserID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Nome VARCHAR (20) NOT NULL,
    Sobrenome VARCHAR (20) NOT NULL,
    Foto LONGBLOB,
    Cidade VARCHAR (180),
    PRIMARY KEY (UserID)
);

CREATE TABLE Friends (
    UserID INT UNSIGNED NOT NULL,
    FriendID INT UNSIGNED NOT NULL,
    PRIMARY KEY (UserID, FriendID),
    CONSTRAINT FK_UserID
    FOREIGN KEY UserID REFERENCES Users (UserID),
    CONSTRAINT FK_FriendID
    FOREIGN KEY FriendID REFERENCES Users (UserID)
);

I pictured the Friends table as a self-relationship using UserID (declared as the Users table primary key).

Beyond some basic #1064 errors I'm working on: Is it possible to add a Block Friend option to this relationship? For example, if I don't want to talk with a person anymore, I just block them.

I would cover the “Block Friend” requirement introducing the concept of Friendship Status to the system—where “Blocked” is only one of the possible instances, since a Friendship may be also be, e.g., “Accepted” or “Declined”—, as I will detail as follows.

Business rules

When modelling a relational database, I highly recommend (a) identifying and (b) formulating all the rules that apply in the business environment with respect to the informational characteristics of relevance, and do so before (c) creating tables and declaring constraints. In this way you can delineate the corresponding conceptual schema with the needed precision, and then the logical-level declarations are much more easy and natural.

So let us start with some basic expository rules of a Friendships system:

A User is the Requester of zero-one-or-many Friendships
A User is the Addressee of zero-one-or-many Friendships
A Friendship is primarily identified by the combination of its RequesterId and its AddresseeId

Then the Status aspect comes into play:

A Friendship holds one-to-many FriendshipStatuses
A FriendshipStatus is primarily identified by the combination of its RequesterId, its AddresseeId and its SpecifiedDateTime
A User specifies zero-one-or-many FriendshipStatuses
A Status classifies zero-one-or-many FriendshipStatuses
A Status is primarily identified by its StatusCode
A Status is alternately identified by its Name

Expository IDEF1X diagram

After having formulated all the business rules with accuracy, one can then draw an IDEF1Xa diagram like the one expounded in Figure 1 to supply a graphical communication device:

As shown, Requester and Addressee are labels that indicate the specific Role performed by each User taking part in a given Friendship.

The Friendship entity type depicts an association with a many-to-many (M:N) cardinality ratio that can involve distinct occurrences of the same entity type, i.e., User. This is an example of the classic conceptual construct known as “Bill of Materials” or “Parts Explosion”.

Status and Friendship are connected by way of the FriendshipStatus entity type, which portrays what may become a sequence of Status specifications for the involved Friendship instances.

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modelling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

Illustrative logical SQL-DDL design

Subsequently, I created the following logical-level arrangement in order to represent the conceptual aspects previously formulated (naturally, you will have to adapt it to your exact needs, as it may entail, e.g., several constraints not considered nor declared here and a different quantity of columns):

-- You should determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on your business context characteristics.

-- At the physical level, you should make accurate tests
-- to define the mostconvenient INDEX strategies based on
-- the pertinent query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile ( -- Represents an independent entity type.
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- Single-column ALTERNATE KEY.
);

CREATE TABLE Friendship ( -- Stands for an associative entity type.
    RequesterId     INT      NOT NULL,
    AddresseeId     INT      NOT NULL, -- Fixed with a well-delimited data type.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
);

--
CREATE TABLE MyStatus ( -- Denotes an independent entity type.
    StatusCode CHAR(1)  NOT NULL,
    Name       CHAR(30) NOT NULL,
    --
    CONSTRAINT MyStatus_PK PRIMARY KEY (StatusCode),
    CONSTRAINT MyStatus_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE FriendshipStatus ( -- Represents an associative entity type.
    RequesterId       INT      NOT NULL,
    AddresseeId       INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StatusCode        CHAR(1)  NOT NULL,
    SpecifierId       INT      NOT NULL,
    --
    CONSTRAINT FriendshipStatus_PK             PRIMARY KEY (RequesterId, AddresseeId, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipStatusToFriendship_FK FOREIGN KEY (RequesterId, AddresseeId)
        REFERENCES Friendship  (RequesterId, AddresseeId), -- Composite FOREIGN KEY.
    CONSTRAINT FriendshipStatusToMyStatus_FK   FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipStatusToSpecifier_FK  FOREIGN KEY (SpecifierId)
        REFERENCES UserProfile (UserId)
);

Tested on MySQL 5.6 in this SQL Fiddle.

A Friendship row and its earliest FriendshipStatus related counterpart

With this arrangement, when a User requests a Friendship, the corresponding Friendship row has to be inserted along with the complementary FriendshipStatus counterpart, which in turn must contain a value of 'R' in StatusCode, denoting the fact that a User has simply requested the Friendship, and is waiting for the Addressee to accept it ('A'), decline it ('D'), or leave it as requested.

As usual, I would decidedly set up the multi-row operations that have to be executed as a single Unit of Work with the aid of ACID TRANSACTIONS. Needless to say, the assistance of the application program(s) component(s) accessing the database would as well be very helpful to facilitate the processing of the Friendships.

Later FriendshipStatus rows

Subsequently, every time that the Status of a certain Friendship has to be put up-to-date, the Users would only have to INSERT a new FriendshipStatus row, containing:

the respective RequesterId and AddresseeId values —taken from the concerning Friendship row—;
the new and meaningful StatusCode value —drawn from MyStatus.StatusCode—;
the value of the exact INSERTion instant, i.e., SpecifiedDateTime —preferably using a server function so that you can retrieve and retain it in a reliable manner—; and
the SpecifierId value that would indicate the respective UserId that entered the new FriendshipStatus into the system.

Sample data

To that extent, let us suppose that the MyStatus table, serving a “look-up” role, encloses the following data —with PK values that are (1) end user-, app programmer-, and DBA-friendly and (2) small and fast in terms of bytes at the physical implementation level—:

 +-——————————-+-—————————-+
 | StatusCode | Name      |
 +-——————————-+-—————————-+
 | R          | Requested |
 +------------+-----------+
 | A          | Accepted  |
 +------------+-----------+
 | D          | Declined  |
 +------------+-----------+
 | B          | Blocked   |
 +------------+-----------+

So, the FriendshipStatus table may hold data like shown below:

 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 | RequesterId | AddresseeId | SpecifiedDateTime       | StatusCode | SpecifierId |
 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 |        1750 |        1748 | 2018-04-01 16:58:12.000 | R          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-02 09:12:05.000 | A          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-04 10:57:01.000 | B          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-07 07:33:08.000 | R          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-08 12:12:09.000 | A          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+

It can be said that the FriendshipStatus table serves the purpose of comprising a time series, which stands for the evolution of the Friendships of significance.

You can decidedly declare one or more views that comprise columns from several tables to facilitate, e.g., writing code for data retrieval.

Blocking a Friend

As demonstrated in the sample data of the FriendshipStatus table, having a row that holds a value of 'B' in the StatusCode column would represent that some User decided to block one of his or her Friends.

And, yes, you can use your application program(s) component(s) to access the database and check the latest FriendshipStatus row (via MAX(SpecifiedDateTime)) for a given (RequesterId, AddresseId) combination of values to control the flow of the applicable processes of the system.

Other option: Retaining only the most recent Status

In case that, for whatever reason, you have to avoid retaining the FriendShipStatus history in the fashion previously described, you can discard the creation of a FriendshipStatus table and simply set up Friendship so that it includes a FOREIGN KEY constraint making a reference to Status.StatusCode directly:

CREATE TABLE Friendship (
    RequesterId    INT      NOT NULL,
    AddresseeId    INT      NOT NULL,
    CratedDateTime DATETIME NOT NULL,
    StatusCode     CHAR(1)  NOT NULL,
    CreatorId      INT      NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId),
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
    CONSTRAINT FriendshipToMyStatus_FK  FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipToCreator_FK   FOREIGN KEY (CreatorId)
        REFERENCES UserProfile (UserId)
);

Tested on MySQL 5.6 in this SQL Fiddle.

In this manner, (i) the INSERT of a certain Friendship row goes with a value of 'R' in the StatusCode column, and each time that the Status of a Friendship is modified you just have to (ii) UPDATE the values in StatusCode, SpecifiedDateTime and CreatorId —which must enclose the identifier value of the latest User who specified the Status of the concerning Friendship—.

Related posts

Various ideas presented in this post are based on this Q & A, where I propose a database design that meets the informational requirements of a similar business environment. I recommend reading those posts because, although the focus of that question is different from yours, that answer is more detailed and contains several points of relevance.


Remove the deeper jsonb element in Postgres [
   {
      "type":"test.added",
      "values":[
         {
            "field":"field1",
            "value":"fieldValue1"
         },
         {
            "field":"field2",
            "value":"fieldValue2"
         }
      ]
   }
]

I want to remove the 'field1' object pair from the JSON in postgres

Here is what I have tried so far,

updatedJson -> 0 ->> 'values' - '"field1"'
i tried using the #- Delete the field or element with specified path
(for JSON arrays, negative integers count from the end)

Both steps didn't help me. I am new to Postgres. Is there any way like getting the path and deleting it. I am using jsonb. PostgreSQL version: 9.6.
Kindly help

i tried using the #-

What specifically did you try?

Using #- works for me, specifically updatedJson #- '{0,values,0}'


Mysql binlogs exists, show binary logs is empty I am working on a replication setup and have turned on bin-logs. I have moved my mysql datadir to a new location as well as the binlog path

From my.cnf

datadir = /vol/data/mysql
log-bin=/vol/data/mysql/mysql-bin
binlog_format=mixed

When I start mysqld and check the status:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000315 |      106 |              |                  |
+------------------+----------+--------------+------------------+

When I check the contents of /vol/data/mysql, I can see a lot of bin logs, but specifically, I can see this file was just created:

mysql-bin.000313
mysql-bin.000314
mysql-bin.000315
mysql-bin.index

If I tail the index file:

$ sudo tail mysql-bin.index
/vol/data/mysql/mysql-bin.000313
/vol/data/mysql/mysql-bin.000314
/vol/data/mysql/mysql-bin.000315

In mysql, there are no binary logs at all.

mysql> SHOW BINARY LOGS;
Empty set (0.00 sec)

This means that purging binary logs in mysql has no affect. I checked the permissions of the directory and files and all belong to mysql user and group. Why can't mysql see and/or purge those binary logs?

mysql> PURGE BINARY LOGS BEFORE NOW();
ERROR 1373 (HY000): Target log not found in binlog index

You may need to try something a little unorthodox
service mysql stop

Open mysql-bin.index in vi
Change the contents to this
./mysql-bin.000313
./mysql-bin.000314
./mysql-bin.000315

Save mysql-bin.index
service mysql start

Login to MySQL and run
SHOW BINARY LOGS;
SHOW MASTER STATUS;

This stunt worked a few times for me.
Give it a Try !!!


Design: similar tables and foreign keys Description

I am building a database for storing social events. They can be one-time and recurring. The price and capacity can differ on per-date basis. The events have variants that can also alter the base price. Not all variants are available every date, thus dates are per-variant.

Tables

I was thinking of having the following structure (fields omitted for simplicity):

event  event_variant  event_date  event_recurrence  event_config
-----  -------------  ----------  ----------------  ------------
id     id             variant_id  variant_id        id
name   event_id       config_id   config_id         event_id
       plus_price     date        rrule             price
                                                    capacity

The recurring events would then be generated in a materialized view. If date and recurrence overlap, date would be preferred.

Event

The event table holds basic information important for searching, like the location, description, event category.

Event variant

A specific variant of an event. Each event has 1-n event variants. Event variants have their title and description, as well as additional price information.

Event date and recurrence

Recurrent dates are ones that are known to happen beforehand. Dates are for irregular events. Each irregular and recurring date has a variant assigned. Some variants may not be available on certain dates. Each date also has a config assigned, with price and capacity for the specific date/recurrence.

Event config

Holds information about the max capacity (current cap. calculated based on purchases) and the price of the current event.

Example

Event named escape room. Has a description, images, etc.

Variants

Basic - no extra cost, lasts 1h
Extended - more sub-rooms unlocked, lasts 2h. Costs 5$ extra

Dates

Every tuesday and friday at 18:00 (stored in event_recurrence), with special dates for some holidays (stored in event_date).

Prices

20$ during summer holidays, 15$ the rest of the year (recurring dates). Certain dates can cost extra (christmas, easter...).

The problem

Basically this rustles my jimmies in two different ways:

Does event_config need a reference to event via event_id? It makes sense to know to which event the config belongs to, but at the same time it feels redundant since it's implicit from the relationship with dates.
Isn't event_config an unneeded complexity? Its sole purpose is to provide shared fields to event_date and event_recurrence. Wouldn't it be better just to have the price and capacity columns both in date and recurrence?

Your questions, with my responses:

  Does event_config need a reference to event via event_id? It makes sense to know to which event the config belongs to, but at the same time it feels redundant since it's implicit from the relationship with dates.

Adding the event_id to the event_config table allows you to easily design a user-interface for modifying the event_config table, without needing a complex reference to an actual event instance.  This might be useful for "default" configs.

This also allows an easier code path for presenting data to the user prior to the user choosing a date.

  Isn't event_config an unneeded complexity? Its sole purpose is to provide shared fields to event_date and event_recurrence. Wouldn't it be better just to have the price and capacity columns both in date and recurrence?

event_config looks like a good optimization to me.  I see it as a way to manage configurations independently of specific events in time.  For instance, you might have a "holiday" config, a config for each weekday, and a config for "saturday" and "sunday", each of which might be exactly the same for each and every event that occurs on those days.  Why duplicate that data by storing the info in event_date and event_recurrence?


Are files and file groups included when doing a full backup? I have a database with 4 files and 2 file groups. According to the size of the files, it's 1TB. However, the backup (non-compressed) is only 350GB. Is this typical? Or, do I need to specifically select every file/file group when doing backups?

A full backup is everything...

SQL Server backups will only contain the actual data in the database. The size of the physical file is the allocated size that SQL Server has for the file, including the free space.

It does however also keep up with the initial size of your files for the database in the backup as well. So when you restore the database the files will be created and attempt to allocate the same amount of space.


Drive holding my database is running out of space. What are my options? I am new to database administration. I've been scraping a website for a month now. I have two drives, each 2TB in size. I have PostgreSQL 10 on my first drive (c:) and it has 50GB left. I've only scraped about 75% of the website and have another 25% to go. What are my options?

I can't extend my first drive because my computer is old (hp z600) and the RAID software only supports up to 2TB. I would prefer not to stop my scraping process because I was an idiot and didn't keep track of pages that I've already visited. I thought 2TB would be enough, but I was wrong.

Running SHOW autovacuum; shows that autovacumm is on.

It depends on how your database is architectured (you do not say) and may also depend on the PostgreSQL version you are using (you do not say).

Anyway, in general, with PostgreSQL, you can create a new "tablespace" that is to make a specific new directory (so you choose the place, like on second drive) available to create new PostgreSQL objects, such as tables, in it.

See https://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html that says Tables, indexes, and entire databases can be assigned to particular tablespaces. and gives this example:

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
CREATE TABLE foo(i int) TABLESPACE space1;

Also, are you sure that the current content is really only data and not temporary stuff or useless indexes or just overblown tables because you are not running autovacuum or things like that (this depends a lot on which PostgreSQL version you run)?

If you have only a single table, with really 1950 GB of data in it, you will first need to partition it, in order to be able to move some of the content to a new tablespace (and/or archive some old content somewhere else).


Is there a way to insert multiple rows into a table with default values for all columns? I can insert multiple rows into a table with default values for all columns the RBAR way:

create table course(course_id serial primary key);

do $$
begin
  for i in 1..100000 loop
    insert into course default values;
  end loop;
end;$$;

Is there a way of doing the same with a single SQL statement?

Using generate_series() and ctes. Tested in rextester.com:

create table t
( tid serial primary key,
  i int default 0,
  name text default 'Jack'
) ;

with ins as
  (insert into t (i, name)               -- all the columns except any serial
   values (default, default)
   returning i, name
  )
insert into t
  (i, name)
select
  ins.i, ins.name
from
  ins cross join generate_series(1, 9);  -- one less than you need

For the case when there is only one column and it's a serial, I see no way to use the default. Using the generate_series is straight-forward:

insert into course
  (course_id)
select
  nextval('course_course_id_seq')
from
  generate_series(1, 10);

If there are other, more "peculiar" default values, like a UUID function or the non-standard clock_timestamp(), the statement will have to be adjusted accordingly, like the serial case.


sqlserver RESTORE missing views I amb trying to do a full database backup from one server to restore into another one, this is the backup script (creating a dated name for the backup).

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:\' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
BACKUP DATABASE @DB
TO  DISK = @DISK
WITH COMPRESSION, COPY_ONLY, STATS = 5
GO

And this is the restore one (restoring from the previously created file):

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:\' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
-- #commented until needed (dangerous)
SELECT @DISK
--EXEC('ALTER DATABASE '+ @DB +' SET OFFLINE WITH ROLLBACK IMMEDIATE')
--EXEC('DROP DATABASE '+ @DB)
-- #show 1% fractions as it is restoring
--RESTORE DATABASE @DB FROM DISK = @DISK WITH REPLACE, STATS = 1
GO

Both scripts run without error messages, but the developers are saying that they are missing some VIEWs from the restored database. About those views:

Some of them use linked servers, some don't
The linked server in the DEST server has a different name that the one in the source (for those using linked servers).

I saw this about clr, but doensn't seem related.

Any ideas on what might be failing?
UPDATE1: After asking averyone, on of the devs told me that they did a failed update after my restore that deleted the views I was missing, my procedure was fine.
UPDATE2: I accepted Aaron's reply because altough it wasn't the cause in my case, it's nice addon to the backup script.

Backup is most likely Appending to the Last Backup--add WITH INIT to the backup (see below)

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

"A disk device does not have to exist before it is specified in a BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device."

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:\' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
BACKUP DATABASE @DB
TO  DISK = @DISK
WITH COMPRESSION, COPY_ONLY, STATS = 5, INIT
GO

p.s. Just saw Aaron's comment--if it's right--please give him a bump as well. Done


Cassandra nodes are not equal We have two nodes. Node1 was down for a long time. During this time Node2 raised 1 TB capacity when Node1 has 100 GB.

We tried to repair Node1 with nodetool repair but nothing changed. After that we started nodetool repair in Node2, it took 5 days to compaction but nothing changed either.

Actual status here:

Datacenter: dc1
===============
Status=Up/Down

|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens       Owns (effective)  Host ID    Rack

UN  172.x.y.z     149.46 GB  256          100.0%            xxx  rack1
UN  172.x.y.k     1.04 TB    256          100.0%            xyz  rack1

Nodes are in AWS. What should we do?

We had a solution but don't know how it happened. Here what we did:

Took snapshot of both servers in case of data loss over AWS
We detached Volume where data is stored. (for instance 172.x.y.k)
We formatted instance and loaded Cassandra with new version. Then we
attached Volume where data is stored.
Finally we started nodetool repair --full in terminal and it took 4
days.

Now our nodes are equal.


MySQL issue - startup I have Percona Server 5.6 running in linux platform.. recently I faced an issue where mysql instance was continuously restarting for no reason.. mysql logs had this error:

[ERROR] InnoDB: File ./TestBAMC/s_snippet.ibd: 'Linux aio' returned OS error 0. Cannot continue operation

Instance has binary logging enabled and has a master-slave configured. This issue is with the master. Does the above error cause the instance to reboot continuously?

Oracle say: This is most probably due to table corruption. See the error number is 0, which indicates success, not actual error. So, this means that read from file was successful and OS returned 0, but actual read data length is different from expected data length. This is unlikely to be a problem on MySQL, but most likely to be a problem on OS including file system.

InnoDB writes data to data file per page. So, size of data file is always multiple of page size. In general, writes against a file is atomic. So, data file will never be non-multiple of page size, if OS works properly. If it's not multiple of page size, InnoDB will read incomplete page at the end of data file, then it results in this error.

Recommended solutions are:
Take following actions to mend or prevent this problem:

Run fsck and check if the file system is flawless.
Replace suspicious disk.
Ensure that the filesystem is transactional. (such as NTFS and ext4)
Check if journaling setting of filesystem is fine.
Change block size of filesystem to same as InnoDB page size (16KB by default)
Upgrade OS to the latest version.


Why does NOT IN with a set containing NULL always return FALSE/NULL? I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.

What's the best way to understand this? I thought of NULL as something without a value, and therefore wasn't expecting the query to fail, but obviously that's not the correct way to think of NULL.

Boolean logic - or Three valued logic

IN is shorthand for a series of OR conditions
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
... is the same as x <> 1 AND x <> 2 AND x <> NULL
... is the same as true AND true AND unknown **
... = unknown **
... which is almost the same as false in this case as it will not pass the WHERE condition **

Now, this is why folk use EXISTS + NOT EXISTS rather than IN + NOT IN. Also see The use of NOT logic in relation to indexes for more

** Note: unknown is the same as false at the end of an expression in a WHERE condition.
While the expression is being evaluated, then it is unknown
See @kgrittn's comment below for why


Statistics. Are multicolumn histograms possible? I am thinking of a situation where I have two columns with high density but these columns are not independent.

Definition

Here it is the definition of the table that I have created for testing purposes.

CREATE TABLE [dbo].[StatsTest](
    [col1] [int] NOT NULL,  --can take values 1 and 2 only
    [col2] [int] NOT NULL,  --can take integer values from 1 to 4 only
    [col3] [int] NOT NULL,  --integer. it has not relevance just to ensure that each row is different
    [col4]  AS ((10)*[col1]+[col2])  --a computed column ensuring that if two rows have different values in col1 or col2 have different values in col4
) ON [PRIMARY]

Data

The data for the experiment is the following

col1    col2    col3    col4
1       1       1       11
1       2       2       12
1       2       3       12
1       3       4       13
1       3       5       13
1       3       6       13
1       4       7       14
1       4       8       14
1       4       9       14
1       4       10      14
2       1       11      21
2       1       12      21
2       1       13      21
2       1       14      21
2       2       15      22
2       2       16      22
2       2       17      22
2       3       18      23
2       3       19      23
2       4       20      24

Step 1: Filtering by col1

SELECT * FROM StatsTest WHERE col1=1

As expected the Query Optimizer guesses the exact number of rows.

Step 2: Filtering by col2

SELECT * FROM StatsTest WHERE col2=1

Again we have a perfect estimation.

Step 3: Filtering by col1 and col2

SELECT * FROM StatsTest WHERE col1=1 AND col2=1

Here the estimation is far from being close to the actual number of rows.

The problem is that the query analizer implicity assumes that col1 and col2 are independent but they are not.

Step 4: Filtering by col4

SELECT * FROM StatsTest WHERE col4 = 11

I can filter by col4 = 11 to get the same results as the query in Step 3, because col4 is a computed column and according to the way it has been defined col1 = 1 and col2 = 1 is equivalent to col4 = 11
Here, however, as expected the estimation is perfect.

Conclusion/Question

¿Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing with filtering by two or more not independent columns?
¿Is the computed column and the filter by the computed column estrictly neccesary for obtaining actual precision?

Example in sqlfiddle

Are multicolumn histograms possible?

Not true multi-dimensional histograms, no.

  Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing with filtering by two or more not independent columns?

SQL Server does support "multi-column" statistics, but they only capture average density (correlation) information in addition to a histogram on the first named column. They are only useful for equality comparisons.

The average density information does not capture any detail, so you will get the same selectivity for any pair of values on a two-column statistics object. In some cases, multi-column statistics can be good enough, and better than nothing. Multi-column statistics are automatically built on multi-column indexes.

Depending on SQL Server version, you may also be able to make use of filtered indexes and filtered statistics:

-- Filtered statistics example
CREATE STATISTICS stats_StatsTest_col2_col1_eq_1
ON dbo.StatsTest (col2)
WHERE col1 = 1;

CREATE STATISTICS stats_StatsTest_col2_col1_eq_2
ON dbo.StatsTest (col2)
WHERE col1 = 2;

Or you could build an indexed view (which can support indexes and statistics of its own). Indexed views are the mechanism behind the DATE_CORRELATION_OPTIMIZATION database setting, a little-used feature for inter-table correlations, but which applies to the spirit of the question.

  Is the computed column and the filter by the computed column strictly necessary for obtaining actual precision?

It is not the only method. In addition to the things already mentioned, you could also specify the exact textual definition of the computed column and the optimizer will generally match it to statistics on the computed column.

There are also trace flags that change the assumptions made about multi-column correlations. Also, the default correlation assumption in SQL Server 2014 (with the new cardinality estimator enabled) is changed from Independence to Exponential Backoff (more details here and here). Ultimately, this is just a different assumption though. It will be better in many cases, and worse in others.

Exact precision in cardinality estimation is not always needed to obtain a good execution plan. There is always a trade-off between generating a plan that can be reused for different parameter values, and a plan that is optimal for a specific execution, but not reused.


Script to install Oracle client 12c2 on Redhat linux 7.5 Can someone help me to provide a script which I can use to install the Oracle client as part of the server build or AMI. Below script I was able to pull out from  the guide in the Oracle site. But still I am missing something. I have added the main code area. Prerequisites are taken care.

By this code doesn't work if it is executed as script, due to user change to oracle just before execution of runinstaller and once that is done it continues to throws. But required permission are already granted. Someone please help.

[INS-30131] Initial setup required for the execution of installer validations failed.
   CAUSE: Failed to access the temporary location.
   ACTION: Ensure that the current user has required permissions to access the temporary location.
*ADDITIONAL INFORMATION:*
 - PRVG-1901 : failed to setup CVU remote execution framework directory "/tmp/CVU_12.2.0.1.0_oracle/" on nodes "ip-10-81-140-134"
 - Cause:  An operation requiring remote execution could not complete because the attempt to set up the Cluster Verification Utility remote execution framework failed on the indicated nodes at the indicated directory location because the CVU remote execution framework version did not match the CVU java verification framework version. The accompanying message provides detailed failure information.
 - Action:  Ensure that the directory indicated exists or can be created and the user executing the checks has sufficient permission to overwrite the contents of this directory. Also review the accompanying error messages and respond to them.
Summary of the failed nodes
ip-10-81-140-134

groupadd oinstall
groupadd dba
sudo useradd -g oinstall -G dba oracle; echo Nbst@AWS01 | passwd oracle --stdin
cd /opt
mkdir app
mkdir app/oracle
chown oracle:oinstall app
chown oracle:oinstall app/oracle
cd /tmp

wget https://amazonaws.com/linuxx64_12201_client.zip
chown oracle:oinstall linuxx64_12201_client.zip
unzip linuxx64_12201_client.zip
chown oracle:oinstall client
cd /tmp/client/
wget https://amazonaws.com/client_install.rsp
chown oracle:oinstall client_install.rsp
chmod 775 client_install.rsp
sudo su oracle; echo Nbst@AWS01 | passwd oracle --stdin
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/client_1
/tmp/client/runInstaller -silent -responseFile /tmp/client/client_install.rsp
exit
cd /opt/app/oraInventory/
./orainstRoot.sh
cd /opt/app/oralinventory/
./orainstRoot.sh

cd /opt/app/oracle/product/12.1.0/client_1/network/admin/
wget https://amazonaws.com/tnsnames.ora
chown oracle:oinstall tnsnames.ora
chmod 775 tnsnames.ora

That will not work, that is not how you execute commands as an other user. + Only root can run passwd username, not even oracle user can run passwd oracle.

sudo su oracle; echo Nbst@AWS01 | passwd oracle --stdin
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/client_1
/tmp/client/runInstaller -silent -responseFile /tmp/client/client_install.rsp
exit

Use something like this:

echo Nbst@AWS01 | passwd oracle --stdin
sudo su oracle -c "export ORACLE_BASE=/opt/app/oracle;
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/client_1;
/tmp/client/runInstaller -silent -responseFile /tmp/client/client_install.rsp"


Migration from SQL Server 2008 to Postgres 9.2 I have a rather large database (2 GB of data, 25 million rows in the largest table) on a shared web hosting. The database engine is Microsoft SQL Server 2008.

Due to high fees for the SQL Server web hosting, I would like to migrate my data from SQL Server to a Postgres database.

In my SQL Server database I have tables, indexes, constraints and stored procedures. The data types in my tables are VARCHAR, TINYINT, SMALLINT, INT, SMALLDATETIME, REAL and FLOAT.

In my stored procedures I am using some built-in T-SQL Date/Time manipulation functions like DateAdd() or DatePart().

Is there any FREE tool available that would help me migrate my data (tables, constraints, and if possible also the stored procedures) from SQL Server to Postgres?

TinyInt and SmallDateTime (AFAIK) are non-SQL Standard datatypes.
EDIT: SQL Standard types nearest equivalents are, respectively, SMALLINT and TIMESTAMP. I suggest you do an conversion of columns using those types before the migration to see if you identify any issues with the application. It's your call to solve that before the migration or note that in a checklist for use after. The nearest MSSQL equivalent of TIMESTAMP (AFAIK) is DATETIME2.

I believe that most of the tables creation script is capable of running with few edits.
Additional index creation can be more trouble if you used some unique capabilities
of SQL Server. An decent DB case tool can make that transition less difficult (sorry, don't know of a free one - we use ER/Studio here for MSSQL).

Stored Procedures - that's where is the real pain. You have to learn the PL/pgSQL and translate your logic to it. Since tricks used on one db engine have very high chance of
not working/getting very lousy performance on another, get an Postgres database developer to help you on that.
EDIT: While an DB case tool can do a decent job converting an db structure from an engine to another, I firmly believe that an tool to convert stored procedures from an procedure language to another can cause more harm than progress.


MySQL Full Text search increase relevance for exact matches? I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching "doobry institute" etc.

The problem I have is that I have lots of entries like "institute of doobry canteen" and "institute of doobry alumni association".

MATCH (names) AGAINST ("doobry institute")

will return all of these records and will not score institute of doobry higher than the canteen.

I'd sort of like to say: score it higher if the word count is similar.

Is there any way to achieve this?

Messy workaround answer:

    SELECT *
      FROM table
     WHERE MATCH ("doobdy institute") AGAINST (name)
  ORDER BY MATCH ("doobdy institute") AGAINST (name),
           ABS( LENGTH("doobdy institute") - LENGTH(name) )


postgres table sizing inconsistency I've been doing some experimentation/testing with VACUUM ANALYSE and table sizing.

I've been using the queries here to get a figure for table size, and I've been using this to get approximate rowcount....

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'TABLE_A'

I have one table with 22 thousand rows sized at 700 Mbytes or approximately 32k per row. The table consists of 4 timestamps, 4 integers, 2 booleans and one 6 character field as a unique primary key.

I have a 2nd table with a very similar structure, albeit only 18 thousand rows, but that occupies 6Mbytes or 333 bytes per row.

Why the enormous discrepancy? 32Kbytes per record for the 1st one, and 333bytes per row for the 2nd one!

The tests were run immediately after doing a VACUUM ANALYSE on each table and no other processes were accessing the database at the time. The only other factor that may be important is that the first table is continuously being updated, whereas the 2nd is largely static, however I would expect that after shutting down all other sessions and running a VACUUM ANALYSE, the way the tables are used wouldn't make any difference.

I would expect that after shutting down all other sessions and running
  a VACUUM ANALYSE, the way the tables are used wouldn't make any
  difference.

It's the VACUUM ANALYZE that does not make a difference on the size of tables. Because it does not immediately recover the bloat resulting from old versions of rows, it merely marks these rows are reusable.

From the documentation (VACUUM):

  Plain VACUUM (without FULL) simply reclaims space and makes it
  available for re-use

Only VACUUM FULL would recover that space, by rewriting the table from scratch with only the live rows.


If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why? I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database:

create or replace function tail_f return varchar2_tab pipelined as
   n number;
begin

    loop
      exit when ...

      select count(*) into n from ... where ...;

      pipe row(sysdate || ' n= ' || n);
      dbms_lock.sleep(60);
    end loop;

    return;
end tail_f;

And then I'd like to select * from table(tail_f) in SQL*Plus.

In order to fetch the rows one by one, I SET ARRAYSIZE 1. Yet, the records (except the first one) are fetched in pairs.

Is there an explanation for this and how can I get the records as soon as one is piped?

Metalink bug 9103343 states:

  This is expected behaviour.  SQL*Plus is written in oci and oci has a
  default  prefetch value of 1 row.  However prefetch upon a fetch (as
  opposed to upon  an execute) only takes place when you are not
  performing an array fetch, so  when arraysize is 1.   Regardless of
  arraysize the first fetch in the trace is always 1 row as 1 row  is
  prefetched on the execute.  Then it either performs a scalar fetch, so
  one  requested row plus one prefetched row, or it performs an array
  fetch so you  see eg :   a) arraysize = 1, fetches are:  1, 2, 2, ...
  b) arraysize = 2, fetches are:  1, 2, 2, ... c) arraysize = 5, fetches
  are:  1, 5, 5, ...

Also see Metalink doc 1265916.1


How do I show the binlog_format on a MySQL server? How do I show the binlog_format on a MySQL server?

And if I dont like it how do I set it to XX permanently?

Where XX is STATEMENT, ROW or MIXED.

To see the current binlog_format value:

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

To change it:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

Source: http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html


Grouping data based on conditions How would I go about grouping data according to values given in a separate file/table?

For example. I have one column in the table I want to be grouped, called ID, and a separate CSV file with IDs to group the data by. I have not created the CSV file yet, so if You think there is a better way to store these values, feel free to tell. The CSV file could for example look like:

ID1,ID2,ID3;ID4,ID5;ID6,ID7,ID9; ... ;

In this case I would want to group the data by ID1 + ID2 + ID3 and ID4 + ID5 and ID6+ID7+ID9 and so on. The point is, that the groups are consistent, but the data is frequently changed and the tables updated. We are talking of hundreds of groups, so it would be neat to organize the groups into a separate file.

SQL is designed to work with rows and columns, it does not do CSVs very well, especially ones that use multiple delimiter symbols with different meaning.

You should figure out how to present your CSV in the form:

ID,GroupID

where IDs that need to be grouped have the same GroupID. Once that CSV is imported, it will be very easy to group your rows using a join to the imported data:

SELECT
  d.GroupID,
  AGG(...),
  ...
FROM
  YourTable AS t
  INNER JOIN ImportedData AS d ON t.ID = d.ID
GROUP BY
  d.GroupID
;


What is the difference between logical schema and physical schema? While going through Database architecture I came across Schema's . I am very much confused with:

Logical schema

Physical schema

How these two schema exist in the Database?

How can these be manipulated according to need?

How Logical schema and physical schema are related?

A logical schema won't exist in your database.  A logical schema is a design-centric database structure built to meet your business requirements.  It is a model that exists on a white board or in a diagraming tool.  It is like the architect's drawings of your database.

A physical model is what is actually implemented in your DBMS.

The two can be different for a variety of reasons and in several ways:

Your logical model should be properly normalized, but your physical model may have denormalization which you've added deliberately and for all the right reasons.
Your physical model may have different naming conventions.  Some people use plain English (or the language of choice) for their logical models and impose a more "system-ish" naming convention in their physical model.
Your logical model may have many-to-many relationships.  Physical models implement m:n using intersection tables.
Your logical model may only use natural or business keys.  Physical models may also add surrogate keys.

You should have a logical model because it lets you think about your database design without having to get bogged down in physical constraints.  You need to have the physical model because that will be where your data eventually lives.


Is there any reason for an index to contain the primary key? I inherited a project using SQL Server 2005.  Recently there were some performance problems and I started looking at the indexes, and found

_dta_index_survey_25_135059617__K1_K10_K19:
    id ASC, sent_date ASC, group_id ASC

and

_dta_index_survey_21_364632442__K18_K1_K2_K9:
    group_id ASC, id ASC, campaign_id ASC, sent_date ASC

which appears to be generated by some kind of automatic performance tuning tool.  I think SQL Server comes with something like that but I'm not familiar with it.

Anyway, id is the primary key, and what I'm trying to understand here is how it could possibly be beneficial to include the primary key within a secondary index... because you have to have the leftmost pieces of information to use an index and if you know id then you've got the row anyway.

Can someone explain why a tool would have generated these indexes and if there's any point in keeping them?

Just because the indexes are getting used doesn't mean they're a good design.  SQL Server is using the wrong indexes lately, and I'm trying to determine why. More specifically, queries against this table that ran in milliseconds a month ago are now taking multiple seconds.  The table is huge (4M rows), and growing steadily, but at the same pace it's been going at for years.

Almost every query joins across group_id or campaign_id.  Often both.  The application almost never specifically selects or sorts by the PK, but I could see how ID could be useful when comparing or merging lists of IDs within the database engine.  ... But id is also an identity column, and an index holds lists of row ids, so it still seems redundant to have the ID in the key when it's also in the index "buckets".  And the additional fields could still be covered by the index without being part of the key.

I discovered that (id, sent_date, group_id) hardly ever gets read.  Meanwhile (group_id, id, campaign_id, sent_date) is one of the hottest indexes.  Next I found that an index which I thought was pretty reasonable (campaign_id, sent_date, group_id, id) also has a index_columns_include of every other column in the table, and occupies more space than the primary clustered index on id!  (but it's also pretty hot...)  Surely I can replace it with a simple (campaign_id, sent_date, group_id)?

You are right to be suspicious of the two indexes that you listed in your question, but there are a few narrow use cases for including the primary key in an index as a key column. By default SQL Server will make your primary key into a clustered key which is the more important concept for performance for this question, so instead if referring to primary keys to answer I'll refer to clustered keys instead.

For the index on id, sent_date, and group_id, queries that only select those columns may benefit because the index will be a covering index for that query. You say that if you know id then you've got the row but what you really have is a very efficient way to read all of the data in data pages for that row. Suppose your (rowstore) table just has a clustered index on the id column. Consider the following query:

SELECT id, sent_date, group_id
FROM your_table
ORDER BY id;

SQL Server is likely to perform a clustered index scan to satisfy that data. All of the data from the data pages will be read which includes columns in the table that aren't referenced by the query.

After creating the _dta_index_survey_25_135059617__K1_K10_K19 index then SQL Server will be able to satisfy that query with an index scan on the new index. It is possible that the index will take up less pages than the table so that could be more efficient from an IO perspective. For example, suppose that you had a VARCHAR(2000) column that was heavily populated with data. That column will be included in the data pages for the clustered index but will not be included in the nonclustered index. That means that satisfying the query using the nonclustered index could incur fewer logical reads.

Note that sent_date and group_id aren't useful as key columns for this example. They could be INCLUDED columns for the same benefit. Note that without the ORDER BY clause an index on just sent_date and group_id would also be a covering index, so including the id column wouldn't be useful for that example without ORDER BY.

That index could also be marginally useful if your clustered key was defined as id DESC (id ASC is the default). SQL Server cannot do backwards clustered index scans in parallel, but the following query could be run in parallel with a scan on _dta_index_survey_25_135059617__K1_K10_K19:

SELECT id
FROM your_table
ORDER BY id DESC;

Again, the key columns on sent_date and group_id aren't useful for this query, but the query optimizer could use the first column of the defined index. I cannot think of a query that benefits from all three key columns.

It is more difficult to think of queries that would benefit from the index on group_id, id, campaign_id, and sent_date. Assuming that the clustered key is defined as id ASC, I believe that ORDER BY group_id, id, campaign_id, sent_date can be satisfied by an index on just group_id because id is a unique clustering key and that index will be created in group_id then id order. As before, you could benefit if your index on id is sorted in a different order than the clustering key on id.

I suspect there are also some data distributions that could benefit from the _dta_index_survey_21_364632442__K18_K1_K2_K9 index when filtering on both group_id and id, even though id is a unique clustered key. Suppose that each data page in the table contains 2 rows and half the rows in the table have group_id = 1. In theory, SQL Server could use that index to satisfy the following query:

SELECT group_id, id, campaign_id, sent_date
FROM your_table
WHERE group_id = 1 AND id IN (1,3,5,7,9,...);

and possibly do less IO than using the clustered index. However, I do not know if the query optimizer would actually do this.

In summary, there are some some corner cases with covering indexes and sort orders which could benefit from the indexes that you listed in the question. However, in my experience it will be pretty unlikely those indexes will provide a noticeable (or even any) benefit for your workload. If those indexes do happen to provide a benefit it's likely that there will be smaller indexes that could provide the same benefit.


Can I query Microsoft GP database and save the result as MDF file? I have a request as below for auditing purpose on Microsoft Dynamic GP database.

Database files are required as .mdf and .ldf files for certain companies
Only need the certain period time of data, not entire database from day 1 till now

My questions:

Is this even possible to provide only certain period time of activities from database as .mdf file? I mean if the .mdf file is required, it must be the entire database right?
If it is possible, how? Query the database for everything happened in that period of time and save it as .mdf file and .ldf file? Or somehow somewhat cut the .mdf file? I don't think so...
or if I provide entire database with only that period of transaction log?

I posted this question on overflow, It should be posted here.

The way to handle this is to create a new database with a different name (for example, MyDB_Export), then use the Export functionality in Sql Server Management Studio - right-click on the database in SSMS, and click Tasks / Export Data... on the pop-up menus. The only thing you will do is to specify a query to select the data to export to the new DB.

Its pretty intuitive to walk through the steps, so you should be able to work out how to complete this easily once you are in the Import/Export Wizard. If not, just add a comment below and I'll expand on this.


NUMA Nodes - MAXDOP - PLE We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article:

https://support.microsoft.com/en-us/kb/322385

So we need to change it to 4.

However my question is what is the impact of having maxdop set to 8? So going parallel across two NUMAS ? The reason I ask is that we just had a strange issue where queries were very slow to return and PLE dropped off rapidly.

Even when nothing was running against SQL PLE didn't not improve. CXPACKET wait types went up. Then all of a sudden CXPACKET wait type dropped completely and PLE began to rise and now has returned to normal.

Throughout this time small queries were being executed against a database, however it was not the case that one query finished causing the CXPACKET wait type to drop off and PLE to rise again - we don't know what causes that.

A possible explanation is the incorrect MAXDOP setting.

Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

Thank you

SQL SERVER 2005 SP3

Your best bet first is to upgrade it to a supported SP - First Download SP4 then Download CU3 --> Build NO:    9.00.5266. Support for SQL server 2005 is ending next year (2016/04/12) even for the latest SP and CU - so better upgrade to a 2012/2014.

You can use my script to help you suggest a good MAXDOP value.

  Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

SQL server is NUMA aware. This means that SQL server knows what NUMA node the processors are in and what NUMA node the memory is in. Due to this, SQL server will assign  worker thread on correct NUMA node for the data that you want to access.

Refer to :

How It Works: SQL Server 2008 NUMA and Foreign Pages
How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

Below are some queries that will help you find out if there is an imbalance of schedulers between the NUMA nodes - which can lead to significant performance problems under load :

SELECT
  parent_node_id,
  scheduler_id,
  [cpu_id],
  is_idle,
  current_tasks_count,
  runnable_tasks_count,
  active_workers_count,
  load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE';

--- check the current tasks, runnable tasks, active workers and avg load factor COUNT ...
SELECT
  parent_node_id,
  SUM(current_tasks_count) AS current_tasks_count,
  SUM(runnable_tasks_count) AS runnable_tasks_count,
  SUM(active_workers_count) AS active_workers_count,
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;

You should monitor SQL Server:Buffer Node and SQL Server, Memory Node as opposed to just PLE - monitor the individual Buffer Node:Page life expectancy counters (there will be one Buffer Node performance object for each NUMA node).

  exhausting worker threads

IF you set the MAXDOP setting as default (which is 0) then it could lead to worker thread starvation.


Access to tables in SQL Server I would like to know if there is a table where accesses to tables are saved, per user or per system.

I'm using both SQL Server 2005 and 2008.

The closest match to what you're looking for seems to be the SQL Server Audit feature, which was added in SQL Server 2008 as a replacement/successor for SQL Trace.

I recommend following the link to read up on it, since its setup/use is too big a topic to describe here in any meaningful way.


Does the SQL Server generate the contents for a derived table contents for every matching record in a join I am on SQL Server 2008 R2. I am executing the following SQL a

use tempdb
go
create table t1 ( c1 int, c2 int, c3 uniqueidentifier null)

go
insert t1(c1,c2) values (1,2),(1,3)
go
update t1
set c2 = 5 + c2,
    c3 = t2.c3
from t1 join ( select c1=1, c3=newid())   as t2 on t1.c1 = t2.c1
go
select * from t1
go
drop table t1
go

The result of the query is

c1          c2          c3
----------- ----------- ------------------------------------
1           7           D6BE2119-CECB-4413-94E4-6099E5CC9028
1           8           0BF8A3E2-4E66-4091-A1B0-4FB8E130B347

I was expecting the GUID values in c3 to be the same. It appears that derived table t2 is being "reevaluated" for each row in the table t1 even though it is not a co-related sub-query. Is this expected behavior ?

You would expect the NEWID() to be calculated once, for the derived table t2. This is not the case, as it is calculated for each row of the join resultset of T1 and T2.

Simply put, for each row where T1.C1 = T2.C1 or in other words where T1.C1=1.

Here is a part of a statement from Microsoft regarding this:

  Closing the loop . . . I've discussed this question with the Dev team.
  And eventually we have decided not to change current behavior, for the
  following reasons:

  1) The optimizer does not guarantee timing or number of executions of
  scalar functions. This is a long-established tenet. It's the
  fundamental 'leeway' that allows the optimizer enough freedom to gain
  significant improvements in query-plan execution....

As such the optimizer can choose the timing or number of executions, to get the best performance.

The source of the quote and some more information and examples and the response from microsoft

To explain it we best go through the query plan that is created for the UPDATE statement.

Right of the bat we don't see a join predicate, but simply a  table scan.
A predicate is added to that table scan, to only get the rows where C1 = 1

This  is the 'join' reduced to a predicate.

After this, 2 rows are found that match this predicate, and are processed by the first Compute Scalar operator.

And NEWID() is calculated for each column of the joined table + derived table.

A workaround

The obvious fix is to use a variable to hold the NEWID() value as to calculate it before the join & reuse it.

create table t1 ( c1 int, c2 int, c3 uniqueidentifier null)

go
insert t1(c1,c2) values (1,2),(1,3)
go
DECLARE @test uniqueidentifier = newid()
update t1
set c2 = 5 + c2,
    c3 = t2.c3
from t1 join
( select c1=1, c3=@test)   as t2 on t1.c1 = t2.c1
go
select * from t1
go
drop table t1
go

If you would have more than 1 source columns (T2) you would need to store the NEWID() values in a temporary table or table variable

Another interesting part:

  So, we have a choice: if we want to guarantee a certain behavior in
  the presence of non-deterministic (side-effecting) code – so that
  results of JOINs, for example, follow the semantics of a nested-loop
  execution – then we can use appropriate OPTIONs to force that behavior
  – as UC points out. But the resulting code will run slow – that's the
  cost of, in effect, hobbling the Query Optimizer.

Source

By using functions we could force the optimizer to first calculate the NEWID() on the source table, before the join. But I am not able to do that on the query at hand (probably not the query's fault).


Assign value based on table and ID I wish to assign a value in my row which should be based on the country as:

Only US: Should be assigned USA
only Other countries: Should be
    assigned International
US + Other countries: Should be assigned
    Global

So for the below table:

    DECLARE @A TABLE
    (
    PolicyID int,
    PolicylocationID int,
    Country VARCHAR(100)
    )
    INSERT INTO @A
    (
    PolicyID,PolicylocationID,Country
    )
    VALUES
    (1,1,'US'),
    (1,2,'US'),
    (1,3,'US'),
    (1,4,'US'),
    (2,1,'NA'),
    (2,2,'PA'),
    (2,3,'CA'),
    (2,4,'ZA'),
    (3,1,'US'),
    (3,2,'NA'),
    --(3,3,'PA'),
    (4,1,'US'),
    --(4,2,'US'),
    (4,3,'US'),
    (5,1,'US'),
    --(5,2,'NA'),
    (5,3,'PA'),
    (6,1,'NA'),
    --(6,2,'PA'),
    (6,3,'CA')

    Select * into #temp
    from @A
Select * from #temp

the output should be:

DECLARE @A TABLE
(
PolicyID int,
PolicylocationID int,
Country VARCHAR(100),
CType VARCHAR(100)
)
INSERT INTO @A
(
PolicyID,PolicylocationID,Country,CType
)
VALUES
(1,1,'US','US'),
(1,2,'US','US'),
(1,3,'US','US'),
(1,4,'US','US'),
(2,1,'NA','Internationl'),
(2,2,'PA','Internationl'),
(2,3,'CA','Internationl'),
(2,4,'ZA','Internationl'),
(3,1,'US','Global'),
(3,2,'NA','Global'),
--(3,3,'PA','Global'),
(4,1,'US','US'),
--(4,2,'US','US'),
(4,3,'US','US'),
(5,1,'US','Global'),
--(5,2,'NA','Global'),
(5,3,'PA','Global'),
(6,1,'NA','Internationl'),
--(6,2,'PA','Internationl'),
(6,3,'CA','Internationl')

SElect * into #temp
from @A

Any ideas on how to achieve this?

WITH cte AS ( SELECT PolicyID,
                     PolicylocationID,
                     Country,
                     COUNT(CASE WHEN Country = 'US' THEN 1 END) OVER (PARTITION BY PolicyID) us,
                     COUNT(Country) OVER (PARTITION BY PolicyID) total
              FROM test
            )
SELECT PolicyID,
       PolicylocationID,
       Country,
       CASE WHEN us = total THEN 'US'
            WHEN us = 0     THEN 'International'
            ELSE 'Global'
       END CType
FROM cte

fiddle


Complex query with multiple normalized fields We have a fairly simple table structure, but with a LOT of fields per table (talking 40+).  This data is initially produced in plain-text, user-readable tables, but then it is translated into higher-performance, easier to query tables before being installed for use in production.

What we do is, wherever possible and reasonable we translate certain fields into enumerated values, and keep track of the enumerations in a MasterEnum table.  There are usually 20-25 enumerated fields out of 40 or so.

Sample table structure:

Plain text version:

    |  PartNumber   |  Manufacturer  |  SomeData  |  SomeMoreData  |  SomeTextData ...
    ----------------------------------------------------------------------------------
    |  1x9kdah      |  GizmoCorp     | ThisIsData |  OtherData     |  ThisStaysText ...
    |  8xcjkzh      |  GadgetInc     | MoreData   |  OtherData2    |  ThisTooStaysText ...

Target table sample structure:

    |  PartNumber  |  Manufacturer  |  SomeData   | SomeMoreData  |  SomeTextData ...
    -------------------------------------------------------------------------------------
    |  1x9kdah     |       1        |    1        |      1        |  ThisStaysText ...
    |  8xcjkzh     |       2        |    2        |      2        |  ThisTooStaysText ...

Master Enumeration Table Structure

    |  FieldName     |  InputText  |  ValueCode |
    ---------------------------------------------
    |  Manufacturer  |  GizmoCorp  |  1         |
    |  Manufacturer  |  GadgetInc  |  2         |
    |  SomeData      |  ThisIsData |  1         |
    |  SomeData      |  MoreData   |  2         |
    |  SomeMoreData  |  OtherData  |  1         |
    |  SomeMoreData  |  OtherData2 |  2         |

We have a means of doing this translation that works and works well; however it's a little on the slow side since all the processing is done in Java via Spring/Hibernate.  My question is:

Is there a way to write a single query that would accomplish all the above translations?  (Note that we have an excellent way of keeping track of our field definitions programmaticly, so generating complex SQL queries on the fly is not an issue).  If it is not possible to do it in a single query, how would I structure queries to iterate over the individual fields and make sure that as the translations happen the data is inserted into the new table remains associated with the correct rows?

Note that it is safe to assume the target table is always empty at the beginning of the process.

Not many people like them, but I would suggest you use a cursor in a stored procedure to loop though the fields and normalise them into the target table (silly ETL process).

You could probably do better having had separate lookup tables for each type of field, but it depends on you scenario.


In place upgrade from Express 2005 SP 4 to Express 2014, does database size limitation follow? We are currently attempting get around a file size limitation issue with our database. The database file size hovers around the 4GB mark (the database size limit is 4GB in SQL Express 2005). We always trim data past a certain age to keep it below this limit.

To avoid this, we are looking to perform an in place upgrade to SQL Express 2014 (10GB size limitation). Will the file size limitation follow the database when utilizing the in place upgrade?

No it will not follow, you will be able to utilize the migrated database up until the 10GB limit.

I've written an article on maximizing space savings with regard to SQL Server Express, which may also be of interest to you to help reduce the your periodic data pruning further.

You may also want to consider upgrading again after 2014 to 2017 or 2019 to keep up with the latest advances in SQL Server features, stability, etc.