Export SQL query output to a file in a scheduler job I need to export data to a csv file in a scheduler job. Here are the commands:

SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE;
SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;
SPOOL OFF;

When I make the "type of job" a "PL/SQL Block", it fails and I get the following error:

"ORA-06550: line 1, column 764:
PLS-00103: Encountered the symbol "\\AServerName\Output\RMS_DataFileSizes.csv" when expecting one of the following:

   := . ( @ % ;
ORA-06550: line 17, column 7:
PLS-00103: Encountered the symbol "OFF" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "OFF" to continue.

What am I doing wrong?

How about a slightly different approach?

Put that code into a .SQL file, let's call it p.sql:

spool p.txt

SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;

spool off
exit

I'm on MS Windows, so - create a .BAT file; let's call it p.bat:

sqlplus sys/pwd as sysdba @p.sql

Finally, use your operating system scheduling program (Task Manager on my Windows) and schedule p.bat to run as often as you want, e.g.

let it run daily at 22:00
action = Start a program (navigate to p.bat)
optionally (I'd suggest you to use it), set "Start in" directory

Should work; at least, works for me.


SQL ANSI JOIN precedence I have a query that looks like this:

    SELECT *
      FROM TBLA A
 LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

In which order will the joins be resolved?  I'm most interested in SQL Server, and will mark an explanation for it as the answer, but am equally interested in the ANSI/ISO standard and how it works in the various RDBMS.

The reason for this question was to figure out why the results differed from this query

    SELECT *
      FROM TBLA A
CROSS JOIN TBLC C
 LEFT JOIN TBLB B ON A.Col1 = B.Col2 AND B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

Logically the joins are resolved in the order of the ON clauses from left to right.

The output of each join is a virtual table that goes into the next join.

So for the query in your question the virtual table result of A LJ B is then right joined onto C. The join condition of B.Col3 = C.Col4 will lose any null extended rows preserved by the original left join effectively turning the first join back into a inner join and the resulting virtual table (which preserves all rows from C) is then inner joined onto D.

So your initial query can be simplified as

SELECT *
FROM TBLA A
INNER JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

Which is effectively the same as (A IJ B) ROJ (C IJ D)

The order of the ON clauses is not necessarily the same as the order the tables appear in the query. This could also be rewritten as (C IJ D) LOJ (A IJ B)

SELECT *
FROM   TBLC C
       INNER JOIN TBLD D
         ON C.Col5 = D.Col6
       LEFT JOIN TBLA A
                 INNER JOIN TBLB B
                   ON A.Col1 = B.Col2
         ON B.Col3 = C.Col4

The position of the on clauses means that the outer join is carried out between the two virtual tables resulting from  (C IJ D) and (A IJ B) rather than just on a single table.

In your second query conceptually the virtual table A x C is left joined onto B preserving the entire cartesian product then the result of that is joined onto D with the predicate C.Col5 = D.Col6. This eliminates any rows from the final result that do not inner join between C and D meaning it is equivalent to

SELECT *
FROM   TBLC C
       JOIN TBLD D
         ON C.Col5 = D.Col6
       CROSS JOIN TBLA A
       LEFT JOIN TBLB B
         ON A.Col1 = B.Col2
            AND B.Col3 = C.Col4


Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error

  ERROR 1118 (42000) at line 7481: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I got most of the tables to go through, but the table that doesn't want to pump through is kind of a strange one.  It's got about 10 blob fields in it (none of which should be over 1000 bytes each because of what they're supposed to be used for in my program).  The only other datatypes we've got are VARCHAR, CHAR, SMALLINT and INTEGER.

What I want to achieve is an automated way to convert MySQL to MariaDB so my customers can do the same thing.  So, even if I fix this problem, I'd like to know exactly what the problem is and if I can fix it on the MariaDB installation or the MySQL dumping.

Our customers use MyISAM tables because of a few outlying cases we can work around (in case that makes a difference). Seems like MariaDB created all these tables as InnoDB tables.

The syntax I used for creating the dump file was just mysqldump.exe DatabaseName --compatible=mssql > DatabaseName.sql.  I don't think the mssql compatibility causes issues, it was just a holdover I found in the batch file.

InnoDB has Limitations on row lengths and BLOBs. According to the MySQL Documentation:

  The maximum row length, except for variable-length columns (VARBINARY,
  VARCHAR, BLOB and TEXT), is slightly less than half of a database
  page. That is, the maximum row length is about 8000 bytes. LONGBLOB
  and LONGTEXT columns must be less than 4GB, and the total row length,
  including BLOB and TEXT columns, must be less than 4GB.

  If a row is less than half a page long, all of it is stored locally
  within the page. If it exceeds half a page, variable-length columns
  are chosen for external off-page storage until the row fits within
  half a page, as described in Section 14.3.12.2, “File Space Management”.

  Although InnoDB supports row sizes larger than 65,535 bytes
  internally, MySQL itself imposes a row-size limit of 65,535 for the
  combined size of all columns:

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB; ERROR 1118 (42000):
Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to
TEXT or BLOBs

  See Section E.10.4, “Limits on Table Column Count and Row Size”.

You are going to have to recreate the mysqldump so that the Storage Engine is Specified. Perhaps just drop the --compatible=mssql from the mysqldump command. The end result is the that the table will remain a MyISAM table when being imported into MariaDB.

This is just a guess but look at the error message you posted. If a BLOB prefix is 768 bytes and you have 10 BLOBs, that 7680 bytes. That leaves you with 320 bytes. If the remaining datatypes exceed 320 bytes, then it is impossible to convert to InnoDB.

Since a TEXT column is the variant of a BLOB, converting to TEXT does nothing since the storage requirements for TEXT and BLOB field are identical.


SQL Server max memory, page file, max degree parallelism Three questions hope you DBA masters of the universe can answer:

I know it is best practice to set SQL Server's max memory. So if a server with 44 GB RAM has SQL Server installed we should set the SQL Server max memory to approx 37GB.

But what should we set if SQL Analysis Service and SQL Reporting Services are installed on the same server as SQL Server? Is it then better to set SQL Server's max memory to 22GB RAM so Analysis can take the other 22GB RAM?

On a server running SQL Server with 44 GB RAM what is best practice for setting the page file size? I read on Brent Ozar (http://www.brentozar.com/archive/2014/06/sql-server-setup-checklist-free-ebook-download/) the following:

SQL Server doest not need a giant page file. if you're installing other applications on the server (which we don't recommend-- SQL Server should be isolated). you may need a larger page file. If SQL Server is the only major service running on the box, we typically create a 2GB size page file on the system drive.

Microsoft says we should set page file system managed to 1.5x the RAM which matches the advice given by Remus (see -> http://rusanu.com/2009/11/22/system-pagefile-size-on-machines-with-large-ram/).
So on our server with 44GB RAM we have SQL Server installed next to SQL Analysis Service and SQL Reporting Services. What size should we set the page file?

For SharePoint the SQL Server that is part of SharePoint's farm and where SharePoint stores it's databases, the max degree of parallelism should be set on 1. Now that we have a server that is not part of the SharePoint farm (call it sql2) on this server we have SQL Server, SQL Analysis and Reporting installed. SharePoint does not store its databases on this server, but we do have reporting and analysis databases/cubes created that SharePoint reads and writes to.

Brent Ozar says:

Rule of thumb: set this to the number of physical cores in a single NUMA node (processor) sockets on your hardware or less

What should we configure on this SQL Server instance for max degree of parallelism when it has 4 VCPU's?

Hope you guys give not only me but more sql admins a bit more insight in the best practices than the jungle on the interwebs.

Your 3 questions touch max memory, page file and max dop setting.

MAX MEMORY

If the server is dedicated SQL Server only without Analysis services (since  SSAS is another beast), then Glenn's recommended best practices is a good starting point. For additional components, you should refer to Jonathan's blog on how to configure max memory.

I have answered on SQL Server Maximum and Minimum memory configuration. This will be helpful if you have lots of SSIS packages running on your server instance.

Page File

I normally set it away from C:\ drive on the drive which has sufficient disk space. I agree with Remus and Brent's recommendation. It should be PAGE FILE FOR SQL SERVER = 1.5 * RAM. A page file will be useful when you encounter yourself in a situation wherein you have to take a FULL MEMORY DUMP (normally asked by Microsoft CSS) for troubleshooting.

Read How to determine the appropriate page file size for 64-bit versions of Windows KB 889654 thoroughly and Buck Woody talks about page file here.

MAX DOP Setting

For sharepoint its recommended that maxdop = 1 be set instance wide. For a normal sql server, I would suggest you to follow  - What is a good, repeatable way to calculate MAXDOP on SQL Server? I have a script written which will calculate it for you.

The gist is below (KB 2806535 also mentions the same thing):

8 or less processors    ===> 0 to N (where N= no. of processors)
More than 8 processors  ===> 8
NUMA configured         ===> MAXDOP should not exceed no of CPU’s assigned to each
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===> Should not exceed the number of physical processors.

As a side note, I would suggest you to read  - Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads


Use of '@' '.' and 'public operator/keywords in Oracle database? I got into a weird SQL query-patch which looks for @ . and public keywords.  I was wondering what is the purpose of these operators/keywords?

I suspect that it might be looking for SQL injection or some weird buffer overflow
but I can't seem to understand why it looks for those characters, etc, and I can't find much information about those keywords.

If anyone could enlighten my eyes, it will be very helpful.

This diagram is from the Oracle Docs Syntax for Schema Objects and Parts in SQL Statements page:

It shows the general use of the period character (.) for qualification and the @ symbol for referencing dblinks.

The @ symbol is also used when creating dblinks.

Given that the public keyword is also used for creating them, I think it is likely that your external function is doing something with dblinks!


I am getting a failed login message in the SQL Server error logs however, nothing actually failed? I have a server which runs third party software (called Visualcut) to email Crystal Reports to our users.

When I log onto the Visualcut Server, I can open the software and then preview a report. This will ask me to enter a username and password (for an Microsoft SQL account) and then the report will display with no errors however, whenever I do this, an error is logged in the SQL Server error logs as follows:

  10/26/2015 08:15:39 Logon Unknown Login failed for user ''. Reason: An
  attempt to login using SQL authentication failed. Server is configured
  for Windows authentication only. [CLIENT: 10.10.10.26]

  10/26/2015 08:15:39 Logon Unknown Error: 18456 Severity: 14
  State: 58.

I don't understand why the report would authenticate seemingly without any issues however, the log file states otherwise.

I would like to add that our SQL Server is configured to use mixed authentication.

From Aaron Bertrand's blog on Troubleshooting Error 18456

  Error: 18456, Severity: 14, State: 58.
  Login failed for user ''.
  Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication.

You have to Change Server Authentication Mode and restart sql server, if you/your software requires SQL Server authentication and your sql server is configured for Windows Authentication.


Is there any way to recover  a database after dropping from phpmyadmin? I was planning to drop all the tables from a test account, but unfortunately, I didn't notice that I was in the production account when I chose to drop all the tables of my db from phpmyadmin interface.

Is there any way of recovering my db content?

Sorry, that you had such an accident.

If you have binary logs on, you could try the recovery options described here:
Point in time recovery

Another thing is to restore a backup, hopefully, you have one.
Changes made after this, could be restored as mentioned above.

Good luck!


Can I defer a configuration change until the next time SQL Server is restarted? Let's say that I want to change a SQL configuration setting like MAXDOP.  I know that changing this on an active instance will severely impact performance, as it will clear all of the cached query plans. Doing this while the instance is running is simply not an option.

Is there some way to modify the value now, but to "defer" the actual change until the next time SQL server is restarted?

What I'm looking for is an option like Oracle's "scope=spfile" for parameter changes.  Does such an option exist in SQL Server?

Create a stored procedure in master:

USE master;
GO
CREATE PROCEDURE dbo.FixMaxDop
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sys.sp_configure N'show advanced options', 1;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'max degree of parallelism', 8;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'show advanced options', 0;
  RECONFIGURE WITH OVERRIDE;
  -- optionally, drop or alter the procedure so it doesn't run again
END
GO

Now, you can set the procedure to be a startup procedure (see here and here):

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'true';

If you only want this to run once, then you'll want to flip that option off after the configuration option has been set correctly:

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'false';

Alternatively, you can do as MichaelK suggested - create a job with a step that calls the above procedure, make sure it is owned by sa or someone in the sysadmin role, and then set it to start when SQL Server Agent starts. However, you're relying heavily on SQL Server Agent being set to start automatically (it might not be, and that might change before the next restart), and that Agent will start successfully after the next service restart (it might fail for a variety of reasons, including service account changes). You also can't create a schedule that runs when Agent starts and only runs once (it's one or the other), so you'd have to do something else like script an additional job step that disables the job or removes the schedule.

Not really a big deal for the procedure not to run again, I suppose, unless you make some other manual change later and forget about this - then you'll revert to this explicit setting the next time you restart after that change...


SQL Server Log Shipping Alert Job Issue We have log shipping taking place between server A and server B.

The logs are backed up every 15 minutes on server A.
Over on server B, the logs are copied every 15 minutes successfully.
The logs are restored on server B once per day at 3:35am successfully.

My issue is the alert job on server B is running every 2 minutes. It runs perfect for 45 minutes after the restore at 3:35am and then on the 47th minute, it fails and fails every 2 minutes until the next restore the next time at 3:35am

The threshold was set for 45 minutes so that part was correct.

My current considerations

This is what I'm thinking:

The copy job is working and I would get alert if it failed.
The restore job is working and I would get alert if it failed.

Questions

Why would I need the alert job? There is no way it can succeed. Since I am only restoring once per day, after that first 45 minute threshold is reached, it is an automatic failure every 2 minutes from then on.

Does this job alert on both copy and restore? What am I missing here?

Change the threshold to 23 hours 15 minutes...I assume the restore job is the one alerting, correct?  I believe there is an alert on all three.   possibly just backup and restore, but I don't have a LS setup in front of me.


Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table? Suppose I have the following view:

CREATE VIEW dummy AS
SELECT a.some_column, b.other_column
FROM a LEFT JOIN b USING (key_column);

When I do a SELECT some_column FROM dummy, is there a way to instruct the optimizer to rewrite the query as SELECT some_column FROM a and skip the join? How?

The decisive factor is whether there is a unique constraint/index on b (key_column).

If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column).

If on the opposite, there is a unique constraint, the query is equivalent to your rewriting (SELECT some_column FROM a; and the question is whether Oracle has implemented this trick in its optimizer's suite of query transformations.

Indeed it has been implemented - in version 10gR2 and further enhanced in  11gR1 - and it's called Table Elimination. More info can be found in the oracle blog post: Why are some of the tables in my query missing from the plan? The article explains that the 11gR1 enhancement included this particular case (Outer Join Table Elimination) of an outer join to a table with a unique constraint and has an example almost identical to yours.

We can also check with a simple comparison. Notice the difference on the execution plan between the 2 cases in the SQLfiddle:

-- Case 1
create table b
( key_column int                      -- no unique constraint
, other_column int
) ;
insert into b values (1, 1) ;
insert into b values (2, 2) ;
insert into b values (3, 3) ;
insert into b values (4, 4) ;
insert into b values (1, 2) ;      -- some duplicate values
insert into b values (1, 3) ;      -- in key_column

-- Case 2
create table bb
( key_column int primary key       -- has a unique constraint
, other_column int
) ;

insert into bb values (1, 1) ;
insert into bb values (2, 2) ;
insert into bb values (3, 3) ;
insert into bb values (4, 4) ;

-- Both cases
create table a
( some_column int primary key,
  key_column int
) ;

insert into a values (101, 1) ;
insert into a values (202, 2) ;

We create views for both cases:

CREATE VIEW dummy AS
SELECT a.some_column, b.other_column
FROM a LEFT JOIN b USING (key_column);

CREATE VIEW dummy_bb AS
SELECT a.some_column, bb.other_column
FROM a LEFT JOIN bb USING (key_column);

Now the run the queries, using the view and using the tables - identical except for joining to table b vs. bb where the tables differ only that the second has a unique constraint on key_column:

SELECT some_column FROM dummy ;

SELECT some_column FROM (
    SELECT a.some_column, b.other_column
    FROM a LEFT JOIN b USING (key_column)
);

We see that on case 1 does access table b while in case 2 it does not (using the unexposed transformation).


Select most recent row for each order Currently writing a query which will be used to see where abouts on our production line a certain item is by showing what the last completed 'Resource' was. (resources on our system are just different areas of the assembly line eg, metal fabrication, spray shop, electrical wiring, testing etc.)

I've written a query that gives me ALL completed resources on unfinished jobs, but as each job has several completed resources on it the table that gets output still needs refining.

SELECT
T0."DocNum",
T2."U_CustomerRef",
T1."DocNum",
T6."ItemCode",
CONCAT(T5."firstName", T5."lastName"),
T4."U_Time",
T4."U_Date",
T4."U_StopTime",
T4."U_StopDate"
FROM
ORDR T0  INNER JOIN RDR1 T2 ON T0."DocEntry" = T2."DocEntry"
LEFT OUTER JOIN "NL_LIVE"."OHEM" "OHEM" ON T0."OwnerCode"="OHEM"."empID"
LEFT OUTER JOIN OWOR T1 ON T1."OriginAbs" = T0."DocEntry" AND T2."ItemCode" =T1."ItemCode"
INNER JOIN WOR1 T6 ON T1."DocEntry" = T6."DocEntry"
INNER JOIN OITM T3 ON T2."ItemCode" = T3."ItemCode"
LEFT OUTER JOIN "NL_LIVE"."@OCHLABBOOK" T4 ON T4."U_ProdOrderNo" = T1."DocNum" AND T4."U_ProdOrderLine"=T6."LineNum"
LEFT OUTER JOIN "NL_LIVE"."OHEM" T5 ON T4."U_StartEmp"=T5."U_UniqueID"
WHERE
T0."DocStatus"='O'
AND T6."ItemType"=290
AND T4."U_Action"<>'Started' AND T4."U_Action"<>'Stopped' AND T4."U_Action"<>'Paused'

I've tried doing SELECT MAX on the results using the date and time, but that leaves me with only one result in the table as there aren't any groupings for each area it just gives me the latest results for the whole table not each individual job/Document. In this image you can see I've drawn red borders showing that certain parts should be grouped, essentially what i need is a way of selecting the most recent date from each smaller group of data within the Query. Each subset needs to be grouped by Document number (column 3 document number not column 1)

Large shaded red area is just to cover employee names and isn't relevant to the question

Thanks in advance for any help given. If more information is required I can  go into more detail of table structures etc.

Since the data model of your SELECT is difficult to understand for me, I prepared following sample

The sample HANA SQL CTE selects the production lines with the products that are manufactured on it. I number each production according to their production date in an descending order on production line basis. For this, I use SQL Row_Number function with Partition By clause

After I filter the CTE data using rn = 1 WHERE clause, I get the most recent production for each production line.

I used LEFT JOIN so the result set displays product line 2 although there is no production on it.

/*
create column table ProductLines (
 pline int,
 productline varchar(100)
);

create column table ProductLineProduction (
 pline int,
 product varchar(10),
 productdatetime datetime,
 quantity int
);
insert into ProductLines values (1,'LineA');
insert into ProductLines values (2,'LineB');
insert into ProductLines values (3,'LineC');

insert into ProductLineProduction values (3,'Hat','201711011010',100);
insert into ProductLineProduction values (1,'Rug','201711031500',5);
insert into ProductLineProduction values (3,'Shirt','201711051200',200);
insert into ProductLineProduction values (1,'Fabric','201711080830',1000);
insert into ProductLineProduction values (3,'Trouser','201711091600',50);
*/

with cte as (
    select *,
        row_number() over (partition by pl.pline order by p.productdatetime desc) as rn
    from ProductLines pl
    left outer join ProductLineProduction p
        on pl.pline = p.pline
)
select * from cte where rn = 1;

The output for my sample will be as follows


Dealing with large log files I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers.  The two largest tables are of ~50 million and 25~million rows.  The database contains about 350,000 ID numbers for people and I often need to retrieve information about all individuals that involves joins to both of my very large tables (they're one to many relationships).  These queries are also temporal in nature and involve  the between operator to determine events that happen without a certain time frame.  It will often take 10-15 minutes for some of these queries to run (I'm still learning and try new indexes to see if I can improve performance.  After running out of ram running a particular query I had to my computer froze and I had to reboot.  Even after restarting I was unable to detach, drop connections and delete my log files to delete my database (which was in recovery mode).

I booted into safe mode to delete the mdf and ldf files and saw that my log file was twice the size of the data file.  If I routinely need to run queries that will return aggregate, temporal information on tables of the above-specified size, is there anything I can do to prevent log bloat?  Also, I know SQL Server eats resources for lunch, but what type of specs would a computer need to have to run a query like the following with the table sizes listed above? (it takes an hour on my local machine)

EDIT: this database is static in nature and will not have anything added to it.  it is also only unavailable to one user, me.  I'm not sure what type of recovery it had, I don't have that PC in front of me at the moment.

query:

--my real database field names are not this generic
;with x
as
(   --the first CTE finds the number of different classes a person qualified for and lists a number for a given date
    select  rx.pat_id
           ,c.cal_date
           ,COUNT(distinct rx.class) as distinctClass
           from rx,Calendar as c
           where c.cal_date between rx.fill_date and rx.script_end_date
           group by rx.pat_id,c.cal_date

),y
as
(   --the second CTE gives interval number to the date so the max and min date values can be grouped by grp_nbr to give a time span
    select x.pat_id
          ,x.cal_date
          ,c2.id-ROW_NUMBER() over(partition by x.pat_id,x.distinctClass
                 order by x.cal_date) as grp_nbr
          ,x.distinctClass
          from x
          inner join Calendar as c2
          on c2.cal_date=x.cal_date
)
--this groups the max and min dates with a particular grp_nbr and the number of different classes a person qualifies for
select y.pat_id,min(cal_date),max(cal_date),MIN(distinctClass)
    from y
    group by y.pat_id,grp_nbr
    order by MIN(distinctClass) desc

If the database is truly static in nature, putting it into read-only mode would prevent log growth.  The queries that are causing the growth would fail however, so this might not actually be what you are looking for.

Put the database into simple recovery mode.  That way the transaction logs will be routinely truncated.  When in full mode, your logs will continue to grow until they are backed up.

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH ROLLBACK IMMEDIATE
GO


Row size too large (> 8126) I have a table with 156 columns and most of them are either VARCHAR(1000) or TEXT.

Now, when entering data into one such row, I get

  mysql_error() = Row size too large (> 8126). Changing some columns to
  TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may
  help. In current row format, BLOB prefix of 768 bytes is stored
  inline.

I am thinking of changing the table type to MyISAM.
Its on a shared server, but can setup a private MySQL instance.

From here :

  InnoDB implements transactions, foreign keys and relationship
  constraints, MyISAM does not.

I can still use JOINS etc on MyISAM, right ? I'm not implementing any other complex stuff on these tables.

5.5.14 and 5.6.3 and 5.7 have innodb_file_format=Barracuda as an option.

Prior to 5.7.7, do these:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;

In 5.7.7 and after, most of that is defaulted as you need.

DYNAMIC is the format you need -- it leaves only 22 bytes in the main part of the row (which is limited to about 8KB), and puts the rest in overflow blocks.  This applies to VARCHAR, TEXT, VARBINARY and BLOB.  22*156 is well under 8K.  "When the row is too long, InnoDB chooses the longest columns for off-page storage."  That is, some of the shorter columns for a given row may still reside 'on-page'.

I think the "22" is a 2-byte length, plus a 20-byte "pointer" to the data.  That is, none of the data is stored 'on-page', and the 768 is not relevant.

Another possibility (but I don't recommend it) is to increase the default page size from 16KB to 32KB, thereby increasing the max from about 8K to about 16K.  (There is a 64KB page size, but the limit is still 16KB for each row.)

Also, COMPRESSED can be used instead of DYNAMIC, but again, I don't recommend it, especially before 5.7.  ("Compressed = Dynamic plus compression.")

Splitting the table ("vertical partitioning") is a good idea.

MyISAM is a bad idea.

Checking to see if you really need 1000 is a good idea.


SQL update query with join and subquery I am trying to update the values of a column to the results of a sub query. I get the error that the sub query is returning more that one row, which it should. It should be returning a column of values.

Update companies
set c_c_score = ( select t.summ
              from ( select sum(case when b.hot_tip = 1
                                     then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3)
                                     else 0
                                     end) as summ
                     from companies a
                     left join table2 b
                         on a.id = b.company_id
                     left join table3 c
                         on b.table3_id = c.id
                     group by a.id ) AS t);

The sub query runs fine by itself.

    select a.id,  sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end)
        from companies a
        left join table2 b
        on a.id = b.company_id
        left join table3 c
        on b.table3_id = c.id
        group by a.id;

This seems to work:

Update companies c
inner join (select a.id as company_id, sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end) as cc_score
from companies a
left join table2 b
on a.id = b.company_id
left join table3 c
on b.table3_id = c.id
group by a.id) x ON x.company_id = c.id
set c.c_c_score = x.cc_score;


Removing Tempdb .ndfs Tempdb is configured with 8 files and we are reducing them to 4.  I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the dbcc shrinkfile with the emptyfile clause, then the alter db command with the remove file clause.  When this was attempted, the error

DBCC SHRINKFILE: Page 9:364016 could not be moved because it is a
work table page.
Msg 2555, Level 16, State 1, Line 19 Cannot move all
contents of file "tempdev8" to other places to complete the emptyfile
operation. DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

My question:
Should I attempt to remove the locked temp tables by identifying them, then, re-run the dbcc statements, or, simply run an alter statement, then restart the instance, or restart the instance, then rerun the alter?  I realize the issue that the temp tables are being used by tempdb consistently which is causing the remove file not to work.

Per Mike Rose's answer on the Microsoft Social site, I believe this should work:
DBCC FREESYSTEMCACHE ('ALL');

DBCC FREEPROCCACHE;

There will be some performance hit for this as SQL Server will recreate Query Plans, but it should allow you to shrink TEMPDB.


Do I need a "downtime" to update a stored procedure? (Due to "definition changed" error) I always thought that updating a stored procedure was something I could do while users were actively using the system.

But I am currently testing out an update to a stored procedure and while a long running call to the sproc was being executed (it was 4+ min into the call), I updated the sproc.  (I was planning to try a different query plan in a seperate window.)

When I went back to the long running one, I had this error:

The definition of object 'MySprocName' has changed since it was compiled.

This error seems to say that any sproc that is being actively executed will fail if the definition of that sproc is updated. (I thought that once a sproc started running, it would use that plan for the rest of the run despite changes to the definition.)

Is this true?  Do I need to have downtimes to update definition of a sproc?

Yes it is true that you should not change an actively running stored procedure, because SQL Server doesn't like it.

As for taking downtime, well yeah, you shouldn't be making changes to frequently-used stored procedures in a busy production environment during operating hours as a best-practice.


Association Tables and Data Integrity I'm developing a solution related to classroom materials. The hierarchy for classes is as follows:

Course Type (e.g., Car Courses)

Course (e.g., Fixing Cars, Driving Cars)

Class (e.g., Fall semester class)

My problem deals with class materials. Currently I have a "Material" table that has MaterialId, Title, Href, etc. I need to associate Materials with 4 possible scenarios:

Global materials (material applies to all classes regardless of course or
course type)
Course Type materials
Course materials
Class specific materials

I'm currently handling global materials with a field in the Material table (IsGlobal), and I'm handling the other 3 scenarios using linked tables (MaterialCourseType, MaterialCourse, MaterialClass). The problem with this solution is it requires me to enforce data integrity through application code. For example, if I associate a material with a course type, I would need to delete all references to materials in the MaterialCourse and MaterialClass tables (since it's inclusion in MaterialCourseType would cover those). It is possible in my select statement to use a UNION which would automatically select distinct materials across all 4 tables, but that would leave invalid data. When the user through the front end decides to make a material available to the entire course instead of for a specific class, not cleaning up the MaterialClass table seems messy to me.

Is there a better database design I could use to enforce data integrity, or am I forced to deal with this problem in my application code to first delete invalid entries whenever a user associates a material differently?

I fully support your inclination not to delete material records. The association records joining the Type/Course/Class to Materials however, you will need to delete if you no longer need the association.

I would recommend the following schema, which I've written in T-SQL as it's my native SQL flavor. It provides you the basis you create you Type -> Course -> Class structure, and enables to you share a global materials list across all three, whilst creating unique associations at each level.

Here is a sqlfiddle to let you play around and get familiar with the design.

create table CourseType (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table Course (
  Id int identity primary key
  ,CategoryId int not null foreign key references CourseType(Id)
  ,Name nvarchar(100) not null
);

create table Class (
  Id int identity primary key
  ,CourseId int not null foreign key references Course (Id)
  ,Name nvarchar(100) not null
);

create table Material (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table CourseTypeMaterials (
  CourseTypeId int not null foreign key references CourseType(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseTypeId, MaterialId)
);

create table CourseMaterials (
  CourseId int not null foreign key references Course(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseId, MaterialId)
);

create table ClassMaterials (
  ClassId int not null foreign key references Class(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (ClassId, MaterialId)
);


Would it be possible/wise to execute 2 parallel restores from a single backup? Every night I create a backup (to disk) of a particular database. Subsequently that backup has to be restored to 2 different servers. These restores now happen sequentially and take about half an hour per restore.

Would it be possible/wise/faster to execute the 2 restores in parallel?

I am pretty sure that I have done this, though I didn't think twice about it at the time.

You definitely can restore a bak file using a UNC path. Mapping drive letters is to be avoided because the mapping must be done in the context of the SQL service account. Most people map the drive under their own account and are frustrated when the restore command doesn't work as they expect.

Naturally, there will be a little more load on the NIC on the server that holds the BAK file and there could be some contention if the database mdf and ldf files are on the same SAN, but these are probably minor problems, if you notice them at all.


Does the compatibility level for sql server work reliably for scripts? We have some DB scripts that need to be migrated from SQL Server 2008 R2 back to 2005, often scripts created in SQL Server 2008 / 2008 R2 won't run on a SQL Server 2005 installation (which some customers still use).

So my question: from your experience/knowledge, is this really necessary, or does setting the compatibility level back to 90 on SQL Server 2008 (2008 R2) fix the issue of unnoticed breaking scripts in SQL Server 2005? MSDN says "compatibility level provides only partial backward compatibility with earlier versions of SQL Server ", so I'm unsure here.

Thanks...

I would like a concrete list of “features will pass compatibility level 90 but break on SQL Server 2005" or link. If the list is short list, we could convert that to an internal "don't do that!" list and save a lot of work.

No the compatibility level is to allow deprecated features to work not to prevent you from writing code that won't work on a lower version.

If your production machine is SQL Server 2005, you must develop on a SQL server 2005 machine to ensure that features allowed in 2005 are the only ones in the code.

Of course in Books online there is a page which shows all the features that are new in 2008, so you could simply forbid your developers from using them. And check for them in code reviews, but developing against a correct database is the only real solution.

The list is not short and exotic, the compatibility mode is not intended to allow you to develop on a higher database and deploy to a lower one.


Tools for Identifying Needed Indexes I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?

Yes, there are tools, like the database engine tuning advisor. It ships with SQL Server and does some pretty nasty things:

It allows you to consider only a very small workload (even a single query). Which will then suggest indexes that may help that small workload only, with no regard whatsoever to the rest of the workload (indexes help speed up some queries, but they can slow down others, especially insert/update/delete), never mind the rest of the business cycle.
It will often recommend redundant indexes that only differ by a trailing column or by an included column.
It really, really, really likes included columns - I think it may be a fetish.

The proper approach to index tuning is to consider your entire workload over a complete business cycle. Sometimes it is better to turn to 3rd party tools than reinvent the wheel. DBSophic makes a free product called Qure Workload Analyzer that I think does a much better job than DTA - but their licensed tool (Qure Workload Optimizer) is absolutely phenomenal at what it does. Last year I blogged about how the tool works when integrated with SQL Sentry).

There are some other options, too, for example missing indexes are reported in the DMVs, and you can also discover which indexes are being written to (so they are taking up space and affecting insert/update/delete operations) but never (or rarely) used by user queries:

Find missing indexes using the DMVs
Find unused indexes using Policy-Based Management
Discovering unused indexes
Deeper insight into unused indexes

But these also need the caveat that the DMVs are only valid since the last SQL Server restart, and that they won't necessarily cover a proper business cycle (and hence may be missing vital information


PostgreSQL JSON query array against multiple values I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups.

Given this example table:

CREATE TABLE grp(d JSONB NOT NULL);

INSERT INTO grp VALUES
   ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}')
 , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}')
 , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}');

I found similar question (PostgreSql JSONB SELECT against multiple values) and managed to achieve what I want on simple array using this query:

SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar'];

However, I can't make it work when when array contains JSON objects:

SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}'];

Here is what I expect from my query:

grp "First" -> customer "1"

grp "Third" -> customer "5"

There is a way: combine the containment operator @> with the ANY construct:
SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:
...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.
You can use an index for this:

Index for finding an element in a JSON array

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?


MySQL:- Fetch records from 31 to 60 days I have a table with a field called date_created with TIMESTAMP format now I have a requirement to fetch records between 0-30,31-60,61-90 like this.
I able to fetch record between months like

SELECT  received_quantity , delivery_qty , unit_cost
FROM    tbl_inward_list
WHERE   date_created BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

also

SELECT  received_quantity , delivery_qty , unit_cost
FROM    tbl_inward_list
WHERE   date_created BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 2 MONTH ) AND DATE_SUB( CURDATE() ,INTERVAL 1 MONTH )

But I want a query between 31 to 60 also 61 to 90 like this way.

WHERE   date_created >= CURDATE() - INTERVAL 60 DAY
  AND   date_created  < CURDATE() - INTERVAL 30 DAY

WHERE   date_created >= CURDATE() - INTERVAL 90 DAY
  AND   date_created  < CURDATE() - INTERVAL 60 DAY

No, don't use 31 and 61; use the inequality.

CURDATE() will give you boundaries at midnight; NOW() will be based on the current time.  (This matters if date_created is a DATETIME instead of a DATE.)

Don't use DATEDIFF, it would let you use any index involving date_created for efficiency.


Should I duplicate the data or have linking table? We have the following data:

Collections of items
Individual items

These will go into the following tables:

ItemCollectionTable (Id, Name, OtherFields (desc, date, etc..))
ItemTable (Id, OtherFields)

When an item is added the user will be giving it a collection straight away which is fine. We have a situation however, where users may want to take a whole collection and copy it into a new collection. To do this we've come up with these solutions:

Create a duplicate for every item in the Item table with the original scenario, only changing the PK and Scenario FK.
Problem obviously being duplicating data

OR

Use a linking table showing that an item is in two collections, only adding a new entry when data between the two differs.
The problem being the huge complexity of accessing the data but saves the duplicating issue above

Is there an obvious better solution or does the amount of data in the collection change the answer to go with? I'd be very grateful if there was another solution instead?

It's hard to say, without knowing how you plan to use this data.

For your first option, where you make physical copies of the records when the collection is copied, this might make sense if you plan to treat each instance of an item as distinct, and you anticipate that the details of the copied items will eventually diverge from where they were copied.

The second option makes more sense to me (but again, I don't have enough usage information). This option would let an item be referenced in multiple collections, but they are not true copies. I'd build the tables like this:

items
-----
  id (PK)
  (other fields)

collections
-----------
  id (PK)
  (other fields)

items_in_collections
--------------------
  id (PK)
  item_id (FK to items.id)
  collection_id (FK to collections.id)
  (other fields)

With this structure, you can easily copy an item to another collection by adding a new record to items_in_collections with the collection_id of the new collection. Accessing the data is not that complex. Here's how to get all items for a specified collection

select items.*
from items
inner join items_in_collections on items.id = items_in_collections.item_id
inner join collections on collections.id = items_in_collections.collection_id
where collections.id = $COLLECTION_ID --user specified collection ID


MySQL ON DUPLICATE KEY UPDATE performance issue We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like

insert into foo (catgeory_id, family, text)
values ('1', 'test', 'my text'), (50k more rows)
on duplicate key update family = values(family), text = values(text)

Running that solely is relatively fast. Our production server has many databases and when we run the script on the different databases at the same time the whole machine starts swapping and on some point the kernel is killing the mysql process.

Where should we start to optimize? We are running MySQL 5.5.

Try to start a transaction or set autocommit=false prior the updates.

start transaction;
.... a lot of updates here ...
commit;

Also, the swapping may yield to the lack of physical RAM which occurs in some situations (big resources created by MySQL).

You may also try to
- increase the "key_buffer_size" to the maximum permitted by your SO and MySQL version (check manual). This will improve index updates.
- reduce "max_heap_table_size" (128MB or less) and increase "tmp_table_size" (the bigger, the better).
The idea is to avoid swappiness when some resources needs too much RAM, by sending the work to on-disk temporary tables by default. Some operations will suffer on speed (the bigger ones), but in whole the server will run smoother.

Of course, you may have a lot of memory so a perfect answer cannot be supplied without all informations, at least RAM quantity and server variables, because your problems may start there.


Backup not creating a new file I run a daily backup of my database through command prompt:

c:\sqlcmd -H localhost -Q "BACKUP DATABASE test TO DISK='c:\test.bak'"

But the new backup replaces the previous backup. Please tell me what to change so that all backups will be stored on disk.

Instead of writing your own backup solution, I would suggest you to look at SQL Server Maintenance Solution by Ola Hallengren.

If you want to use sqlcmd then you can do as below:

Save below file as backup.sql

DECLARE
    @DatabaseName sysname = N'test_kin'
    ,@DatabaseBackupFileName varchar(255);

SET @DatabaseBackupFileName =
    'C:\test\' + @DatabaseName
      + REPLACE(CONVERT(char(8), GETDATE(), 103), '/', '_') + '.bak';

BACKUP DATABASE @DatabaseName
TO DISK = @DatabaseBackupFileName with init, stats =10;

Then using sqlcmd you can do as below :

sqlcmd -S ServerName\InstanceName -i C:\YourScriptDir\Backup.sql


PostGIS: how to retrieve a report of costs by decades from a table I have a table with a list of 'films' and, for each film, information about the 'company' that produced, the 'cost' of the production and the 'year' of production. I want to consult the 'total_cost' that each 'company' spent every 'decade' from the 1940s to the 1990s, so the information retrieved should be a table with the following columns:

'company'
'sum_cost_1940s'
'sum_cost_1950s'
'sum_cost_1960s'
'sum_cost_1970s'
'sum_cost_1980s'
'sum_cost_1990s'
'total_sum'

I´ve been able to obtain a new table all the information in three columns ('company', 'decade', 'sum_total_decade'), with the next consult:

SELECT company, sum(cost) AS total_cost_by_decade, trunc(year,-1) AS decada   FROM data_table GROUP BY company, decada ORDER BY company, decada ASC

And I've benn able to obtain a new table with the columns 'company', 'sum_cost_1950s', 'total_sum', using the next consultation:

SELECT base.company AS company,
       decada5.sum_cost AS sum_cost_1950s,
       base.total_sum
FROM   (SELECT company,
               sum(cost) AS total_sum
       FROM data_table
       GROUP BY company
       ORDER BY company ASC) base,
       (SELECT company,
               sum(cost) AS sum_cost
       FROM data_table
       WHERE trunc(ano,-1) = '1950'
       GROUP BY company ORDER BY company ASC) decada5
WHERE base.company = decada5.company

But I wans't able to increase the table, with only one SELECT, with the other decades. How can I to it?

Postgres allow to extract DECADE from a timestamp value, use a CASE statment to sum each one.

SELECT
    company,
    sum(case when extract(DECADE from movie_date) = 195 then cost else 0 end) as Total_Decade_50s,
    sum(case when extract(DECADE from movie_date) = 196 then cost else 0 end) as Total_Decade_60s,
    sum(case when extract(DECADE from movie_date) = 197 then cost else 0 end) as Total_Decade_70s,
    sum(case when extract(DECADE from movie_date) = 198 then cost else 0 end) as Total_Decade_80s,
    sum(case when extract(DECADE from movie_date) = 199 then cost else 0 end) as Total_Decade_90s,
    sum(case when extract(DECADE from movie_date) = 200 then cost else 0 end) as Total_Decade_00s,
FROM
    data_table
GROUP BY
    company
ORDER BY
    company;

In case you get an error on extract sentence, cast your date as timestamp.

extract(DECADE from film_date::timestamp)


two auto incrementing columns OK I have hit an issue, now I was going to look at having two auto incrementing columns, but I cant using InnoDB.  So I am after a Solution.

I have a table called queue. Everything to do with my task queue should go into it, But each time something happens with a task I want it to write a new entry, so I can see what has been happening with my tasks. But I also Need a Primary ID for each entry, and a different key for the Tasks

So any suggestions, in how I might go about conquering this small issue.

CREATE TABLE IF NOT EXISTS `Queue` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TaskID` int(11) NOT NULL,
`Task` varchar(255) NOT NULL,
`Client` varchar(30) NOT NULL,
`AddedInfo` varchar(255) NOT NULL,
`Status` varchar(20) NOT NULL,
`Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
);

If the Queue table is MyISAM, I have just the thing for you.

OPTION #1

First, you should store task names in its own table

CREATE TABLE IF NOT EXISTS `Tasks` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Task` varchar(255) NOT NULL,
    PRIMARY KEY (`ID`).
    UNIQUE KEY (`Task`)
) ENGINE=MyISAM;

Then, remove Task and TaskID from Queue and change the primary key to (TaskID,ID)

CREATE TABLE IF NOT EXISTS `Queue` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `TaskID` int(11) NOT NULL,
    `Client` varchar(30) NOT NULL,
    `AddedInfo` varchar(255) NOT NULL,
    `Status` varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`TaskID`,`ID`)
) ENGINE=MyISAM;

Then, you would do something like this each time

INSERT IGNORE INTO Tasks (Task) VALUES ('Mow the Lawn');
SELECT ID INTO @GivenTaskID FROM Tasks WHERE Task = 'Mow the Lawn';
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Valued Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Forgot to Pay','Suspended');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Has Not Paid in 90 Days','Deadbeat');
INSERT IGNORE INTO Tasks (Task) VALUES ('Plant New Flowers');
SELECT ID INTO @GivenTaskID FROM Tasks WHERE Task = 'Plant New Flowers';
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Pamela Edwards','First Time Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Pamela Edwards','Made First Payment','Active');

OPTION #2

If you do not want to introduce a new table for tasks then redo the table like this

CREATE TABLE IF NOT EXISTS `Queue` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Task` varchar(255) NOT NULL,
    `Client` varchar(30) NOT NULL,
    `AddedInfo` varchar(255) NOT NULL,
    `Status` varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`Task`,`ID`)
) ENGINE=MyISAM;

Then, you would do something like this each time

INSERT INTO Queue (Task,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Valued Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Forgot to Pay','Suspended');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Has Not Paid in 90 Days','Deadbeat');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Plant New Flowers','Pamela Edwards','First Time Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Plant New Flowers','Pamela Edwards','Made First Payment','Active');

EPILOGUE

In both cases, you will have ID of 3 for 'Mow the Lawn' and an ID of 2 for 'Plant New Flowers'

I have done this before. See my posts

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin

GIVE IT A TRY !!!


Update all columns from another table I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this:

update tableA
set * = tableB.*
from tableB where tableA.id = tableB.id

I tried in psql, it doesn't work. I have to list every column like this:

update tableA
set c1 = tableB.c1, c2 = tableB.c2, ...
from tableB where tableA.id = tableB.id

tableB is created use create .. like tableA. So they are basically identical. And the reason I'm doing it is that I need to load .csv data to a temp table tableB and then update tableA based on the new data in tableB. tableA needs to be locked as little as possible and tableA needs to keep integrity. I'm not sure 'delete then insert' would be a good option?

There is no syntax variant that lets you update the whole row at once. However, there is a shorter form than what you have so far.
Also, you do not actually want to update all columns. The WHERE condition on id pins down at least one column (id) to remain unchanged. But that's just nitpicking.
UPDATE table_a a
SET    (  c1,   c2, ...)
     = (b.c1, b.c2, ...)
FROM   table_b b
WHERE  a.id = b.id;

More details in this related answer:
Bulk update of all columns
DELETE / INSERT
Internally, due to the MVCC model of Postgres, every UPDATE effectively inserts a new row anyway and marks the old one as obsolete. So, behind the curtains there is not much difference between UPDATE and DELETE plus INSERT.
There are some details in favor of the UPDATE route:

HOT UPDATE.
TOAST tables:  If you have large columns, the content may be stored."out-of-line" in TOAST tables and the new row version can link to the same row in the TOAST table if toasted columns remain unchanged.
Index maintenance may be cheaper for updates.

Otherwise, locking should be about the same. You need an exclusive lock on affected rows either way. Just make it quick.
If you are dealing with a huge number of rows and you don't need a consistent state (all rows or none), you can split the operation into multiple batches. (Separate transactions!) Increases the total cost, but keeps the lock time per row shorter.


Help on designing SSAS Dim and Fact table I am having a problem designing a Dim and Fact Table for the following problem.

I have a Pivot Grid with data as below :

USER | RELATED USER | RELATIONSHIP | PHONE NUMBER
--------------------------------------------------
Alex | Alex         | Self         | 123456
     | Alice        | Spouse       | 123456
--------------------------------------------------
Alice| Alice        | Self         | 654321
     | Alex         | Spouse       | 654321

If i try to remove the Related User Dimension, i am getting:

USER | RELATIONSHIP | PHONE NUMBER
-----------------------------------
Alex | Self         | 123456
     | Spouse       | 123456
-----------------------------------
Alice| Self         | 654321
     | Spouse       | 654321

Anyone can help me how to design the table for this SSAS Cube, so that it will show as?

USER | RELATED USER | RELATIONSHIP | PHONE NUMBER
--------------------------------------------------
Alex | Alex         | Self         | 123456
       Alice        | Spouse       | 654321
--------------------------------------------------
Alice| Alice        | Self         | 654321
     | Alex         | Spouse       | 123456

and

USER | RELATIONSHIP | PHONE NUMBER
----------------------------------
Alex | Self         | 123456
Alice| Self         | 654321

Thanks >.<

You want your Users table to be just the Self records, as a dimension table. Phone number should be a column here. Then you want your Relationships table to be your Fact table, hooked into the Users Dimension table twice, once through Original User and once through Related User.

For your main report, pull the Phone number from the Related User dimension, not the Original User dimension.


Is MySQL/MariaDB getting a better IOPS deal than Postgres in RDS? According to the documentation:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

The page size of Mysql is twice bigger than the postgresql one:

MySQL/Maria, 16 KB Pages, 30,000 max IOPS

PostgreSQL, 8 KB Pages, 30,000 max IOPS

This is a problem because the max data block considered a single IOPS is 32kb in RDS. so if you are asking for 30,000 pages pf MySQL data you may reach a 480Mb/s throughput vs 240Mb/s in Postgres for exactly the same amount of money.

Is this really how RDS works? is MySQL twice as good in disk access cost according to the AWS pricing?

As I read that page, "Provisioned IOPS works with an I/O request size of 32 KB. An I/O request smaller than 32 KB is handled as one I/O; for example, 1000 16 KB I/O requests are treated the same as 1000 32 KB requests.", your conclusion is correct.

MySQL has an additional advantage: innodb_flush_neighbors.  If on, it will try to combine adjacent blocks together, thereby cutting down the number of IOPs.  (This is designed to cut down on head seeks on hard drives, but counterproductive on SSDs since there is no seek time.)

(I don't know if Postgres has a similar thing.)


How do I delete a file that has been locked by UTL_FILE? I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type).

Now I can't delete the file. Short of restarting the instance or rebooting the box, is there anything I can run within Oracle to clear the handle? I've system user access to the box in question.

Update: Oracle is running on Windows.

Is the session dead?

SELECT sid, serial# FROM v$session WHERE /* conditions */;

-- ALTER SYSTEM KILL SESSION '<sid>, <serial>';
ALTER SYSTEM KILL SESSION '123, 456';

If killing the session doesn't work, you can explicitly close the file handle with a SysInternals utility called Handle.

https://technet.microsoft.com/en-us/sysinternals/bb896655

I also found a SuperUser thread on this which mentions LockHunter and Process Explorer as other tools that can do the job:

https://superuser.com/questions/425727/how-to-unlock-file-without-kill-related-process


How do I rebuild single full text catalog (or why is "rebuild" full text catalog option disabled) In SSMS. I have Full Text Catalog defined. If I select "Full Text Catalogs" folder - there is an option "Rebuild All" and it is enabled.
But if I select individual FTC - "Rebuild" option (in context menu) is disabled.

Question Why is it disabled? and how do I enable it?

Unless there's something weird going on, this state occurs when the catalog is currently being populated.

You can see the current state of the catalog by looking in the Properties window of the catalog.

So... wait until the catalog has finished populating, and then refresh the node in SSMS.


How can deleting similar number of rows result in very different log growth? I have (in my opinion) very strange behavior of SQL Server log growth. I have a database with approximately 8GB log size. I made transaction log backup and started deleting about 4 million rows. The log file did not grow. Then I tried to delete another 4.9 million from another table - the log file grew to about 26 GB and filled the drive.

I managed to fix the situation, but I was curious - since (as far as I am aware) the log file contains only commands to delete records (in this case), how can there be such a big difference between 2 operations when the number of rows affected is only 20% different?

Way too much for a comment, but just to prove that the amount of data has a much bigger impact on how much gets logged, compared to simply the number of rows.

First, create a database, and back it up so we aren't in pseudo-simple:

CREATE DATABASE RLS;
GO
ALTER DATABASE RLS SET RECOVERY FULL;
GO
BACKUP DATABASE RLS TO DISK = 'c:\temp\rls.bak' WITH INIT;
GO
USE RLS;
GO

Now, create a skinny table and a wide table, and insert 2,000 rows:

CREATE TABLE dbo.skinnyTable(id int PRIMARY KEY);

CREATE TABLE dbo.wideTable(id int PRIMARY KEY,
  dt datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
  n nchar(2000) NOT NULL DEFAULT USER_NAME(),
  x uniqueidentifier NOT NULL DEFAULT NEWID(),
  y uniqueidentifier NOT NULL DEFAULT NEWID(),
  z uniqueidentifier NOT NULL DEFAULT NEWID());

INSERT dbo.skinnyTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

INSERT dbo.wideTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

Backup the log twice:

BACKUP LOG RLS TO DISK = 'c:\temp\rls1.trn' WITH INIT;
BACKUP LOG RLS TO DISK = 'c:\temp\rls2.trn' WITH INIT;

With a clear log, let's delete half of each table (1,000 random rows):

;WITH cte AS (SELECT TOP (1000) * FROM dbo.skinnyTable ORDER BY NEWID())
  DELETE cte;

;WITH cte AS (SELECT TOP (1000) * FROM dbo.wideTable ORDER BY NEWID())
  DELETE cte;

SELECT AllocUnitName, log_rows = COUNT(*)
  FROM sys.fn_dblog (NULL, NULL) AS l
  WHERE AllocUnitName LIKE N'%[ey]Table%'
  GROUP BY AllocUnitName;

I get:

AllocUnitName                                     log_rows
----------------------------------------------    --------
dbo.skinnyTable.PK__skinnyTa__3213E83F83E76BF2    2008
dbo.wideTable.PK__wideTabl__3213E83FEFF3F638      14991

Clearly, there is much more log space required to delete the same number of rows from a wide table, when compared to a skinnier table.

As a logical extension, number and width of indexes can influence this difference as well. And I didn't even try LOB data, nor did I investigate the rows where AllocUnitName was NULL. The above alone demonstrates a 7X difference.


MySQL - Does using an explicit transaction affect query performance? In MySQL 5.6, using InnoDB, I have a rather large table, call it A; (about 4 million rows) in which I need to update a field. The condition regarding what to update is held in another table, B; and the relation between the two is also held in another table, C; so I created my update statement with two joins, like so :

UPDATE A a JOIN (B b, C c) ON (conditions) SET a.myField = 'myValue'

Now I tested this statement on a dump of my target database, on a equivalent machine. Because I needed this dump for other purposes, I tested the thing using a transaction in order to rollback afterwards :

START TRANSACTION;
UPDATE [...]
ROLLBACK;

Which gave me pretty good performance (I think) for my update, about 17 seconds and 8 for the rollback.

My question is this: If I were to run this statement as is, not in a transaction, on my target database, would I obtain the same performance ? In other words, does using explicit transactions affect performance, and if yes, how ?

I tried to look the documentation on this but it is rather sparse on this topic.

1: Using autocommit to automatically commit each transaction:

SET autocommit = ON;
UPDATE ...

2: Explicitly turning a single statement into a transaction.  This is 'identical' to #1.

BEGIN;     (aka START TRANSACTION;)
UPDATE ...;
COMMIT;

3: Multiple statements in a transaction:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

4: Don't use this; some day you will forget to COMMIT:

SET autocommit = OFF;
UPDATE ...;
...
eventually: COMMIT;

5: Not advised.  It can lead to slowdowns due to overflowing the "log":

BEGIN;
a million statements
COMMIT;

Notes:

Each SQL has some small overhead (network transmission; parsing; etc)
Each transaction as a noticeable overhead.  In particular there is disk I/O to make it crash-safe.  Hence #3 is faster than two copies of #1 or #2.
See innodb_flush_log_at_trx_commit for a tradeoff between integrity and speed.

Advice:

First priority: Do what the application needs -- this is, put the appropriate number of statements in each transaction.
Lower priority: Throw together multiple statements when suitable, thereby amortizing the transaction overhead.
In the case of INSERT, it is even better to have a single INSERT statement with multiple rows.  Davit's Comment show a 9x speedup by putting multiple INSERTs in a single transaction.  That is similar to the effect of the single batch insert.
Use ROLLBACK only when appropriate.  It is costly.  This is because InnoDB optimizes for COMMIT and has to do extra effort to undo.
For performance improvements, this whole discussion is pretty far down the checklist.  More important things involve indexing and query formulation.
Multi-thousand-row DELETE and UPDATE have a lot of overhead and stress on the log.  It is better to break them up.


How to set up simple database replication using WAMP server? Just need a little help here about replication. I have an intermediate knowledge in Mysql. And I can't find any simple solutions on web how to establish a database replication in Mysql. All I need to do is to create a back up from my master database to my localhost.
For example if I have a master database on 192.145.114.12 and a database 'myorg_site' and in my localhost I have database 'bk_myorg_site'. Any changes in the table data in myorg_site will change as well in bk_myorg_site. Can you provide me a simple instruction for that? Starting from mysql configuration? That's all guys I hope you can help me. Thanks.

Replication setup is very easy, please follow these steps to make proper replication setup:

1) Backup your Master server's data as:

  mysqldump --all-databases --master-data > dbdump.db

2) Import this backup file on Slave server

3) Create a user in Master server to connect to Slave

  GRANT REPLICATION SLAVE ON .  TO 'repl'@'slaveip'
   IDENTIFIED BY 'slavepassword';

4) Edit Slave server and give server-id to something other that Maser server_id in my.ini file as well specify replicate_do_db as database you want to replicate from Master.

This will replication only database which you want to replicate to slave server, other database from Master will be skipped for replication

5) Execute this query on Master and get it's filename and position

  Show Master status;

6) Now execute this query on Slave

  CHANGE MASTER TO
                  MASTER_HOST='masterip',
                  MASTER_USER='slave_user',
                  MASTER_PASSWORD='slavepass',
                  MASTER_LOG_FILE='Master-bin.000001',
                  MASTER_LOG_POS=101;

7) Now execute this on slave

  Start Slave;

You are done with your replication

8) To check your replication status execute this query on Slave server:

  Show slave status;


When to use JSONB type for such a column? I am designing some schema in my Postgres 12. And I have an entity (lets call it "Laptop") with an attribute (lets call it "Properties") like:
{ <someKey>: { <someLabel>: <someValue> } }

For example, the laptop has these properties:
{ "cpu": { "count": 8, "model": "Core i7" },
  "disk": { "size": 123456789, "model": "Samsung", "speed": 7200 },
  ...
}

And each laptop has own set of properties...
And now I have to choose how to implement it: as tables or just as a JSON/JSONB column in the table "laptops". What are the criteria / principles when to use JSON/JSONB columns or to design such attributes as tables?

The rule of thumb is: if the data are just payload to the database and will never be used in WHERE conditions and the like, use json. Else, use jsonb.


MySQL selects/updates only using 1 core I have a server with 4 physical Xeon processors, each with 8 cores and each core is capable of 2 threads.

When I run MySQL queries I'm only seeing 1 CPU go to 100% in top and the total CPU usage at only 1.5%. I'm using MyISM as the MySQL engine.

Is it possible for MySQL to use all 64 threads instead of just the single one it is using right now?

If so, what outside of the default config do I need to change?

So far I tried everything in this article: Possible to make MySQL use more than one core?

put innodb_thread_concurrency at 0
innodb_read_io_threads and innodb_write_io_threads to 64.

The only other idea I had was to run the query 64 different times and piece the results together which actually does engage all the threads but that seems a rather crude way to accomplish the multi-threading.

The innodb threads have their place; but they do not help you out in your SELECT query. With MySQL, unfortunately, your SELECT can only use one single core.

This immediately means that if you want to use more cores, you must parallelize your queries. Very often this is possible. Do you have this huge script you're running? Are there independent parts to it? Parallelize them.

But if you cannot parallelize for some reason, you're more limited. Take a look at Shard Query. Either use it or get some ideas from it.


Backup to local and MIRROR TO Azure Files returns ERROR I have an Azure VM with SQL Server 2014, with a local drive (X:) for storing backups. Using Ola Hallengren's scripts. I added an Azure Files share and tried to do a mirror backup, but I get the following error:

BACKUP DATABASE [Test] TO DISK =
    N'X:\Backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak'
MIRROR TO DISK =
    N'\\storage.file.core.windows.net\backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak'
WITH NO_CHECKSUM, COMPRESSION, FORMAT

Msg 3212, Level 16, State 0, Line 3
The mirror device "X:\Backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak"
and the mirror device "\\storage.file.core.windows.net\backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak"
have different device specifications.

Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Outcome: Failed

Initially, the local drive was formatted with 64K allocation unit size. Tried switching to 4K, and using the BlockSize switch, but no luck. Any ideas?

I can't see that you are using backup to URL. It looks like a local drive and a file share.

If you read about mirrored backups you see that there are some special requirements:

"Mirroring applies both to disk and tape (disks do not support continuation tapes). All backup devices for a single backup or restore operation must be of the same type, disk or tape."

"Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent, such as, only drives with the same model number from the same manufacturer."

https://msdn.microsoft.com/en-us/library/ms175053.aspx


SQL Server query performance affected by rebuilding an un-fragmented index I have a query within a stored procedure that the same input will periodically time out ( > 30 seconds)  when it normally runs in milliseconds.
The table has 2077748 rows, 59 columns, and 13 indexes.
We have found that rebuilding one of the indexes takes the performance back to milliseconds.
Here is the fun part.  The percentage fragmentation on this index every time it is rebuilt is < 1%, the last time it was .01%.  I've been playing with the fill factors because I thought it may be an expansion issue, it was 100, then 80, now 60.  This has not effected the frequency of this problem.
When I run the following
select b.name,*
from sys.dm_db_index_physical_stats(5,1075691080,NULL,NULL,NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id

I get

index_id  partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count
66    1   NONCLUSTERED INDEX  IN_ROW_DATA 3   0   0.01    1116    18.9229390681004    21118

Any suggestions on other places to look or sources of this problem?

It sounds like the rebuild of the index is a red herring, especially given it was non-clustered. The rebuild is more likely to be invalidating a bad query plan in cache, and on re-run the good query plan is generated and used again.

Captue the query plan when the performance is bad and then after the rebuild and compare them, I suspect you will see a different plan.

That would be my first step - e.g. demonstrate what changes to the plan due to the index rebuild, or prove there is no change on the plan.


Track grant permission on tables I'd like to see if there are any changes on the permissions granted for a specific table.

For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices.

If this permission is somehow GRANTed to John, I want to know about it. I'd like to know who has granted him the permission and when.

I'm using SQL Server 2012 Enterprise Edition.

Since you're using SQL 2012, you can leverage SQL Server Auditing, which was introduced with SQL 2008.  SQL Server Auditing can be used to track a multitude of actions within SQL Server, include database object permission GRANT/REVOKE/DENY actions.  You'll be able to see the security principal that executed the statement, when the statement was run, and what that exact statement was.  To create your audit, you will need to perform the following:

Create an Audit

The Audit defines the over-arching logging for all the audits.  To create an Audit, you can use the object explorer to browse under the instance to Security->Audits.  Right click on Audits and select "New Audit".  Fill out the next dialog (the important bit here is where you store the audit file, the rest you can use defaults on).  After you create it, you will need to right click on the Audit in the object browser and enable it.

Create an Audit Specification

The Specification defines what action you want to track, using any combination of actions/groups to define the Specification.  There are many different actions, both on the server and database level, that can be tracked.  To create a Specification for database permission changes, browse under the database you wish to track in to Security->Database Audit Specifications.  Right click and fill out the dialog.  Select the Audit you created above to write to.  Under Audit Action Type, select SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP from the drop down.  This action allows you to track permissions changes on schema objects.  Once you have this, click 'Ok' to create the specification.  Then, in the object browser, enable it.

Viewing the audit

Once this is all complete, the audit is running and will capture and GRANT/REVOKE/DENY actions in that database.  To view the audit logs, browse to the audit under the instance in Security->Audits.  Right click on the audit and select 'View Audit Logs' and you will be able to see all the audit actions that have occurred in your database since you started the auditing.

As an alternative, your auditing can be enabled using T-SQL with the following commands:

--Create Audit
USE [master];

CREATE SERVER AUDIT [PermissionTracking]
TO FILE
(   FILEPATH = N'C:\DBData'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

--Enable Audit
ALTER SERVER AUDIT [PermissionTracking] WITH (STATE=ON);

--Create Database Audit Specification
USE [foo];

CREATE DATABASE AUDIT SPECIFICATION [FooDBPermissions]
FOR SERVER AUDIT [PermissionTracking]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE=ON);


Functional Indexes in Oracle, compared to MySQL Hoping you can help me understand a particular problem I'm facing when converting an Oracle 12c database to MySQL 5.7.

For context, let's say we have a table that looks a bit like this:

id    firstname    lastname
---------------------------
1     John         Jones
2     tom          smith
3     ALEX         JonEs

Data can be entered into the table in any case or mixed case, there is no control at the application level or any constraints or triggers at the database level.

So for the purpose of effective querying, a functional index has been created in Oracle as follows, because we will always query the lastname field using the lower case function:

CREATE INDEX idx_table1_lastname ON schema1.table1 (LOWERCASE(lastname))

We can't do that in MySQL because functional indexes are not a thing. The same column in MySQL has been created as collation utf8-general-ci so my question is this.

If we create the same index on our MySQL database, we can only do:

CREATE INDEX idx_table1_lastname ON table1 (lastname)

Will the index be effective, given the collation of our column is case-insensitive? Or should we be putting something in place to ensure all our database writes for this field are being written in a consistent case?

Thanks

MySQL does not have functional indexes but it does have (at least in version 5.7 and in MariaDB 5.5+) generated columns which can be indexed.

For details see the official documentation: Generated Columns.

Example of usage:

ALTER TABLE schema1.table1
    ADD lower_lastname VARCHAR(100) AS (LOWER(lastname)) ;

CREATE INDEX idx_table1_lastname
    ON schema1.table1 (lower_lastname) ;

And test at dbfiddle.uk where you can see that the index is used.

Now, the above construction is not needed if you never need to distinguish 'Jack' from 'jack' and you have defined the column with a case insensitive collation. You can just the case insensitive column and index it.

The construction is useful if you want to have case sensitive and case insensitive comparisons and also in other areas where the Oracle database has functional indexes (i.e. with numeric or date columns).


Pick up the name of the column by two diff column in single table Thank you guys in advance! i am desperately need to display my data in this way,

My database structure is,

Column idcat, parent_id and child_id is sharing single column name, catname.

I tried to write my own query,

SELECT idcat, parent_id`, child_id, catname, catDesc, catstat, del_cat
(SELECT catname WHERE parent_id=idcat FROM categories ) as parent,
(SELECT catname WHERE child_id=`parent_id FROM categories) as child
FROM categories

I hope you can help me guys! thank you!

Use aliases like that:

Select a.Column, b.Column
FROM TableA AS a
JOIN TableB AS b on a.TableBId = b.Id

This way the engine knows when you want to use Column from TableA and when from TableB.

It is a good practice - you should always use aliases when writing SQL queries.

The query you are looking for might be:

SELECT c.catname
    ,(
        SELECT c1.catname + coalesce('>' + cast(
                    SELECT c2.child_id
                    FROM categories c2
                    WHERE c2.idcat = c1.child_id
                    ) AS char(50)), '')
        FROM categories c1
        WHERE c.parent_id = c1.idcat
        )
    ,c.catDesc
    ,c.catstat
FROM categories c


Foreign Key Constraint into an different database An SQL server hosts all the data for multiple entities (e.g sub-companies or organizational units OU).
An "enterprise" database holds the "enterprise" data such as configuration data, data about processes that are enterprise-wide ones, and at least one lookup table which stores the data about all the OUs.
The initial design called for the option to be able to split the database of some or all OUs into their own database server that can even physically reside in a different location. So far, both databases reside on the same SQL server.
Every table has a column identifying the id of the OU (OUId) as an FK for OU information and will enable to make the split in the future.
Would you replicate the OU table instead of setting an FK to an external database? How much performance hit an FK to an external database can cause?
Thanks

Would you replicate the OU table ...?

Yes.  Because local lookups of master/reference data are important for application performance, reliability, and to enforce referential integrity because this:

an FK to an external database

is not possible.


Ordering of table is based on Clustered index or non clustered primary key? I have created a table with primary key on name column, the ordering of the table is based on name column

create table TestPrimaryKey (Name varchar(20) primary key,id int identity)

insert into TestPrimaryKey (SomeText) values ('john')
insert into TestPrimaryKey (SomeText) values ('ryan')
insert into TestPrimaryKey (SomeText) values ('jennifer')
insert into TestPrimaryKey (SomeText) values ('martha')
insert into TestPrimaryKey (SomeText) values ('luke')
insert into TestPrimaryKey (SomeText) values ('alex')
insert into TestPrimaryKey (SomeText) values ('wayne')

Now I'm changing the clustered index on primary key to nonclustered index, still the ordering is based on the name column

    begin tran
    alter table dbo.TestPrimaryKey drop constraint PK__TestPrim__BC2905030401D947
    alter table dbo.TestPrimaryKey add constraint PK__TestPrim__BC2905030401D947 primary key nonclustered (SomeText)
    commit tran

Now I'm creating a unique clustered index on the other (id) column, still the ordering is the same

    create unique clustered index CI__TestPrim__BC29050354394FAA on TestPrimaryKey(id)

Now I'm dropping the clustered index from the table

    drop index CI__TestPrim__BC29050354394FAA on TestPrimaryKey

But now the ordering changes from the "name" column to the "id" column. I need to know why that happens and also if the table has a non clustered primary key and a clustered index column will the ordering be based on primary key column, clustered index column or both.

Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier.

If there is a clustered index then the data pages are stored with the rows in this order, but that does not guarantee that they will be processed in exactly this order (it makes it likely if there are no joins or ordering clauses, but with no explicit ordering request the order rows will be output is officially "undefined").

Without a clustered index the rows are stored in data pages in an unordered manner (this is referred to as a "heap table"). This may initially be the same order that the data was inserted, but as soon as deletes and updates start to happen this becomes definitely not true.

If you want to read around a little theory lookup "set theory" upon which relational databases are based. Mathematical sets are by their nature unordered, which means anything in a relational database should be considered unordered by default.

Regarding non-clustered primary keys: these do not affect the ordering of stored row data at all. They have extra meaning to your data model but the don't physically do anything that a non-clustered index with the "unique" property set would do. Similarly a clustered primary key is just a clustered unique index internally with extra meaning for your data model.

tl;dr: if the data needs to be ordered a particular way, you must directly specify that order in an ORDER BY clause.


Is there any limit on how deep the Server Owner can access my data? this is my first post on DBA. I'd like to get some clear ideas from you.
First of all, this may sound as a discussion but it's not a debate.
What I'm looking for could be something like "Yes, they can do whatever they want" or "No, no way!"

Recently I've finished developed a web application. Once it's done I was told to contact Server Area department so they could help me out with uploading my SQL db, grant permissions, etc etc.

So, I did that.
They explained how they were used to work and we agreed on uploading a non-operational version db, so we could test permissions etc. They told me to handle them a back up of the db. They would restore it.
First thing making noise within me. Shouldnt I be the one restoring back ups? "Well.. I'm no dba yet, I'm sure they know better." was my answer to myself since those could be their practices.

Two days later they send me an email, saying the backup is up and giving some suggestions.
They needed my db tables to be normalized and have the proper indexes.

My reaction wasn't sweet. First of all, we both had talked about it and the back up wasnt not the operational one... So, are any conclusions relevant?... and bay the way, tables are all fully normalized. Second, is it ok for them to access that deep? What if the information contained in my db is critical?

Thanks a lot, hope for some insights on this matter.

From what I gather in your post, you are on the development side and they are on the administration side.  I can see why they would like to handle the administration portion of the database(s).  That would most definitely include backup/restore, security, etc.  If they are responsible for the maintenance and administration of the data layer, then that is more than appropriate.

As for their suggestions about data normalization, there is a possibility there suggestions are prudent.  At the same time, that would be a database design/development decision which sounds more like your responsibility.  Maybe they were just friendly suggestions, that's up to you to decide.  But if they were forceful "recommendations" then that would be another story, as you application is likely to adhere to the schema.

As for the restore on a development server, it is very shop-specific.  The overruling argument will be who had jurisdiction on that particular server (or servers).  Again, this is very shop-specific.

What I'm trying to get at is there are too many variables, and as participants of DBA.SE there is really no way we can give you sound advice.  I recommend you take this argument to management in your company that can make a decision one way or another.


Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns? Basically what the title says: What are the differences in performance (if any) between using a clustered index on a GUID column vs a clustered index on an INT column, especially when those columns are part of your join predicates?

Edit: To clarify, I was always under the assumption GUIDs typically perform worse than INTs when used in join predicates.
I understand a GUID is 4x the data size of an INT, but I was asking my question to really understand why and what's going on "under the hood" that causes GUIDs to perform worse, even after the indexes are rebuilt and there's minimal fragmentation differences between the two types.

I understand a GUID is 4x the data size of an INT, but I was asking my question to really understand why and what's going on "under the hood" that causes GUIDs to perform worse, even after the indexes are rebuilt and there's minimal fragmentation differences between the two types.

I would say that the issue is primarily just what you started out with here, in what I quoted: UNIQUEIDENTIFIER is 4x bigger than INT (16 bytes compared to 4). This hurts performance in three ways (I am ignoring fragmentation since the question explicitly accounts for that):

Every bit compared is another thing to do. More things to do for the CPU takes more time. That time won't be noticed on modern systems when looking at 100 rows. But over 1 million rows? Now you are talking about (in terms of bytes), comparing 16 million things vs. comparing only 4 million things.

Memory is not free: neither in terms of money, nor in terms of time spent allocating and deallocating. In order to join rows, data pages containing those rows must be read into memory (the buffer pool). There is not an infinite amount of memory (especially if you are using Standard Edition or Express Edition). The more memory you take up in one operation is that much less left over for other concurrent operations. But even if memory were infinite, it still takes time to load those values into memory so that they can then be compared. Loading 16 million bytes naturally takes longer than loading 4 million bytes.

Disk is not free: even with SSD, physical I/O is still a cost you pay, which is why things are cached in memory. Data pages are only 8kb. If you have a 16 byte value, it will reduce the number of rows that can fit on that 8kb data page as compared to having a 4 byte value. The more data pages that a table takes up, the more physical I/O is needed to read and write the same number of rows.
A related issue is with indexes. You will likely index the columns that you are joining on. Same concern as with the main table: fewer index rows per page means more pages required for the index, meaning more time spent reading and writing to every index affected by any given operation (new row in a table will need to create that new row in all indexes on that table (minus filtered indexes that would exclude it), and assuming we are talking about the clustered index, the clustered index key is copied to all of the non-clustered indexes)


Synonyms and transactions I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code.

BEGIN TRANSACTION
if (@isRemote = 1)
    BEGIN
       CREATE SYNONYM SY_SUBJECTS FOR [MT00011].[DBO].[SUBJECTS];
    END
ELSE
    BEGIN
       CREATE SYNONYM SY_SUBJECTS FOR [SUBJECTS];
    END
-- do some complex work on SY_SUBJECTS
DROP SYNONYM SY_SUBJECTS
COMMIT

This code gets called by multiple requests simultaneously. I am concerned that it will cause problems if this code gets called at the same time. It executes 99% of the time on a single SQL database, and the "remote" case is run after hours.

Is there a problem here?

Whenever you create or drop a SYNONYM you are changing the definition of your database landscape.  So, yes I would consider that risky unless you have tight controls on when those steps can run.

If a connection resets the SYNONYM it is changing that synonym for the Server and database not for the connection. This means that a set of "complex work" running in another process could wind up switching from your remote data (for example) to the local data without any warning.  That would leave a mess to clean up.

View this like you would view dropping and recreating tables in a running system.  It may often work without anyone knowing, but it can indeed cause you problems.

Of course, if another connection tries to define a SYNONYM that already exists it will raise an error such as: "There is already an object named 'SY_SUBJECTS' in the database."  This will save you from switching context (but you must deal with the error) until after the synonym is dropped.  (There is no ALTER SYNONYM function, which would apparently be much more dangerous.)

Therefore if you try to change the SYNONYM without dropping it first, it will fail.

If a code path runs DROP SYNONYM it will succeed once it can acquire the needed locks.  From MSDN: "References to synonyms are not schema-bound; therefore, you can drop a synonym at any time."

So, the DROP SYNONYM will not stop a running transaction, but will wait for the transaction to finish.


What is the point of a consistent and partition tolerant multi-master DB Architecture as opposed to Master-Slave I should emphasize that my question is in regard to CP (Consistent and Partition Tolerant) systems.

A lot of widely used NoSQL databases such as Mongo and Redis seem to fall in the CP category. And in addition to that, RDBMS's such as SQL Server and MySQL can also be configured to operate in a CP multi-master mode.

Two arguments I could find on the Internet are:

Load balancing write queries
Decreasing downtime in case of failure of a Master

I have doubts about the first reason because as far as I understand, in order to maintain data consistency between these Master replicas, the write operation should take place in every Master. If so, that will create the same burden for all the Masters plus the overhead associated for synchronization.

What are the solid reasons for such systems? Or am I missing something?

The two arguments you mentioned are independent of weather the system is CP or AP. They are advantages in general for any distributed system with replication. Let's stick to CP as that is the main topic. In a CP distributed system, with replication factor 2, the write should finish on 2 nodes before the client is acknowledged of the write. So, yes that overhead of writing on multiple nodes and the synchronisation is present. But that distributed system may have 50 nodes. The writes will be load balanced across those 50 nodes. And because the replication factor is 2, even if 1 node is down, the reads and writes can continue without any down time.

why CP then ? Putting in a very very naïve way, CP will never loose acknowledge write but AP can loose acknowledged write in a split brain situation. Also, CP will guarantee that if an application updates a value from A to B, a subsequent read by it will always read B. But in an AP system, it may read either A or B. A CP system will deny a write or read if it cannot guarantee its consistency. An AP is system is more relaxed about it. This can be a pretty big deal depending on the type of application. For e.g. if the application is tracking the number of likes, its probably ok to loose a few in a worst case scenario. If the application is dealing with user's money, it should be accurate in all scenarios. Such applications are typically ready to pay the extra overhead.


non clustered index coverage with INCLUDED columns If I have two indexes:

IDX_1 = (COL1), (COL2) DESC
IDX_2 = (COL1) INCLUDE (COL3)

will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE statement for covering both queries?

IDX_3 = (COL1) INCLUDE (COL2), (COL3)

You didn't show us any queries.

Perhaps you asked whether IX3 will be a full substitute for 1 and 2? If so:

Pretty much. Except:

For IX1, SQL Server can navigate the index tree to find all values for COL1 and keep navigating the tree to find all COL2.
Also, IX1 can potentially assist in eliminating a sort for COL1, COL2 DESC.

Except for above cases, as far as I can see, IX3 will be a substitute for both IX1 and IX2 (pls comment other cases I missed and I'll add them to the above list).

DESC has no bearing on included columns. They are just "brought along", they don't influence any sorting for the index (and the values are only in the leaf).