Prime numbers in a given range Recently, I was given a task to print all the prime numbers (1-100). I failed drastically there. My Code:

Create Procedure PrintPrimeNumbers
@startnum int,
@endnum int
AS
BEGIN
Declare @a INT;
Declare @i INT = 1
(
Select a = @startnum / 2;
WHILE @i<@a
BEGIN
@startnum%(@a-@i)
i=i+1;
)
END

Though I ended up without completing it, I wonder is it feasible to do such programs on Database (SQL Server 2008 R2).

If yes, how it may end.

By far the quickest and easiest way to print "all the prime numbers (1-100)" is to fully embrace the fact that prime numbers are a known, finite, and unchanging set of values ("known" and "finite" within a particular range, of course). At this small of a scale, why waste CPU each time to calculate a bunch of values that have been known for a very long time, and take up hardly any memory to store?

SELECT tmp.[Prime]
FROM   (VALUES (2), (3), (5), (7), (11), (13),
        (17), (19), (23), (29), (31), (37), (41),
        (43), (47), (53), (59), (61), (67), (71),
        (73), (79), (83), (89), (97)) tmp(Prime)

Of course, if you do need to calculate the prime numbers between 1 and 100, the following is fairly efficient:

;WITH base AS
(
    SELECT tmp.dummy, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [num]
    FROM   (VALUES (0), (0), (0), (0), (0), (0), (0)) tmp(dummy)
), nums AS
(
    SELECT  (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) * 2) + 1 AS [num]
    FROM        base b1
    CROSS JOIN  base b2
), divs AS
(
    SELECT  [num]
    FROM        base b3
    WHERE   b3.[num] > 4
    AND     b3.[num] % 2 <> 0
    AND     b3.[num] % 3 <> 0
)
SELECT  given.[num] AS [Prime]
FROM        (VALUES (2), (3)) given(num)
UNION ALL
SELECT  n.[num] AS [Prime]
FROM        nums n
WHERE   n.[num] % 3 <> 0
AND     NOT EXISTS (SELECT *
                    FROM divs d
                    WHERE d.[num] <> n.[num]
                    AND n.[num] % d.[num] = 0
                    );

This query only tests odd numbers as even numbers won't be prime anyway. It is also specific to the range of 1 - 100.

Now, if you need a dynamic range (similar to what is shown in the example code in the question), then the following is an adaptation of the query above that is still rather efficient (it calculated the range of 1 - 100,000 -- 9592 entries -- in just under 1 second):

DECLARE  @RangeStart INT = 1,
         @RangeEnd INT = 100000;
DECLARE  @HowMany INT = CEILING((@RangeEnd - @RangeStart + 1) / 2.0);

;WITH frst AS
(
    SELECT  tmp.thing1
    FROM        (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) tmp(thing1)
), scnd AS
(
    SELECT  0 AS [thing2]
    FROM        frst t1
    CROSS JOIN frst t2
    CROSS JOIN frst t3
), base AS
(
    SELECT  TOP( CONVERT( INT, CEILING(SQRT(@RangeEnd)) ) )
            ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [num]
    FROM        scnd s1
    CROSS JOIN  scnd s2
), nums AS
(
    SELECT  TOP (@HowMany)
            (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) * 2) +
                (@RangeStart - 1 - (@RangeStart%2)) AS [num]
    FROM        base b1
    CROSS JOIN  base b2
), divs AS
(
    SELECT  [num]
    FROM        base b3
    WHERE   b3.[num] > 4
    AND     b3.[num] % 2 <> 0
    AND     b3.[num] % 3 <> 0
)
SELECT  given.[num] AS [Prime]
FROM        (VALUES (2), (3)) given(num)
WHERE   given.[num] >= @RangeStart
UNION ALL
SELECT  n.[num] AS [Prime]
FROM        nums n
WHERE   n.[num] BETWEEN 5 AND @RangeEnd
AND     n.[num] % 3 <> 0
AND     NOT EXISTS (SELECT *
                    FROM divs d
                    WHERE d.[num] <> n.[num]
                    AND n.[num] % d.[num] = 0
                    );

My testing (using SET STATISTICS TIME, IO ON; ) shows that this query performs better than the other two answers given (so far):

RANGE: 1 - 100

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon      0                 0                   0
Dan        396                 0                   0
Martin     394                 0                   1

RANGE: 1 - 10,000

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon        0                   47                170
Dan        77015                 2547               2559
Martin       n/a

RANGE: 1 - 100,000

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon            0                 984                996
Dan        3,365,469             195,766            196,650
Martin           n/a

RANGE: 99,900 - 100,000

NOTE: In order to run this test I had to fix a bug in Dan's code -- @startnum was not factored into the query so it always started at 1. I replaced the Dividend.num <= @endnum line with Dividend.num BETWEEN @startnum AND @endnum.

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon       0                   0                   1
Dan           0                 157                 158
Martin      n/a

RANGE: 1 - 100,000 (partial re-test)

After fixing Dan's query for the 99,900 - 100,000 test, I noticed that there were no more logical reads listed. So I retested this range with that fix still applied and found that the logical reads were again gone and the times were slightly better (and yes, the same number of rows were returned).

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Dan                0             179,594            180,096


How do I do data entry with Foreign Key integer IDs? This seems like a basic question to me but I'm having trouble finding practical solutions.

I have two tables, one for video game consoles, and another for the games themselves.

Consoles
+ -- + --------- +
| ID | Name      |
+ -- + --------- +
| 0  | Console X |
+ -- + --------- +
| 1  | Console Y |
+ -- + --------- +

Games
+ -- + ------ + ------- +
| ID | Name   | Console |
+ -- + ------ + ------- +
| 0  | Game X | ?       |
+ -- + ------ + ------- +
| 1  | Game Y | ?       |
+ -- + ------ + ------- +
| 2  | Game Z | ?       |
+ -- + ------ + ------- +

My question is how do I do the data entry from scratch for the Game.Console column here? As a human, it'd be easy to write the Console.Name there, but obviously that's not recommended to use as a Foreign Key ID. But if I use the integer Console.ID as the Foreign Key, for 700 games and 30 consoles, it seems like I'd have to manually cross-check the table as I enter in all those numbers.

How do I do the task of data entry for an association like this? Is there a better solution than manually entering numbers? I'd think if I was using an IDE there might be some feature for a drop-down of foreign rows to fill in a foreign key value, but I haven't found anything.

Additional Info: I'm not expecting user input in this application, just my own personal data, so I'm not worried about validation, just how to alleviate tedium in the initial data entry. Entering cross-referenced integers seems like the job for a computer or tool.

I think the easiest solution for you would be to create a stored procedure that took @GameName and @ConsoleName.

CREATE PROCEDURE InsertGame @GameName VARCHAR(50), @ConsoleName VARCHAR(50)
AS
BEGIN
DECLARE @ConsoleID INT

SELECT @ConsoleID = ConsoleID FROM Console WHERE ConsoleName = @ConsoleName

IF @ConsoleID IS NULL
BEGIN
INSERT Console (Name) SELECT @ConsoleName
SELECT @ConsoleID = scope_identity()
END

INSERT Game (Name, Console) SELECT @GameName, @ConsoleID

END

If you entered in a @ConsoleName that didn't already exist in the Console table, it would INSERT it there. Next the stored procedure would look up the ID for the console name you provided it, and INSERT a record into the Game table with @GameName and the @ConsoleID.

Then you'd call that stored procedure for each Game you were planning to enter:

EXEC InsertGame 'Grand Theft Auto 5', 'PS4'
EXEC InsertGame 'Grand Theft Auto 5', 'PC'
EXEC InsertGame 'Final Fantasy XV', 'PS4'
EXEC InsertGame 'Overwatch', 'PC'
EXEC InsertGame 'Civilization 5', 'PC'

And so on until you've inserted all of your games.

Please note that the exact syntax I've used is for the database platform I am most familiar with, but the underlying logic should be usable in any database platform.

Also, as I write this I am struck by the idea that what you really want is to support a many-to-many relationship between game titles and platforms, since a platform can have many games and a game can be available on multiple platforms. That would take a little more work, but would be helpful for efficient storage and would give you the ability to easily determine which platform(s) a given game was available for without doing a text-match.

So, at a high level you'd need another table. The existing Game table would not have a ConsoleID column. Instead there would be a new table, likely called Console_Game, that would include only ConsoleID and GameID as keys. Then the stored procedure would need to check for both the existence of ConsoleName, and the existence of GameName. Inserting into both tables as necessary, and then finally write a record to Console_Game to show that a given game is available on a given console.

This will be more work upfront, but it is a more scalable solution than what you currently have. If this is for a job and not a hobby you should probably go that route.


Restored database from .bkp file in SQL Server 2014 generates some strange errors I have restored the well-known Northwind database and executed a trivial query:

Notice the strange error "Invalid object name..." although the query has been executed successfully and both tables can be seen in Object Explorer.

Is there an explanation for this?

The errors have nothing to do with the fact that the database is restored (in fact they aren't even errors, just intellisense being flaky).

As you can see in the dropdown top left and the status bar (in yellow) you are executing your query against the master database.

Either change your database in the dropdown or execute a USE mydatabasename;.

As noted by Aaron Ctrl-shift-R refreshes the intellisense but that could take a while before taking effect.


Difference between chained and migrated rows What is the difference between chained and migrated rows? Also what query should be used to display these rows? (I'm using Oracle 11gR2 database)

Chained and Migrated Rows are explained in the Logical Storage Structures of the Concepts Guide.
A migrated row would be a row who's column data was completely transferred from one block to another due to an update. The original block would essentially only have a "forwarding address" stored for that row.
A chained row would have parts of its column data in multiple blocks. The original block would contain both actual column data and a forwarding address for the rest of it. (You can get rows chained to more than two blocks.)
Both are implemented the same way deep down, so they're really two aspects of the same thing.
Also note that for tables with more than 255 columns, all rows are technically chained - one "row piece" can only contain 255 column values. The chaining can happen in the same block, or with other blocks depending on space availability (and isn't particularly "bad" if all the data ends up in the same block).
The only way, as far as I know, to get accurate data on row chaining is to use:
ANALYZE TABLE your_table [partition (your_part)] LIST CHAINED ROWS

See Listing Chained Rows of Tables and Clusters.
This is potentially expensive, the whole table needs to be scanned. Statistics gathering doesn't fill the CHAIN_CNT column of the dba_tables view. (I think it might have at some point, but it doesn't in 11.2 at least.)
You can monitor the table fetch continued row1 statistic (v$sysstat) to see if a query is affected by chained or migrated rows, but I don't believe you can have that metric per-session so either you need a quiet system to measure, or the reading will be "noisy".
The Secrets of Oracle Row Chaining and Migration has interesting information about chained and migrated rows, how you measure them, and potential ways of fixing them.
As always, don't go about rebuilding tables or changing storage parameters "just because" you see chained or migrated rows. Do so only if you measure that it's actually causing you performance problems.
1 From Statistics Descriptions:

Number of times a chained or migrated row is encountered during a fetch
Retrieving rows that span more than one block increases the logical I/O by a factor that corresponds to the number of blocks than need to be accessed. Exporting and re-importing may eliminate this problem. Evaluate the settings for the storage parameters PCTFREE and PCTUSED. This problem cannot be fixed if rows are larger than database blocks (for example, if the LONG datatype is used and the rows are extremely large).

See also Table Fetch by Continued Row.


SQL Insert via mappings table As part of a SSIS ETL process, I have multiple staging tables. I have used xxxx just to resemble data:
e.g.
tblStage1

Field_ABC  Field_ZXC  Field_QWE
 xxxx       xxxxxxxxx   xxxxx

tblStage2

Field_PLM   Field_WER   Field_YTE
 xxxxx       xxxxxx      xxxxxxx

I then have the 'real' tables that they need to be inserted into:

tblDest1
FldABC    FldZXC    FldQWE

tblDest2
FldPLM  FldWER   FldYTE

Using info from the Information Schema I have built a mapping table that details which table and field from the staging tables map to which real table:
tblMapping:

StageTble   StageColumn   RealTable    RealCol    OrdinalPosition
tblStage1   Field_ABC     tblDest1     FldABC     1
tblStage1   Field_ZXC     tblDest1     FldZXC     2
tblStage1   Field_QWE     tblDest1     FldQWE     3
tblStage2   Field_PLM     tblDest2     FldPLM     1
tblStage2   Field_WER     tblDest2     FldWER     2
tblStage2   Field_YTE     tblDest2     FldYTE     3

How do I loop through each staging table and insert into the real tables\columns based on the values in tblMapping?

Would this be possible via a Cursor and a Loop?

Any help would be greatly appreciated.

Perhaps you could create an SSIS script process to dynamically build the insert commands and execute them.

--Demo setup
drop table if exists MappingTable;
create table MappingTable
    ([StageTble] varchar(9), [StageColumn] varchar(9), [RealTable] varchar(8), [RealCol] varchar(6), [OrdinalPosition] int)
;

INSERT INTO MappingTable
    ([StageTble], [StageColumn], [RealTable], [RealCol], [OrdinalPosition])
VALUES
    ('tblStage1', 'Field_ABC', 'tblDest1', 'FldABC', 1),
    ('tblStage1', 'Field_ZXC', 'tblDest1', 'FldZXC', 2),
    ('tblStage1', 'Field_QWE', 'tblDest1', 'FldQWE', 3),
    ('tblStage2', 'Field_PLM', 'tblDest2', 'FldPLM', 1),
    ('tblStage2', 'Field_WER', 'tblDest2', 'FldWER', 2),
    ('tblStage2', 'Field_YTE', 'tblDest2', 'FldYTE', 3)
;

SSIS script task

--SSIS script task
--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble]  as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd
--EXECUTE sp_executesql @cmd

Produces the following dynamic SQL

insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1;
insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2;


Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables I am designing a mysql database using phpmyadmin 3.3.10.4. Everything in the database is InnoDB.

I have two tables, person with primary key person.id_numberand e-mail_address with pk e-mail_address.id_number. Since one person can have many e-mail addresses and since sometimes two people share a single e-mail address, I consider this a many to many relationship. I am trying to create a mapping table to relate them, map_person_e-mail_address. I have the table already created with PK on two columns, map_person_e-mail_address.person_id_number and map_person_e-mail_address.e-mail_address_id_number. I also have an auto-incrementing, unique map_person_e-mail_address.id_number. I need this because later a table for tracking the history of e-mails sent will have a foreign key on this ID number to indicate who was e-mailed and what e-mail address was used.

All of this has worked so far. However, in the relation page for my mapping table, I am trying to set map_person_e-mail_address.person_id_number as the a foreign key on person.id_number and map_person_e-mail_address.e-mail_address_id_number as a foreign key on e-mail_address.id_number. I can set either foreign key individually, but when I try to make both columns foreign keys I receive a message that just says "Error" without any explanation.

Am I not able to set the two columns in the PK as FKs from two different tables? If not, can someone please let me know why and what I should do instead? Or if that's not the problem, then what is?

Tried to create a database with the exact tables, PKs and FKs as yours and I receive no error.
Tried both creating the tables with the constraints and without it, creating the relationships via PHPMyAdmin.

This "Error" you're getting really doesn't have a message or a error number?

To answer you, yes, you can set two columns as Primary Keys that are also Foreign Keys. They should look like this:

person_id_number 1 (referencing p.id_number 1)
email_addres_idnumber 1 (referencing e.id_number 1)

Now, as they are PKs, you can't repeat them. You could only use the Person ID 1 to another Email ID, and vice-versa.

But, as you also have and ID number in your map, there will be some repetitions:

id_number 1
person_id_number 1
email_address_id_number 1

Looks ok, right? But:

id_number 1
person_id_number 1
email_address_id_number 2

Now you have two id_numbers, because your Primary Key consists of three different tables. What I'd suggest you is for you to create the PK as the id_number of the map table and the foreign keys as a Unique Key. It should look like PK(id_number), UK(person_id_number, email_address_id_number).


placing conditions in a join query join on vs where Is there and advantage to one of the following options over the other or are they the same ?

select *
from T1
Join T2
   on T2.id=T1.t2_id
   and T2.field=value;

vs.

select *
from T1
Join T2
  on T2.id=T1.t2_id
where T2.field=value;

And in general - are there performance (dis)advantages to placing the conditions within the JOIN ON statement as opposed to the WHERE statement ?

When using inner joins there is no semantic difference between the two, and it would usually be make sense to put join related conditions in the JOIN and filtering ones in the WHERE clause.

However, when using outer joins the semantics are not the same.
for example: in a left join - rows from the left table will be returned even if there the JOIN ON includes some limitation on the fields from right table that are not met. but if the same limitation would be moved to the WHERE clause - the (already Left Joined) row from the left table that has no matching row on the right table will be discarded.

So for INNER JOIN - it does not matter - for OUTER JOIN - you should think what exactly do you want to get.

Note that in some cases this is actually desirable.
e.g. - In Queries such as:

SELECT *
FROM T1 LEFT JOIN T2 ON T2.id = T1.t2_id
WHERE T2.id IS NULL ;


Sync postgreSQL with SQL Server database- on data change event I have one PostgreSQL database and its data are being changed regularly. I want to sync all its data with my SQL Server database. One thing I can do is use query to check all required tables and columns whether data is same or changed. If changed, then update the same in SQL Server.

Is there any other method is available(like replication)? Or any synchronization software available?

A tool like Daffodil Replicator or SymmetricDS is designed to sync between different brands of databases.
You could try the tds_fdw Foreign Data Wrapper, which may be able to write to SQL Server.


Who changed the job schedule on a SQL Server Agent Job (SQL 2000) Someone (or some unknown process) changed the job schedule on
a SQL Server Agent Job on a SQL 2000 database. The original
start time was 6:00, but was changed to 4:45; that much I can determine.

Is there a way to find the history of changes
involving what user may have altered the job time
and at what time they altered the schedule?

Without reading the transaction log and/or log backups (which may be impossible, if msdb is set to the default simple recovery), I can't think of any good way.

Going forward, if you want to log the changes, you could create some log tables, and add logging triggers to these tables in msdb.

sysjobs
sysjobsteps
sysjobschedules

I checked those names on 2008, but I seem to recall they have the same names on 2000 (though the structure may differ).


SQL Jobs logging only 2 days When I righ click the job + view history, I can only see 2 days of logs. I'm sure this is a really noob question but I can't find the answer.

I've been changing values but with no success. The same config I have in this server, I have in other servers.

You should either remove the checkbox to limit the size of the job history or increase it.  One of your maintenance steps should be to run the sp_purge_jobhistory MSDN command to remove job history older than your retention period.

The "Remove Agent History" checkbox does this behind the scenes but is NOT an automated process that removes history older than the time period specified.  It's really badly named and placed.

If you have lots of jobs or a few jobs that run very often then you will quickly use up 1000 logs of job executions.  As mentioned in the comments, another solution is to archive the contents of the sysjobhistory table.


Update field1.table1 based on compound join between table1 and table2 - Oracle I want to run update statement on Oracle 11g, but it fails with the following error:

  SQL Error: ORA-01427: single-row subquery returns more than one row
  01427. 00000 -  "single-row subquery returns more than one row"

Here's what I'm trying to do in pseudocode:

Update Table1
Set Flag_Column=1
Where
Table1.ColumnX=Table2.ColumnX
and
Table1.ColumnY=Table2.ColumunY

Is there a way to do this in Oracle SQL?

My code looks like this:

update Table1
set Flag_Column=1
where ColumnX=
    (select distinct Table1.ColumnX
    from Table1, Table2
    where Table1.ColumnX=Table2.ColumnX
    and Table1.ColumnY=Table2.ColumnY)
and ColumnY=
    (select distinct Table1.ColumnY
    from Table1, Table2
    where Table1.ColumnX=Table2.ColumnX
    and Table1.ColumnY=Table2.ColumnY)

update table1 set flag_column = 1
 where exists (select 1 from table2
                where table1.columnx = table2.columnx
                  and table1.columny = table2.columny)

This will update every row in table 1 where the x, y column combination exists in table2.


Optimizing MySQL query with left joins Ok, so first off i am going to provide a bit of information on the database structure

I have 3 indexed tables that are used in this query (though one of them should probably be a weak entity, but this is how it was when i took over)

person
person_id (pk)

classes
class_id (pk)
course_id (fk)

learners_to_classes
id (pk)
person_id (fk)
class_id (fk)

The persons Table has about 46,100 records
learners_to_classes has about 51,100 records
and classes has about 1670 records.

The query i am working on is built dynamically. I am working on a person search feature which will list the records from person, plus an indicator if that person has completed 2 particular course types, based on a number of parameters.

there issue i come across how ever is the query time for when the only parameters supplied is either none, (which would probably be a rare use) or a combination of the course types has been completed.

so for example. i want a list of people who have aTrained set to Yes would produce the following query:

SELECT
    p . *,
    IF(sum(c.course_id = 2) > 0, 'Yes','No') as `cTrained`,
    IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as `aTrained`
FROM
    (`person` p)
LEFT JOIN
    (SELECT
        `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
    FROM
        (`classes` c)
    INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 4) > 0
ORDER BY p.`firstname` asc

searching for people who have cTrained='Yes' and a aTrained='Yes' generates the following query:

SELECT
    p . *,
    IF(sum(c.course_id = 2) > 0,'Yes','No') as `connectorTrained`,
    IF(sum(c.course_id = 4) > 0,'Yes','No') as `asistTrained`
FROM
    (`person` p)
        LEFT JOIN
    (SELECT
        `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
    FROM
        (`classes` c)
    INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 2) > 0
    AND sum(c.course_id = 4) > 0
ORDER BY `firstname` asc

These queries how ever are taking over 100 seconds to execute,
and i cant seem to think of a way to optimize them.
I was hoping someone one here might have an idea

First, get rid of the subquery.  It is not needed and it can interfere with optimization.  Second, you don't need a left join, because the having clause is requiring matches.

SELECT p .*,
       IF(sum(c.course_id = 2) > 0, 'Yes', 'No') as cTrained,
       IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as aTrained
FROM person p JOIN
     learners_to_classes ltc
     ON p.person_id = ltc.person_id JOIN
     classes c
     ON ltc.class_id = c.class_id
GROUP BY p.person_id
HAVING sum(c.course_id = 4) > 0
ORDER BY p.firstname asc

For this query, you want the obvious indexes:  learners_to_classes(person_id, class_id) and classes(class_id).  The first seems to be missing.


What is this error about in sql server? Trying to restore database, getting a dialog in SQL Server Management Studio

  You cannot select
  backup sets from different databases"

Any idea why I am getting this error?

BTW backup is from SQL Server 2000 and trying to restore in SQL Server 2000 using SQL Server 2008 R2 Management Studio

The message usually means that you're trying to restore a backup of another - unrelated - database over your existing database. Double check that the backup you're restoring is really of the database you think it is.

Edit: If I remember correctly, you can also get this message if you're trying to restore a differential backup without first restoring the correct full backup.


MYSQL joining 3 tables with left join I have these tables:

pupils: (id_pupil, name, surname, email, user, pass, level, class)
incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester)
qualifications: (id_qualification, qualification, date, time, subject, id_pupil, id_user, id_trimester, type_qualification)

and i did this query:

SELECT
    pupils.id_pupil
  , name
  , surname
  , round(avg(qualifications.calificacion),2) as average
  , count(qualifications.calificacion) as count
  , COALESCE(sum(type_incident='miss' and level=1 and class='A' and id_trimester=1),0) as misses
  , COALESCE(sum(type_incident='delay' and level=1 and class='A' and id_trimester=1),0) as delays
  , COALESCE(sum(type_incident='attitude' and level=1 and class='A' and id_trimester=1),0) as attitude
  , COALESCE(sum(type_incident='miss_justif' and level=1 and class='A' and id_trimester=1),0) as misses_justificadas
FROM
    pupils
    left join incidents         ON incidents.id_pupil=pupils.id_pupil
    left join qualifications    ON qualifications.id_pupil=pupils.id_pupil
WHERE
    level=1
    and class='A'
    and id_trimester=1
    and type_qualification='class'
    and qualifications.id_trimester=incidents.id_trimester
GROUP BY id_pupil

But the problem is, the correct result i would have to get it would be:

And i get:

The problem is that multiplicate the number of class qualifications and the misses (17x6(2 misses, 1 delay, 1 attitude and 2 misses_just)=102)) and i don´t want that. I want the correct information showed in the first picture.

Would you like to help me, please?

Thanks!

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM
    pupils                   AS p
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class'
WHERE
    p.level = 1
    AND p.class = 'A'
    AND p.id_trimester = 1
GROUP BY
    p.id_pupil
  , p.name
  , p.surname ;


What will be the approximate downtime for indexing a column in psql I have an Elixir application that uses Postgres Db and I want to index a field with a row count of 54K. There are few NULL values as well.
I use Ecto.Migration
def change do
   create(index(:rules_engine_test_cases, [:rule_set_version_id]))
end

Will there be any downtime in this case?

You can create indexes concurrently to prevent locks.
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Docs


dynamic or static database tables I am creating a little mulitplayer game. The game creates a session where players can join in. Then the session has to store some data provided in the game.

Should I create a new session table dynamically or a one table with the col sessionId where I store the data from all sessions?

Easier and better structured would be the dynamic solution, but I have heard this is not the best practise.

Relational databases in general, but specifically MySQL are thought to have a static structure. Let me elaborate a bit on what static entails (as it doesn't necessarily mean immutable):

  Each version of the application should work with a predefined set of
  tables and internal structure

Only when requirements change (be it functional or things like performance improvements) you will perform an application "migration" to adapt the logic and data to the new ones, and add, delete or ALTER existing tables. If your table structure changes frequently (compared to the number of read and write operations), that is normally a sign of a bad design.

In particular, new users or state of an application (sessions), should not create new sets of tables, but add rows to existing ones.

As exceptions, Schema-based Multi-tenancy is a pattern that would fit the "creation of a new database" for each "application user group", but normally the scope of that is new organizations sharing the same software, or separate communities that require separate configuration; and normally with things like security or cost in mind. For sessions, table-based multi-tenancy is probably the most common pattern.

Dynamicaly creating and deleting tables usually has a high cost compared to inserting and deleting rows, specially on MySQL (creation of new tablespaces, invalidating high regions of memory, extra metadata locking, etc.)


Archery League Database design I'm fairly new with database desings (only worked on a couple of projects) and i kinda need some info on how to contruct the following.

I need to keep stats for an archery league and the players.
Players info i had no trouble figuring it out. But the match and the stats confuses me.

League consists of matches.
Each match consists of 2 rounds
Each round consists of 10 shots.
Each shot consists of 3 arrows (where we get a score).

How can i sort the stats for each player?
I was hoping you could help me. Number of tables i'll need at least.

Thank you in advance

You can create the following tables:

  Player, League, Match(with FK to league), Round(with FK to Match), Shot(with FK to round & Player)

And the following N:N table

  PlayerLeague(a player subscribed to the league)

if the situation exists that a player  can subscribe to a league but doesn't participate in some matches, then another N:N table will be needed so you know to what matches the player has subscribed to

you can then sort the results by a query like

select
 P.PlayerName,
 sum(S.Score),
 avg(S.Score)
from Shot S
inner join Player on P on S.PlayerID=P.PlayerID
group by P.PlayerName
order by sum(S.Score) desc

you can add extra joins/groupby's if you want to have a more detailed result grouped on league/match


Query data from multiple linked remote databases Using SQL Server 2005 databases and SQL Server Management Studio 2008, I need to query a single value from each of n linked databases at a regular interval. The servers are physically remote and I can basically count on a timeout, attempts to create a query that completes have failed. Every attempt fails when a server times out, I've specifically gotten the 'Login timeout Expired' error.

Ideally I need a stored procedure that generates a row or table with a column or row for each server and a value indicating what my value is in the remote database, or null if I get a timeout (the value on the remote database cannot be null).

My first thought to gather the data was

select
(SELECT TOP 1 [value]
FROM
[RemoteServer1].[DB].[dbo].[Table]) as
RemoteServer1,
(SELECT TOP 1 [value]
FROM
[RemoteServer2].[DB].[dbo].[Table]) as
RemoteServer2,
...

Which generates a single row with all the values I need and indexes I can use to report the needed values. But a single time out in one select statement kills the whole query (it does execute correctly if all servers are reached). I can safely assume that at some point in the query at least one server will time out.

I tried an exception, but it doesn't seem to work (I've never trued to use exceptions in SQL Server before). I tried this in place of each subquery:

begin try
(SELECT TOP 1 [Value]
FROM
[RemoteServer1].[DB].[dbo].[Table])  as RemoteServer1
end try
begin catch
select null as RemoteServer1
end catch,

But the timeout still causes the whole query to fail, I think it's because try only works if the connection doesn't fail?

Is there some way this can work? The only other solution I can think of would be a stored procedure on each remote database to send this record so it's all on our local database (which we can depend on reaching) but that would introduce much more complexity than this query is worth.

I wrote something a while back, perhaps this link will help you.

http://www.mssqltips.com/sqlservertip/2017/script-to-check-all-your-linked-server-connections-for-sql-server/

I had a need to verify that the linked servers were working, especially after a server rebuild. And I had the same issues you are seeing with regards to using straight t-sql. So I ended up using Powershell in order to get the job done.

HTH


Restoring encrypted database on another server (using Backup Encryption) I have two SQL Server instances on same machine. I want to create an encrypted backup on one of the databases and then restore it on the second instance. I am doing the following steps:

Create and backup database master key in the master database which is going to be used to encrypt our certificates

USE MASTER;
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';
GO

BACKUP MASTER KEY
TO FILE = 'E:\GKKeys\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';
GO

Create and back up the certificate that is going to be used for encryption:

USE MASTER;
GO

-- създаваме сертификат, който ще използвам за криптиране на backup-a
CREATE CERTIFICATE BackupEncryptTestCert
WITH SUBJECT = 'smGK_BackupCertificate'
GO

BACKUP CERTIFICATE BackupEncryptTestCert
TO FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
WITH PRIVATE KEY
(
    FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key'
   ,ENCRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
);

Creating the backup:

BACKUP DATABASE smGK
TO DISK = 'E:\GKKeys\smGKFULLEncrtypted.back'
WITH COMPRESSION, STATS = 10, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = BackupEncryptTestCert)

Now on the second instance I want to restore the master key but I am not allowed:

USE MASTER;
GO

RESTORE MASTER KEY
FROM FILE = 'E:\GKKeys\MASTER_KEY.key'
DECRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password'
ENCRYPTION BY PASSWORD = 'smGK_MasterKeyPassword';

  Msg 15317, Level 16, State 2, Line 4 The master key file does not
  exist or has invalid format.

Could anyone tell what is causing this?

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
    Apr 20 2015 17:29:27
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on
    Windows NT 6.3  (Build 9600: ) (Hypervisor)

Create a brand new master key on your second instance. i.e. don't create it from backup you taken from 1st instance. Then restore certificate from the backup taken and then try. I guess you don't need master key and only certificate is required for restore purposes. Follow the below steps:

Step1: Create Master Key

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';

Step2: Verify permissions on cert and pvt key

Make sure SQL Server service account of second instance has FULL permissions on cert and pvt key that you created.

Step3: Create cert from backup

CREATE CERTIFICATE BackupEncryptTestCert
    FROM FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
     WITH PRIVATE KEY
      (
        FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key' ,
        DECRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
      )

Step4: Restore the DB


How to copy a table in a production server without WRITE lock and without writing to a file I'm trying to copy a table in a production table. When I'm performing on InnoDB tables:

INSERT INTO new_table (SELECT * FROM table)

I have a write lock on table. Is it possible to avoid this lock? I am aware there is a solution to write to a file, but due to disk space limitation, I would have to pass on that solution. Also, are there any flags I might be able to add that will make it possible to kill the query during execution without performing rollback?

I suppose you mean you have a "read lock on the table" (you can't write, also known as shared locks). InnoDB writes like yours create read locks (writes have to wait). Reads can work continue without problem. Be careful with the terminology, a write lock is an "exclusive lock".

You can avoid most of the lock problems if you avoid the extra locks that innodb sets because of the statement based replication:

binlog-format = ROW
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2

Be careful, because if the server is a master, or if you need point in time recovery, in certain cases it may create inconsistences. It also changes the locking logic, so be careful, as it may change the application behaviour. However, if you are not using replication or it is a development database, it will be safe. With this, you will be able to update and insert new records on the table at the same time the command runs.

The second part (kill the query without doing a rollback) is not possible for InnoDB due to internal consistency requirements (atomicity of transactions). However, there are several tricks to overcome it. One is to restart the server with innodb_force_recovery = 4 and then drop the table. A better approach would be to do the copy in chunks - a tool like pt-archiver with the --no-delete option, which allows you to copy in several transactions controlling the impact on the server (by the way, probably solving your first problem, too). The tool is a free and open source perl script, easy to use.


PostgreSQL DELETE query hangs (and so does EXPLAIN ANALYZE!) I'm trying to delete a bunch of rows from a table matching a query. The general form of my query is:

DELETE FROM mytable WHERE _id IN (SELECT _id FROM mytable WHERE ...);

The _id column is a SERIAL PRIMARY KEY.

If I run the inner SELECT query on its own it runs in about 1 second and returns about 100,000 rows. But when I add the DELETE call onto it, it seems to just sit and chug away. And chug away. I let it run for about a minute or so, and then cancelled it assuming no progress was being made.

I added EXPLAIN ANALYZE onto the front of it to see if I could check what was taking so long, but that call also hangs for a very long period of time.

Can anybody tell me what could be going on such that it's very quick to identify the rows I'd like to delete but takes an indeterminate amount of time to delete them?

Update: The full query is as follows.

DELETE FROM cards
WHERE _id IN (
    SELECT _id
    FROM cards
    LEFT JOIN game_results ON game_results.card_id = cards._id
    WHERE NOT available
    AND game_id IS NULL
)

A simplified version of the tables would be:

CREATE TABLE cards (_id INTEGER PRIMARY KEY, available BOOLEAN);
CREATE TABLE games (_id INTEGER PRIMARY KEY);
CREATE TABLE game_results (game_id INTEGER REFERENCES games, card_id INTEGER REFERENCES cards);

I'm sure this query would eventually finish running, I'm just surprised at how long it's taking given that retrieving all of the IDs to delete is so quick.

Solution:

The problem was three-fold!

I had one of the earlier (very slow!) queries running that had been triggered by an aborted script. The query was still running even though the script had been aborted. As such, operations like trying to drop indexes were locked waiting for the query to finish. Manually cancelling that query using pg_cancel_backend allowed me to experiment with dropping indexes and foreign key constraints.
Foreign key constraints seem to be the issue slowing everything down. The fact that the game_results table referenced cards made the delete take forever. The funny thing is, I was only deleting cards explicitly unused in the game_results, but of course that didn't stop the foreign key checks from happening upon delete! That made things very slow. I dropped the foreign key constraint before running the delete and that sped things up to a level I felt was appropriate.
For whatever reason, the second delete query in the accepted answer ran much quicker than the first.

By combining these three things I was able to do the whole delete in a few seconds, whereas until I had combined these three factors together my queries were running to 5-10 minutes (before I was cancelling them!).

Thanks to all for the help!

Assuming that the columns are not nullable, the query can be simplified (without the self join) to using either NOT IN or NOT EXISTS:
DELETE FROM cards
WHERE available = FALSE
  AND _id NOT IN
      ( SELECT card_id
        FROM game_results
      ) ;

DELETE FROM cards AS c
WHERE c.available = FALSE
  AND NOT EXISTS
      ( SELECT *
        FROM game_results AS gr
        WHERE gr.card_id = c._id
      ) ;

Still, deleting 100K rows can not be instantaneous. Performance may be affected by lack (or excess) of indexes, triggers, cascade deletes, etc.
You can use EXPLAIN (only, without ANALYZE) to get the execution plan of a DELETE. See EXPLAIN for details, especially if you want to try ANALYZE or other options of EXPLAIN:

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;


select data from related tables and display their counts in the relating table I have a table which stores data about an inspection. Inspections are carried out in a particular company belonging to an industry, located in a region. the inspections table has the industry_id field referencing the industry table and the region_id field referencing the region table.

Both industry and region tables have id and name fields.

running this query:

SELECT i.name industry, r.name region, COUNT(*)
FROM labour_inspections l
left join company_industries i on l.industry_id=i.id
left join regions r on l.region_id=r.id
where i.name != 'NULL' and r.name != 'NULL'
GROUP BY i.name and r.name

gives me the results i need but not in the way i want it for this view.

I achieved that by writing separate queries and putting the results together with PHP. It is not elegant and I am concerned about performance.

Is there a way I can achieve the view in the image with a single query? Thank you all in advance

name != 'NULL'

That assumes that name can be the 4-letter string 'NULL'.  If, in reality, it can be the special value NULL, then do this:

name IS NOT NULL

Please provide SHOW CREATE TABLE for each table, plus a small amount of representative data including some nulls.


MySQL Sharding vs MySQL Cluster Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning

When I refer to sharding, I'm considering sharding made in the application layer, for instance, distributing records evenly across independent MySQL instances. For two servers, it could be (key mod 2).

Disclosure: I am a MySQL employee, working on MySQL Cluster.

I would say that MySQL Cluster could achieve higher throughput / host than sharded MySQL+InnoDB provided that :

Queries are simple
All data fits in-memory

In terms of latency, MySQL Cluster should have more stable latency than sharded MySQL. Actual latency for purely in-memory data could be similar.

As queries become more complex, and data is stored on disk, the performance comparison becomes more confusing.  To get a more specific answer, you need to describe more about your application and the queries you perform, as well as the number of hosts and volume of data.  MySQL Cluster has recently gained parallel localised query execution (AQL) which means it can be competitive with standalone MySQLD despite having data distributed across multiple hosts.

MySQL Cluster is currently limited to 'sharding' over 48 hosts.  Sharded MySQL in theory has no limit.  However, for a given target throughput, fewer MySQL Cluster hosts may be needed than sharded MySQL hosts.

More interesting differences are when you look at areas other than performance :

MySQL Cluster supports arbitrary queries across all shards
MySQL Cluster supports arbitrary transactions across all shards
MySQL Cluster supports synchronous replication of shards with automatic failover and recovery
MySQL Cluster supports online add node (cluster expansion)
Sharded MySQL is more 'roll your own'

Having sharding built into your application gives you maximum scaling potential, but adds complexity and limits your flexibility in terms of cross-shard queries and operations.  If your sharding is premature then it may be the root of some problems for you.  MySQL Cluster lets you get some of the benefits of sharding without having to constrain your application to be single-shard only.

Regarding the previous answer, a few clarifications :

  "Although MySQL Cluster is ACID-complaint, it does not provide a suitable storage engine for data with compound keys."

MySQL Cluster supports compound primary and secondary keys.  Not sure what's not 'suitable' about it.  Perhaps the previous poster can explain?

  "In order to have data with the same key characteristics stored in a particular set of data nodes, you could doing the following:

  Take all data nodes offline, leaving only those data nodes you want to house data with the same key characteristics.
  Load your data into the MySQL Cluster, which populates only your select data nodes
  Bring all data nodes back online"

This is incorrect.  Data distribution is independent of which nodes happen to be online at any time.  MySQL Cluster supports various data distribution schemes to support the optimisations you describe.  I describe data distribution in MySQL Cluster in a blog post here : Data distribution in MySQL Cluster


UPDATE Only one row (using update and join) I'm trying to get a fast, simple sql query to update only one row at a time while using join too.

I have tried LIMIT, but to no success.

Query:

UPDATE
table1
JOIN
table2 ON table2.col=table1.col
SET
table1.row1='a value'
WHERE
table1.row2 LIKE '%something%'
LIMIT 1

Message:

  Error Code: 1221. Incorrect usage of UPDATE and LIMIT

LIMIT is not allowed when the UPDATE has joins. You can however move the join and the LIMIT inside a subquery and then join back to the table to be updated using the primary or a unique key. This is allowed:

UPDATE table1 AS upd
  JOIN
    ( SELECT t1.pk
      FROM table1 AS t1
        JOIN
           table2 AS t2 ON t2.col = t1.col
      WHERE t1.row2 LIKE '%something%'
      -- ORDER BY some_expressions
      LIMIT 1
    ) AS sel
    ON sel.pk = upd.pk
SET
   upd.row1 = 'a value' ;

It's also good to use ORDER BY with LIMIT. Otherwise an arbitrary row will be selected.

If you want to update both tables (one row from each one), then you just need to join the derived table to the second table, too:

UPDATE
    ( SELECT t1.pk AS pk1,                     -- The PK columns
             t2.pk AS pk2                      -- of each table
      FROM table1 AS t1
        JOIN
           table2 AS t2 ON t2.col = t1.col
      WHERE t1.row2 LIKE '%something%'
      -- ORDER BY some_expressions
      LIMIT 1
    ) AS sel
  JOIN table1 AS upd1  ON sel.pk1 = upd1.pk    -- join table1
  JOIN table2 AS upd2  ON sel.pk2 = upd2.pk    -- join table2
SET
   upd1.row1 = 'a value',
   upd2.roww = 'some other value' ;

Test at rextester.com


Cross Database Query between Azure SQL Database and SQL Server Database Can we built a Cross database query for Insert, Update and Delete between SQL Server and Azure SQL DB and vice versa?

No. In fact, a traditional cross database query isn't possible. You can set up a mechanism for querying between two Azure SQL Databases, but it's not the traditional mechanism. I have a blog post on how that works and there are links there to the documentation. However, even with that, you can't combine it to a query with on-premises SQL Server.


What could trigger that a login created from a certificate is not visible in metadata I am trying to create a login and grant it rights to run and unsafe assembly but the check on server_principals does not return the login when it already exists. The script works perfectly fine on my local development server, but fails on our test servers:

My dev server: Sql Server 2008 R2 SP1 --> works ok
Remote server1: Sql Server 2005 SP4 --> error
Remote server2: Sql Server 2008 R2 RTM --> error

My user is a member of the sysadmin role.

The script will create a certificate (The real deal extracts the certificate from a .net assembly), check if the login exists and if not, it will create the login and grant unsafe assembly rights.

declare @cert_name sysname
      , @safe_cert_name sysname
      , @trust_level nvarchar(20)
      , @login_name sysname

select @cert_name = N'Cert01'
     , @trust_level = N'unsafe'
     , @login_name = QUOTENAME(@cert_name + N'Login')

select @safe_cert_name = QUOTENAME(@cert_name)

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'USE master;
       CREATE CERTIFICATE ' + @safe_cert_name + N'
       ENCRYPTION BY PASSWORD = ''omg00001111;!@#$%^&''
       WITH SUBJECT = ''Test Certificate you can drop me'',
       EXPIRY_DATE = ''20120801'';')
   END

IF NOT EXISTS(SELECT * FROM master.sys.server_principals WHERE name = @cert_name + 'Login')
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

The error returned by this script is the following:

Creating login [Cert01Login] from certificate [Cert01]
Msg 15025, Level 16, State 2, Line 2
The server principal 'Cert01Login' already exists.
Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'Cert01Login', because it does not exist or you do not have permission.

Since my user is sysadmin on all servers I am really puzzled why this is behaving like this (unless it is a bug fixed in SQL 2008 R2 SP1).

Edit: Wrapped object names in "Quotename"

Edit: Added content of server_principals

SELECT name
  FROM sys.server_principals
 ORDER BY name

name
=========================================
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##
##MS_SQLAuthenticatorCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLResourceSigningCertificate##
##MS_SmoExtendedSigningCertificate##
OURDOMAIN\ACR
...
OURDOMAIN\MYUSER
...
OURDOMAIN\ZVA
NT AUTHORITY\SYSTEM
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
SQL_Linker
archiving
bulkadmin
dbcreator
diskadmin
processadmin
public
sa
securityadmin
serveradmin
setupadmin
sysadmin

Edit: Added output of sp_helpsrvrolemember

exec sp_helpsrvrolemember 'sysadmin'

ServerRole  MemberName                MemberSID
==========  ===========               =========
sysadmin    sa                        0x01
sysadmin    NT AUTHORITY\SYSTEM       0x010100000000000512000000
sysadmin    NT SERVICE\MSSQLSERVER    0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E
sysadmin    NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357
sysadmin    OURDOMAIN\ADMINS          0x0105000000000005150000003301161E3D17782BC63FD75B922C0000
sysadmin    OURDOMAIN\MYUSER          0x0105000000000005150000003301161E3D17782BC63FD75B8B150000

This code executed for me fine. The only issue I might suspect is that your server is case sensitive. The EXISTS check will come up empty if you have a login named cert01login but the create will fail because it is not case sensitive. So you'll want to make sure the EXISTS check is not case sensitive, e.g.

WHERE name COLLATE Latin1_General_CI_AI = @cert_name + 'Login'


SSIS and Excel import I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file.  My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?

The error is Could not retrieve the table information for the connection manager 'Excel Connection Manager'.  Failed to connect to the source using the connection manager.

Thanks

You'll need to configure SSIS to run in 32-bit runtime as Excel does not support 64-bit

Go to the Property page for the Solution, select Debugging and change Run64BitRuntime to False

Running 32-bit SSIS in a 64-bit Environment


creating user related user specific content Should you create a table for each user to contain information on specific content for an account, say favorite articles, interests, ect. User defined lists that would be used to generate useful content using a tag system to define groups of content. Would i be approaching this close to right, or is there a better way. i know u should not define more than one thing in a column, but i also would want the fewest number tables too?

Do not create a table for each user!  Create a table for each category of information you want to store.

In a relational database, a table typically represents a list of a particular type of thing which is of interest to your system.  This can be a tangible thing, like users, or an intangible thing, like interests.  The point is that each table is about a kind of thing.  The things themselves are rows in a table.  Every row in a table needs to have the same structure.  If you have no consistent structure for your data then you have to consider a No-SQL database instead.

For relational databases, study the rules of database normalization.  This will help you to understand how to structure your database tables.


ora-03114 not connected to oracle Since the upgrade to the 19C Oracle DB (before 12C) I receive randomly those ORA-03114 error message's.
Before the upgrade I never received it. Any idea's what exactly why those error messages pops up suddenly and what changes needs to be done?

Domain Win10 PC
Not all PC's in the domain having this issue
only localy tnsnames.ora change

ORA-03114: not connected to ORACLE

ORA-03114 not connected to ORACLE
Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection.
Common causes for the ORA-03114 error include:

Listener process is not running
DB is shutting down or session has been forcibly disconnected
Missing entry to the tnsnames.ora
Network issues such as timeouts
Problems with connection pooling (shared servers, MTS)

If - as you seem to suggest - your tnsnames.ora was modified recently, an incorrect configuration could be one possible reason for the error, especially if other clients are not experiencing any problems. Compare the tnsnames.ora and sqlnet.ora files between the client that fails and the clients that work. Also check the network path for routers or firewalls that could be enforcing idle timeouts, if that is applicable in your environment.
See here for further explanation:

https://www.tekstream.com/resource-center/ora-03114-not-connected-to-oracle/
http://www.dba-oracle.com/t_ora_03114.htm


MySql 5.5 in ubuntu repository Sorry for duplicate question.

I am looking for the best way to install mysql 5.5 on ubuntu.

So far, there are few options:

Installing from tar.gz
Installing from PPA.
Alien on RPM.

All of them don't seem to be optimal to me. Installing from tar.gz takes long time and might have room for mistake. Installing from PPA is dangerous, I don't want to install something on production server which is non-official or might have differecens with official version. Running alien on RPM not a good way to install.

How did you install Mysql 5.5 on ubuntu server?

Version: Ubuntu 10.04.3 LTS

There are .deb packages available. Using the ones from http://people.debian.org/~nobse/mysql-5.5/:

sudo su -
wget http://people.debian.org/~nobse/mysql-5.5/mysql-common_5.5.13-2_all.deb
wget http://people.debian.org/~nobse/mysql-5.5/mysql-server_5.5_5.5.13-2_amd64.deb

dpkg -i mysql-common_5.5.13-2_all.deb
dpkg -i mysql-server_5.5_5.5.13-2_amd64.deb

The first dpkg execution may ask for you to install dependencies. If you're installing on a 32-bit system, alter the URLs accordingly.

Latest .debs are available on http://dev.mysql.com/downloads/mysql/#downloads - Select "Debian Linux".


MySQL no query cache hits My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB.

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| query_cache_limit            | 268435456  |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 4294967296 |
| query_cache_strip_comments   | OFF        |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
+------------------------------+------------+

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 622        |
| Qcache_free_memory      | 4291758416 |
| Qcache_hits             | 0          |
| Qcache_inserts          | 1471501    |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 8995948    |
| Qcache_queries_in_cache | 2269       |
| Qcache_total_blocks     | 5199       |
+-------------------------+------------+

Any ideas ? I saw some other similar questions like this - the answer just suggests turning it off though without any good explanation except "the InnoDB Storage Engine likes to play traffic cop with the query cache".

Please look back at my answer again. Issue #1 might be your one and only problem. Why ?

FACT #1 : 5199 Qcache_total_blocks allocated
FACT #2 : 622 Qcache_free_blocks
FACT #3 : 2269 Qcache_queries_in_cache
FACT #4 : query_cache_limit is 268435456 (256M)
FACT #5 : query_cache_min_res_unit is 4096 (4K)

What can said here ?

There are 2269 queries in the Query Cache using either 2 or 3 blocks (not accounting for fragmentation). Query Cache is 88.0362% (1 - (622/5199)) used. The largest a query can be inside in the Query Cache is 256M, and the smallest is 4K. So, queries are definitely piling up in it when SELECT queries retrieve result sets that's not there before.

If any SELECT query returns more than 256M or less than 4K, none of the data retrieved will be in the query cache. Consequently, running the same SELECT query (that begger than 256M or smaller than 4K) requires reading the data from disk all over again.

All you could really do is lower query_cache_min_res_unit to the minimum value. That's 512, which 0.5K. You can do that dynamically by running

mysql> SET GLOBAL query_cache_min_res_unit = 512;

Then, go monitor Qcache_hits. If this solves it for you, go add this to my.cnf

[mysqld]
query_cache_min_res_unit = 512

You don't have to restart mysql since you changed it dynamically.

If this does not change anything, then you need to examine the nature of your app. If your app retrieves data by some SELECT query and you never run that same SELECT query again, Qcache_hits cannot be anything other zero. If Qcache_hits is always going to be 0, that's why I had to bring up Issue #2. No sense in having a query cache on if you never cache anything. It will just nominally slow down InnoDB looking for any changed data. You can be sure that the data retrieved from SELECTs will be sitting in the InnoDB Buffer Pool.

You may need to spend some time examining the size of all your result sets and tuning the query cache to behave and dwell in peace with InnoDB (Why query_cache_type is disabled by default start from MySQL 5.6?)


Does logging affect query performance I want to benchmark the performance of MySQL, I want to know if logging affect performance.
MySQL has logs: general-log, binlog, errlog, redo log. Do these logging behaviors affect performance? For performance, I mean throughput, TPS obtained from benchmarks like TPC-C TPC-H YCSB sysbench oltp.
I know that the redo log (WAL) does affect performance because the queries should wait until the log to be flushed to disk. But do queries wait for other logs?
Thanks!

Log files

Error log -- very little activity; don't worry about it.
redo/undo -- Mandatory; you cannot avoid their use (for InnoDB).
binlog -- for replication and/or recovery.  Clearly state in your benchmark results the setting of log_bin.
general log -- This should normally be turned off.  It is a disk hog.  Leave it off for benchmarking.
slowlog -- It is reasonable to leave it off for benchmarking.  (And so-state.)  For production systems, I recommend leaving it on.  It does not hurt much, even with long_query_time = 1.  (That is "seconds".)
Double-buffer -- Keep on unless the disk subsystem handles it.  (Cf "torn page")
innodb_flush_log_at_commit -- =1 for banking-like security; =2 for more speed, at the expense of very rare loss of data.

The disk subsystem makes a big difference in certain write-intensive apps:

Plain HDD -- slow
SSD -- noticeably faster, but non-zero write time.
RAID with Battery Backed Write Cache -- virtually eliminates I/O time for writes.  The BBWC avoids data loss even during power failure.

Rant
Benchmarks are a popular way to lie about how great your thing is.  What is your goal for the benchmark?
It is somewhat reasonable to compare two hardware setups or versions via comparing benchmarks.  However, TPC (etc) benchmarks only test a few focused things.  Keep in mind that your app may not be really like any of the TPCs.
Also, any 'real' system has multiple things going on.  Benchmarks (like drug trials) mostly punt when it comes to combinations.
Some benchmarks focus on "how many connections can be run before the system keels over?"  If you are anywhere near that limit, you are already in trouble, and latency may make the system a failure.


Predefined rows conflicting with user entered rows Is there a pattern to deal with a set of predefined rows in one or more tables that can also have user-defined rows inserted in them?

I could want to distribute an application with a table called Colors where I defined some colors that are, for example, used in the application's GUI.
I might use one column for a numerical primary key, one for the name and three other columns for R,G,B values. I would put an unique constraint on the name column and one for the three R,G,B columns.
The contents of this table are intended to be available to the user so that when he needs to specify a color he can choose one from the table.
However it makes sense to allow the user to add his own colors to the table.

Issues however could arise if the user wants to:

define a color whose R,G,B value are already used in a predefined color
use a name that is already in use by a predefined color
hide predefined colors he's not interested into

I need to have some predefined, read-only, rows on which the application can rely but at the same time I don't want to impose anything on the user while providing him with the opportunity to use some of the predefined rows if he chooses to.

I thought of separate tables or a single table with extra columns that specify if the row is predefined, hidden by the user and so on.
However I can see flaws in both approaches.

Reflection, not an answer.

  define a color whose R,G,B value are already used in a predefined color

I cannot find a reason to forbid for a user to create his own color name for some color. So (R,G,B) must NOT be unique.

  use a name that is already in use by a predefined color

I cannot find a reason to forbid for a user to create his own color for some color name. So (color_name) must NOT be unique.

As I see there is to exist additional field user, which allows each user to create his own colors collection. user field is nullable, Null value means this is a color available to all users (predefined colors, for example). But:

  hide predefined colors he's not interested into

means that there must be another method - while creating user you must copy all predefined colors to him personally. To mark any color as not interesting for current user you may add additional field or use any another method (for example, add some char - asterisk or minus sign, etc. - in front of color's name which is not allowed as first char for active color name).

Alternatively you can create a table with base colors and a table with color aliases personal for each user. Each new color created by some user is inserted in both tables whereas any existing color is inserted in second table only.


Pivoting row contents to produce one dimensional result I have a table with a varchar column containing pipe-delimited values

For example:

Row 1 Column 1 = a|b|e|gg|foo
Row 2 Column 1 = oV|foo|do
Row 3 Column 1 = boop

How can I query this to return something like? Duplicates are ok.

Results:

column
a
b
e
gg
foo
foo
oV
do
boop

I realize this is not optimal but my hands are tied with the current schema

If you cannot change your current schema, then you can create a split function:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end;

Then you can use an outer apply to join with your table:

select x.items
from yourtable t
outer apply dbo.split(col1, '|') x;

Which will produce the following results:

| ITEMS |
---------
|     a |
|     b |
|     e |
|    gg |
|   foo |
|    oV |
|   foo |
|    do |
|  boop |

See SQL Fiddle with Demo

Or if you do not want to use a split function, then you can perform this with CTE:

;with cte (value, fullValue) as
(
  select
      cast(left(col1, charindex('|',col1+'|')-1) as varchar(50)) value,
      stuff(col1, 1, charindex('|',col1+'|'), '') fullValue
  from yourtable
  union all
  select
    cast(left(fullValue, charindex('|',fullValue+'|')-1) as varchar(50)) ,
    stuff(fullValue, 1, charindex('|',fullValue+',|'), '') fullValue
  from cte
  where fullValue > ''
)
select value
from cte

See SQL Fiddle with Demo


Shift db data to another column I have the following table

id  |   ev_id   | ev_loc_id |   a   |   b   | prev_a    |   prev_b

If I the (ev_id AND ev_loc_id) not exist in the table then I just insert them, otherwise I'm putting the a to the prev_a and b to prev_b.

I solved this with a selection:

SELECT a, b FROM tb WHERE ev_id=XX AND ev_loc_id=XXX

If the array is empty than I inserted the new data, otherwise I replaced the a,b and the selected values to prev_a, prev_b

Is there most time efficient solution?

Here is what you can do:

INSERT ... ON DUPLICATE KEY UPDATE

You can do the following

make prev_a assume the value of a
make prev_b assume the value of b
make a assume the value of the new a
make b assume the value of the new b

Here are the statements to create sample data for this test:

use test
DROP TABLE IF EXISTS ev;
CREATE TABLE ev
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Here is the output:

mysql> use test
DROP TABLE IF EXISTS ev;
Database changed
mysql> DROP TABLE IF EXISTS ev;
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE ev
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     ev_id INT NOT NULL,
    ->     ev_loc_id INT NOT NULL,
    ->     a FLOAT DEFAULT 0.0,
    ->     b FLOAT DEFAULT 0.0,
    ->     prev_a FLOAT DEFAULT 0.0,
    ->     prev_b FLOAT DEFAULT 0.0,
    ->     PRIMARY KEY (id),
    ->     UNIQUE KEY (ev_id,ev_loc_id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,5.0,7.0),
    -> (1,3,9.0,3.0),
    -> (2,2,7.0,9.0),
    -> (2,3,3.0,9.0);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ev;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    5 |    7 |      0 |      0 |
|  2 |     1 |         3 |    9 |    3 |      0 |      0 |
|  3 |     2 |         2 |    7 |    9 |      0 |      0 |
|  4 |     2 |         3 |    3 |    9 |      0 |      0 |
+----+-------+-----------+------+------+--------+--------+
4 rows in set (0.00 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,8.0,4.0) ON DUPLICATE KEY
    -> UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    8 |    4 |      5 |      7 |
+----+-------+-----------+------+------+--------+--------+
1 row in set (0.00 sec)

Give it a Try !!!


Update with distinct on violates unique key in postgres? update schema.current_status a
set status       = stat.overall_status,
    created_date = stat.created_date,
    parent_id    = stat.parent_id,
    id  = stat.id
from (select distinct on (id) id,  parent_id, overall_status, created_date
from schema.daily
where parent_id
          =
      'abc'
     ) stat;

This is the query I am using, when I run just the sub query, I can see it is returning unique records only, but when I try to update the table, it says it violates unique key. My unique key is on parent_id, id

As Akina hinted at, without a relational predicate between your subquery and the outer parent query, this is going to try to UPDATE all rows in your schema.current_status table to the same value from your schema.daily table, violating the unique key constraint. This would only work if there was only a single record in your schema.current_status table. What is the relationship between schema.current_status and schema.daily?...as that's what you're currently missing in a predicate clause (such as a WHERE clause).


When a transaction truncate a global temporary table, is that logged in transaction log or error log? When a transaction truncate a global temporary table, is that logged in transaction log or error log ? I have inherited bunch of old SQL 2012 servers from my senior dbas who left the organisation. I'm stuck with queries from report users which processes are truncating certain business critical tables.
If it is logged in transaction log, then could the SID,login details be retrieved? Suspect, there are multiple agent job using those ##table.

You mean a ##temp table? It isn't logged in error log. But it must be logged in transaction log since you can rollback a truncate table command.


In SQL server is a check constraint applied before or after a default constraint on insert? As per the title: In SQL server is a check constraint applied before or after a default constraint on insert?

So taking the following table (Unnecessary columns omitted for brevity)...

CREATE TABLE [dbo].[Customer](
    [CountryCode] [varchar](5) NULL CONSTRAINT [CK_Customer_DefaultCountryCode]  DEFAULT ([app].[GetUnknownCountryIsoCountryCode]()),
) ON [PRIMARY]

...Which also has the following check constraint...

ALTER TABLE [admin].[Customer]  WITH CHECK
    ADD  CONSTRAINT [CK_CustomerHasCountryWhenIntercompanyBusinessModel]
    CHECK  (
        ([app].[BusinessModelMustHaveCountry]([BusinessModelId]) = (1) AND [app].[CheckCountryCodeExists]([CountryCode]) = (1) OR
        [app].[BusinessModelMustHaveCountry]([BusinessModelId]) <> (1))
    )
GO

... will the default constraint be applied and populate the default value before the check constraint is applied?

The check constraint is applied after.

You can see this from the fact that the following succeeds.

DECLARE @T TABLE(X INT DEFAULT 1 CHECK (X = 1));

INSERT @T DEFAULT VALUES;

Also the  execution plan shows that the assert operator operates on the values output from the table insert operator so is checking the values that were actually inserted.


Using Select Statement to get column name and table name from the same table I have a table containing 2 Columns :
Column1 (table_name) and column2 (column_name)
like this :

     (Column 1)    (Column 2)

    |Column_Name | Table_Name |
    -------------|------------|
    |Address     |  TBL_Person|
    |Email       |  TBL_User  |

It has about 300 records

I want to have a select statement which returns these 2 columns and a column which gets the maximum length of the column of the table

like this :

    |Column_Name | Table_Name | Max_Length |
    -------------|------------|------------|
    |Address     |  TBL_Person| 1020       |
    |Email       |  TBL_User  | 50         |

I can make a string calculating max_length, but I don't know how to use it in the select statement.

I tried using a function but in a function I had to execute SQL which is not allowed in a function because you can not use a sp in a function.

COL_LENGTH returns the default length of the column, not the maximum length.
All the columns I have are varchar(max) or nvarchar(max).

Currently there is no syntax directly supporting what you are trying to do. As you probably know, names cannot be parametrised in a SQL statement. That means that when you need to substitute names from column values of another table, you have to use dynamic SQL: first build the query string and then execute it. There is just no working around using dynamic SQL in such cases. Furthermore, you have already established for yourself that you cannot use dynamic SQL in a function. So there you are, seemingly stumped.

However, if you insist on using a single SELECT statement for this, there is one way – provided you agree to bend over backwards slightly to achieve the goal, that is. And accept a major limitation of the method.

The solution involves creation of a loopback linked server and using the OPENQUERY function. But first you will need to make sure your dynamic SQL solution works as it is. For the purpose of this answer, I am going to assume that the dynamic SQL looks like this:

DECLARE @sql nvarchar (max) = '', @sqltemplate nvarchar(max) =
'UNION ALL
SELECT
  Column_Name = ''{Column_Name}'',
  Table_Name  = ''{Table_Name}'',
  Max_Length  = MAX(LEN([{Column_Name}]))
FROM
  [oil stop].dbo.[{Table_Name}]
';
SELECT
  @sql += REPLACE(
            REPLACE(
              @sqltemplate,
              '{Column_Name}',
              Column_Name
            ),
            '{Table_Name}',
            Table_Name
          )
FROM
  tempdb.dbo.YourMetaDataTable
;
SET @sql = STUFF(@sql, 1, 9, '');  -- remove the leading UNION ALL
EXECUTE sp_executesql @sql;

Once you have verified the script is working, and made sure the loopback linked server is created, just put the script inside the OPENQUERY function like this:

SELECT
  *
FROM
  OPENQUERY(
    YourLinkedServerName,
    '...'  -- the dynamic SQL script
  )
;

Remember to double each quotation mark (apostrophe) inside the script.

One other important change you will likely need to make is to add a WITH RESULT SETS clause to the EXECUTE statement to describe the result set, so that OPENQUERY can process the output correctly for you. When describing the result set, you will likely just repeat the same type for Column_Name and Table_Name as defined for them in the metadata table. For the example below I am assuming the type to be sysname in both cases. And as for the Max_Length column, I believe int would work well there. So, the modified EXECUTE statement would look like this:

EXECUTE sp_executesql @sql
WITH RESULT SETS
(
  (Column_Name sysname, Table_Name sysname, Max_Length int)
);

For completeness, and to make the lack of elegance in this solution more evident for the wider audience, this is what the final query would look like:

SELECT
  *
FROM
  OPENQUERY(
    [OIL STOP],
    'DECLARE @sql nvarchar (max) = '''', @sqltemplate nvarchar(max) =
    ''UNION ALL
    SELECT
      Column_Name = ''''{Column_Name}'''',
      Table_Name  = ''''{Table_Name}'''',
      Max_Length  = MAX(LEN([{Column_Name}]))
    FROM
      [oil stop].dbo.[{Table_Name}]
    '';
    SELECT
      @sql += REPLACE(
                REPLACE(
                  @sqltemplate,
                  ''{Column_Name}'',
                  Column_Name
                ),
                ''{Table_Name}'',
                Table_Name
              )
    FROM
      tempdb.dbo.MetaData
    ;
    SET @sql = STUFF(@sql, 1, 9, '''');  -- remove the leading UNION ALL
    EXECUTE sp_executesql @sql
    WITH RESULT SETS
    (
      (Column_Name sysname, Table_Name sysname, Max_Length int)
    );
    '
  )
;

The main problem, though, is that the query above still cannot be parametrised, and that is the principal limitation I was talking about. Even though the OPENQUERY script is specified as a string literal, it can only be a single string literal – not a variable, not a complex expression. That means that if you want to apply the query to a different subset of rows of the metadata table, you will have to use a new script for that.


Is it possible to get the information of which user changed what in a database? I'm trying to create a log of changes in a given table in a database.
To do this, I'm going to get the info about all the fields in the original table and create a new one using a function.

In addition to the fields in the original table, the new table should contain the time of the change, the type of the change (insert, delete, update) and an identification of the user that made the change.

I've figured out where I can get the information about the columns of the original table in MySQL (table INFORMATION_SCHEMA.Columns), but I don't know where I can find the information related to the user.

Can anyone please point me in the right direction?

The right direction for this is to use an audit logging plugin. There are some choices out there. Please check out the following post for more information

http://www.mysqlperformanceblog.com/2014/05/20/database-auditing-alternatives-mysql/

My personal preference is the Percona Audit Plugin but you should be using Percona Server (MySQL Alternative) for this.


How Commit works in a transactional push replication with updatable subscribers I want to know if in a transactional push replication with updatable subscribers, a commit is only done when all subscribers have succeeded to make it, or if it only guarantees the correct commit in the publisher and the subscriber that generated the change in the database.

I've found this in msdn, but I don't know if the two-phase commit protocol is referring to all instances (publisher and subscribers, or just one subscriber and the publisher)

  When data is updated at a Subscriber, it is first propagated to the
  Publisher and then propagated to other Subscribers. If immediate
  updating is used, the changes are propagated immediately using the
  two-phase commit protocol. If queued updating is used, the changes are
  stored in a queue; the queued transactions are then applied
  asynchronously at the Publisher whenever network connectivity is
  available. Because the updates are propagated asynchronously to the
  Publisher, the same data may have been updated by the Publisher or by
  another Subscriber and conflicts can occur when applying the updates.
  Conflicts are detected and resolved according to a conflict resolution
  policy that is set when creating the publication.

To paraphrase the quoted block, with immediate update the only guarantee is that the commit is immediately updated to the publisher.  The changes will be sent to the other subscribers in due course.

Without immediate updating, your changes are only committed to the local subscriber, and queued for distribution back to the publisher, and following that, the other subscribers.

In both cases there is a period of time when a stale record is still at the other subscribers, and one of those subscribers may update the stale record and cause a conflict.


Find Candidate Keys by looking to Functional Dependencies I'm studying Database Design and I am in trouble with the Candidate Keys and the Functional Dependencies.

I have these Functional Dependences:

B → CEF

G → AD

B → DG

GA → B

My idea is: Since GA is the only one attribute that is not functionally determined by anyone, then, by using Armstrong's Axioms, I see that GA determines all attribute of the Schema S.

So there is only one Candidate Key: GA.

Am I right? Thanks in advance.

The relation with the above functional dependencies has two different candidate keys: B and G.

You can verify this if you compute the closure of both to see if it contains all the attributes:

B+ = B
B+ = BCEF (by using the dependency B → CEF)
B+ = BCEFDG (by using B → DG)
B+ = BCEFDGA (by using G → AD), all the attributes, B is a candidate key

While for G:

G+ = G
G+ = GAD (by using G → AD)
G+ = GADB (by using GA → B)
G+ = GADBCEF (by using B → CEF), all the attributes, G is a candidate key

No other set of attributes is a candidate key, since the only other attribute on the left side is A, but GA is determined by G, so it can be eliminated from that dependency.

Actually a canonical cover of the original set of dependencies is:

{ B → C
  B → E
  B → F
  B → G
  G → A
  G → D
  G → B }


Choice- or ids-list- field representation I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to Many relation).

I would like to know what is commonly used and what are the drawbacks/assets of each solution (in terms of performances, integrity and sustainability).

To some extent any solution is going to depend to a great deal on exactly what you are doing and what RDBMS you are using.  In general though using a text field to store a comma separated list of integers is a bad idea.

The starting point solution is always id-based reference.  This is a good place to start because it obeys proper relational rules, and it allows you to handle things like multiple values by breaking these off into new relations.  In fact I would say it is always best to start here.

Alternatives however, work well in some cases and with some RDBMS's.  ENUM fields (or alternatively text domains with a check to keep them within some values) are helpful if you never need to drop options.  If you do, then you have to determine what you want to do about this before you drop them.  Secondly array types can be useful in certain cases too (and they don't necessarily break 1NF if the array as a whole is an atomic value of a domain, for example if we represent an IPv4 address as an array of 4 8-bit integers). Array-based approaches depend highly on what you are doing and db-level support, but on PostgreSQL I would choose an array where cardinality matters (i.e. an array is a tuple instead of a set), and where the semantics are entirely self-contained.


How can I get LOAD DATA LOCAL INFILE to work for MySQL 8.0.21 on a Windows machine? I've created a file: 'C:\fff.txt' which contains data for my database table.
Then I'm trying to load data from that file like this:
LOAD DATA LOCAL INFILE 'C:\\fff.txt' INTO TABLE fff
LINES TERMINATED BY '\r\n';

but I get the following error all the time:
ERROR 2 <HY000>: File 'C:\fff.txt' not found (OS errno 2 - No such file or directory)

I've added local_infile=1 to [mysql] and [mysqld] sections of my.ini file.
When I type: SHOW GLOBAL VARIABLES LIKE 'local_infile'; I can see that it's set to 'ON'. Is there a way to solve this problem?

Windows restricts the acces for that folder.
Best is to create a separate folder and give it the right access rights.
I tried a lot but i can't replicate your message, when the file exists it can open it and insert the data.
so the basic steps
Open your my.ini file
and find, so put there the new created folder

"c:/my_folder/"

in double quotes
# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/"

restart your server
place there your fff.txt
Which should look like this
test
test
test
test
test
test

and then run
LOAD DATA INFILE 'c:/my_folder/fff.txt' INTO TABLE fff
LINES TERMINATED BY '\r\n';

and sex rows are inserted.


Database Engine and SSISDB architecture advice I'm seeking some architecture advice involving the database engine and Integration Services components of SQL Server 2017.  We have an existing SQL Server environment which is due for an upgrade and has historically been used in a less than ideal way.  With an upgrade to a new SQL Server environment now on the horizon, as a systems administrator I want to use this opportunity to rethink the existing architecture to provide a more robust flexible environment going forward.

The Current Environment

A SQL Server 2012 Enterprise instance running on a 2-node Windows Failover Cluster.  The database engine is clustered and DTS packages are stored in a clustered volume and these packages are executed as jobs via the SQL Server Agent.  They do not use the explicit Integration Services component

The Problem

Staff who administer the data warehouse and create/manage the DTS packages have traditionally insisted on working directly on the clustered server nodes via RDP.  This creates access issues with the systems admins who are responsible for the servers themselves.  The data staff also require a variety of software tools which has led to persistent disk space issues over time.  Additionally, many of these software tools are not cluster-aware and are only installed on one node, effectively leaving us with a crippled cluster right from the start.  The result is that any time there is a planned or unplanned failover, there is pressure to get the original primary node back online asap because most jobs are failing because they rely on local software which is not cluster-aware.

A Proposed Solution

Install the database engine alone on its own cluster with no RDP access allowed by any staff.  They will receive just the SQL Server clustered instance endpoint URL.  Install the Integration Services component on a separate server and propose they begin using the SSISDB catalog to store their packages instead of the file system.  My understanding is that there are many advantages to using the SSISDB.

Questions

This only shifts the RDP access issue to another server.  Because the data staff collaborate in real-time on packages and rely on all the same software tools, they argue they must have direct access to the server in order to provide a shared identical environment.  My belief is that there must be a more modern design approach where they can use their development tools on their local workstations and connect to SSIS using the SSMS, where the jobs will be published and scheduled/executed.  Any software that packages rely on (ftp, ssh, perl, python, etc) would be installed and managed by server admins on the SSIS.  Since packages would be stored in the SSISDB, there would no longer be a need for the shared storage which has traditionally housed the package files and this should remove the need for any direct RDP access.  Is my belief logical or is there still some argument for data developers to have direct server access?
Now the SSISDB becomes a single point of failure since the packages are no longer part of the failover cluster as they traditionally have been.  How would I build high availability into this component?  I've read about SSIS Scale Out features but that seems more about balancing load rather than fault tolerance.  I've begun reading about AlwaysOn availability groups, could that be a solution?

Any and all design advice is much appreciated.  Thanks.

This is a culture change as much as it a process change, so having management support you will be important, and ultimately shield you from some level of technical resistance and aid in broadcasting how this strategic shift will improve the lives of everyone.

Before we get into the specific case of the SSIS Catalog, let’s look at an overview of your options to better arm you with the knowledge to communicate why developers should change their system:

File system

Individual Package are stored on any folder, any location.
No security, files can be modified by any user or process without prior knowledge and no process natively exists to prevent this from occurring.
Not part of the regular backup schedule. System snapshots are not a viable backup and do not prevent accidentally modifications that can cause irreparable damage.
Must manually organize packages and no ability to group packages outside physical location and/or documentation
Forced to use Configuration Files to parameterize values

SSIS Package Store

Packages by default are stored specific folder “C:\Program Files\Microsoft SQL Server\<SQL Version Compiled>\DTS\Packages”. Can be moved.
Limited security; can be modified by elevated permissions
Not part of any backup strategy outside system snapshots (again, NOT A VALID BACKUP SOLUTION)
Can organize packages under Folders
Cannot use roles to restrict access to packages from the SSIS Package Store

SQL Server (msdb)

Packages are stored in msdb and can be exported to a file-share.
Security is tied to access to msdb.
System msdb is part of a valid backup solution.
Cannot be added to an AlwaysOn solution
Folders can be used to categorize
Can use Roles to manage permissions in 2016+ that avoid using sysadmin. Integration Services Roles (SSIS Service) - MS Docs

SSISDB Catalog

Packaged are stored exclusively inside SSISDB that is encrypted with a certificate
Security is further strengthened by separate permissions and roles (see above link).
SSISDB is part of a valid backup solution
SQL Server 2016+ has added support for AlwaysOn! SQL Server 2016 HA Series Part 2 – SSIS and Availability Groups – Blogs Microsoft
Mature management hierarchy that includes Parameters, Environments Variables.
Offers Full integration with Visual Studio
SQL Agent Jobs no longer directly use configuration file references or actual passwords (hope no one is doing this anyways) from being scripted out from SSMS.

  Ultimately, you can decide what path will work for your organization.
  SSIS Catalog is one of several methods, so getting your management to
  support you in this endeavor will go a long way to making changes.
  After describing an example scheme for deployment, the rest of this
  post will highlights some Visual Studio Management and SSIS Catalog
  Tips.

An example scheme could be the following:

All new efforts will use [Visual Studio] 2017 or 2015 for backwards compatibility and SQL Server Data Tools - (Download SQL Server Data Tools (SSDT) - docs.microsoft.com).
o   note in VS 2019 they added this as a marketplace component)
projects will be saved in the code management folder (GitHub folder) so efforts are versioned or they will use the SSIS Catalog on a DEV environment, though ideally use the GitHub Repository.
Package components are installed on each node for Dev/Cert environments first. The Enterprise DBA assists in gathering these downloads in a single file-share for ease of access/ transportation to target clustered servers and ensures they are on each target server.

BONUS SECTION:

Visual Studio Management Tips:

Your Package depends on both the Deployment Version (must match Integration Services of SQL Server instance)

And the 32-bit/64-bit configuration depending on the components used.

In order to use the Parameters, you must either user a package “parameter” (not variables) or Project Parameter. You an use these to environmentally control what connections are used.

You can connect and deploy to your target location (it doesn’t HAVE to use the SSISDB)

SSISDB Catalog Management Tips:

1)  You can Restore Versions with a click of a button to undo negative changes or even revert forwards!

2)  You can manage project parameters, validate, control versions from SSMS. No VS required.

Configure the Environment Parameters from here:

Passwords, unlike in the SQL Server Agent jobs, are securely protected inside the SSISDB database.

Example:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'<name of step>',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'SSIS',
        @command=N'/ISSERVER "\"\SSISDB\Replication\SSISDB_Replication\SSISDB_Replication.dtsx\"" /SERVER <ServerName> /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
        @database_name=N'master',
        @flags=0

3)  Each SQL Server Agent Job then uses these parameters after you select the environments set.

Pick the Package Source SSIS Catalog.

Set the environment for your variables.

In most cases, the parameters will automatically be set.

4)  Check what actually went wrong inside SSMS without resorting to RDPing into your server.

5)  See performance between runs that uses real statistics!

Am I missing anything? Feel free to make this a community answer.