Groups relationships - Detailed or Direct? I have a Person model:

PersonId    Name
----------- ----------------
1           Jessica
2           Jennifer

Then I have a ShareModel which have the Personand an Itemassociated.

ShareId     ItemId
----------- ----------------
1           1
2           2

And finally I have a SharePeoplewhich tells all people that have access to that Item.

ShareId     PersonId
----------- ----------------
1           1
1           2

So now a new feature must be added, which is Groups. So I thought about two aproaches and I would like to know which one is the more correct.

So two things I'm clear, one is to add a new IsGroup property to the Person Model.

PersonId    Name             IsGroup
----------- ---------------- -----------
1           Jessica          False
2           Jennifer         False
3           Blondes          True

And another is to have the GroupPeople

GroupPersonId    ChildPersonId
-------------    ----------------
3                1
3                2

OPTION 1 : Should I create the association on the SharePeople with the IsGroup rows directly:

ShareId     PersonId
----------- ----------------
2           3

OPTION 2 : should I create the association always with the persons which are part of the group, and have another column on that will identify from which Group the association was made.

ShareId     PersonId            GroupPersonId
----------- ----------------    -------------
2           1                   3
2           2                   3

The second option is more simple when getting data from tables, it will be fast and will create less impact, however it creates a lot of rows on the database.

The first option is cleaner but I will have to do a lot of logical statements inside my code wheter the row it's a Group or a single Person.

But bottom line is that I really don't know what's better concerning performance, and I also don't whant to do tons of odd code.

Thank you very much,

First, it's hard to ascertain what you need as I don't know what your data means. What is ShareID and what are the groups for? How do ItemIDs relate to ShareID? Is the data one-to-one or one-to-many? etc...

With that said, I do know for sure that Option 1 does not follow good practice. You don't store different types of data in the same columns. You have to think not only about how complicated your logic in your queries will be, but also what about data changes? It looks like you have the each person in a group in their own row followed by the group in a different row. Data changes will painfully difficult on top of slow because you're not going to have to use very complicated logic.

So option 2 seems to better to me. It doesn't have any blaring issues. Also remember the number of rows does not impact performance in the way you think it does. If you have more rows with good structure so you are using SQL the way it was optimized(dealing with sets not Row by Agonizing Row(RBAR)), then it will actually perform better than less rows in bad structure.

Just remember keep narrow data types, use indexes, and follow good SQL coding practices: always set-based(no cursors or loops), only the columns you need, and no functions on the predicate of the where clause.

I do suggest you read up on data normalization(1NF,2NF,3NF) and I'm sure there are plenty of helpful articles and examples on how to structure your tables that you can find online.


How do I return all column values for the recently inserted row/rows? I have these two tables in a PostgreSQL 12 database:
CREATE TABLE public.test (
    entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

CREATE TABLE public.test_registration (
    entity_id uuid REFERENCES test(entity_id),
    row_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    entry_name text,
    code text
);

entry_name is populated by a trigger AFTER INSERT:
CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger AS
$$
BEGIN
   UPDATE test_registration
   SET entry_name = new_inserts.code
   FROM new_inserts
   WHERE new_inserts.row_id = test_registration.row_id;
   RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert_trigger
AFTER INSERT ON test_registration
REFERENCING NEW TABLE AS new_inserts
FOR EACH STATEMENT
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE test_registration_entry_name();

The trigger function differs from table to table.
My current issue is when I insert a row into test_registration as such:
INSERT INTO test DEFAULT VALUES
RETURNING entity_id;  -- let's say: 'a17e66c5-1049-4ba9-bed3-90bbc823e064'

And then insert a registration and return all row values for the inserted row:
INSERT INTO test_registration (entity_id,code)
VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'EB')
RETURNING *

I get all column values correctly, except for the entry_name which is null. Which makes sense because entry_name is being set after the insert.
So I tried to expose this by altering my INSERT using the row_id as such:
WITH create_query AS (INSERT INTO test_registration (entity_id,code)
VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'13')
RETURNING *) SELECT v.entity_id, v.row_id, b.entity_id,b.entry_name, b.row_id
from create_query AS v, test_registration AS b WHERE V.Row_id = b.Row_id

This then turns out to not return anything. create_query.row_id seems to differ from the one in test_registration - which makes no sense? Why would they be different?
How can I extract all column values for the inserted row, after the triggers has been triggered.

Impossible with an AFTER trigger
The version of the row visible to the RETURNING clause is the same version that's visible to an AFTER trigger. The UPDATE in your trigger function creates a new row version. It's impossible to see the effects in the RETURNING clause, which is based on the prior row version. Demo:
db<>fiddle here
Effectively, the output of RETURNING is "dead on arrival", obsoleted immediately by the AFTER trigger. You would need a separate SELECT to see the new row version. (Can be in the same transaction.)
Impossible with a CTE
Your query is bound to return nothing:
WITH create_query AS (
   INSERT INTO test_registration (entity_id,code)
   VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'13')
   RETURNING *
   )
SELECT v.entity_id, v.row_id, b.entity_id, b.entry_name, b.row_id
FROM   create_query AS v, test_registration AS b
WHERE  v.row_id = b.row_id;

All sub-statements of a query with CTEs see the same snapshot of the database. Since the SELECT cannot yet see the row inserted in the CTE (and row_id is populated with uuid_generate_v4()) your implicit JOIN is bound to return no row.
See:

Why can't rows inserted in a CTE be updated in the same statement?

Proper trigger
I see nothing to warrant a STATEMENT-level, nor the need for an AFTER trigger. Use a much simpler (and faster) ROW-level trigger BEFORE. Then RETURNING * will just work:
CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.entry_name := NEW.code;
   RETURN NEW;
END
$func$;

CREATE TRIGGER test_insert_trigger
BEFORE INSERT ON test_registration
FOR EACH ROW
WHEN (pg_trigger_depth() = 0
      AND NEW.entry_name IS DISTINCT FROM NEW.code) -- optional
EXECUTE FUNCTION test_registration_entry_name();

db<>fiddle here
(The fiddle operates with integers because the additional module uuid-ossp providing uuid_generate_v4() is not installed on dbfiddle.uk. But it works all the same.)
I added another (optional) trigger condition: NEW.entry_name IS DISTINCT FROM NEW.code to avoid needless work if the trigger wouldn't change anything.
Related:

Assign to NEW by key in a Postgres trigger

Aside: more efficient INSERT
Do both inserts in a single query with a data-modifying CTE like this:
WITH ins_test AS (
   INSERT INTO test DEFAULT VALUES RETURNING entity_id
   )
INSERT INTO test_registration (entity_id, code)
SELECT entity_id, 'EB'
FROM   ins_test
RETURNING *;

Then you can immediately reuse the auto-generated entity_id without another round-trip to the server. See:

Insert data in 3 tables at a time using Postgres
Bulk insert in multiple tables
Conditional INSERT with a nested CTE?


I couldn't see Conflict tables in replication monitor SQL Server 2008 R2 in Merge Replication I work with merge replication in SQL Server 2008 R2. In the replication monitor I can't see conflict table, I must to see conflict tables in "MSmerge_conflicts_info", please help me.

How to see conflict tables in replication monitor? Thanks

You cannot see the conflict tables from within Replication Monitor.

However, you can view the Conflict Viewer by right-clicking your Merge Publication in Object Explorer -> View Conflicts.

Alternatively, you can directly query MSmerge_conflicts_info.

For more information, see View and Resolve Data Conflicts for Merge Publication (SQL Server Management Studio) and Microsoft Replication Conflict Viewer (Merge Replication).


Calculate start and end dates from a list of only start dates and partitioned I'm currently working with a data set with the following table structure

create or replace TABLE FACT_TBL_PRODUCTSUPPLIER (
    DATEID NUMBER(18,0) NOT NULL,
    PRODUCTNUMBER NUMBER(38,0) NOT NULL,
    SUPPLIERNUMBER NUMBER(38,0) NOT NULL
);

Example Data Set:

    DATEID        PRODUCTNUMBER SUPPLIERNUMBER
    2019-07-06    555           1234
    2019-07-06    343           545
    2019-07-10    555           8888
    2019-07-16    555           1234
    2019-08-01    555           1234
    2019-09-15    555           6666

I'm trying to keep the product number linked to the supplier number until the product gets taken over by another supplier. When the product does get taken over then I should add another 30 days to the product so it remains linked.

Expected Output:

    DATEID       END_DATEID    PRODUCTNUMBER SUPPLIERNUMBER
    2019-07-06   2019-08-07              555           1234
    2019-07-06   2020-02-06              343           545
    2019-07-10   2019-08-13              555           8888
    2019-07-16   2019-08-29              555           1234
    2019-08-01   2019-10-13              555           1234
    2019-09-15   2020-02-06              555           6666

I have tried the following options so far:

select td.dateid,
LEAD(DATEADD(day, +28, dateid)) OVER(ORDER BY dateid) AS EndDate,
productnumber, ps.suppliernumber,
from FACT_TBL_PRODUCTSUPPLIER PS

select
    distinct dateid,
    coalesce(
        dateid,
        lag(dateid, 1) over(
            partition by
                productnumber,
                suppliernumber
            order by
                dateid asc
        )
    ) as dateid,
    dateadd(
        'day',
        28,
        coalesce(
            lead(dateid) over(
                partition by
                    productnumber,
                    suppliernumber
                order by
                    dateid asc
            ),
            current_date()
        )
    ) end_dateid,
    productnumber,
    suppliernumber
from
    FACT_TBL_PRODUCTSUPPLIER;

If no one takes over the product number then the current supplier that has been assigned the product should be able to see it.

What I'm trying to do is a product should be displayed to a supplier until someone takes over. When someone does takeover the end date would have an additional 28 days to display. If no one takes over a specific product then the supplier should be able to see it indefinitely e.g. current date.

To make sure I've answered correctly, I'll restate the requirement. The result set should show the first date (DATEID) that a SUPPLIER took control of a PRODUCT and the last date the PRODUCT was visible (ENDDATEID) when another SUPPLIER took control.

ENDDATEID should add an additional 28 days to the date the next SUPPLIER took control of a PRODUCT. If no new SUPPLIER has taken control, the ENDDATEID should show the current date + 28 days.

If I've stated this correctly, you can use LEAD and a CTE to return the result set you're looking for. Code below, and working example here:

WITH CTE AS (
SELECT DATEID
  ,LEAD(DATEID, 1, GETDATE()) OVER (PARTITION BY PRODUCTNUMBER ORDER BY DATEID) AS TAKEOVERDATE
  ,PRODUCTNUMBER
  ,SUPPLIERNUMBER
FROM FACT_TBL_PRODUCTSUPPLIER
)

SELECT DATEID
  ,DATEADD(DAY, (DATEDIFF(DAY, DATEID, TAKEOVERDATE))+28, DATEID) AS ENDDATEID
  ,PRODUCTNUMBER
  ,SUPPLIERNUMBER
FROM CTE

Output:

DATEID      ENDDATEID   PRODUCTNUMBER   SUPPLIERNUMBER
----------------------------------------------------
2019-07-06  2020-02-27  343             545
2019-07-06  2019-08-07  555             1234
2019-07-10  2019-08-13  555             8888
2019-07-16  2019-08-29  555             1234
2019-08-01  2019-10-13  555             1234
2019-09-15  2020-02-27  555             6666

In the LEAD syntax, we're partitioning the data by PRODUCTNUMBER to ensure we only get dates relevant to the product that row represents. We then get the next DATEID in order (defined as DATEID ascending in the LEAD syntax). If no next date value exists, we get the current date. This gives us the date a product was taken over (or the current date if it hasn't been taken over).

From here, we can use DATEADD outside the CTE to add 28 days to the 'TAKEOVERDATE' column to produce the desired ENDDATEID value.

NOTE: This syntax is for SQL Server. You haven't stated an RDBMS, but tagged the question T-SQL so I'm assuming SQL Server.


Error 0xc000007b with Oracle 19c As our (32-bit) application uses the Oracle client for communication with the Oracle server I have set up a test environment on a Microsoft Windows Server 2019 Datacenter. I have installed the Oracle DB 19c (19.3.0.0 64-bit) and the appropriate Oracle client (19.3.0.0 32-bit).
As soon as I start TNSPING or SQLPLUS out of the installation directory of the Oracle client, the Windows error message "The application was unable to start correctly (0xc000007b). Click OK to close the application." occurs.
I have searched the internet for remedy and tried all the 'restart Windows' and 'reinstall framework' hints without success. The hint "64-bit software on 32-bit Windows doesn't work." simply doesn't apply to my problem.
Is there anybody out there who has further ideas - perhaps ideas more close to the Oracle world.
Thanks in advance,
Ben

I had exactly the same errors after installing a 32-Bit client (19.9.0.0.201020). Windows application event log stated an error concerning "C:\Windows\system32\ntdll.dll". This is a 64-Bit DLL with the 32-Bit counterpart in "C:\Windows\SysWow64".
This is the solution
You need to add C:\Windows\SysWow64 to the PATH environment variable. This can be done temporarily using:
SET PATH=%PATH%;C:\Windows\SysWow64

Furthermore you need to have 32-Bit Microsoft Visual C++ Runtime 2015-2019 installed. The latest Visual C++ Redistributable can be downloaded from:

The latest supported Visual C++ downloads (Microsoft | Support)

Hope this helps.


Scramble the data output of a stored procedure I have a stored procedure that produces data for a call center system every morning. However, the data is sent in the same order daily. Is there a way to send the day in a different order every day without having to manually change the where clause? For examples today's data is : 1,2,3 tomorrow's will be :3,2,1 the day after 2,1,3 .. Same data different order daily.

Assuming you want randomish ordering of your output you can just ORDER BY NEWID().  That would jumble up the ordering of the results reasonably well.  If you only need to randomize after a certain order is established then you can still use the NEWID() trick.  Below are some quick examples:

-- Fully random ordering
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    NEWID() ;

-- Random ordering of results but the results still preserve the order of UserId
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    F.UserId
    , NEWID() ;

Another way, which will not produce all possible orderings but only a limited set but can be more efficient with large tables because it doesn't use NEWID(), only a single value for @aRandomNumber which should be drawn from the range of the column that is compared against (F.UserId here) :

WITH cte AS
  ( SELECT *, 0 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId >= @aRandomNumber
    ORDER BY F.UserId
  UNION ALL
    SELECT *, 1 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId < @aRandomNumber
    ORDER BY F.UserId
  )
SELECT <list of columns except the "ord">
FROM cte
ORDER BY ord, UserId ;


SQL Server replication for off site copy We have our Primary SQL Servers in-house (15 primary servers at the moment (500 databases approx overall), most of the servers have hex core processors).

These are mirrored to other backup servers in a near-by building (via a dedicated 0ms fibre link).

What we'd like to do is maintain a live copy of our data in an off-site data centre miles away (as part of a much bigger DR project - i.e. if there is a comms issue in our area).

Currently we use SQL Server 2008 R2 - Standard edition.

Obviously using the "Standard Edition" of SQL Server 2008 R2 limits us to Synchronous mirroring as asynchronous is an "Enterprise Edition" feature.

We've done testing with offsite synchronous mirroring but the latency just made it a no-go.

I'd love to upgrade to SQL Server 2012 Enterprise and implement AlwaysOn availability groups, but the company is not willing to spend the £100,000+ to upgrade the SQL licensing (2012's per core licencing + our hex-core processors = epic fail) - they're not even willing to spend the money to upgrade to 2008 Enterprise so that’s asynchronous mirroring out of the window too.

So, with my hands tied by these financial constraints i'm stuck with 2008 R2 Standard edition.

The only options left open to me are log shipping and replication (correct me if I’ve missed anything) - Log Shipping is crude - but when managed properly can be feasible - so we've got that on the back burner for now.

My Question(s):

Does SQL Server 2008 R2 Standard have all the replication features that we'd need to do off-site replication?
Would the replication of in-house servers to an off-site data centre (20-30ms latency) be feasible without impacting performance on the primary server in any way (would we be ok setting the Replication Distributor on the "Principle" server)?
Given that replication is not an instance level replication solution - would the management of this be more than 1 DBA could handle? (given that i'm already managing 15 Principle servers & their Mirror counterparts - admittedly with lots of automation)
Would replication be possible from our primary without impacting the existing mirroring configuration - it's my understanding that best practice is to mirror the Subscriber as opposed to the Publisher - is this correct?
Is there anything else Replication Virgins forget that you need to bring to my attention?

Based on what you have described Log shipping is going to be the way to go.  Log shipping is old school, tried and true.

Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.

As for 2012 upgrade costs they may not be as bad as you think. If you have software assurance and an enterprise agreement that'll save you a bunch of money on licensing.


How to Find Pages/Sec from sys.dm_os_performance_counters How do we find pages/sec from dm_os_performance_counters DMV, I do not see it here:

SELECT * FROM sys.dm_os_performance_counters
where counter_name like '%page%'

Monitor Memory Usage

  Pages/sec Definition: The Pages/sec counter indicates the number of
  pages that either were retrieved from disk due to hard page faults or
  written to disk to free space in the working set due to page faults.

The page you reference talks about monitoring a system running SQL Server, but it does not make any implicit or explicit promise that Memory:Pages/sec will be available specifically in the DMV sys.dm_os_performance_counters. This is more about general system monitoring using performance monitor, 3rd party tools, etc.

The DMV only exposes a specific subset of counters that are SQL Server-specific (according to someone, since it can be subjective). Memory:Pages/sec is, at least currently, not one of them - primarily because it measures for the whole operating system, not for the current instance of SQL Server. You can see what counters are available simply by querying the DMV. If the DMV is empty, Erik's link is a good one.

You can also look directly at the perf-{instance}sqlctr.ini in your instance's Binn folder. You may be tempted to hack the ini file to stuff your desired counters into the DMV, but I haven't tried this, don't recommend it, and am sure you will be on your own in terms of support. Better solution is to use a different method to monitor performance counters (both those you can get from the DMVs and those you can't).


SQL Server Extended Events to Capture Objects access by a User I am in for a requirement where I need to use EE to capture the Objects Accessed by a particular SQL User.
I want to avoid the action sqlserver.sql_text, if I use this, I will need to manually scan though the data which may be prone to missing some data or incorrect results.
Is there an alternative to using sqlserver.sql_text

I gave up on EE and instead using SQL AUDIT now.
  https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15


MAX (id) function with MySQli How can I use the following statement to return only the record with the maximum PersonID from the table?

/* Create the prepared statement */
if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS PersonID,familyname,firstname,othernames,gender,CountryID,dateofbirth,nrcno,passportno,address,occupation,village,chief,district,StatusID,imgurl

FROM t_persons")) {

/* Execute the prepared Statement */
$stmt->execute();

/* Bind results to variables */
$stmt->bind_result($PersonID,$familyname,$firstname,$othernames,$gender,$CountryID,$dateofbirth,$nrcno,$passportno,$address,$occupation,$village,$chief,$district,$StatusID,$img);

/* fetch values */
while ($rows = $stmt->fetch()) {
 // display records in a table

in PostgreSQL you could use use something called "windowing and analytics". this does not exist in MySQL however.
an alternative way would be to use GROUP BY and do ..
SELECT max(id), person, address, ... FROM tab GROUP BY person, address, ...
this should work somewhat efficiently if you need it for all. if you need it for jsut one folk you should go for ypercube's answer.


SQL Server Replication or Backup via sftp I have a SQL Server database that is hosted in a cloud service as part of one of our ERP systems . Currently we are getting daily backup dumps via sftp which i am restoring to our on prem SQL Server for the departments to use with Services like PowerBI, SSAS and on site SSRS. Recently a request was made to try and increase the frequency of the updates. What i was wondering is if this would be a task for which Replication would be a better tool than more frequent backups?

Can you even setup a Snapshot & Distribution Agent to basically place a file in a location and then have another program pull the snapshot down via sftp and have a subscriber then pull that snapshot in to the local database?

Esentially a push type Replication (merge or snapshot) where the subscriber can not directly connect to the Distributor.

Or is more frequent backups with the associated locks and overhead a better solution?

In order for replication to work, the publisher and subscriber need to be able to talk to the distributor directly so I don't think this will work in your current setup.

Depending on the amount of data you're producing, a version of log shipping may give you what you're looking for, but you are limited by the database being in a state that allows you to restore further logs and you will still get some disruption when the logs are restored.

The two 'seamless' options I can think of to give you more frequent updates are:
1) restoring your backups to a new database on the on prem server and then using SSIS or stored procedures etc to push this into your actual database(s).
2) using Enterprise Edition and AlwaysOn groups to restore the data to the primary server, which will then be replicated over to the read only secondary which your reporting services etc are pointing at.


Remove duplicate records with special rules - Is it possible with a single query? I have the following data in a table:
id    loc1    loc2    zip_code
------------------------------
10    1       null    12345
10    null    1       null

11    1       null    null
11    null    1       43210

12    1       null    54321
12    null    1       87654

13    1       null    null
13    null    1       null

14    1       null    65432
15    1       null    null
16    null    1       76767
17    null    1       null

The goal is to remove the duplicates with some special rules.
To accomplish this, I have the option to delete the "bad" records directly from my original table or copy the "good" records into a new table.
Rules:

Each record always has one location set. i.e. we either have loc1 or loc2 for each row.
The unique rows (id: 14-17) are good as they are
For the duplicate rows (id: 10-13), I want to choose only one row:

The row that has a zip_code
For all other cases, we must always choose the primary location. ie. the one that has loc1

After de-duplication, the final data should look like this:
id    loc1    loc2    zip_code
------------------------------
10    1       null    12345
11    null    1       43210
12    1       null    54321
13    1       null    null

14    1       null    65432
15    1       null    null
16    null    1       76767
17    null    1       null

WITH cte AS ( SELECT *,
                     ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
              FROM test )
SELECT id, loc1, loc2, zip_code
FROM cte
WHERE rn = 1
ORDER BY id;

If I want to use this for deleting the duplicates, all I need to do is change WHERE rn = 2. Is that correct? – advncd
@advncd No, MySQL does not support updatable CTE. You must use this query as a subquery which selects rows which must be stored while deleting from another copy of source table.

DELETE t1.*
FROM test t1
JOIN (WITH cte AS ( SELECT *,
                           ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
                    FROM test )
      SELECT id, loc1, loc2, zip_code
      FROM cte
      WHERE rn > 1) t2 ON t1.id = t2.id
                      AND t1.loc1 <=> t2.loc1
                      AND t1.loc2 <=> t2.loc2
                      AND t1.zip_code <=> t2.zip_code;

fiddle


Reasons for avoiding large ID values We are working on a web application, not yet accessible to users. My boss noticed that newly created records get an ID of over 10 000, even though we only have under 100 records in the table. She assumed that the web interface for some reason creates over a 100 times more temporary records than actual ones (and deletes them) and that this can lead us to running out of range within a few months of release.

I don't think she is correct about the cause of the ID inflation (the colleague who can answer this is on vacation, so we don't know for sure), but let's assume that she is. She said that she'd hate to use a bigint column, and that she'd like us to stop autoincrementing the ID column and write server-side code which chooses the first "unused" integer and uses it as an ID.

I am a computer science grad student with little practical experience, filling a junior developer role. She has years of experience of managing all of our organisation's databases, and designing most of them. I think that she's incorrect in this case, that a bigint ID is nothing to be afraid of, and that mimicking the DBMS functionality smells of an antipattern. But I don't trust my judgment yet.

What are the arguments for and against each position? What bad things can happen if we use a bigint, and what are the dangers of reinventing the wheel autoincrementing functionality? Is there a third solution which is better than either one? What could her reasons be for wanting to avoid an inflation of ID face values? I'm interested in hearing about pragmatic reasons too - maybe bigint IDs work in theory, but cause headaches in practice?

The application is not expected to handle very large amounts of data. I doubt that it will reach 10 000 actual records within the next few years.

If it makes any difference, we are using Microsoft SQL server. The application is written in C# and uses Linq to SQL.

Update

Thank you, I found the existing answers and comments interesting. But I'm afraid you misunderstood my question, so they contain what I wanted to know.

I'm not really concerned about the real reason for the high IDs. If we can't find it on our own, I could ask a different question. What I'm interested in is to understand the decision process in this case. For this, please assume that the application will be writing 1000 records per day, then deleting 9999 of them. I'm almost sure this is not the case, but this is what my boss believed when she made her request. So, under these hypothetical circumstances, what would be the pros and cons of either using bigint or writing our own code which will assign IDs (in a way which reuses the IDs of already deleted records, to ensure there are no gaps)?

As for the actual reason, I strongly suspect that this is because we once wrote code to import data from another database, as a proof of concept that a later migration can be done to a certain extent. I think my colleague actually created several thousand records during the import and later deleted them. I have to confirm if this was actually the case, but if it is, there is not even need for action.

Without seeing code, it is pretty hard to say conclusively what is happening.  Although, most likely the IDENTITY value is being cached, causing gaps in the value after SQL Server is restarted.  See https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server for some good answers and info about that.

A simple INT field can hold values up to 2,147,483,647.  You can actually start the identity value at -2,147,483,648, giving a full 32 bits of values.  4 Billion distinct values.  I doubt very much you're going to run out of values to use.  Assuming your application is consuming 1,000 values for each actual row added, you'd need to be creating nearly 12,000 rows per day every day to run out of IDs in 6 months assuming you started the IDENTITY value at 0, and were using an INT.   If you were using a BIGINT, you would have to wait 21 million centuries before you ran out of values if you wrote 12,000 rows per day, consuming 1,000 "values" per row.

Having said all that, if you wanted to use BIGINT as the identity field data type, there is certainly nothing wrong with that.  That'll give you for all intents-and-purposes, a limitless supply of values to use.  The performance difference between an INT and a BIGINT is practically non-existent on modern 64-bit hardware, and highly preferable over for-instance using NEWID() to generate GUIDs.

If you wanted to manage your own values for the ID column, you could create a key table, and provide a pretty bulletproof way of doing that using one of the methods shown in the answers on this question:  Handling concurrent access to a key table without deadlocks in SQL Server

The other option, assuming you're using SQL Server 2012+, would be to use a SEQUENCE object to get ID values for the column.  However, you'd need to configure the sequence to not cache values.  For example:

CREATE SEQUENCE dbo.MySequence AS INT START WITH -2147483648 INCREMENT BY 1 NO CACHE;

In answer to your boss' negative perception of "high" numbers, I would say what difference does it make?  Assuming you use an INT field, with an IDENTITY, you could in fact start the IDENTITY at 2147483647 and "increment" the value by -1.  This would make absolutely no difference to the memory consumption, performance, or disk space used since a 32 bit number is 4 bytes, no matter if it is 0 or 2147483647.  0 in binary is 00000000000000000000000000000000 when stored in a 32-bit signed INT field.  2147483647 is 01111111111111111111111111111111 - both numbers take precisely the same amount of space, both in memory, and on disk, and both require precisely the same amount of CPU operations to process.  It is far more important to get your application code designed correctly than to obsess about the actual number stored in a key field.

You asked about the pros and cons of either (a) using a larger-capacity ID column, such as a BIGINT, or (b) rolling your own solution to prevent ID gaps.  To answer these concerns:

BIGINT instead of INT as the data-type for the column in question.  Using a BIGINT requires double the amount of storage, both on-disk, and in-memory for the column itself.  If the column is the primary key index for the table involved, each and every non-clustered index attached to the table will also store the BIGINT value, at twice the size of an INT, again both in-memory and on-disk.  SQL Server stores data on disk in 8KB pages, where the number of "rows" per "page" depends on the "width" of each row.  So, for instance, if you have a table with 10 columns, each one an INT, you'd be approximately able to store 160 rows per page.  If those columns where instead BIGINT columns, you'd only be able to store 80 rows per page.  For a table with a very large number of rows, this clearly means I/O required to read and write the table will be double in this example for any given number of rows.  Granted, this is a pretty extreme example - if you had a row consisting of a single INT or BIGINT column and a single NCHAR(4000) column, you'd be (simplistically) getting a single row per page, whether you used an INT or a BIGINT.  In this scenario, it would not make much appreciable difference.
Rolling your own scenario to prevent gaps in the ID column.  You'd need to write your code in such a way that determining the "next" ID value to use does not conflict with other actions happening to the table.  Something along the lines of SELECT TOP(1) [ID] FROM [schema].[table] naively comes to mind.  What if there are multiple actors attempting to write new rows to the table simultaneously?  Two actors could easily obtain the same value, resulting in a write-conflict.  Getting around this problem requires serializing access to the table, reducing performance.  There have been many articles written about this problem; I'll leave it to the reader to perform a search on that topic.

The conclusion here is: you need to understand your requirements and properly estimate both the number of rows, and the row width, along with concurrency requirements of your application.  As usual, It Depends™.


postgres parse_indent() function hang select parse_ident('abc,efi&xyz');

Will stay there for ever, not returning. But a similar command will finish.

select regexp_split_to_array('abc,efi&xyz', '[,&]');
 regexp_split_to_array
-----------------------
 {abc,efi,xyz}
(1 row)

Not sure what's wrong with my database.  I am running a vacuum full analyze at the moment. Not sure that cause the hang.

You're losing error messages,

# select parse_ident('abc,efi&xyz');
ERROR:  string is not a valid identifier: "abc,efi&xyz"

They also do different things:

parse_ident takes a string a fully-qualified identifier -- something you can use to identify a thing in a database, and returns the breakdown of it in an array. For example, if you had a column named abc,efi&xyz on a table named table in a schema named schema in a database named db, you could do,

SELECT parse_ident('"abc,efi&xyz".table.schema.db');
           parse_ident
---------------------------------
 {"abc,efi&xyz",table,schema,db}
(1 row)

From this point you could use array addressing to identify the table, (parse_ident(...)[2]).
regexp_split_to_array takes any string and simply splits it returning an array. Unlike parse_ident it doesn't require a fully-qualified identifier, and it doesn't understand ANSI-quotes inside the identifier (as I used in my example above).


How to subtract fields and insert the value in other field automatically? id  val1   val2    total

1    10     4       10-4(automatically get the value)
.
.
.
.

I am using php and mysql. I have a total column I need to make calculation automatically in a field, how it can be done?

If this requirement is very important to you, take a look here - calculated fields in MariaDB - never any need to perform calculations - it's all taken care of for you! Although, as has been pointed out, you can always perform the calculations on the fly.


SQL Server 2012 - Common Table Expression (CTE) returning more rows than it should CTE SQLFiddle

I have one lifetable with 121 rows and 3 columns (age, male and female). I have to fetch all the 121 rows considering the gender. If the gender is "Male" I fetch the column "Male" from lifetable as qx and the column "Female" as qy and vice-versa.

However, I need to calculate a fourth column, vx, whose value starts with 1 and the subsequent values are calculated as follows, using the previous value of vx itself:

vx = (vx / (1 - @interestRate)^age)

The age always goes from 0 to 120, incremented by one each step.

I am not well versed with CTEs, therefore, I'm taking baby steps to accomplish my tasks with this new method to me. and I need help.

When I try to calculate vx, the query returns 7381 instead of 121.

Here is my CTE:

DECLARE @gender VARCHAR(10) = 'MALE'
DECLARE @interestRate NUMERIC(7, 5) = 0.0354

;WITH cte AS (
    SELECT
        lt.Age,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Male
            ELSE
                lt.Female
        END AS qx,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Female
            ELSE
                lt.Male
        END AS qy,
        CAST(1.0 AS NUMERIC(7, 5)) AS vx
    FROM LifeTable AS lt

    UNION ALL

    SELECT
        cte.Age + 1,
        cte.qx,
        cte.qy,
        CAST((vx / (1 + @interestRate)) AS NUMERIC(7, 5)) AS vx
    FROM cte

    WHERE cte.Age < 120

) SELECT COUNT(*) AS NumberOfRows FROM cte OPTION (MAXRECURSION 0);

I am a little bit tired for I spent my afternoon trying to solve this, it's already night, so my question might be taken as low effort, but I really need your help to see where I am making mistakes.

Your problem is the anchor in the CTE returns all 120 rows instead of a single anchor row. Try this code instead, notice the WHERE clause in the CTE SELECT:

DECLARE @gender VARCHAR(10) = 'MALE'
DECLARE @interestRate NUMERIC(7, 5) = 0.0354

;WITH cte AS (
    SELECT
        lt.Age,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Male
            ELSE
                lt.Female
        END AS qx,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Female
            ELSE
                lt.Male
        END AS qy,
        CAST(1.0 AS NUMERIC(7, 5)) AS vx
    FROM LifeTable AS lt
    WHERE Age = 1
    UNION ALL

    SELECT
        cte.Age + 1,
        cte.qx,
        cte.qy,
        CAST((vx / (1 + @interestRate)) AS NUMERIC(7, 5)) AS vx
    FROM cte

    WHERE cte.Age < 120

) SELECT COUNT(*) AS NumberOfRows FROM cte OPTION (MAXRECURSION 0);


How to track only DDL statements executed in MySQL Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements.

I had gone through the below link in MySQL Site. But in the site, it is not mentioned how to enable/disable this log.

http://dev.mysql.com/doc/refman/5.6/en/ddl-log.html

I added below line in my.cnf and restarted MySQL service. MySQL got restarted successfully, but I could not find any DDL log in the below path.

ddl-log=/var/log/ddl.log

Is this feature released or will it be released in future.

Please look at the MySQL Documentation again

A record of metadata operations such as those just described are written to the file ddl_log.log, in the MySQL data directory. This is a binary file; it is not intended to be human-readable, and you should not attempt to modify it in any way.
ddl_log.log is not created until it is actually needed for recording metadata statements, so it is possible for this file not to be present on a MySQL server that is functioning in a completely normal manner.
There are no user-configurable server options or variables associated with this file.

I looked at the Server Variables and there is no ddl_log option.
You can count ALTER TABLE commands from status variables (Com_alter_table).
SUGGESTION
If you are using MySQL 5.6 and should you decide to bite the bullet and use mysqlbinlog and parse the output, you may need to setup mysqlbinlog on an external server, redirecting output to your own log file for DDLs. You will also have to use stop-never. here is what the MySQL Documentation says on stop-never:

This option is used with --read-from-remote-server. It tells mysqlbinlog to remain connected to the server. Otherwise mysqlbinlog exits when the last log file has been transferred from the server. stop-never implies --to-last-log, so only the first log file to transfer need be named on the command line.
--stop-never is commonly used with --raw to make a live binary log backup, but also can be used without --raw to maintain a continuous text display of log events as the server generates them.
This option was added in MySQL 5.6.0.


how to upgrade mongodb 2.2 to 2.4 properly I have a community edition mongodb of 2.2 version and I have read this article: https://docs.mongodb.com/manual/release-notes/2.4-upgrade/

However, I got following errors when I tried to follow the above instruction:

Transaction Check Error:
 file /usr/bin/bsondump from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongo from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongodump from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongoexport from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongofiles from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongoimport from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongooplog from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongoperf from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongorestore from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongostat from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongotop from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64

Error Summary
-------------

System: Fedora 17

You have to go to their repo for downloads that old:
http://downloads-distro.mongodb.org/repo/

Pick your distro (for example, if Ubuntu pick Debian), drill in to the arch (x86/x64) and pick your download.

example:
Ubuntu 64 bit 2.4 download:
http://downloads-distro.mongodb.org/repo/debian-sysvinit/dists/dist/10gen/binary-amd64/mongodb-10gen_2.4.14_amd64.deb

Try to always go for the latest release of any numbered version (the link is for 2.4.14) and avoid .0 versions (such as 2.4.0).


Grouping count by interval of 15 minutes I was trying to write a query where I can group the data by 15 minutes interval.

Example:

I want to count the orders by interval.
So Output should be:

How can this be written?

If you can deal with not counting zeros, you can avoid a calendar table

SELECT
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101'),
    count(*)
FROM
    Transactions
GROUP BY
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101')

gives

2017-07-10 11:30:00.000 2
2017-07-10 11:45:00.000 1
2017-07-10 12:00:00.000 5
2017-07-10 12:15:00.000 1


fetching externally hosted db's table from within the local mysql server Let's say we have a mysql database on localhost with table A and table B.

Is it possible to fetch details from a 3rd party mysql server to which we have login info and which allows external access?

Let's say there are 3 tables in the database we can connect to - table A2, B2 and C2.

How do you fetch the rows from table C2 and make it VIRTUALY accessible (as table C) from within the original mysql server (just like A and B), just like if it was hosted on it?

I normally would not recommend this but here it goes...

If the tables on the remote DB Server are MyISAM, try using the http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html">FEDERATED Stroage Engine. First starters, find out if the FEDERATED Storage Engine is Enabled. This what I get running SHOW ENGINES; in MySQL 5.5.12 for Windows:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

In MySQL 5.5.12 for Windows it is not enabled

So, I added this to my.ini

[mysqld]
federated

I then restarted MySQL on my machine

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.02 sec)

Now, it is running !!!

On your remote server perform this query:

SHOW CREATE TABLE C2\G

This will show you the create table statement for C2.

Now just append the URL of the remote table usign the CONNECTION clause.

According to the MySQL Documentation

For that link

  The format of the connection string is as follows:
  scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
  Where:

  • scheme: A recognized connection protocol. Only mysql is supported as
  the scheme value at this point.

  • user_name: The user name for the connection. This user must have
  been created on the remote server, and must have suitable privileges
  to perform the required actions (SELECT, INSERT, UPDATE, and so forth)
  on the remote table.

  • password: (Optional) The corresponding password for user_name.

  • host_name: The host name or IP address of the remote server.

  • port_num: (Optional) The port number for the remote server. The
  default is 3306.

  • db_name: The name of the database holding the
  remote table.

  • tbl_name: The name of the remote table. The name of the local and
  the remote table do not have to match.

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

Suppose that table C2 in the mydb database looks like this:

CREATE TABLE C2
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
)
ENGINE=MyISAM;

Just append the CONNECTION clause and run it on the Local Server:

use mydb
CREATE TABLE C
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@hostname/mydb/C2';

Finally, in MySQL on your local machine, you should be able to access the C2 table like this:

mysql> SELECT * FROM mydb.C;


SQL import data when both source and destination has data and identity column I am using Import and export data option to import data from one database to another database.
Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.

Now when I import data I get error like Failure inserting into the read-only column

So I enabled enable identity insert check-box and tried to import data. In this case I got error as shown below

Copying to [dbo].[Misc_Data] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_Misc_Data'. Cannot insert duplicate key in object 'dbo.Misc_Data'. The duplicate key value is (1).".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (50)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (50)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Misc_Data" (37) failed with error code 0xC0209029 while processing input "Destination Input" (50). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

I think when i enable identity insert on SQL try to insert rows with values same as in souce table for identity column and gives error.

So how can I import data in this case. When having data in both source and destination and identity column is set as primary key.

EDIT
The query I used to import table except one column(identity column) is shown below

SELECT [Name]
  ,[Status]
  ,[Last_Checked]
  ,[RecievedDate]
  ,[RecptNo]
  ,[Date2]
  ,[processedDate]
  ,[ind_num]
  ,[AcNo]
  ,[returnType]
  ,[sign]
  ,[oldStatus]
  ,[Acno_old]
  ,[Remarks]
  ,[FileName]
  ,[Status_checked]
  ,[Date_Time]
  ,[By]

FROM [dbname].[dbo].[mytable]

In this case i get error like

- Pre-execute (Error)
Messages
Error 0xc0202004: Data Flow Task 1: The number of columns is incorrect.

(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
 (SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task 1: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

Both tables have same structure and same datatypes. Still i am getting error.

Don't just use the whole table. Instead choose the option to write a query.

Then simply write a select statement that doesn't use that Identity Column.

Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the source system and it should have remained in the destination, then this step will not be right for you.

Your package would look something like this


Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance I'm moving from a SQL Server 2008 R2 instance to a SQL Server 2012 instance on a separate box. I want to restore msdb to get all my agent jobs and the like and I'm getting the following error:

  Restore of database 'msdb' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)
  System.Data.SqlClient.SqlError: The backup of the system database on the device
  \10.0.0.3\BackUps\NOFAULT2010_msdb_FULL_20121016_220006.bak cannot be restored because
  it was created by a different version of the server (10.50.1600) than this server
  (11.00.2100).
  (Microsoft.SqlServer.SmoExtended)

I would understand this if I was trying to go from 2012 to 2008 but this is new to me.

Anyway to get this to work? Or if not best way to transfer the agent jobs, users, blazi blah ?

Thanks

If you were trying to restore a user database - this would restore alright. The problem is that this is a system database. The system databases are designed for the version of SQL they were intended to work in. In order for SQL Server 2008 R2 MSDB to work in SQL Server 2012, you'd have to have no features different between the two versions. Make sense?

The Short Answer
To bring your jobs across, you'd script them out from the old server and apply the script on the new one. You can do this as simply as right clicking and scripting each job if you have a few. Or looking into a script with PowerShell or some other approach if you have a ton and don't want to one at a time.

To take the Logins you'd use the Sp_help_revlogin script I reference below and a script to copy server level roles and permissions from the old and copy them to the new..

Basically for 90% of what you'd bring across, I'm pretty sure the answer is "script it out" and then just apply that script on the new server and the logins, the jobs, etc. will all now live in the Master and MSDB databases designed for SQL Server 2012.

The slightly longer answer and a quick discussion on approaches to migration/upgrade

So if you are trying to transfer all of these objects to SQL Server 2012 you have two basic options on your approach.

Do an in place upgrade. Upgrade your instance from SQL Server 2008 R2 to SQL Server 2012..

Pros - you get all that 'stuff' (agent jobs, linked servers, logins, alerts, operators, mail profile, etc.) and you don't have to copy objects around..

Cons - it can be a bit messy, it works fine nowadays and is supported but I am paranoid and like to know for sure that I have success and a quick rollback option (if issues on new server, just revert back to your old server during a migration. With an in place upgrade, it is much more, well, final).

You can start here for an in place upgrade.

Migrate to a new server...

In this case you just flip the pros and cons from above.. The approach isn't that tough or strenuous. It just requires some good planning. Basically you:

On your old server, script out all of the objects you want to move according to the instructions and approach for each object type which can typically be found in Books Online (objects like Jobs, Linked Servers, SQL Agent Alerts, etc.)...
Use a tool like sp_help_revlogin to move your logins across and a script kind of like this to move the login permissions across..
Backup and restore your databases which already contain the in-DB users and permissions(I like doing this over detach attach because it helps preserve that rollback ability but I've seen and done this either way).. Change your compatibility mode if you are planning on supporting the DBs in 2012 mode and have tested them that way. Or keep them in 2008 mode if that was your plan.
Run those scripts for all of the objects like jobs, logins, etc. on the new server that you created above from the old server(good to do most of these after the DBs.. as logins will error on you if their databases that they default to aren't there or a T-SQL step in a job's database isn't there, etc.)

The other nice thing about this approach is you can do a trial run ahead of time during business hours.. Point a test or dev version of the app(s) used on that instance to the 2012 and see what breaks.. Fix it and test out your checklist, your rollback plan, etc. and do that prep for go live night.


Single query to get data from multiple tables in one database I have 36 Microsoft SQL Server VM's, each with a specific database ending in _Sec as the DB name.  There are 2 tables in this database that I need to get data from in a single query: tblConnGroups and tblConnGroupsDet.

tblConnGroups has 3 columns that I need to include in the results

tblConnGroups.ID
tblConnGroups.GroupName
tblConnGroups.Provisional

tblConnGroupsDet has essentially one column that is needed in the results.

tblConnGroupsDet.GroupID
tblConnGroupsDet.DBName

tblConnGroups.ID is a key used in tblConnGroupsDet.GroupID, however in tblConnGroups the ID field has a one-to-many relationship to the GroupID field in tblConnGroupsDet.

The Connection Group Name only appears in the tblConnGroups with its ID, and other information, but the GroupID will be in each row of the tblConnGroupsDet with a member database name.

USE [Name_Sec];
GO
SELECT ID,Groupname,Provisional
from [tblConnGroups]
ORDER BY ID ASC

ID   GroupName       Provisional
514  Name_FY0910     0
844  Name_FY1011     0
1588 NameTest_FY1516 1

USE [Name_Sec];
GO
SELECT GroupID,DBName
FROM [tblConnGroupsDet]
ORDER BY GroupID ASC

GroupID  DBNAME
514      Name_Base
514      Name_FY0910
514      Name_State0910
514      Name_portal
514      Name_FY1011

I tried a couple of different join queries to match the tblConnGroups.ID and tblConnGroupsDet.GroupID and haven't quite figured out how to get the information I want out of these two tables.

Basically I need a query that will give me these results where GroupName will get repeated for each DBName where the ID/GroupID match:

GroupName,DBName,FileSystemPath

I'm hopeful that this is clearer than mud, I don't do a whole lot of DB Admin work, we've been trying to find a DBA, and this is a hat I don't put on very often.

SSMS has a feature called Server Groups, which allows exactly that functionality.

As in the above image, add a new Server Group, then add each target SQL Server to the group.

Then right-click the newly created group, and choose "New Query".  Enter the following into the query window:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + 'USE ' + QUOTENAME(d.name) + ';
SELECT SECDB = ''' + d.name + '''
    , g.*
    , gd.*
FROM dbo.tblConnGroups g
    INNER JOIN dbo.tblConnGroupsDet gd ON g.ID = gd.GroupID
ORDER BY gd.GroupID, gd.DBName;
'
FROM sys.databases d
WHERE d.name LIKE '%_Sec';

EXEC sys.sp_executesql @cmd;

The code above will dynamically execute your query against any/all databases named like *_Sec on all SQL Servers in the group.


Oracle AWR shows missing stats for SQL ID as top elapsed statement Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared pool got flushed or what else can cause this?

I could think of eviction from shared pool, like your guess, or the statement running with statistics_level=basic. The statistics_level can be dynamically set on a per-session basis.

  Setting the STATISTICS_LEVEL parameter
  to BASIC disables the collection of
  many of the important statistics
  required by Oracle Database features
  and functionality, including:

*

  Automatic Workload Repository (AWR) Snapshots


Dealing with Difficult DBAs At one point, test database deployments within my department were handled directly by the database developers. The typical process was that the developer would unit test their changes in the development environment, then:

1) Migrate their changes to the test environment.

2) Send an email to the testers to let them know the changes were available in the test environment.

This was a solid process, which typically took only a few minutes to complete, and was nearly always completed successfully.

However, at one point the head DBA for my company took away all database owner permissions from the database developers on the test environment so that test database deployments are now handled only by DBAs. Over time the process morphed into the following:

1) Database developers complete a 15 page form to list the changes they need to migrate (for a typical deployment, most sections are blank, but the applicable sections are scattered throughout the form)

2) The database developer completes a 1 page script header for any migration scripts involved in the plan

3) The database developer references a deployment project containing all of the objects to be deployed in the plan

4) The database developer sends an email to the department head requesting the deployment

5) The department head approves the plan, scripts and project

6) The department head forwards the email to the primary DBA for the database

7) The DBA either approves the migration, or rejects it (typical rejections are due to a section of the 15 page form or 1 page script template not being filled in, or the 15 page form not being the most recent version of that form)

8) The developer completes a 2 page form email to the testers, letting them know the changes that are scheduled to be migrated

9) The DBA sends an email to the developer and department head letting them know the deployment was complete. Or alternatively, that it could not be completed.

10) The developer replies to the 2 page form email, letting the testers know the deployment was completed.

So, a process that once involved a single person, took less than five minutes to complete and had a near-zero failure rate now involves a minimum of three people and takes a minimum of twenty minutes to complete. In addition, deployments submitted after 2:00 PM often cross over into the next day based on DBA and department head availability.

Along similar lines, the head DBA has published a 50 page standards document. The majority of the document is focused on what he feels things should be named, the use of acronyms, underscores, capitalization and so forth. However, it also includes some time-consuming non-functional requirements. For example, adding database descriptions to all parameters for all objects. Or requiring all constants used in the database to be drawn from a numerically keyed constants table. Which means that execution plans can't be optimized based on the values for the constants. The 50 page standards document also includes a number of prefix/suffix naming conventions that are now considered industry anti-patterns.

The head DBA often refuses to deploy anything that does not conform to the latest version of this document, which changes on average every two months and often reverses previous positions. Ostensibly this is to 'raise the standards used in the department' but in reality quality has fallen off considerably since he implemented these policies. The time that used to be spent improving the existing code base and adding new features has been diverted to renaming database objects and regression testing the changes. The database developers in the department estimate they now spend 40% off their time dealing with standards and processes put into place by the head DBA. All of which have a negative or no impact on the performance and stability of the applications supported by the databases.

Has anyone out there had luck dealing with inefficient DBAs? Or alternatively, have any similar stories they'd like to share?

The DBA StackExchange was made to address technical and strategic aspects of DBMS products and deployment of such into infrastructures. When it comes to dealing with difficult DBAs, there is no black-and-white answer to offer. In your particular case, I will make an exception.
I answered two interesting posts four(4) years ago

May 02, 2011 : How could DBAs be more 'programmer friendly'?
May 24, 2011 : Why is Database Administration so hard?

I discussed 4 ways people have become DBAs. Somewhere, over the course of time, your head DBA has lost touch with what Developers and Developer/DBAs are doing and their ever-changing needs. In fact, times have changed and the developer technologies of today may no longer be in the head DBA's wheelhouse.
You need to comply with the standards put in place until productivity slows down considerably. OH, I AM SORRY, THAT'S WHATS HAPPENING NOW !!!
Guess what? You must get a third-party involved (his boss or his boss's boss) and thoroughly prove that the standards are interfering with product development. In other words, your Agile Development has become Fragile Development.
I recently saw a picture in LinkedIn that might reflect what you are going through

Have all your ducks in a row. Do all you homework. Have all your facts and figures down in writing. Think of it in terms of Six Sigma: You want to streamline process development while eliminating wasted efforts.
FINAL WORDS
Mind you, you should never approach this with a complaining spirit. You need to present this situation in such a way that the you want the head DBA on your side instead of off your back. If the head DBA is in your company, he/she brings something to the table in the eyes of your company. You don't want to diminish the head DBA's dignity. You must find a way to appeal to it. Notwithstanding, you must also let the current productivity speak for you and the head DBA (without casting blame). In the end, you will find the solution that creates a team atmosphere which includes the head DBA or the company may step in with standards even harder to deal with. Now, GO DO YOUR HOMEWORK ...
Please let us know how it turns out !!!


MySQL reuse select aliases I currently have a query where I'm doing two subqueries to get X, Y data:
SELECT
  t.series AS week,
  ( ... ) X,
  ( ..., AND ... ) Y,
  ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp t

Y is kind of subset of X, since I apply just an additional condition to the existing ones, if X is:
SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY

Then Y has an additional AND condition:
SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY
AND t1.some_state = 'x state'

And for the value of X I need to take those two results - X and Y and do some calculation. Since I can't use the aliases, I have to use a subquery, right? But in that case it seems too much.
Is there a way to reuse those subqueries? It seems to be too much of the same.
I'm using MySQL 5.6 so I'm not able to use CTEs :(
PS: series_tmp comes from this wonderful idea (thanks to them).

For that you can use user defined variables
SELECT
    t.series AS week,
    @x:=(SELECT
            COUNT(*)
        FROM
            t1
                INNER JOIN
            t2 ON t2.id = t1.another_id
        WHERE
            t2.something = 1
                AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY) X,
    @y:=(SELECT
            COUNT(*)
        FROM
            t1
                INNER JOIN
            t2 ON t2.id = t1.another_id
        WHERE
            t2.something = 1
                AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY
                AND t1.some_state = 'x state') Y,
    ROUND((@x * 100) / @y, 2) Z
FROM
    series_tmp t

If you want to use your aliases, MySQL allows you only to use those in ORDER BY , GROUP BY and HAVING
As exampple you can use ORDER BY X DESC,Y ASC or HAVING X > 0.5


Oracle Client sessions keep disconnecting We recently moved our Oracle server from a remote location to a local server and now the Oracle clients keep disconnecting.

It does not seem to be a timeout issue because it happens while we are actively using it. We are using Oracle SQL Developer, PL/SQL Developer, and Toad.  We are running Oracle 12 Enterprise on a VM.

We do not see any errors - our sessions just drop. Our server is running on a VM on Redhat 7. We are running the clients on Windows.  Often when we run a query we have to wait for it to re-initialize before the query is executed. Hence we are assuming that since it has to re-initialize that our session dropped.

When we write a query the client (Toad, PL/SQL Developer, etc) has to reconnect in order to execute the query.  I would understand if we stepped away but we are actively writing queries and executing them and then the client disconnects and has to reconnect in order to execute.

How can we solve or debug this issue?

Check if sqlnet_expire_time is set in any of the sqlnet.ora files on the machine that hosts the DB, using some variant of

find / -name sqlnet.ora -exec grep -i sqlnet_expire_time {} \;

If it's not set in any of them then you can quickly rule out dead connection detection.  If it is set then you have DCD enabled and it's likely that which is killing your connections.

You are using three apps - my bet is that you're in one of them for more than the DCD time, so the other apps' connections are being killed, then when you switch to another app you find its connection has gone.  This is because they each have their own connection, and it doesn't matter how busy connection A is - if connection B is idle for the DCD time it will be killed.

If DCD is ruled out then the next place to check will be the firewall.  This needs to be SQL*Net aware; it's not just port 1521 (or whatever you've configured) that needs to be open, but the firewall needs to check the comms for the random port number the server returns to the client and open that as well - if it doesn't do that then the firewall will kill the "unauthorised/unknown" link, thus breaking the connection.


Which databases can have row and column level security based upon Active Directory? I have various database types, Oracle 11g, MySQL 5, Sql Server 2012.  I have direct access.  I can have other databases if I need them such as PostgreSQL.

What I'm doing is protecting data at the database and table level as opposed to the application level though I there will be some mixing.

What I'm hoping to do is control all the security from Active Directory.  I don't want to do security in MySQL, security in Oracle, etc.  That is a lot of duplicate entry.

I will have a webpage send credentials to the database engine to determine which bits of data the user can see.

Thanks for ideas.

I can't speak for any of the others but as for as SQL Server goes there would be some setup involved but after that you can control security at some level from active directory.

What you would do is create a series of roles (server and database level) and grant them the permissions needed.  Then you would create AD groups and create logins and users based on them.  Then assign those logins and users to the various roles.  Once that is done you can control who has what access by adding and removing individuals from the various AD groups.

There are options for column level permissions and row level permissions can be handled by views.

Now if new objects are created in the database that require additional security then that will have to be managed within SQL server by granting those permissions to the appropriate roles.


Can SQL Profiler filter data columns only for one event? I am using SQL Profiler in MS SQL Server. I can filter data columns by clicking 'Column Filters'. But this filter applies to all events. I want to filter login only for 2 events. Like, audit login and logout only for specified login, but all other events for all logins. Is it possible to do that, and how if yes.
Thanks for your time!

Unfortunately, this can't be done in the trace definition. Each filter defined executes sp_trace_setfilter filter which groups filters by column, but applies all the filters as a whole...

In the trace definition snippet below it ends up grouped like this:

(DatabaseId = 5 OR DatabaseID = 6) AND (DatabaseId <> 1 AND DatabaseID <> 0) AND (Login like User1 OR Login like User2)

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

/******Filter by database ID include*****/
SET @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter  -- 3,0,0 = ColumnID, logical operator (and/or), comparison operator (0 = EQUAL, 1 = NOT EQUAL)

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

/******Filter by database ID exclude*****/
SET @intfilter = 1
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

set @intfilter = 0
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

/******Filter by database Login LIKE*****/
exec sp_trace_setfilter @TraceID, 11, 0, 6, N'user1' -- 11,0,6 = ColumnID, logical operator (and/or), comparison operator (6 = LIKE)
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'user2'

For real-time filtering you'd need to use something like the Save to table option and filter a query against the output there.


LOG all SQL Server queries with their durations Is there a way to log all Microsoft SQL Server queries executed on a certain server, with their respective durations?

I'd like to be able to analyze all queries will a tool like what PGBADGER does for PostgreSQL. While we're at it: do you know of any such tools?

What is your actual goal? Are you trying to track down a troublesome query, performance tune your whole environment, or is this just out of curiosity?
Ultimately, I wouldn't recommend trying to track and log all queries. I suspect you only really care about poorly performing queries which are an actual business impact.
On most production servers, you will have far more queries that either run quickly enough that even a 10-20% performance gain is barely noticeable, or are already optimized well enough to make any performance tuning poor use of your time.
So instead I'd suggest targeting your performance tuning to queries that actually need it.
In any case, you have a few options:
Query Store
If you are running SQL 2016 or higher, the Query Store is my preferred method. It is very low overhead and provides some neat methods to track query performance over time. Books Online has a good article on using it here. Note that it needs to be enabled per-database, and will only capture information once it has been enabled.
DMVs
sys.dm_exec_query_stats stores aggregated (by query plan) stats around query execution. This can be useful for a high level view of what queries are consuming the most resources on a server. You will not get individual execution statistics (unless it's only run once), so it's not really suitable for identifying "one off" performance issues with normally well behaved queries.
It doesn't include the statement text, but you can get this using the sys.dm_exec_sql_text DMF. As an example:
select top 100
    qt.text
    , qs.execution_count
    , qs.total_worker_time
    , qs.total_physical_reads
    , qs.total_logical_reads
    , qs.total_physical_writes
    , qs.total_logical_writes
    , qs.creation_time
    , qs.last_execution_time
from
    sys.dm_exec_query_stats as qs
cross apply
    sys.dm_exec_sql_text(qs.sql_handle) as qt
order by
    total_worker_time desc

Extended Events
If you are really set on capturing every single query, your best bet will be to use XEs. Understand that there will be a significant performance overhead if you go down this route (especially if you're capturing things like the callstack, or doing something crazy like filtering by sql text with a predicate), and it's not something I'd recommend doing by any means.
If you set something like this up, I would suggest being extremely careful and not allowing it to run without monitoring its impact.


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

For example my graph will include

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

......

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

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

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

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

This is the final result that I need from this query

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

Here is what I think you want,

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

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

What we're doing is,

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

Result,

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


SSMS: possible to script 'file per object' into categorised folders like in object explorer? e.g. if you run the 'generate scripts' function (by right clicking on a db in object explorer) to script out an entire database, it creates all the files in one folder and annoyingly names them as [schema.objectname].[objecttype].sql

(at least if it were type.name it might be a little more readable)

Fine but I would rather mirror the object explorer convention of having Tables, Views, etc on a per folder basis containing only those object types and the file having exactly the same name as the object

Is there a simple way to achieve this?

You could import the database definition in SSDT using the "Schema Compare" tool.

Simply create an empty database project, right click and select "Schema compare". Select the project as target and the live database as source. Click "Compare" and then "Update": SSDT will script all objects and add them to your project.

SSDT by default creates a folder for each schema and in each folder creates separate folders for tables, stored procedures, views and so on.


You can use a numbers table for this. Here's a very simple way to create one that will support 5 year+ ranges:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);

INSERT dbo.Numbers SELECT ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects;

Now, given the sample data:

CREATE TABLE #CN(ID INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME);

INSERT #CN(ID, StartDate, EndDate)
SELECT           1417,'2015-09-30 09:45','2015-10-10 09:45'
UNION ALL SELECT 973 ,'2015-09-14 11:03','2015-09-24 11:03';

We can join against the numbers table like this in order to produce the result you're looking for:

;WITH cn AS
(
  SELECT ID,
         s = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', StartDate)),
         e = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', EndDate))
  FROM #CN
)
SELECT cn.ID, DATEADD(DAY, n.Number-1, cn.s)
  FROM cn
  INNER JOIN dbo.Numbers AS n
  ON n.Number <= DATEDIFF(DAY, cn.s, cn.e) + 1;

Clean up your #temp table:

DROP TABLE #CN;


Are MySQL InnoDB transactions serializable? I am confused whether or not MySQL transactions are serializable and whether or not read-anomalies are guarded against?

For example, what if I SELECT a value from a row, then UPDATE that row by incrementing the value. There is an opportunity for another query to UPDATE the row which is being read, leading to an improper update.

However, I was under the impression that even if I wrap these two statements in a transaction, that I am not guaranteed to be free of race conditions. See this StackOverflow thread. So to prevent this I used UPDATE with COALESCE which is a single query and thus guaranteed to be atomic. Was this assumption correct? Or would a transaction have worked here to guarantee no race conditions?

I see from the MySQL 5.7 documentation  on isolation levels that transactions are by default REPEATABLE_READ rather than SERIALIZABLE. What if I had set the transaction level to SERIALIZABLE? I attempted to read the documentation on REPEATABLE_READ but it did not increase my understanding with regards to this issue.

Per the MySQL documentation, SERIALIZABLE is:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Locking rows FOR SHARE is not equivalent to a SERIALIZABLE transaction because it doesn't protect against rows being inserted by racing transactions that could have been returned by a SELECT statement.

The PostgreSQL docs on SERIALIZABLE provide an example case where this will occur. In your specific case, I believe MySQL's SERIALIZED isolation level will not protect you because two competing transactions can both lock the row FOR SHARE, then both can issue UPDATEs incrementing the value, one transaction will go through, the next will wait for the first to commit, but then go through. You will get a net of one increment instead of two.


Partial restore to a different database I have a database with 3 filegroups: PRIMARY, AP, and GL.

I did a full database backup. I did a partial restore of just the PRIMARY and AP files successfully using this command:

-- Partial Backup (Primary & AP) to same database (works)
RESTORE DATABASE TestPartial
    FILE = N'TestPartial',
    FILE = N'TestPartialAP'
    FROM DISK = N'J:\SQL\MSSQL10_50.SGA\MSSQL\Backup\TestPartial.bak'
    WITH
        FILE = 1, NOUNLOAD, REPLACE, PARTIAL, STATS = 10

Is it possible to do this same partial restore, but to a different database? I have a database called TestPartial1, which I am trying to partially restore into. However, I'm getting the following error:

The backup set holds a backup of a database other than the existing 'TestPartial1' database.

Here is my code:

-- Partial Backup (Primary & AP) to a different database (doesn't work)
RESTORE DATABASE TestPartial1
    FILE = N'TestPartial',
    FILE = N'TestPartialAP'
    FROM DISK = N'J:\SQL\MSSQL10_50.SGA\MSSQL\Backup\TestPartial.bak'
    WITH
        FILE = 1, NOUNLOAD, REPLACE, PARTIAL, STATS = 10,
        MOVE N'TestPartial' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1.mdf',
        MOVE N'TestPartial_log' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1_log.ldf',
        MOVE N'TestPartialAP' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1AP.ndf'

Is it possible to do this same partial restore, but to a different
  database?

Short answer I'm afraid, no.

Best bet is to do a partial restore to a new database then copy the tables and data to your target with:

SSIS Transfer SQL Objects Task. If this is a one off just use the import/export wizard in SSMS.
Use an INSERT SELECT script
If the tables are large, consider BULK INSERT.


SQL Server Self Signed Cert - no private key? I recently turn on SSL encryption using SQL server self-signed cert then I start to ponder more about

1) encryption algorithm, authentication method

It seems that the encryption/hashing algorithm used depends on the SChannel negotiation between the client/server's OS. - fine.

2) key length of the self-signed cert.

I tried query this

select  name, pvt_key_encryption_type_desc from sys.certificates

##MS_SQLResourceSigningCertificate##    NO_PRIVATE_KEY
##MS_SQLReplicationSigningCertificate## NO_PRIVATE_KEY
##MS_SQLAuthenticatorCertificate##  NO_PRIVATE_KEY
##MS_AgentSigningCertificate##  NO_PRIVATE_KEY
##MS_PolicySigningCertificate## NO_PRIVATE_KEY
##MS_SmoExtendedSigningCertificate##    NO_PRIVATE_KEY
##MS_SchemaSigningCertificate990F36EF1B3577FE5687C7465F0A5135DE9E6834## NO_PRIVATE_KEY

q1) can I check if the cert use for SSL encryption is the MS_SQLAuthenticatorCertifcate ?

q2) How could a SSL encryption / CERT work with "NO PRIVATE KEY"  ? In all my understanding, a cert/asymmetric encryption will involved a PUBLIC key (provided in the cert) for encryption, and the owner of the cert will have its PRIVATE key for decryption ?

Why is this not the case here ?

This is too big for a comment on Sean's answer so let me add to it.

  q1) can I check if the cert use for SSL encryption is the
  MS_SQLAuthenticatorCertifcate ?

According to the Principals (Database Engine) doc
the ##MS_SQLAuthenticatorCertificate## cert is used for an internal certificate-based login.

The self-signed certificate generated during instance start-up is not exposed by a DMV, at least as far as I know. EDIT: Also, Sean mentioned in his answer this certificate is resident only in the SQL Server process memory space and is removed upon SQL Server shutdown for security reasons.

  q2) How could a SSL encryption / CERT work with "NO PRIVATE KEY" ? In
  all my understanding, a cert/asymmetric encryption will involved a
  PUBLIC key (provided in the cert) for encryption, and the owner of the
  cert will have its PRIVATE key for decryption ?

The actual SQL Server self-signed certificate is an X509 certificate with a public/private key pair. However, note that TLS uses a symmetric key (same key used for both encryption and decryption) to encrypt/decrypt messages over the wire for performance reasons. This session-specific key is generated during the TLS handshake and the self-signed cert (or provisioned one) is used to generate and protect the secrets exchanged, not to encrypt the subsequent messages.
This Microsoft doc (https://technet.microsoft.com/en-us/library/dn786441(v=ws.11).aspx) describes how the Transport Layer Security (TLS) protocol works and provides links to the IETF RFCs for TLS 1.0, TLS 1.1, and TLS 1.2.

You can find more details of the actual negotiated TLS protocol and cypher from this Extended Event trace but this requires SQL Server 2016 SP1 or later:

CREATE EVENT SESSION [tls] ON SERVER
ADD EVENT sqlsni.trace(
    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
ADD TARGET package0.event_file(SET filename=N'tls_trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Below is an example of the event text field using a self-signed certificate from the trace against SQL Server 2017

SNISecurity Handshake Handshake succeeded. Protocol: TLS1.2 (1024), Cipher: AES 256 (26128), Cipher Strength: 256, Hash: SHA 384 (32781), Hash Strength: 0, PeerAddr: ::1


Can't auth admin db on the replica set have one problem with a replica set

There is an admin user with root permissions
It's fully working on config servers, but when I trying to auth with it on any RS - got:

2017-09-18T09:01:53.755-0400 E QUERY    Error: 18 Authentication failed.
at DB._authOrThrow (src/mongo/shell/db.js:1271:32)
at (auth):6:8
at (auth):7:2 at src/mongo/shell/db.js:1271
exception: login failed

User was added on config servers, after all Replica Set was deployed, maybe that is the problem?

Yes. When you "try" to authenticate directly to any replica set at the cluster, "local" admin database is used (admin database at the replica set).

You need to create that admin user locally. IF there are NO users inserted to your RS's admin database, "localhost exception" is still valid and you should not have any problem login without authentication (no -u and -p parameters on mongo -command) and create that admin user. After first user is created to RS, localhost exception is gone and you need authenticate. So, first created user MUST be admin with root rights.


mysql off-site replication ( file only ) I am new to mysql, I have extensive experience with MS Access and some with Tivoli. I have tried to search the net for my solution but there is such a massive quantity of results I can't find a solution. My question is a two part.

What I need to do is to have mysql operate two database files, the first would be a normal setup, the second would not. I need the second file to be on a remote file system, possibly on my google drive. I would somehow need to create the db locally then xfer it to the gdrive, then have mysql connect to the shared url.
Part two: mysql will need to have full two replication between the two db files. I want to be able to delete records on the gdrive file. I know how to do this in Access but I have no idea how to do this in mysql. The server is behind several NAT's and can only make outbound connections, I cannot change this. I will be using Java for input/reading/maintenance. On the server side Java will work normally. On the gdrive side, will the server be able to handle java making deletions directly?

Can this be done? Any thoughts and comments are welcome.

It might work.  But you probably need to have a separate instance of mysqld running on your machine, using the cloud "drive" as the directory for holding the mysql files.

Assuming you now have two instances of mysqld running on the same machine (your machine), they would need to be segregated -- either by being in different VMs, or by using different "ports".  (3306 is the default port; some use 3307 or 4306.)

Then set up replication.  I would make your mysql on your machine with 3306 be the Master and the "drive" instance be the Slave.

If you try this, please report back on whether it worked or what issues you had.


"Lost connection to MySQL server during query" error I have MySQL 5.5.20 and this table:

mysql> desc country;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id             | int(255)    | NO   | PRI | NULL    |       |
| iso            | varchar(2)  | NO   |     | NULL    |       |
| name           | varchar(80) | NO   |     | NULL    |       |
| printable_name | varchar(80) | NO   |     | NULL    |       |
| iso3           | varchar(3)  | YES  |     | NULL    |       |
| numcode        | smallint(6) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

If I run a query like this

SELECT country.ID, country.ISO, country.NAME,
       country.PRINTABLE_NAME, country.ISO3, country.NUMCODE
FROM country;

It returns:

ERROR 2013 (HY000): Lost connection to MySQL server during query

If I change the order of the columns (ISO3 before PRINTABLE_NAME for example) like this:

SELECT country.ID, country.ISO, country.NAME,
       country.PRINTABLE_NAME,  country.NUMCODE, country.ISO3
FROM country;

It works fine!

Or if I rewrite the query using lower-case letters for columns, it works as well.

This issue appears from time to time (about once a month) and the only solution to fix it is to restart MySQL!
i have checked error log and nothing added to error log upon this error!!
I am really confused and don't know why this happens! Any ideas on how this could be fixed?

I am running MySQL 5.7.26 on Windows and when doing just a simple query from the MySQL command line client I get that same error you describing. I found out it only occurs when the query length is exact 64 characters! Just by adding a space fixed the problem for me.
It doesn't matter if it's a select or insert/delete/update query.
I have run 66.000 queries on my server and only the queries with exact 64 characters failed.
I checked the database for corruption. But I could also reproduce same problem when making a new database and table on that server.
I could only reproduce this problem on that production server. I created a new server with same version with exact same settings as a VMWare machine but could not recreate. Used the same MySQL client.


Reuse a complex non-deterministic expression in SELECT and WHERE clauses I have a query like this:

-- This is inefficient, and may even be wrong if expression is nondeterministic
SELECT TOP 10 team_name,score_points=<big expression>
  FROM teams
 WHERE <big expression> >= 100
 ORDER BY 2 DESC

-- This gives error
SELECT TOP 10 team_name,score_points=<big expression>
  FROM teams
 WHERE score_points >= 100
 ORDER BY 2 DESC

What I want to do is clear: list the teams with the highest score_points. List all teams with at least 100 score_points, but no more than 10 teams.

If I repeat the expression, it will be very ugly and inefficient. Plus, it will give wrong results when my expression is not deterministic.
Using the name score_points in the WHERE clause gives an error.
Is there an efficient way to have the results of my expression both returned as a column in my query, and used as a filter?

Please don't tell me about computed columns, I know about them but I can't use them here.

This can't help with the determinism issue, but CROSS APPLY is a good tool for big expressions that need to be reused:

SELECT TOP 10 team_name, CxA.Score_Points
FROM teams t
CROSS APPLY (SELECT Score_Points = <big Expression, refer to t>) CxA
 WHERE CxA.Score_Points >= 100
 ORDER BY CxA.Score_Points DESC

CROSS APPLY expressions get calculated for every row and can be referred to in any part of the query.


Storing the correct answer to check against I'm designing a database for a polling app. Users get to answer a bunch of questions and we store them.

The relevant ERD part

I want to store the correct answer for each question so we can check whether they answered the question right or not.

The questions can have different "types":

string
number
single_response
multiple_response

How should I store the correct answer?

Adding correct_answer to both questions and options

adding just a correct_answer field to questions wouldn't work in case the question has type multiple_reponse. I've thought about adding correct_answer to both questions and options where questions would have a string or number with the correct answer and options would simply get a true or false. We'd check if all options with correct_answer set to true are selected or not and if an option with correct_answer set to false is selected

Creating a new table called correct_answer

Add 2 columns:

correct_answer
options_id

And then check if either is set. We'd have to create a new correct_answer for each option.

Adding correct_answer to questions

We could use type to determine how to read correct_answer and then either check correct_answer directly or explode() a comma delimited option_id value to check the answer

I'd like to know which option is the best practice or if anyone has a better idea.

My entire sql: fiddle

How about adding 2 Columns in Question_Option entity, one as IsCorrectAnswer and HasMultiAnswers ? You can letter check the counts of answers where HasMultiAnswers is set to true.