How to find the columns and tables used in a stored procedure (SQL Server) I want to find all Tables alongside the columns used in a special Stored Procedure?

Can anyone help me here?

By the code below I found the tables but I want columns too

SELECT DISTINCT
o.id ,
o.name AS 'Procedure_Name' ,
oo.name AS 'Table_Name' ,
d.depid FROM
sysdepends d ,
sysobjects o ,
sysobjects oo WHERE
o.id = d.id AND
oo.id = d.depid ORDER BY
o.name ,
oo.name;

Update: You can get the information from a non-deprecated dynamic management function rather then the older dynamic management view, as pointed out by dfundako in the comments below. This would be the recommended approach as the function is available in all currently supported SQL Server releases and likely to be so in at least the next few releases too.

To update my previous example:

CREATE PROCEDURE test_depends
AS BEGIN
    SELECT TOP 5 ou_id, prop_text FROM tr_n_ou WHERE prop_name = 'display_name'
    SELECT * FROM tr_n_org WHERE parent_display_name = 'Global'
    EXEC some_stored_procedure
END
GO

SELECT ISNULL(referenced_schema_name+'.','')+referenced_entity_name+ISNULL('.'+referenced_minor_name,'') AS Dependency
     , ISNULL(so.type_desc, sn.type_desc) AS DependencyType
     , CASE WHEN ISNULL(so.type_desc, sn.type_desc) IN ('USER_TABLE', 'VIEW')
            AND referenced_minor_name IS NOT NULL
            THEN 'Column in '
            ELSE ''
            END
     + ISNULL(so.type_desc, sn.type_desc) AS DependencySubType
FROM sys.dm_sql_referenced_entities('dbo.test_depends', 'OBJECT') sr
LEFT OUTER JOIN
     sys.objects so
     ON  so.object_id = sr.referenced_id
LEFT OUTER JOIN
     sys.objects sn
     ON  sn.name = sr.referenced_entity_name
     AND sr.referenced_id IS NULL

GO
DROP PROCEDURE test_depends

The caveats about ad-hoc SQL still apply.

Also if a procedure references something that is user schema dependent the referenced ID will be NULL in the DMF which is why I'm doing the extra lookup by name in this example. If the EXEC line specified a schema (i.e. EXEC some_schema.some_procedure) then the ID would have a value so the ID lookup would work, otherwise there could be several procedures with the same name in different schemas and the one called will depend on run-type details. Note also that the name lookup will return multiple rows for the same dependency in this case if there are multiple procedures of the same name.

________________________________________

Previous answer:

You can get this information, mostly, from sys.sql_dependencies but be aware that this is marked for deprecation so may not be available in future versions of SQL Server.

To test in one of your databases do something like:

CREATE PROCEDURE test_depends
AS BEGIN
    SELECT TOP 5 column1, column2 FROM some_table WHERE column3 = 'some value'
    SELECT * FROM another_table
    EXEC some_stored_procdure
END
GO

SELECT sd.*, so.name, sc.name
FROM   sys.sql_dependencies sd
LEFT OUTER JOIN
       sys.objects so
       ON  so.object_id = sd.referenced_major_id
LEFT OUTER JOIN
       sys.columns sc
       ON  sc.object_id = sd.referenced_major_id
       AND sc.column_id = sd.referenced_minor_id
WHERE sd.object_id = (SELECT object_id FROM sys.objects WHERE name = 'test_depends')

GO
DROP PROCEDURE test_depends

You will see that is correctly lists the individual columns for the first table the procedure references, all columns in the second table as that selects *, and the procedure it calls.

Note that this does not deference views so you need a recursive query to get down to the base tables if that is your requirement, and you'll need to dig further too if a procedure calls other procedures.

Note again that sys.sql_dependencies is flagged as deprecated. I ran the above on a 2008r2 instance, the documentation on MSDN seems to list it only as far as 2014 so it may not be present in 2016 (I don't have access to any instances above 2012 in my current location in order to test this) though Kris notes in comments below that the view is still present in 2016. The replacement (sys.sql_expression_dependencies) doesn't list individual columns, at least in my test on a 2008r2 instance (the is only one entry per table with the minor ID 0 is both cases).

Another important thing to consider is that this view can't contain any references made by ad-hoc SQL run via EXEC (<string>) if you have such evilness in your procedures.


SQL Server query with date range in WHERE - execution plan uses clustered PK instance not date column index I have the following query in SQL server:

declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT
    my_date = a.itemTimestamp
FROM
    my_table a with(nolock)
WHERE
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)

It's extremely slow (90s for < 2000 records), because the execution plan uses the clustered index of the PK column for 'my_table':

Why is this happening and how can I optimize the query performance?

What is the datatype of itemTimestamp? Is that column indexed? Is the column nullable?

Assuming the answers are datetime, Yes, and No respectively you might consider

WHERE  a.itemTimestamp >= isnull(@timestamp_start, '17530101')
       AND a.itemTimestamp < isnull(@timestamp_end, '99991231')

If you were to change to datetime2 and actually store dates before 1753 you would need to change the lower bound condition to 00010101. This also won't bring back dates matching the higher bound of 9999-12-31 - but in the real world this is unlikely to cause a problem.


Grouping data based on cumulative sum I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using Postgresql.

I have price data per day.

CREATE TEMP TABLE Price (id,Day, Price) AS
VALUES
  (1, 1, 40),
  (2, 1, 20),
  (3, 1, 50),
  (4, 1, 10),
  (5, 1, 20),
  (6, 1, 60),
  (7, 2, 10),
  (8, 2, 40),
  (9, 2, 10),
  (10,2, 20),
  (11,2, 10);

I want to assign numbers (1, 2, 3...) to the price data based on the day and the sum of the prices. Every time when the sum > 60, the sum calculation starts again + every time when a new day is reached, the sum calculation starts again. So for example:

Row 1 [day 1, price 40] = 1. Then for row 2 [day 1, price 20] the price sum is 20 + 40 < 61, therefore row 2 also gets assigned to 1. Then for row 3 [day 1, price 50] the price sum is 20 + 40 + 50 > 60, therefore the counting of the sum has to restart and number 2 is assigned to row 3. The result will look like:

Does anyone know if this is achievable and how? I understand how to take the SUM(Price) OVER (PARTITION BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), but do not know how to restart the sum counting when the condition is met. Thank you in advance for the help!

You want something like this,

SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
  SELECT
    id,
    day,
    price,
    sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
  FROM price
) AS t;

Working from the inside out we calculate the cumulative sum with sum() in a window function,

SELECT
  id,
  day,
  price,
  sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
FROM price;

 id | day | price | cumsum
----+-----+-------+--------
  1 |   1 |    40 |     40
  2 |   1 |    20 |     60
  3 |   1 |    50 |    110
  4 |   1 |    10 |    120
  5 |   1 |    20 |    140
  6 |   1 |    60 |    200
  7 |   2 |    10 |     10
  8 |   2 |    40 |     50
  9 |   2 |    10 |     60
 10 |   2 |    20 |     80
 11 |   2 |    10 |     90
(11 rows)

Notice our cumsum resets on a new day, but it persists past your 60. We need to fix that, for that we simply look at the prior row, and divide by 60. We don't want to start a new group, unless the last row exceeds a multiple of 60.

SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
  SELECT
    id,
    day,
    price,
    sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
  FROM price
) AS t;
 id | day | price | cumsum | grp
----+-----+-------+--------+-----
  1 |   1 |    40 |     40 |   0
  2 |   1 |    20 |     60 |   0
  3 |   1 |    50 |    110 |   1
  4 |   1 |    10 |    120 |   1
  5 |   1 |    20 |    140 |   2
  6 |   1 |    60 |    200 |   2
  7 |   2 |    10 |     10 |   0
  8 |   2 |    40 |     50 |   0
  9 |   2 |    10 |     60 |   0
 10 |   2 |    20 |     80 |   1
 11 |   2 |    10 |     90 |   1
(11 rows)

And you're done.


Oracle dbms_redefinition fails with ora-01749, why? I ran this as a part of redefining a table

DECLARE
  l_num_ers PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'myuser',
    orig_table        => 'mytable',
    int_table         => 'redef',
    copy_indexes      => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers     => TRUE,
    copy_constraints  => TRUE,
    copy_privileges   => TRUE,
    ignore_errors     => FALSE,
    num_errors        => l_num_ers);
   DBMS_OUTPUT.put_line('l_num_ers=' || l_num_ers);
END;
/

And got the error

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
ORA-06512: at "SYS.DBMS_REDEFINITION", line 752
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1698

So how can I solve this?.
The same code worked on another database where the only difference I've found is that the user myuser did not have select right on the table.

I did

Select PRIVILEGE, GRANTEE from dba_tab_privs where owner = 'MYUSER' and TABLE_NAME = 'MYTABLE';

To find the differences.

Is the solution to revoke my own privilege to the table?

Having SELECT privilege on your own table is unnecessary.

Granting privileges on your own objects added extra rows in the dictionary in earlier versions and it was considered as a bug, fixed in newer releases.

This note decribes this and why redefinition fails becasue of this:

Bug 8984274 - ORA-1749 Error Running DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (Doc ID 1345539.1)

Just revoke the privilege on your own objects.


How concat two sql query in a single result I have two sql query

First:

SELECT ticketing_ticket.email,
       CONCAT(
              TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
              MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
              MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
             ) AS 'temps de traitement '
FROM ticketing_ticket
WHERE DATE(date_close) = CURDATE();

Output:

   |  email       |       Time
   |test@test.com | 15 jours 23 heures 7 miinutes
   |test@test.com | 15 jours 22 heures 54 miinutes

Second:

SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := id_parent FROM ticketing_category WHERE id_category = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 38, @l := 0) vars,
        ticketing_category m
    WHERE @r <> 0) T1
JOIN ticketing_category T2
ON T1._id = T2.id_category
ORDER BY T1.lvl DESC

Output:

 |          Domaines           |
 |Elections;Aides et démarches |

How Can i combine my sql query  in a single result like this

|  email       |       Time                        | Domaines
|test@test.com |    15 jours 23 heures 7 miinutes  | Elections;Aides et démarches
|test@test.com |    15 jours 22 heures 54 miinutes | Elections;Aides et démarches

I assume you can join the queries one way or another (I see no correlation so I'll assume a cross join):

SELECT ...
FROM (
    SELECT ticketing_ticket.email,
    CONCAT(
          TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
          MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
          MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
    ) AS 'temps de traitement '
    FROM ticketing_ticket
    WHERE DATE(date_close) = CURDATE()
) AS X
CROSS JOIN (
    SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM (
            SELECT @r := 38, @l := 0) vars, ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) y;

Mixing old "," joins and ANSI joins makes it difficult to read the code. so I'll suggest that you remove the ";" joins:

SELECT ...
FROM (
    SELECT ticketing_ticket.email,
    CONCAT(
          TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
          MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
          MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
    ) AS 'temps de traitement '
    FROM ticketing_ticket
    WHERE DATE(date_close) = CURDATE()
) AS X
CROSS JOIN (
    SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM ( SELECT @r := 38, @l := 0) vars
        CROSS JOIN ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) y;


Change ID(int) to UUID I have a large database with many relationships. The database can be rebuilt and a migration script for an already existing data base

For security reasons, the URL should not show the ID(int) only UUID

Is there any good advice for such a situation?

converting all IDs to UUID(if so how to do it efficiently?)
can I create an additional column in each UUID table and use it at the address? (if so how to do it efficiently?)
other suggestions?

can I create an additional column in each UUID table and use it at the address?

You can add an alternate key using a UUID like this:

alter table SomeTable add uuid uniqueidentifier unique not null default newsequentialid()

Which will add minimal overhead, and provide efficient single-row lookups.


Composing the "ultimate database maintenance script" I'm trying to compose T-SQL script that will:

A) Shrink database size to the minimum possible size;
B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a "fresh" state.

Here is what I currently have:

-- Check database integrity:

DBCC CHECKDB WITH NO_INFOMSGS;
GO

-- Get space usage information:

EXEC sp_spaceused @updateusage = N'TRUE';
GO

-- Shrink database:

DBCC SHRINKDATABASE (0, 0);
GO

-- Reindex all tables:

EXEC sp_msforeachtable N'PRINT ''Indexing table: ?''; DBCC DBREINDEX (''?'');'
GO

-- Update statistics for all tables:

EXEC sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
GO

-- Clear procedure cache:

DBCC FREEPROCCACHE;
GO

-- Update all usage info in the database:

DBCC UPDATEUSAGE (0);
GO

Can you please validate if all these are correct? The precedence of commands (e.g. updating statistics after index rebuild, or vice versa), syntax, arguments, etc. are all optimal? Can you suggest any other commands? Performance is not a problem, I'll be executing this script probably once in a month, night hours, so even if it takes hours it doesn't matter. I just need the maximum optimization of the database. Any input would be greatly appreciated.

Thanks!

I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server.

First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, until the database needs it again? This will cause fragmentation and make your reindex work harder. More on that in a minute.
Next, you reindex all tables in full, whether they need it or not. It is usually much better to analyze the tables first, and perform conditional actions on specific indexes (e.g. reorganize or rebuild) when necessary.
Since you're rebuilding all of the indexes, and an index rebuild requires - on average - 1.5x the space occupied by the index, where do you think it's going to get that space? You've just shrunk the database, so there is no free space. You shrunk the database for nothing because now it's going to have to grow to provide some space to accommodate the index rebuilds.
Then you update statistics with fullscan. You just rebuilt all of the indexes. Do you think updating statistics is necessary or helpful at this point? I don't, since rebuilding an index automatically updates its statistics. So this extra step is just wasteful.
Finally, clearing out the entire proc cache is unlikely to ever lead to overall performance improvement. It may be the case that you get an improvement in a few queries, but it will be much more effective IMHO to sniff those out and clear them out individually (you can manually clear plans in modern versions of SQL Server without throwing the baby out with the bathwater).

I would have to say that what you've got so far is far from optimal and there are much better solutions out there that will help you optimize your database in much more efficient and less wasteful ways. Check out Ola's solution, SQLFool's solution, and SQL Sentry. These don't shrink your database but they do optimize your indexes in an intelligent way and you shouldn't be shrinking your databases anyway.


Sql Server 2008 : What is the cost of accessing an index? I've just read this interesting blog post about selectivity notion. In the section 'When is better to not use a database index?', the author states :

  Accessing the index takes time, and consumes resources. If we are
  accessing the index 5,000 times, it is actually faster to just
  directly access the table and do a full table scan.

which ressources the author is speaking about here ? shoul I bother about this ? or it's up to the RDBMS to manage all this for me ?

That is because SQL Server reads entire pages instead of records and when a index is used SQL Server might need to follow the bookmark to fetch the fields that are not included in the index.

So if your query isn't selective enough, it might end up reading all the pages of the index, and following the bookmarks to read all the pages of the actual table too. This ends up being more expensive than just reading all of the pages of the table.

This effect can be somewhat mitigated using included columns in which some columns are included in the index, but aren't part of the index (i.e. they have no effect on the sorting of the index).

SQL Server estimates the selectivity of your query based on statistics.

This article explains a lot of it. And the stairways series really is a good starting point to understand how indexes work.


Query against history table not using index (Oracle 12c) I have a history table which has an 'ID' and 'TIMESTAMP' column as such

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER, -- this is the id of the table that is being tracked
  --OTHER COLS
);

I also have an index on this table as such

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

This table has a lot of inserts against it and currently has about 30m rows in it.

When I try to run the following query, oracle does a full table scan instead of using the index (which .. at least I believe .. it should be able to use).

SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;

It seems to me that Oracle should be able to use the index to quickly identify which id/timestamp pair is just to the "left" of a specific point in time quickly via looking at the id/timestamp index on an id-by-id basis, but it's insisting on a full table scan.

Any help would be appreciated to get this query running quicker.

I have ran the following to make sure the statistics were up to date

EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');

Also, there are about 1k distinct ID values in the hist table.

With regard to data distribution..  Of the ~1k IDs, 50 have less than 100 entries in the table, 70 have between 100 and 1000 entries, 146 have between 1000 and 10000 entries, and the rest range from 10k to 60k entries. Over half of the entries have at least 30k records.


Disk Architecture on SQL Server I am building a new environment using the SQL Server 2017 Always-On system.

This will be active 24/7 and there will be lots of DML processes.

I have a 100GB SAS for the OS, a 1TB SAS for Data and a 1TB SSD disk for the TempDB and log files.

C:/ - 100GB - SAS
D:/ - 1TB - SAS - Data files
E:/ - 1TB - SSD - Log/TempDB Files

Is the above structure correct for best performance/best practice? Or should I change Log/Tempfile to SAS instead of Datafiles ?

For a single production database, how many Data and Log files do I need to create? 1 data file and 1 log file is best practice?

Storage perspective Best Practices :

Have separate drives for Data,Log and Tempdb.
Do not keep the log files and Tempdb files on same drive as Tempdb is very active and needs good IOPS all the time. So add another drive for Tempdb.
If you can, keep data files too in SSD. This will enhance your performance in case the db is very read/write intensive.
Enable Instant File Initialization.

Also I want to ask that for single production database how much Datafile/logfile need to create? 1 datafile and 1 logfile is best practice?

Adding multiple log files does not help or improve the performance as
the logging is done sequentially. Paul Randal has a great blog article where he proved why multiple log file is bad for you.
On data files, having one mdf file is good enough. If your database will be huge then later go for filegroup partition. There may or may not be any improvement in performance if you add multiple datafiles for user databases which is totally unpredicatable.Another blog from Paul Click here where he answers this. In my environment we only use single datafile unless we run out of space and an extra .ndf needs to be added directing data to another drive.
Below is a snippet from Microsoft Documentation :

Storage
Use different disks or disk partitions for the following:

Windows operating system.
Data files for the system databases.
Log files for system and user databases.
Data and log files for the TempDB database.

For optimal read/write performance, make sure that disks that are used for SQL Server data files are formatted using 64 KB block size.

Microsoft Documentation


you should normalize your tbales
the columns order_items.type and orders.order_type should be Tinyint  and you need also two small tables ordertype and orderItemtype that canes joined when the text is needed
Schema (MySQL v5.7)
CREATE TABLE order_items (
  `id` INTEGER,
  `order_id` INTEGER,
  `item_id` INTEGER,
  `quantity` INTEGER,
  `type` VARCHAR(6)
);

INSERT INTO order_items
  (`id`, `order_id`, `item_id`, `quantity`, `type`)
VALUES
  ('1', '1', '1', '10', 'Accept'),
  ('2', '1', '2', '15', 'Accept'),
  ('3', '1', '3', '20', 'Reject'),
  ('4', '2', '1', '5', 'Accept'),
  ('5', '2', '2', '10', 'Accept'),
  ('6', '2', '4', '10', 'Accept'),
  ('7', '3', '1', '2', 'Accept'),
  ('9', '3', '2', '5', 'Accept'),
  ('10', '4', '1', '5', 'Accept'),
  ('11', '4', '2', '10', 'Accept');

CREATE TABLE orders (
  `id` INTEGER,
  `order_type` VARCHAR(8),
  `order_from` INTEGER,
  `base_order_if_purchase` VARCHAR(4),
  `amount` INTEGER,
  `status` VARCHAR(10)
);

INSERT INTO orders
  (`id`, `order_type`, `order_from`, `base_order_if_purchase`, `amount`, `status`)
VALUES
  ('1', 'sales', '1', NULL, '8500', 'incomplete'),
  ('2', 'purchase', '1', '1', '2500', 'complete'),
  ('3', 'purchase', '1', '1', '5000', 'complete'),
  ('4', 'sales', '1', NULL, '7000', 'incomplete');

Query #1
SELECT
    `row_num`,`item_id`
    ,SUM(`sumitem`)
   FROM
   (SELECT
         `item_id`,o.`row_num` , IF (`order_type` = 'sales',`quantity`,-`quantity`) sumitem
    FROM order_items o_i
   INNER JOIN (  SELECT o.`id`, o.`order_type`,row_num
  FROM `orders` o INNER JOIN (
  SELECT `id`, @rn := @rn + 1  row_num FROM orders ,(SELECT @rn := 0) a WHERE `base_order_if_purchase` IS NULL ORDER BY `id`) o1
  ON o.`id` =  o1.`id` OR o.`base_order_if_purchase` =  o1.`id`) o ON o_i.`order_id` = o.`id`
   WHERE o_i.`type` = 'Accept') t1
  GROUP BY `row_num`,`item_id`;

| row_num | item_id | SUM(`sumitem`) |
| ------- | ------- | -------------- |
| 1       | 1       | 3              |
| 1       | 2       | 0              |
| 1       | 4       | -10            |
| 2       | 1       | 5              |
| 2       | 2       | 10             |

View on DB Fiddle


MySQL Database replication for high availability configuration:

1 balancer
2 Azure machines(4 gb ram, 2 cores), with MySQL master-master replication enabled.
X users connecting to the balancer to sync data.

test:

i've tested the MySQL replication with loader.io (for a connection spike), for 6000 calls for 1 min to the balancer  (100 conn per 1 minute test), and with the current setup both dbs sync in about 6-7 mins (all 6000 syncs)

scenario:

A client should sync it's data from last connection no matter what machine the balancer distributed his connection to, and that db for example might not have finished the previous sync period yet (let's say the 6 minute, as in the above example).
I guess a sticky balancer solution might not help(Nginx, HAProxy) and Azure balancer is not persistent\sticky anyway (for example user is connected to 3G, 4G and IP changes frequent)..

problem:

how do i get the current replication sync the fastest way possible between both machines?
is there a way to transform my current setup to synchronous replication?
is there any other solution (preferable windows OS) that does synchronous sync (like: semisync replication )?

There are currently two options for MySQL 'synchronous' replication.

The first option meets your requirement of "windows OS" fairly well: MySQL Cluster, as long as you use MySQL Cluster 7.1.3+, but might have other limitations depending on your use-case.

The second is Galera, which can be used via normal MySQL, using Percona XtraDB Cluster, or MariaDB. This is the reason I put 'synchronous' in quotes above, due to the certification process Galera undergoes.

There are pros and cons of using MySQL Cluster as well as Galera. Therefore, I leave to you the requirement to research them for your purposes.


How to change the datatype of column without deleting it indexes; I am looking for changing the datatype of a field from nvchar(20) to nvchar(MAX), but it retains the below message.
my questions here:
Deleting the index will affect on the data?
Can I restore the index after deleting it and changing the data type?
Is The index in the screenshot below the only index that will be deleted?
I will be very appreciated for any help :)
Best,
Lubna

The data type of columns included in an index cannot be changed unless
the column is a varchar, nvarchar, or varbinary data type, and the new
size is equal to or larger than the old size.

With this info from the knowledge base a suggestion could be to alter column to a nvarchar(4000) or something like that.
Remember that you can't index a varchar(max) or nvarchar(max) field.


Stored Procedure with Table Value Parameter I'm attempting to write a stored procedure that will have two parameters.

ComputerName | nvarchar
Monitor_Serial | Table-valued parameter (TVP)

I'm using a TVP because some computers have an unknown number of monitors.

I would like my stored procedure to take each serial number in the Monitor_Serial and insert a new line in my table with the associated ComputerName (exactly how a foreach loop would work in programing).

How would I do this? When I was doing some research I saw the use of a cursor mentioned a few times but I'm not sure if this is what I should be using. Any suggestions would be greatly appreciated.

A cursor should very rarely be your first approach. Especially if you've gone to the trouble of making a table type; the primary goal there is to avoid row-by-row processing, splitting strings, etc.

Making some guesses about the destination table, its columns, and the name of the column in your TVP, you can insert multiple rows in the destination table by using INSERT/SELECT:

INSERT dbo.destination_table
(
  computer_name, serial_number
)
SELECT @ComputerName, column_name
  FROM @Monitor_Serial;

To make sure you only insert new serial numbers, use:

  FROM @Monitor_Serial AS m
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination_table
    WHERE serial_number = m.column_name
    -- AND computer_name = @ComputerName
  );

(You may or may not need that commented line, since it's unclear if you want to prevent any duplicates overall or just for a specific computer name.)


mysql workbench connection type tcp/ip over ssh with libpam-google-authenticator enabled I'm attempting to connect to my MySQL Database using the Connection Method "Standard TCP/IP over SSH". I filled out all the SSH and MySQL connection details correctly, however I get the message: "Authentication error." because I'm protecting SSH with Two Form Factor Authenticator (libpam-google-authenticator). Is there another way to connect and resolve this issue?

You can still use Google Authenticator with mysql workbench, but you will need to change how SSH and Google Authenticator work together.  When you login via SSH, you probably enter your password and then you are prompted for your Two-Factor pin.  You want to change this so that you enter your password and pin, one directly after the other, on one line as the password:

[my-password][two-factor-pin]

This will work with MySQL Workbench TCP/IP over SSH.  I modify /etc/pam.d/sshd to use the forward_pass option for the Google Authenticator module.  You will need to put a line like this at the top of /etc/pam.d/sshd:

auth required pam_google_authenticator.so nullok forward_pass

Google Authenticator will authenticate the pin, strip it from the password, and pass the password along to the other PAM modules.


SQL Server Replication questions This is kind of the part 2 of Replicating a SQL Server database & Optimization that I started earlier this week, but I believe it owes to be a different questions now that I have a clear idea of what we are trying to achieve.

Currently we have one box running our application and database server. We about to increase our capacity to two servers. One on DC1 and the other on DC2. Both will have our application server and database running.

What we need:

Not have boxes sitting around doing nothing waiting for a failure.
Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails
Sensible fail-over times (e.g. a five minute fail over is not sustainable)

We can sustain some (sensible) permanent data loss in certain areas of our application. Anything up to a few seconds is fine. If its temporary loss e.g. pointing to an db image valid ten minutes ago is ok as long as the data recorded within those ten minutes make their way to their database at some point.

OPTION 1

Point app server to local db. If local db is down then point to remote db. e.g. App Server A points to DB A. App Server B points to DB B. If DB A goes offline then App Server A points to DB B. If Box A goes offline then Box B continues as it is.

Reasons we like this: Does really well on criteria A, B and C

OPTION 2

Application servers are pointed to one combined DB. This is the combination of DB in box A and DB in box B. No sweat if one database fails.

Reasons we like this: Does well on criteria A, B and C

But:

Disadvantage is that we get Application Server A pointing to box B so increased latency

OPTION 3

Application servers are pointed to one DB, say on box A. So application server A points to DB A and application server B is pointing to DB A. We use database B for read only reporting. On DB A failure applications are automatically switching and point to DB B which is now used for everything, not just reporting.

Reasons we like this: Still does well on criteria A, B and C

What I would like to know is how are the options above implemented with a SQL Server 2012 Enterprise. Mind that the two boxes are on different data centers but IF needed we can change that. Also, if there is a better option that we haven't thought about.

Databases image size is currently in excess of 150GB. There are some very frequently updated tables (the analytics tables are inserted sometimes 50-100 times a second).

Let me know what you think

Thanks,
Yannis

IN SQL Server 2012 Enterprise edition, you can use AlwaysON Availability groups to achieve what you want.

If you have 2 servers set up, then you can configure AlwaysON.

Primary server will be your main and secondary (Replica) - when configured as "Readable" will allow you to redirect all "Read Intent" requests to secondary replica as well as the secondary replica is a read-only replica.

Note that, your clients are connected to the Availability Group using Listener, which allows seamless failover for your application.

In case of disaster you have all your concerns covered

  A. Not have boxes sitting around doing nothing waiting for a failure.

The secondary will be used for read intent requests and will server as a standby server in case the primary fails.

  B. Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails

When you use synchronous mode, it will allow you for automatic failover. In my testing, asynchronous mode (without automatic failover - manual failover)  has proven more benefits in terms of performance. SO depending on the data loss allowed by your company and the downtime, you can choose between them.

AlwaysON allows you to Configure Flexible Failover Policy to Control Conditions for Automatic Failover

  C. Sensible fail-over times (e.g. a five minute fail over is not sustainable)

AlwaysON will provide failover in terms of seconds, but it depends on your n/w.

EDIT :

If you implement AlwaysON (which fits in your situation), below is my recommendation:

Assuming (from your post) DB1 is the main database and DB2 is for reporting.

Configure DB1 as primary in AlwaysON. You get a warm standby database on the secondary server (say DB1_Replica).

Configure DB1_Replica as readable and have asynchronous-Commit mode configured.

  Under asynchronous-commit mode, the secondary replica never becomes synchronized with the primary replica. Though a given secondary database might catch up to the corresponding primary database, any secondary database could lag behind at any point. Asynchronous-commit mode can be useful in a disaster-recovery scenario in which the primary replica and the secondary replica are separated by a significant distance and where you do not want small errors to impact the primary replica or in or situations where performance is more important than synchronized data protection. Furthermore, since the primary replica does not wait for acknowledgements from the secondary replica, problems on the secondary replica never impact the primary replica.

The listener is going to help you with seamless failover.

Refer to below links to get you started :

Failover and Failover Modes (AlwaysOn Availability Groups)
Before You Fail Over a SQL Server AlwaysOn Availability Group - By Brent Ozar
WhitePaper : Offloading Read-Only Workloads to Secondary Replicas
Configure SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing using T-SQL


Keeping data in old Database (SQL 2008) identical to new Database (SQL 2016)? I am moving database to new server.

New server has SQL Server 2016 running, current server has SQL Server 2008.

I need to have backup/fallback plan in place for few weeks (in case something goes wrong - I can switch my application back to 2008 Server and continue working).

I am wondering is it possible to enable replication (or alternatives?) and have all transactions/merges be in sync on 2008 Instance so both databases are up to date in case I need to switch to old one?

Due to business requirements, we must be able to fall back to SQL Server 2008.  This is non-negotiable.

Converted from comments:

  According to Replication Backward Compatibility, Transactional Replication will not work between a 2016 Publisher and a 2008 Subscriber, but Merge Replication would be supported. Check out Merge Replication to see if that will suit your needs. – Scott Hodgin

  Or Upgrade to 2014 and it will be backward compatible to 2008 for transactional replication. Once You have a steady ship, you can move ahead to later versions if needed. – Ramakant Dadhichi


DELETE/TRUNCATE from a large table makes MariaDB crashing I try to execute the two following queries in MariaDB :

DELETE FROM `db_test`.`table1`;
DELETE FROM `db_test`.`table2`;

The problem is : deleting from the first table is ok (it takes a few seconds, nothing not usual), but deleting from the second table seems impossible. The query runs for a long time (more than 12 hours), then MariaDB crashes. The only solution here is to reinstall completely MariaDB, because MariaDB service won't launch anymore.

I think that's because table2 is very large, at the opposite of table1 :

table1 contains 135k lines, 18 MiB
table2 contains 6,2 millions of lines, 1,1 GiB

The same goes with TRUNCATE. The software I use is HeidiSQL.

Do you happen to know how to handle tables like this one ?

You can follow below steps, it may help you.

CREATE TABLE db_test.table2_tmp LIKE db_test.table2;

]RENAME TABLE db_test.table2 TO db_test.table2_to_be_deleted;

RENAME TABLE db_test.table2_tmp TO db_test.table2;

DROP TABLE db_test.table2_to_be_deleted;


Grant permissions to only specific tables in a Schema I am having a schema where I wanted to grant permissions in the following manner. What is the right way of doing this? To keep this simple, I am putting an example.
SCHEMA - HR
TABLES:

GEO_EMPLOYEE
GEO_SALARY
REGION_EMPLOYEE
REGION_SALARY

Now, I have two roles HR_EXEC and HR_GEN. The HR_EXEC role will have access to the entire schema which I kind of grant at the schema level. But for the HR_GEN role, I would like to inherit from HR_EXEC role and at the same time have access to schema and all the tables except denied to 'GEO_' tables. How do I do it in a better way? Should I be creating some DENY statements for each 'GEO_' tables in this case? I have 100 tables like this.
I am looking specifically for a SnowSQL based approach here but I guess this concept would be same for any SQL environment.

In the standard SQL there is no way to explicitly exclude some privileges.
Some RDBMS have the DENY command (MS SQL Server as an example) to do that.


What is the last version of postgres with pgadmin 3? For some reason I cant get used to the pgadmin 4 in web version. I found it very slow. For thing I want do fast I rather have pgadmin 3 even if that mean work with previous version of postgres.

So what was the last version of postgres with pgadmin 3?

While you may be strongly discouraged from continuing to use pgAdmin 3, there's nothing stopping you if you really want to use it. Below you will see pgAdmin 3 connected to a postgresql 12 instance.

Note however, that you're very much doing this at your own risk, you're going to have to click through a lot of error messages when running in an unsupported setup like this.

Some pretty obvious in retrospect...

Some just annoying...

In general I find a lot of these errors stem from breaking-change updates to the system catalogs between 9.* and 10+. Whether or not they're deal breakers to using this configuration is left to you. If you do decide to press ahead and try to use this setup, there will be a lot of heartache (for example, pgAdmin 3 as provided just won't run on macOS Catalina, end of discussion).

Around these parts you will hear a lot of the "just use psql" line. I use psql and I love it. But sometimes it's nice to have a GUI. I use pgAdmin 4 and I have a love-hate relationship with it. The pgAdmin line has been "meta-stable" IMO for quite some time and I imagine it will remain so for all but the most power-users for at least a bit.


Access Denied for root MySQL Disk space was full on my server and I needed to do a mysqldump. I tried logging into MySQL, but temporarily forgot the password. I then tried using the --skip-grant-tables & , but I received an error and it did not work. I tried several times.

I finally got hold of the correct password, but I encountered an issue that:

Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock'

Somehow I was able to get rid of that error through lots of trial and error, but now I can still not login.

Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost'(using   password: YES)

I need to reset the password for MySQL without losing data.

Looks like your socket file is missing

When that happens, you must connect to MySQL using TCP/IP as follows

mysql -uroot -p -h127.0.0.1 -P3306 --protocol=tcp

Specifying the port number and protocol, this will connect to MySQL with or without a socket file.

I have suggested this method many times when mysqld is running without a socket file

NOTE: Restarting MySQL will bring back the socket file. Usually, starting MySQL when mysqld is already running is what usually cause the socket file to disappear. (See Is there anyway that I can manually create a PID for the MySQL instance?)


How to do partition on a non clustered index column? I have table ORDERS .Where there are Million rows in it .I need to add Pationed Index on Order to store data in one of two files based on the new IsLocked field.But when i checked table there is a Non clustered index on ISLocked Column.IsLocked field consists of 1 and O's.Can you guys suggest me best approach.

CREATE TABLE [Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderNo] [varchar](50) NULL,
    [AccountNo] [varchar](20) NULL,
    [CompleteDate] date null,
    [LastUpdateDate]) date null,
    [AccountID]  int NULL,
    [IsWarranty] [bit] NULL,
    [IsEmailAddressFound] [bit] NULL,
    [IsLocked] [bit] NOT NULL,
    [LinkOrderID] [int] NULL
)

But already there is a

CREATE NONCLUSTERED INDEX [IX_IsLockedAccountID] ON [coe].[Orders]
(
    [IsLocked] ASC,
    [AccountID] ASC
)
INCLUDE (     [OrderNo],
    [CompleteDate],
    [LastUpdateDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Orders    IX_IsLockedAccountID    NONCLUSTERED    72057594556710912    1    1146462

Can you giys please help me how to do partioning as i am completely new to Table partioning .

Strictly speaking creating a non-clustered index on a partitioning scheme is done exactly as the clustered index, much as Thomas already showed you:

CREATE NONCLUSTERED INDEX <indexname> ON <tablename>
(<columns>)
INCLUDE (<columns>)
ON <partitioning scheme>;

Having a non-aligned non-clustered index is usually not recommend, see Special Guidelines for Partitioned Indexes:

  Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. This is especially the case when the index is not aligned with its base table or is not aligned with its clustered index, if the table already has a clustered index applied to it.

Similar memory problems also manifest in queries, not only during build, specially if you use versions prior to SQL Server 2012.

But the real problem is the futility of partitioning a non-clustered index by a boolean. Aside from the general issues with misunderstanding partitioning and abusing this very expensive and invasive feature (see How To Decide if You Should Use Table Partitioning), partitioning on a boolean is non-sense, especially on a column that appear to be very volatile (changes often) and highly skewed (far more 0s that 1s). Filtered indexes are probably what you're after. See Filtered Indexes vs. Table Partitioning.

And, finally, having an 'isLocked' column is never a sign of healthy data modeling. In fact a big code smell. You probably need to read about application locks and/or using tables as queues.


Modeling a Car Dealership context in an Entity-Relationship diagram I am creating a conceptual database model for a car dealership. I have spoken to the client, gathered all the requirements, and thought I had my Entity-Relationship diagram all correct; however, I realised I have no idea how to represent the requirements that I will detail bellow.
Scenario description
The dealership offers vehicle inspections and servicing and repairs. When this happens, a service ticket is generated. A service ticket belongs to a customer, is related to a vehicle, and has the following requirements (this is the part I can't figure out how on earth to draw):

A service ticket may contain either one or more services (identified as an entity type), inspections (recognized as an entity type) and/or repairs (classified as an entity type).

Questions
Having described my context of interest:

Could someone please point me in the right direction for how to model a relationship, and how it might be maintained in a database, where a given ServiceTicket must be related to, at least, one specific Service, one particular Inspection and/or one determined Repair, yet may also contain multiple instances of these kind of entities?

How do I express that a ServiceTicket might contain zero occurrences of all three of these entity types, but has to contain at least one of them?

This problem has been doing my head in. Initially, I defined a cardinality of zero-to-many between a ServiceTicket and the three of them, but realised that such depiction does not mean it must be connected with at least one of the three. My textbook does not seem to provide any answers for this diagram and I feel that to be a good DBA I must know how to represent this.

Service, inspection,  and repair are all instances of some work. If you really do need service, inspection,  and repair as separate entities, create a super-entity Work, which would have a 1..* relationship to a ticket, and make service, inspection,  and repair inherit from it (1..1). In many cases you will probably just have Work with the Type being one of service, inspection, or repair.


Using MVIEWS to take in time data snapshot of tables I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.

Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:

1) Is there a way through which i can refresh all the MVIEWs at once?

2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?

3) Is there a better way of achieving this? I don't have the option of using mirror database.

DBMS_MVIEW.REFRESH

  list - Comma-delimited list of materialized views that you want to
  refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.

  atomic_refresh - If this parameter is set to true, then the list of materialized views
  is refreshed in a single transaction. All of the refreshed
  materialized views are updated to a single point in time. If the
  refresh fails for any of the materialized views, none of the
  materialized views are updated.

Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.


Can you "su -" in MySQL? If I have root on a mysql database and I want to drop privileges to that of a normal user, without using their password, can I do it? if so how? think # su - username on unix. Basically, I'm just looking to avoid needing their password to be them, so I can test their privileges at their user. In postgres I could simply allow an ident authentication on the system root user, to bypass password auth. The reason I need this is to be able to reproduce a users problem by being them, not being them will not allow an accurate reproduction. I can of course ask for their password, but that takes more time that bypassing it.

I just realized -- so long as you don't mind locking out the user while you log in --

back up the mysql.user table (well, the user's hashed password, at the very least)
set their password to something you know : UPDATE mysql.user SET password=PASSWORD('new password') WHERE user='username' AND host='hostname';
log in as them
set their password back to what it was : UPDATE mysql.user SET password='saved password hash' WHERE user='username' AND host='hostname';

... you may need to flush privileges; after manipulating the mysql.user table.


Fetching Remote Encryption Key for MariaDB I've set up encryption at rest for MariaDB but I can't seem to get my key fetching service to fetch the key.
I have the following fetch script and a systemd service set up.
# Fetch the encryption file for MariaDB, restart service, then remove file
/usr/bin/wget -P /etc/mysql https://example.com/my_maria_key.key
/bin/systemctl restart mysql
sleep 10
rm /etc/mysql/my_maria_key.key
touch /tmp/mariadb.started
exit 0

And my systemd service file:
[Unit]
After=network.service

[Service]
ExecStart=/etc/rc.local

[Install]
WantedBy=default.target

I made sure I enabled the service and I can run the service using:
sudo service fetch_key restart

But it doesn't fetch the key.
What have I done wrong?

ADDING .CNF FOR REFERENCE

[mysqld]
plugin_load_add=file_key_management
file_key_management = ON
file_key_management_filename = /etc/mysql/secretkeyname.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm=aes_cbc
encrypt_binlog = 1
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
aria_encrypt_tables = ON
encrypt_tmp_disk_tables = ON
innodb_encryption_threads = 8
innodb_encryption_rotate_key_age = 0 # Do not rotate key

ADDING VARIOUS KEY GENERATION RESULTS

when using wget via sudo in the terminal:
644 -rw-r--r-- root root 620 mysecretkey.enc

letting the service download the key:
660 -rw-rw---- root root 620 mysecretkey.enc

generating the key with openssl:
644 -rw-r--r-- root root 336 mysecretkey.enc

Rather than making it its own server, extend the mariadb service:
mkdir /etc/systemd/system/mariadb.service.d

create a file /etc/systemd/system/mariadb.service.d/prefetch.conf with contents
[Service]
ExecStartPre=/usr/bin/wget -P /etc/mysql https://example.com/my_maria_key.key
ExecStartPost={something to ensure the key is loaded - mysql -e '...' }
ExecStartPost=rm my_maria_key.key

I don't know if this will deleting the key too early. Consider including journalctl -u mariadb.service -n 50 in your question.


SQLite DB from file vs "service" I apologize if this question has been asked before, I am not exactly sure how to phrase it.

I am currently creating a SQLite database for use in a NodeJS application. The DB will need to be read from frequently and written to somewhat frequently.

I am wondering if there is a significant difference between running the database from a file on disk or as a "standalone" (i.e. as a DB running inside of a Docker container that the application connects to)?

Have you already had a look at the official material?

Reference: Appropriate Uses For SQLite (SQLlite.com)

(emphasis mine)

Situations Where SQLite Works Well

Embedded devices and the internet of things

Because an SQLite database requires no administration, it works well in devices that must operate without expert human support. SQLite is a good fit for use in cellphones, set-top boxes, televisions, game consoles, cameras, watches, kitchen appliances, thermostats, automobiles, machine tools, airplanes, remote sensors, drones, medical devices, and robots: the "internet of things".

Client/server database engines are designed to live inside a lovingly-attended datacenter at the core of the network. SQLite works there too, but SQLite also thrives at the edge of the network, fending for itself while providing fast and reliable data services to applications that would otherwise have dodgy connectivity.
Application file format

SQLite is often used as the on-disk file format for desktop applications such as version control systems, financial analysis tools, media cataloging and editing suites, CAD packages, record keeping programs, and so forth. The traditional File/Open operation calls sqlite3_open() to attach to the database file. Updates happen automatically as application content is revised so the File/Save menu option becomes superfluous. The File/Save_As menu option can be implemented using the backup API.

There are many benefits to this approach, including improved performance, reduced cost and complexity, and improved reliability. See technical notes "aff_short.html" and "appfileformat.html" and "fasterthanfs.html" for more information. This use case is closely related to the data transfer format and data container use cases below.
Websites

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
Data analysis

People who understand SQL can employ the sqlite3 command-line shell (or various third-party SQLite access programs) to analyze large datasets. Raw data can be imported from CSV files, then that data can be sliced and diced to generate a myriad of summary reports. More complex analysis can be done using simple scripts written in Tcl or Python (both of which come with SQLite built-in) or in R or other languages using readily available adaptors. Possible uses include website log analysis, sports statistics analysis, compilation of programming metrics, and analysis of experimental results. Many bioinformatics researchers use SQLite in this way.

The same thing can be done with an enterprise client/server database, of course. The advantage of SQLite is that it is easier to install and use and the resulting database is a single file that can be written to a USB memory stick or emailed to a colleague.
Cache for enterprise data

Many applications use SQLite as a cache of relevant content from an enterprise RDBMS. This reduces latency, since most queries now occur against the local cache and avoid a network round-trip. It also reduces the load on the network and on the central database server. And in many cases, it means that the client-side application can continue operating during network outages.
Server-side database

Systems designers report success using SQLite as a data store on server applications running in the datacenter, or in other words, using SQLite as the underlying storage engine for an application-specific database server.

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.
Data transfer format

Because an SQLite database is a single compact file in a well-defined cross-platform format, it is often used as a container for transfering content from one system to another. The sender gathers content into an SQLite database file, transfers that one file to the receiver, then the receiver uses SQL to extract the content as needed.

An SQLite database facilitates data transfer between systems even when the endpoints have different word sizes and/or byte orders. The data can be a complex mix of large binary blobs, text, and small numeric or boolean values. The data format can be easily extended by adding new tables and/or columns, without breaking legacy receivers. The SQL query language means that receivers are not required to parse the entire transfer all at once, but can instead query the received content as needed. The data format is "transparent" in the sense that it is easily decoded for human viewing using a variety of universally available, open-source tools, from multiple vendors.
File archive and/or data container

The SQLite Archive idea shows how SQLite can be used as a substitute for ZIP archives or Tarballs. An archive of files stored in SQLite is only very slightly larger, and in some cases actually smaller, than the equivalent ZIP archive. And an SQLite archive features incremental and atomic updating and the ability to store much richer metadata.

Fossil version 2.5 and later offers SQLite Archive files as a download format, in addition to traditional tarball and ZIP archive. The sqlite3.exe command-line shell version 3.22.0 and later will create, list, or unpack an SQL archiving using the .archive command.

SQLite is a good solution for any situation that requires bundling diverse content into a self-contained and self-describing package for shipment across a network. Content is encoding in a well-defined, cross-platform, and stable file format. The encoding is efficient, and receivers can extract small subsets of the content without having to read and parse the entire file.

SQL archives are useful as the distribution format for software or content updates that are broadcast to many clients. Variations on this idea are used, for example, to transmit TV programming guides to set-top boxes and to send over-the-air updates to vehicle navigation systems.
Replacement for ad hoc disk files

Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files. Contrary to intuition, SQLite can be faster than the filesystem for reading and writing content to disk.
Internal or temporary databases

For programs that have a lot of data that must be sifted and sorted in diverse ways, it is often easier and quicker to load the data into an in-memory SQLite database and use queries with joins and ORDER BY clauses to extract the data in the form and order needed rather than to try to code the same operations manually. Using an SQL database internally in this way also gives the program greater flexibility since new columns and indices can be added without having to recode every query.
Stand-in for an enterprise database during demos or testing

Client applications typically use a generic database interface that allows connections to various SQL database engines. It makes good sense to include SQLite in the mix of supported databases and to statically link the SQLite engine in with the client. That way the client program can be used standalone with an SQLite data file for testing or for demonstrations.
Education and Training

Because it is simple to setup and use (installation is trivial: just copy the sqlite3 or sqlite3.exe executable to the target machine and run it) SQLite makes a good database engine for use in teaching SQL. Students can easily create as many databases as they like and can email databases to the instructor for comments or grading. For more advanced students who are interested in studying how an RDBMS is implemented, the modular and well-commented and documented SQLite code can serve as a good basis.
Experimental SQL language extensions

The simple, modular design of SQLite makes it a good platform for prototyping new, experimental database language features or ideas.

Situations Where A Client/Server RDBMS May Work Better

Client/Server Applications

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
High-volume Websites

SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite.
Very large datasets

An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
High Concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

Checklist For Choosing The Right Database Engine

Is the data separated from the application by a network? → choose client/server

Relational database engines act as bandwidth-reducing data filters. So it is best to keep the database engine and the data on the same physical device so that the high-bandwidth engine-to-disk link does not have to traverse the network, only the lower-bandwidth application-to-engine link.

But SQLite is built into the application. So if the data is on a separate device from the application, it is required that the higher bandwidth engine-to-disk link be across the network. This works, but it is suboptimal. Hence, it is usually better to select a client/server database engine when the data is on a separate device from the application.

Nota Bene: In this rule, "application" means the code that issues SQL statements. If the "application" is an application server and if the content resides on the same physical machine as the application server, then SQLite might still be appropriate even though the end user is another network hop away.
Many concurrent writers? → choose client/server

If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.
Big data? → choose client/server

If your data will grow to a size that you are uncomfortable or unable to fit into a single disk file, then you should select a solution other than SQLite. SQLite supports databases up to 140 terabytes in size, assuming you can find a disk drive and filesystem that will support 140-terabyte files. Even so, when the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database.
Otherwise → choose SQLite!

For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps thing simple. SQLite "just works".


The application is not installed - Visual Studio 2013 I need to deploy packages using visual studio 2013 but I am seen this message, the application is not installed, as you can see on the picture below.

what do I need to install?

apparently this is because I need to download and install

Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013

http://www.microsoft.com/en-us/download/confirmation.aspx?id=42313

I will get this done and if it all works I will accept this answer.


Most efficient way of bulk deleting rows from postgres I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions + deletions) into a table. There could be thousands, potentially millions of rows to delete.

I have a file of primary keys, one per line. The two options I was thinking of were along the lines of the below, but I don't know/understand enough of the internals of PostgreSQL to make an informed decision which would be best.

Execute a DELETE query for each row in the file, with a simple WHERE on primary key (or group the deletes in batches of n using an IN() clause)
Import the primary keys into a temporary table using the COPY command and then deleting from the main table using a join

Any suggestions will be much appreciated!

Your second option is far cleaner and will perform well enough to make that worth it.  Your alternative is to build gigantic queries which will be quite a pain to plan and execute.  In general you are going to be better off letting PostgreSQL do the work here.  In general, I have found updates on tens of thousands of rows in the manner you are describing to perform adequately, but there is one important thing to avoid doing.

The way to do it is to use a select and a join in your delete.

DELETE FROM foo WHERE id IN (select id from rows_to_delete);

Under no circumstances should you as follows with a large table:

DELETE FROM foo WHERE id NOT IN (select id from rows_to_keep);

This will usually cause a nested loop antijoin which will make performance rather problematic.  If you end up having to go that route, do this instead:

DELETE FROM foo
WHERE id IN (select id from foo f
          LEFT JOIN rows_to_keep d on f.id = d.id
              WHERE d.id IS NULL);

PostgreSQL is usually pretty good at avoiding bad plans but there are still cases involving outer joins which can make a big difference between good and bad plans.

This is wandering a little more far afield, but I figure it is worth mentioning because of how easy it is to go from the IN to NOT IN and watch query performance tank.


Performance of IN with OR operator I have common clause in most of the procedures like

Select * from TABLE A  + Joins  where <Conditions>

And
(
     -- All Broker
       ('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID))
  OR
       (
         A.BrokerID in
                     (
                       Select BrokerID from SiteUserBroker where SiteUserID
                      = @SiteUserID)
       )
)

Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.

I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?

Any ideas are appreciated.

You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.

What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
For example:

IF (
    -- All Broker
    ( 'True' = (
                SELECT AllBrokers
                FROM   SiteUser
                WHERE  ID = @SiteUserID
                )
    )
    OR
    ( A.BrokerID IN (
                     SELECT BrokerID
                     FROM   SiteUserBroker
                     WHERE  SiteUserID = @SiteUserID
                    )
    )
   )
SELECT *
FROM TABLE A  <+ Joins>
WHERE <Conditions>
ELSE
SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
WHERE 1=0 -- Forces an empty set

This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.

An even better approach would be to raise an explicit access denied message, for example:

ELSE
THROW <Access Denied Error>

However, this approach may require application code changes to handle the message correctly.

BTW - never use SELECT * in production code, see this thread...

HTH


Displaying data horizontally in mysql I have this table in my db

id | major | code_lesson | files

Which the contents look like this

id | major | code_lesson | files
1  | Math  | MAT12       | mat.pdf
2  | Socio | SOC15       | soc.pdf
3  | Math  | MAT12       | mat_pre.pdf
4  | Physic| PHY19       | physci.pdf
5  | Physic| PHY19       | phy2.pdf

I want to display this table like this

major |file1       |file2
Math  |mat.pdf     |math_pre.pdf
Socio |soc.pdf     |
Physic|physci.pdf  |phy2.pdf

Can you please explain me how to achieve this, since my output result  1 rows :

select major, files as file1, (select files where max(id)) as file2 from db_school

Thanks in advance

user can insert record only two files with the same major

SELECT t1.major, t1.files file1, t2.files file2
FROM db_school t1
LEFT JOIN db_school t2 ON t1.major = t2.major
                      AND t1.id < t2.id


Pull the latest Employee and Manager Comments Based on Modified date I have a table Comments which is linked to the Plan table. Each time the user adds a comment on a specific plan, the Comments table is populated. This table stores the historical data. I need to pull out the latest record with the employee comments and the manager comments.

Comments Table schema is as shown below:

EmpFK | PlanFK | Comments | ModifiedDate
  60       7     empCmnt1   13-04-2016
  61       7     mgrCmnt1   14-04-2016
  60       7     empCmnt2   18-04-2016
  61       7     mgrCmnt2   19-04-2016
  60       8     empcmnt1   01-03-2016

Plan Table schema is as follows:

PlanPK |  PlanName
  7         Name1
  8         Name2

Employee Table Schema:

EmpPK | MgrPK  | EmpName
  60      61       user1
  61               mgr1

Here 60 is the empid and 61 is the mgrid. EmployeePK and the ManagerId column in the 'Employee' table gives the employee and manager details. Each employee will have only one manager. No higher lever consideration is needed in this scenario
Expected Result:

empid  |  mgrid  | EmpCmnt  |  MgrCmnt  | ModifiedDate | planFK
  60         61     empCmnt2    mgrCmnt2   19-04-2016       7
  60         61     empcmnt1               01-03-2016       8

Thank you for the schemas. I did modify the employee table to include a manager bit flag which makes the query a bit easier. This doesn't exactly reproduce your desired results, but it is very close. If there is no manager comment on the last plan do you need to include the manager ID? Also, I wasn't sure on the date to include so the date is there for each comment.

--Load base tables
CREATE TABLE #Comments
(EmpFK INT
, PlanFK INT
, Comments VARCHAR(10)
, ModifiedDate DATE)

CREATE TABLE #Plan
(PlanFK INT
, PlanName VARCHAR(10))

CREATE TABLE #Employee
(EmpPK INT
, MgrPk INT NULL
, Mgr BIT
, EmpName VARCHAR(10))

INSERT INTO #Comments VALUES (60,7,'empCmnt1','2016-04-13')
INSERT INTO #Comments VALUES (61,7,'mgrCmnt1','2016-04-14')
INSERT INTO #Comments VALUES (60,7,'empCmnt2','2016-04-18')
INSERT INTO #Comments VALUES (61,7,'mgrCmnt2','2016-04-19')
INSERT INTO #Comments VALUES (60,8,'empCmnt1','2016-03-01')

INSERT INTO #Plan VALUES (7,'Name1')
INSERT INTO #Plan VALUES (8,'Name2')

INSERT INTO #Employee VALUES (60,61,0,'user1')
INSERT INTO #Employee VALUES (61,null,1,'mgr1')

--Load Temp table with the last comment per employee per plan

CREATE TABLE #LastComment (EmpFK INT, PlanFK INT, Comments VARCHAR(10), ModifiedDate DATE, Mgr BIT)

INSERT INTO #LastComment
    SELECT Comment.EmpFK, Comment.PlanFK, Comment.Comments, Comment.ModifiedDate, Emp.Mgr
    FROM #Comments as Comment
    JOIN (SELECT c.EmpFK, c.PlanFK, MAX(c.ModifiedDate) as MaxCommentDate
        FROM #Comments as c
        GROUP BY EmpFk, PlanFk
        ) as LastComment on Comment.EmpFK = LastComment.EmpFK AND Comment.PlanFK = LastComment.PlanFK AND Comment.ModifiedDate = LastComment.MaxCommentDate
    JOIN #Employee AS Emp on Comment.EmpFK = Emp.EmpPK

SELECT EmpLC.EmpFK, MgrLC.EmpFK, EmpLC.Comments, MgrLC.Comments, EmpLC.ModifiedDate, MgrLC.ModifiedDate, P.PlanFK
FROM #Plan AS P
LEFT JOIN #LastComment AS EmpLC ON P.PlanFK = EmpLC.PlanFK AND EmpLC.Mgr = 0
LEFT JOIN #LastComment AS MgrLC ON P.PlanFK = MgrLC.PlanFK AND MgrLC.Mgr = 1


Partition Wise Joins in Mysql Is it possible to create or simulate a partition-wise join in Mysql/Innodb??  Here is a good description of partition-wise joins....

https://blogs.oracle.com/datawarehousing/entry/partition_wise_joins
*A partition wise join is a join between (for simplicity) two tables that are partitioned on the same column with the same partitioning scheme.

If you now join the two tables on that partitioned column you can break up the join in smaller joins exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets, all values required to do the join live in the equivalent bucket on either sides. No need to talk to anyone else, no need to redistribute data to anyone else... in short, the optimal join method for parallel processing of two large data sets.*

The short answer is no, MySQL does not have that 'feature'.

The long answer (and opinion)...

Perhaps 1% of tables are partitioned.  Perhaps 1% of partitioned tables could use that feature.  Now imagine the effort to implement, test, and deploy such an obscure feature.  Oracle has had several decades to incorporate little-used features like that.  MySQL is relatively young.  It is still lean.  (But obscure features are creeping in.  That one has not shown up yet.)

The reason why only 1% of tables are partitioned is that there is no performance to be gained for the other 99%.  Usually an appropriate index will do just as good as partitioning.

Would you like to present your use case?  (Beware:  I may say that you gained nothing by using partitioning.)


Data type for a fuzzy date? I want to store partial information related to a date. I might know the year and month but not the day. I might know the day and month but not the year. I might know the date lies in an open or closed interval. What are my options for modeling this type of data?

I've done this for attorneys in the past. I used an ISO-style date format (yyyy-mm-dd) stored as char(10). Any missing part used question marks.

2002-01-?? -- some day in January 2002
199?-??-?? -- some day in the 1990s
????-01-08 -- January 8th in an unknown year

Values like these are intuitive to people doing data entry, they sort fairly sensibly, and the format can be controlled with CHECK constraints. You lose date and time arithmetic, but when you have unknown dates that usually doesn't matter too much.

  I might know the date lies in an open or closed interval.

That's actually a different kind of information than, say, knowing that something happened on April 1, but not knowing which year. Off the top of my head, you could store four columns.

starting date
ending date
whether the starting date is or is not in the interval
whether the ending date is or is not in the interval

I'd expect to have to store that kind of information separately from the kind of data I mentioned first. "January 8th in an unknown year" would be particularly troublesome to represent in either an open or closed interval.

The latest release of PostgreSQL (9.2) includes support for range types.


Formatting out a column value - mm:ss.hh I have a time in seconds and I want to convert it to the following formats possibilities:

121.17 -> 2:01.17 |
34.28 -> 34.28 |
133.56 -> 2:13.56

I am using the following:

IF(Time > 60, CONCAT(FLOOR(Time/60), ":", Time - (FLOOR(Time/60)*60)),
   Time) as TTime,

Results:
2:1.17 (incorrect) |
34.28 (correct) |
2:13.56 (correct)

I tried a number of things (LPAD, CONVERT, etc)

Please advise. MySQL 5.6.17

You could convert to time by adding the seconds to a constant date and then subtracting it:

select timediff(cast(date_add('2001-01-01', interval t.time second)
                     as datetime(3)),
                cast('2001-01-01' as datetime(3))
               ) as ttime
from tableX as t ;

You can then format the time(3) result with the available time formatting function.


How to Remove Multiple Databases Marked with "Recovery Pending" Recently I tried to clean my appilcations which used SQL database(s), therefore, the leftover databases are now marked with "Recovery Pending".

Right now I can follow this to delete them manually.

How do I remove all those DBs marked with "Recovery Pending" in only one action?

You need to get the meta-data off your SQL Server (as seen in sys.databases) and that is indeed the DROP database command (which is performed by SSMS when you right-click and Delete). Either do them one-by-one, as in

DROP DATABASE a DROP DATABASE b

Or more then one at once, as in

DROP DATABASE a, b

You can also generate those DROP commands, such as:

SELECT 'DROP DATABASE ' + QUOTENAME(name)
FROM sys.databases
WHERE state_desc = 'RECOVERY_PENDING'


postgres: index to SELECT strings like '%foo%'; I have an expensive, periodic PostgreSQL 9.3 query of the form:

SELECT * from mytable where name NOT LIKE '%foo%';

foo is actually a constant here that never changes.  The query is expensive because it requires a table scan.  I expect that only a few rows will actually match the query, so I want to use a partial index to speed up the query.

CREATE INDEX foo_idx ON mytable ((name NOT LIKE '%foo%')) WHERE name NOT LIKE '%foo%';

However, when I do an EXPLAIN on my query, it still is using a sequential scan instead of relying on the new index.  What am I doing wrong?

I have tried several different expressions inside the parenthesized expression clause of the CREATE INDEX statement, but nothing helps:

CREATE INDEX foo_idx ON mytable (name) WHERE name NOT LIKE '%foo%';
CREATE INDEX foo_idx ON mytable ((name LIKE '%foo%')) WHERE name NOT LIKE '%foo%';

I have also tried removing the WHERE clause for the index, and that doesn't help either.

The index should work as is. As pointed out by @jjanes, you just needed to run ANALYZE. However, I suggest you modify the index. The expression in your definition is not useful.

CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%';

The expression is always FALSE and useless noise. Either simplify to the slightly cheaper equivalent index:

CREATE INDEX foo_idx ON intentions ((FALSE)) WHERE data NOT LIKE '%foo%';

Or, better yet, put something useful there. Since each index entry is going to allocate MAXALIGN (typically 8 bytes) for data anyway, and the overhead per index row is another 12 bytes (and the default fillfactor for btree indexes is 90%) you might as well put the index to good use and help other queries. If you have any other queries that might use the same partial index?

If you put something worth up to 8 bytes (like one or two int4 columns), the index retains exactly the same size and performance, but offers more possible use cases. If (one of) the column(s) is involved in updates, you have some additional maintenance cost. Still, if you have anything useful, use it. Any additional column that's used in a JOIN / WHERE or ORDER BY clause. Or column(s) that enable index-only scans:

CREATE INDEX foo_idx ON intentions (<useful_column(s)>)
WHERE data NOT LIKE '%foo%';


what is the difference between 'order' and 'order line'? I was trying to understand degenerate dimensions. But I quite often encounters the term "order line" as grain in an transaction table. Could someone please explain the difference between the order and order-line ?

Generally, an order contains information about the order.  Who placed the order, what time they placed it, the shipping address associated, the billing address, payment method, when it was fulfilled, etc.  It often does not contain any information about what was ordered.

The order line generally contains information about what was ordered, this is done because a single order can have multiple items in it.  So the order line would specify the item ordered, the quantity ordered and the price charged, and there would be one line for each different item ordered.


Installing SQL Server 2012 SP2 on instance with Cumulative Update #10 We have Cumulative Update #10 installed on SQL Server 2012 SP1 that we need because of a specific fix to DQS.

Since the recently released SQL Server 2012 SP2 only contains fixes from CU1 through CU #9, if we decide to update to SP2, will that overwrite and remove the fixes from CU #10? And if so, could we re-apply CU#10 to it? Or would we have to wait until another CU is made for SQL Server 2012 SP2?

http://support.microsoft.com/KB/2958429
http://support.microsoft.com/kb/2954099

Yes, this is how it works: You lose the updates that were made in any CUs not included in the service pack (sometimes it is more than one CU that is "lost" temporarily).

And no, you can't then apply a Service Pack 1 Cumulative Update to Service Pack 2: the installer simply halts and won't let you do it.

The reason it works this way is that the Service Pack code goes through a longer and more rigorous testing cycle, so needs to be "locked down" months before release, unlike Cumulative Updates which go through less rigorous testing in their - typically - 8-week cycle.

While the SP is being tested, issues continue to be discovered, fixes continue to be checked in, and Cumulative Updates continue to be released. The world doesn't stop because a Service Pack is being tested.

In no situation that I have seen has an SP cycle been halted so that it can include a fix for some issue that has been discovered in the meantime - depending on the nature and severity of the issue, they may "fast-track" a post-SP CU, or they may issue a separate on-demand hotfix. I've seen both of these scenarios happen (more below on the latter). Typically, though, the first CU after the SP was released includes all of the fixes from the last CU or two from the previous branch, kind of a "catch-up."

So, please, wait for Service Pack 2 Cumulative Update 1 (should be end of July, according to this post) before installing Service Pack 2, or be willing to lose the fixes in CU #10.

(And in either case, if you are on Enterprise and run your rebuilds online, please see this blog post about an IMHO critical hotfix addressing a data loss/corruption issue.)


Different column for different conditions when joining I am using MySQL database and in that two tables, relation is one to many.
One table is product table, sample data:

+----+----------+
| id |   name   |
+----+----------+
|  1 | product1 |
|  2 | product2 |
+----+----------+

Other table is attribute table

+------------+----------+------------+
| product_id |   key    |   value    |
+------------+----------+------------+
|          1 | format   | mp3        |
|          1 | format   | audio      |
|          1 | category | English    |
|          1 | category | Vocabulary |
|          2 | format   | mp3        |
|          2 | format   | book       |
|          2 | category | Grammar    |
|          2 | category | German     |
+------------+----------+------------+

What i want to achieve is that my query should have different columns for different key used in attribute table.

Result:

+------------+----------+-----------+--------------------+
| product_id |   name   |  format   |      category      |
+------------+----------+-----------+--------------------+
|          1 | product1 | mp3,audio | English,Vocabulary |
|          2 | product2 | mp3,book  | German,Grammar     |
+------------+----------+-----------+--------------------+

So far i came up with the solution including joining attribute table twice, like as follows:

SELECT a.*,
GROUP_CONCAT(b.value,',') as format,
GROUP_CONCAT(c.value,',') as category
FROM products as a
LEFT JOIN attributes ON a.id=b.product_id AND key = 'format' as b
LEFT JOIN attributes as c ON a.id=c.product_id AND key = 'category'
GROUP BY a.id

Is there anything wrong with this solution (joining same table twice)? Or should it be done some other way?

--data population:

DROP TABLE IF EXISTS `attributes`;
CREATE TABLE `attributes` (
  `product_id` int(11) NOT NULL DEFAULT '0',
  `product_key` varchar(50) DEFAULT NULL,
  `product_value` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of attributes
-- ----------------------------
INSERT INTO `attributes` VALUES ('1', 'format', 'mp3');
INSERT INTO `attributes` VALUES ('1', 'format', 'audio');
INSERT INTO `attributes` VALUES ('1', 'category', 'English');
INSERT INTO `attributes` VALUES ('1', 'category', 'Vocabulary');
INSERT INTO `attributes` VALUES ('2', 'category', 'Grammar');
INSERT INTO `attributes` VALUES ('2', 'category', 'German');
INSERT INTO `attributes` VALUES ('2', 'format', 'mp3');
INSERT INTO `attributes` VALUES ('2', 'format', 'book');

-- ----------------------------
-- Table structure for `products`
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', 'product1');
INSERT INTO `products` VALUES ('2', 'product2');

AFAIK the only other way is to run 2 queries and add the columns in the application.

Your solution can work with only one JOIN if you put some IF's in your GROUP_CONCAT's:

`GROUP_CONCAT(IF(a.key = 'format',a.value,NULL))` as formats,
`GROUP_CONCAT(IF(a.key = 'category',a.value,NULL))` as categories,
...

It is debatable if the single JOIN option is better...


Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor? I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0; -- number(10,0)
    v2 tbl_plant.name%type := ''; -- varchar2(50)
begin
    while true
    loop
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        exit when sql%rownum = 0;

        dbms_output.put_line(v1 || v2);
    end loop;
end;

So I figured that compiler optimizes this into an implicit cursor as while loop is used, and tried to circumvent:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0;
    v2 tbl_plant.name%type := '';
begin
<<divein>>
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        if sql%rownum = 0 then
          goto bailout;
        end if;

        dbms_output.put_line(v1 || v2);
        goto divein;
<<bailout>>
    null;
end;

But in both cases I got exactly the same error:

PLS-00207: identifier 'ROWNUM', applied to implicit cursor SQL, is not a legal cursor attribute

But if I commented out either while or goto and the labels, eliminating either type of loop, the same select worked fine and returned the first id/name in tbl_plant by plant ID.

Is it at all possible to iterate w/o using a cursor?

Valid cursor attribute name is one of  %NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN. There is no %rownum attribute . In your question you use implicit cursors, so it should be
 sql%rowcount.


Is it good idea to use RAID 1 mirroring to back up database content I'm working on web hosting project. The planned system structure is going to be like this:

one main hard-drive will contain the binary of mysql.
A second hard-drive will contain the datadir
the third hard-drive should contain a backup of this mentioned datadir.

I can only think of two options here either replication using 2 instances of MySQL servers or just using RAID 1 mirroring only the datadir.

I would go with mirroring but I'm kind of suspicious of using this method and my main frightening thought is what if I lost the main hard-drive that contain the binary of mysql does setting new version will cause my a problem .. any ideas suggestions will be great full thanks

I should mention that using INNODB is a constraint

Relying on RAID 1 as a backup is not a great idea. If you had data corruption in the database, it would likely appear on both drives - certainly if it were caused by faulty SQL commands being executed. RAID 1 will only protect you from physical failure of one of the two drives, not corruption caused by a duff application or end-users.

In addition, backups that are stored on the same machine is also asking for trouble. You should always store backups off the source machine, and use something like mysqldump or the Percona Xtrabackup tool to perform these backups.

If you can mount an external volume onto the machine through NFS (or CIFS if Windows), then use Xtrabackup to backup the database content onto the additional volume, which is physically on a separate machine, then if you lost the machine, you could rebuild and restore from the backup.

Ensure you also backup your configuration, such as OS users, MySQL configuration files, etc. as without these, restoration may prove trickier.

Hope that all makes sense?

Dave


Performance of Combination lookup I've been looking into this most of the day and I'm still not getting far so I thought I'd ask on here.

Basically its a lottery system and what I need to do is check whether 4 of the 6 balls I have selected are already taken in the database.

The table I'm using to test looks like this

CREATE TABLE [dbo].[Lines](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Ball1] [int] NOT NULL DEFAULT ((0)),
   [Ball2] [int] NOT NULL DEFAULT ((0)),
   [Ball3] [int] NOT NULL DEFAULT ((0)),
   [Ball4] [int] NOT NULL DEFAULT ((0)),
   [Ball5] [int] NOT NULL DEFAULT ((0)),
   [Ball6] [int] NOT NULL DEFAULT ((0)))

I have a query that works but its slow, too slow for our requirements. We have estimated around a 20mil record count in this table. So far with just 5mill in our test db its taking ~2 seconds to run the query even with indexing. I see in the plan that this is due to a table scan but I cant figure out how to get around this.

The query I'm testing with is this.

DECLARE @matchCount INT = 4

DECLARE @ball1 INT = 2
DECLARE @ball2 INT = 11
DECLARE @ball3 INT = 16
DECLARE @ball4 INT = 28
DECLARE @ball5 INT = 36
DECLARE @ball6 INT = 39

SELECT
       count(*)
FROM Lines
WHERE
       CASE WHEN [Ball1] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball2] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball3] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball4] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball5] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball6] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END >= @matchCount

Is there a better way to achieve what I am try to do? or do I have to throw more hardware at it?

This is the execution plan from the above query.

Thanks in advance!

I would change your table layout. If you used something like:

(LineId int, BallSequence tinyint, BallVal int)

The combination of (LineId, Ballsequence) would be your PK.

Then your query would be:

SELECT COUNT(*)
FROM
( SELECT 1
  FROM dbo.Lines
  GROUP BY LineID
  HAVING SUM( CASE WHEN
              BallVal IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6)
              THEN 1 ELSE 0 END) >= @matchCount
) AS t ;

...and it could be covered by an index in BallVal.