The biggest issues that jump out to me are the lengths of the NVARCHAR columns (60 characters will certainly not be long enough for all movie titles, and probably not enough for all director names), and the use of the FLOAT data type. In my opinion and experience, FLOAT is almost never the right data type to use.

For Length, the correct type would depend on what is being stored. Most of the time, movie length is stored as a number of minutes (rounded to the nearest whole number). If that is how you are storing it, I would suggest SMALLINT. However, if you are storing length as hours or something where you need non-integer values, I would suggest the NUMERIC (or DECIMAL; they are synonyms and mean exactly the same thing) data type (choose precision and scale to meet your needs; see https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017 for details).

For Rating, I'm guessing you're not talking about something like MPAA rating (which is a string value), but some kind of critics rating or something like that. Again, I would suggest using NUMERIC instead of FLOAT.

Since your question was only about data types, that is all that I have addressed. However, there are certainly other design concerns I would have with this design (like, how are you handling movies with multiple directors, genres, and categories?). But that seems to be out of scope for this specific question.


You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table.

In SQL Server Management Studio, you select the desired target database, right-click the database name, then click 'Import Data', and go through the wizard step-by-step.


Which would be some benefits of storing date ranges instead of single dates?

I use only one date, the date the price took effect. That price remains in effect until the price changes. So if a price changes rarely, there will be few records; if the price changes often, there will be many records.

create table Prices(
  ID      int  not null,    -- identifies the item that is being priced
  EffDate date not null,    -- when this price took/takes place
  Price   decimal not null, -- the price
  ...,                      -- other item data
  constraint PK_Prices primary key( ID, EffDate )
);

Prices:
ID  EffDate     Price
... ...         ...
17  2016-01-01     90
17  2017-01-01    100
... ...         ...

select  *
from    Prices p
where   p.ID = 17
    and p.EffDate =(
        select  Max( EffDate )
        from    Prices
        where   ID = p.ID
            and EffDate <= Today() );

The query will return the current price, 90, every day it is executed this year. After the New Year, it will return the new current price, 100.

The query above asks the question, "What is the current price of item 17?" To ask the question, "What was/will be the price of item 17 on a particular date?" just replace "Today()" with the date.

For example, to see what the date will be on Jan 1, 2017:

select  *
from    Prices p
where   p.ID = 17
    and p.EffDate =(
        select  Max( EffDate )
        from    Prices
        where   ID = p.ID
            and EffDate <= "01/01/2017" );

Of course, using a variable instead of hard-coding the date would give you a single query which will provide the price at any time in the past, the present or any time in the future.


How to check actual execution plan of a query after it has been executed

Prior to SQL Server 2019, there was NO possibility to get the actual execution plan after the query has been executed.

Started with SQL Server 2019+, that option exists.

First you should ALTER DATABASE SCOPE CONFIGURATION.

This option enables the equivalent of the last actual execution plan in sys.dm_exec_query_plan_stats ( the same could be accomplished by enabling TF 2451 ).

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

See more on ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

And than you can execute query like this one in order to get the actual execution plan.

SELECT qps.query_plan,
       st.text,
       DB_NAME(st.dbid) DBName,
       OBJECT_NAME(st.objectid) ObjectName,
       cp.usecounts,
       cp.size_in_bytes /1024 [Size in kB],
       cp.objtype,
   cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.encrypted = 0
      AND DB_NAME(st.dbid) = 'AdventureWorks2016'
ORDER BY [Size in kB] DESC;

And the result is shown in the image below.


How do I allow Service Broker access to Master Key of database?

When the Database Master Key was created a password was supplied to CREATE MASTER KEY it's this password which is required.  If this password is not available to the developers then recreating the encrypted objects losing the currently protected data is the only option.


How (not) to manage SSRS report-execution history retention

Interesting find.  I was unaware of this setting until I built a page that used the SSRS execution history table as the sole source of a report's download count.  To my surprise, the counts disappeared one day.  At that point, I discovered the default value of 60 days retention.  I recall increasing the retention setting in the GUI, but I did not set to 0.

I re-engineered my download count to be stored in a custom table and updated daily via a SQL Agent job using the SSRS history, in order to eliminate any other surprises.  There is definitely conflicting documentation on what "0" accomplishes.  However, if you set the retention days to a very large number (several hundred years), then that is essentially an infinite retention period and should accomplish your goal.


It is only slightly more code to skip the UPDATE if there is nothing to do.  And the code is much cleaner.  And less kludgy.


Optimizing performance of INSERT INTO table from INNER JOIN between two large tables

The easiest way to speed up your query is to insert data from the first query into a #temp table instead of a table variable. Table variables do not get statistics in SQL Server. The query optimizer will always think that your table variable has one row which can lead to very poor performance when the table variable has many more rows than that. Based on your description of the plan, it's possible that the query optimizer is doing full scans of tableA many times which could explain why your query did not finish after 2 days.

Adjusting your code to use a temp table is straightforward. It would look something like this:

CREATE TABLE #accountCard
( numUsu        varchar(10),
quantity int);

INSERT INTO #accountCard
SELECT a.numUsu, count(*)
FROM tableB a
INNER JOIN  tableA b ON b.numUsu = a.numUsu
GROUP BY a.numUsu;

In my experience, table variables can be useful when inserting logging data that you do not want to be deleted when rolling back a transaction. For example, you might detect an error in your application and need to roll back a query. Putting troubleshooting data into a table variable will be preserved after the rollback but putting it into a temp table would not be. Table variables can also be useful when you have a process that runs many times per day (in the thousands at least) that inserts a small number of rows into table variables. Any queries that reference the table variable should not benefit from statistics on the table.

If you think that there's any chance that your queries would benefit from statistics on the table I would err on the side of caution and use a temp table. The query that you have in the question looks something that processes a large amount of data and you definitely can benefit from statistics on the table so I would use a temp table.

If you'd like to learn more about the differences between table variables and temp tables @Martin Smith wrote an excellent summary here.

There is potentially another performance problem with your query. You said that the numUsu column is not unique but you are joining to it twice. Are you sure that your insert query is returning the correct number of rows? Consider the case where you have 100 rows for a single value of numUsu in both tableA and tableB. With the joins that you have you will end up with 100 X 100 = 10000 rows for the single row in your temp table. Is that what you want to happen?

Here's a simple demo to demonstrate the kind of trap that you can fall into:

CREATE TABLE #accountCard
( numUsu varchar(10));

CREATE TABLE #tableA
( numUsu varchar(10));

CREATE TABLE #tableB
( numUsu varchar(10));

INSERT INTO #accountCard
VALUES ('1');

INSERT INTO #tableA
VALUES ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1');

INSERT INTO #tableB
VALUES ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1');

SELECT COUNT(*) -- result is 100 rows
FROM #accountCard a
INNER JOIN #tableB b ON a.numUsu = b.numUsu
INNER JOIN #tableA c ON b.numUsu = c.numUsu;

Of course, depending on your data it's possible that you don't have this problem. I just wanted to mention it as a possible cause for the long running query.


Index issue on upgrade from SQL Server 2000 to 2012

The unstated assumption in the question is that the subquery is executed first, then the outer DELETE is processed. This is not how things work. People write queries that express a logical requirement, then the SQL Server query optimizer tries to find an efficient physical implementation.
The optimizer's decisions are driven by cost estimates for the various possible physical options it explores.
Garbage In, Garbage Out
By using a table variable, the current arrangement deprives the optimizer of two important pieces of information: the number of rows in the table (cardinality); and the distribution of those values (statistics).
In most cases, the optimizer is unable to see the cardinality of a table variable, and guesses at one row. The physical execution strategy it chooses on the basis that there is one row in the table variable is very likely suboptimal for half a million rows.
Assuming one row, the optimizer may well decide that scanning the PersonStudy table looking for matches is a good enough strategy:

In practice, this plan results in the PersonStudy table being scanned 500,000 times at runtime (once per row in the table variable). That is potentially 60,000 * 500,000 = 30 billion rows. No wonder it takes a while.
Given incorrect or incomplete information about the data, the chances are pretty high that the optimizer will deliver a poor execution plan.
Advice

Use a temporary table (e.g. #ExcludedPersons) instead of a table variable. This will provide accurate cardinality information, and allow SQL Server to automatically create statistics.
Constrain the personID columns to be NOT NULL. This gives the optimizer useful information and will allow it to avoid a common problem with NOT IN.
Make the personID column in the temporary table (or table variable) the PRIMARY KEY. Again, this provides useful information to the optimizer (uniqueness, ordering, not null).
Provide a useful index on the PersonStudy table. The suggested index is a reasonable choice, but there may be better options. A good index provides the optimizer with a more efficient data access path.

Especially if you are unable to switch to using a temporary table, test the following (but still add the constraints and indexes/keys mentioned above):

Add an OPTION (RECOMPILE) hint to the query. This will allow the optimizer to see the cardinality of the table variable (but not statistical distribution) at runtime.
Or: Use an OPTION (HASH JOIN) hint. Hash join scales better than nested loops with a table scan. The hash join may well spill and reverse roles at runtime, but this should still be very significantly better than what you have right now.
Or: If your workload often uses table variables with a significant number of rows, test the impact of enabling trace flag 2453. This will expose cardinality as above, without the (typically small) overhead of a plan recompilation.


Mariadb - Variable 'innodb_log_file_size' is a read only variable

You might have to consider two things before doing this.

Ensure innodb_fast_shutdown SHOULDN'T BE 2. If it is? change it
to 1 globally.
Verify  step 1, query: show variables like 'innodb_fast_shutdown';
Shutdown Mysql.
Ensure you copy the iblogfile0&1 into a safer place and then
remove from data dir.
Make changes to my.cnf for innodb_log_file_size as per your
desired option.
Start Mysql

Rollback: If it doesn't start for some reason put back the iblogfiles and start.

Please see link https://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html for detailed explanation

  UPDATE: In our chat we narrowed down that my.cnf was pointed to a
  different file or picked default ones. We forced mysql to pick the
  mentioned my.cnf via mysqld_safe --defaults-file. And it worked.


Query not using index with jsonb - Postgres

Index idx_btree_ig_id will be used only if you write WHERE part like ig ->> 'id' = '"4878142508"'. (I assume that ig has structure like {"id": "4878142508", ...})

If you want to use operator @> you need to create gin index on that column, then you can use this index like so: ig @> '{"id":"4878142508"}'

CREATE INDEX idx_gin_ig_id ON public.influencers USING gin (ig jsonb_path_ops);


Oracle11g - Convert alpha characters to numeric in select

Well, it won't win any code golf awards .. O.o ... but this should work .. the trick is you trying to convert higher letters into double digits . (ie z ==> 26)  if it was 1 to 1 .. TRANSLATE would work quite nicely .. however, I'm not sure to do it more elegantly .. O.o  (not sure if it's better than the REPLACE option or not .. lol)  I suppose with this logic as a base, you could easily turn it into a table driven approach .. using the w_sub query as the join to your mapping tables) ;)

  col after format a50

  with w_input as ( select '1a2b3c4zd5e' c from dual ),
     w_asc as ( select c, level i, ascii(substr(upper(c),level,1))  aa,
                    substr(upper(c),level,1)  cc
                 from w_input  connect by level <= length(c)
               ),
     w_sub as (
        select c, i, aa, cc,
                 case when aa between 65 and 90  -- A-Z
                       then aa-64
                    when aa between 48 and 57  -- 0-9
                       then to_number(cc)
                    else 0     -- non number, non alpha ... could be punctuation, etc.
                 end  zz
          from w_asc
        )
  select c  before,
        listagg(zz,'') within group (order by i)   after
    from w_sub
  group by c
  /

  BEFORE      AFTER
  ----------- --------------------------------------------------
  1a2b3c4zd5e 112233426455

Explanation:

1) w_input is just your input .. put whatever tests you want here.

2) w_asc: is taking the string, and turning each digit into a row ...
   (it also yanks the digit, and converts it to ASCII code )

3) w_sub: this is your conversion/mapping logic - numbers 0-9 ==> 0-9 .. letters: a==>1  z==>26 ... case insenstive.

4) the final query uses listagg to bring the rows back together using the original column "i" to remember the order.

Cheers! :)


dtexec returns immediately without any messages

You are almost there, the syntax you are using is correct by you cannot use an abbreviation /Val. From the official documentation, you can see that:

/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
/Pack[age] PackageName: (Optional). Specifies the package that is executed.
/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
/Proj[ect] ProjectFile: (Optional). Specifies the project from which to retrieve the package that is executed.
Validation and execution phase: The package is run, or validated without running if the /VALIDATE option was specified.

Also note that when executing package from a project don't add the package extension .dtsx
Based on that try to use the following command:
dtexec /Proj my.ispac /Pack my  /Rep V  /VALIDATE


Automated UML2 Class Diagram generation from SQL?

Solution:

Generate SQL file in PowerDesigner (or any other tool capable of
generating SQL, or write it by hand)
Install Eclipse with JPA and Modelling Tools
Follow this guide
When the guide fails you (step 4), open up another tool (or you
could probably do it in Eclipse with a new project), for instance,
PowerDesigner; and reverse-engineer the Java code into a Class
diagram
Profit!

:D


SSIS processing only 5000 rows from csv file to sql table

The source file shows only 5000 coming through from the first step.

This means all of the data in the file got imported and there was a bad delimiter making some of the data get merged in the import or you have error notification / truncation notification turned off and the data was truncated due to destination or transformation constraints.

You could also have configured data rows to skip on accident or there are actually only 5000 rows of data. I'd start by examining the data, then looking into the logs. Make sure SSIS logging is enabled and look to the logs for more information. I would also add a count step between each of the steps and store them into a table for tracking so I can verify the amount of rows coming through each way.


MySQL - Is it possible to assign permission to count the number of tables in a database without permissions on individual tables?

One option would be to give the user access to a single non-NULL column that doesn't provide very much information, but will allow COUNT(*) to succeed.

For example:

GRANT SELECT(TABLE_TYPE) ON INFORMATION_SCHEMA.TABLES TO blind_user@localhost;

Now, blind_user@localhost can execute SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES but can't get any private data out of the table itself.


Adding a date and the time portion of a datetime2 together

You can use CONCAT and rely on implicit conversion to do it less verbosely.  Both queries use 0 CPU, so you may have to run a lot of iterations to see if one is significantly more efficient than the other.

SELECT
    [load_date],
    [load_time],
    CONCAT([load_date], ' ', CAST([load_time] AS TIME))
FROM [test_table_1]

You aren't required to explicitly convert it to DATETIME2 because it will be converted implicitly when it updates the load_time column.  Note that having the shortest code is not always the best.  Using comments to explain what the code is doing will prevent anyone from having to figure out what it's doing.


Where .dmp file is restored after import in oracle?

Thanks. I have imported dmp file by following steps..

Command to open SQLPLUS

In Command Prompt:
C:> sqlplus / as sysdba

Clear Screen:

SQL> Clear Screen

1- First create a new user by

SQL> Create user TestUser1 identified by test; -- (test is password)

2- Grant Privilages to new created user TestUser

SQL> Grant dba, IMP_FULL_DATABASE, EXP_FULL_DATABASE to TestUser;

Optional:

You can check which users have the above three grants

SET lines 100 --May not work
COL privilege FOR a50 -- May not work
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2; -- May not work

3- Check TableSpace for this newly created user

SQL> select * from dba_users

Note: here you will see that the default DEFAULT_TABLESPACE for TESTUSER is USERS

4- Create a new TableSpace for this newly created user "TestUser"

SQL> CREATE TABLESPACE tbs_testuser1
    DATAFILE 'tbs_testuser1.dat'
        SIZE 256M REUSE
        AUTOEXTEND ON NEXT 256M MAXSIZE 2048M

    ONLINE;

5- Now Connect this newly created tablespace  'tbs_testuser1' to 'testuser1'.

SQL>  ALTER USER TESTUSER1 default tablespace tbs_testuser1 quota unlimited on tbs_testuser1;

6- Check whether your 'tbs_testuser1' tablespace is connected to user or not.

SQL> select * from dba_users

7- In command prompt:

c:\>imp system/password file=File Path.dmp log=LOGFileName.log fromuser = <UserName of User from which file is imported> touser=testuser1


SQL Server : reasons not to use default database settings for ANSI_NULLS?

It is not a risk at all.  In fact, the ability to turn ANSI_NULLS OFF has a limited lifespan. Soon enough that won't even be an option, it'll always be ON. The only time I've ever seen any form of reasoning for setting ANSI_NULLS OFF, is because there are developers that can't fathom the true meaning of NULL, and therefore want to use typical conditional logic with it. Again, terrible reasoning and not justifiable.  I look forward to the permanent ON setting for ANSI_NULLS.


I'm not sure which version of Ops Manager you were running, but there's been a lot of improvements in the last year. The latest docs explain the space-spacing approaches of the different storage choices:

MongoDB blockstore and AWS S3 bucket  - Each successive snapshot stores only the differences from the previous snapshot. Compression and block-level de-duplication reduce the size of snapshot data.
File system store - uses compression only.


Changing the representation of NULLs in pg_dump plaintext output

I think the best for your situation is to use the COPY ... TO with one file for each table, and use pg_dump only for schema.

With COPY you can do change the output format (including NULL), example:

COPY foo TO '/path/to/foo.output' WITH NULL '<your null>';

You can even use CSV or other format (I don't know what MSSQL recognizes).

To automatize, you can easily check all the tables with a shell script. To achieve that, first edit as needed the following and save in getcommand.sql:

SELECT 'COPY '||c.oid::regclass||' TO ''/path/to/'||c.oid::regclass||''' WITH NULL '''';'
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind='r' AND n.nspname !~ '^(information_schema$|pg_)';

With that, you use the following to execute the copys (you can even use xargs to parallelize it):

psql <conn_info> -XAtf getcommand.sql | psql <conn_info>

It will generate and execute the COPY commands to export all tables data.

EDIT1: Added regclass to filename and corrected a syntax error on the generated COPY command (the WITH NULL goes at the end, not begining). Added -X to psql to avoid unexpected input through pipe.

EDIT2: Parallelism

I have made the test with xargs to parallelize it, and the result is the following:

psql -XAtf getcommand.sql | xargs -d '\n' -L 1 -P <num jobs> psql -c

On a simple test I did, with a small database I use for tests (~800MB), with no parallelism it took 6 seconds to complete, and with 4 jobs it took little more than 1 second. I imagine that for a 500GB database on a good server, the difference will be bigger. If you want to compare:

time ( psql -XAtf getcommand.sql | xargs -d '\n' -L 1 -P 4 psql -c )
time psql -XAtf getcommand.sql | psql

NOTICE: For those thinking in use it for parallel backups (e.g. to substitute pg_dump): don't do it! Because this solution uses different sessions (and transactions) for each table, so it can't provide consistency on the result if there are other users working on the database. On PG 9.2+ we can use transaction snapshot to guarantee consistency, but PG 9.3 (still beta) already has parallelism on pg_dump.


MySQL - Delete older Records from a huge table

Since you want to archive most of the table, here's what I recommend.  And it will facilitate subsequent archivings.

This assumes

The table has a DATETIME or TIMESTAMP; I'll call it dt in the code below.
New rows will be INSERTed in chronological order or nearly so.  (This assumption may not matter.)
You can take some downtime, roughly equivalent to twice the time it takes to scan the entire table.  If you cannot handle this much downtime, then other (messier) techniques may be possible.

Setup:

SELECT @cutoff = CURDATE() - INTERVAL 2 YEAR;  -- need same cutoff twice
CREATE TABLE new LIKE real;   -- this will become `real` later.
ALTER TABLE new ADD some partitioning ENGINE=InnoDB

The partitioning will be BY RANGE(TODAYS(dt)) with 26 monthly partitions.  See http://mysql.rjweb.org/doc.php/partitionmaint for discussion of what they will look like.  See below for future archivings.

Shrink real to the last 2 years:

-- Stop writes to the table `real`
-- Note:  This will take a long time
INSERT INTO new SELECT * FROM real WHERE dt >= @cutoff;  -- 2 of 10 years = 20% of table?
RENAME TABLE real TO old, new TO real;
-- Allow writes to the table `real`

Archive older stuff:

SELECT ... INTO OUTFILE '...csv' FROM old WHERE dt < @cutoff;

Future archivings:

Now that you have the table partitioned, you need to do some monthly maintenance:

Use "transportable tablespaces" to remove the oldest partition from real and turn it into a csv.  (Perhaps ALTER TABLE foo ENGINE=CSV;?)
REORGANIZE PARTITION future INTO next-month, future to slide the timescale forward.

These steps will be very fast, and have no impact on inserts into real (unlike the original split).


How to find rows that do not match between two tables P and N

SELECT N.*
FROM new_table N
LEFT JOIN previous_table P
     ON P.item_id = N.item_id
    AND P.column_1 = N.column_1
    AND P.column_2 = N.column_2
    AND /* ... */ P.column_n = N.column_N
WHERE P.item_id IS NULL;

or

SELECT N.*
FROM new_table N
LEFT JOIN previous_table P
     ON (P.item_id, P.column_1, P.column_2, /* ... , */ P.column_N)
       =
        (N.item_id, N.column_1, N.column_2, /* ... , */ N.column_N)
WHERE P.item_id IS NULL;


Sql server sleeping state is increasing using ADO.NET?

So I am bit confused now, if I am using only 2 connection string with
  connection pool then why we are seeing so many sleeping connection, I
  am thinking it should be only 2?

Min Pool Size=50 specifies number of connections established when the pool is created and will remain in the pool until the application pool is unloaded (including an IIS restart). With 2 distinct connection strings and security context, you will have at least 100 connections at all times and these will show as sleeping on SQL Server when not in use. The purpose of Min Pool is to ensure at least that number of connections are always available to the application and avoid the overhead of physically closing and opening connections that will be needed but at the costs mentioned below.

The Max Pool Size=500 specification allows up to 500 connections for each pool so you can have up to 1000 total connections if needed. The connections in excess of the Min Pool Size are acquired only when an unused connection is not available in the pool. The high number of connections you see with sp_who2 may be as a result of user activity, long-running queries, because connections are not closed/disposed in a timely manner, or pooled connections are still associated with a transaction context.

  And how can we minimize sleeping sessions. Also not sure whether it's
  an application pool(IIS server) error or application error? Please
  help.

Not sure what you mean by IIS error but a large number of sleeping connections that were not due to query activity may indicate an application issue. All of the examples in the ADO.NET connection pool documentation employ a using block to ensure connections are properly closed and disposed without the need for app code to explicitly close the connection. I suggest you follow that practice with consistently throughout the application with both connections and transaction objects.

The sleeping connection overhead on the SQL server is additional memory and client side connection pool management. The query below will show how much memory is consumed by all SQL Server connections:

SELECT SUM(cntr_value) AS ConnectionMemoryKB
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Connection Memory (KB)';

The ADO.NET connection pool documentation


Trace flag is running, but not message when deadlock happens

The reason the server wasn’t sending emails is due to this flag not being set:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails.
You can see the table here:

select * from master.sys.messages
where text like '%deadlock%'


MySQL binlog format change from MIXED to ROW

I've watched the move from STATEMENT -> MIXED -> ROW over the past decade or more.  There has been virtually no hiccups.  So, I would not worry.
ROW is essentially required for some clustering solutions, etc.  There are subtle things that can go 'wrong' in STATEMENT replication, where ROW solves it.
If RDS requires ROW, go with it.


Remote access to SQL Server 2008 R2 on Windows Server 2012 R2?

It is a known issue with Sql Server 2008. Happens also in SSAS.
Search for Microsoft fix/ or ask support for it (fix applies on the OS site and not the DB site).


I think it is a VERY bad plan to be generating HTML via SQL.  There are many many ways this can go wrong, without even mentioning the maintenance nightmare that this will inevitably cause.

All that being said your solution might be as simple as adding the following:

+ CAST((SELECT COUNT(*) FROM dbo.TABLE_NAME) AS VARCHAR(20)) +

The reason why I think you are having problems is because SQL Server requires you to explicitly convert your integers to strings.


Join to a Culture Table vs Select Culture ID

No need to refactor the single query into two separate ones.  SQL Server should be able to figure out the most optimal way to execute the query on your behalf.  Make sure the Culture table has a unique index on CultureTag and, in the case of a non-clustered index, add all of the columns needed by the query as INCLUDED columns.


When converting a replica set with an arbiter to a shard, does the arbiter also get added to the shard?

When you add a new shard using sh.addShard(...) you are providing a seed list of members which will be used to discover the current replica set configuration. You can specify as few as one member of the replica set or as many as all, and you do not need to specifically include the arbiter.

FYI, the sh.addshard() mongoshell helper wraps the underlying addShard command (which has a few additional options not currently exposed via the sh.addShard() helper).


Table with clustered Index consuming max memory causing low PLE alerts

First and foremost thing, SQL Server in picture is SQL Server 2008 R2 RTM which is not supported by Microsoft in any way please apply SQL Server 2008 R2 SP3 to at least get extended support

  I analyzed and found that one of the User DB A was using 8 GB RAM

I don't think there is any issue because of this, this is totally normal looking at this you cannot draw any conclusion.

  A there was a table having a clustered index consuming on average 6-6.5 GB of the memory

I guess this table is biggest table of database which is consuming most part of buffer pool. I cannot upfront say about why table is holding so much memory so again you should first apply SP3 to rule out any possibility of leaks and then move with troubleshooting.

  For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:

For a system with 12 G RAM PLE should be around 900. But since you say its always around 300-400 which makes me thing SQL Server has to work hard and move pages frequently out of memory to disk because of memory pressure. To confirm memory pressure you have to rely on other counters not just PLE you can open perfmon and add following counters. It would be best Create a Data Collector Set to Monitor Performance Counters

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

( Ideally Target value should be less than or equal to Total)
Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design
Free Pages – Total number of pages on all free lists (free lists track all of the pages in the buffer pool that are not currently allocate to a data page, and are therefore available for usage immediately). Undoubtedly this value should be high
Page Life Expectancy – Number of seconds a page will stay in the buffer pool without references> if you have NUMA system analyze PLE for each node as mentioned in this article
Free List Stalls/sec – Number of requests per second that had to wait for a free page. Ideally stalls should be as zero or as minimum as possible
SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: If you see a non zero value of memory grant pending with Low PLE and High free List stalls you definitely have a memory pressure and should consider providing more RAM.

Please note: the value for above counters should be collected for at least 3-4 hours and when load on system is relatively very high. If possible before collecting the values please flush buffer cache , although I am not saying you to do it I know for Prod system it would not be possible. If you would produce the values I could analyze it for you.

I have no idea about workload of the system and just giving you option how to proceed. You should also analyze costly queries. Look for one that involves hash joins and sorts.

A bad query with does lots of joins and scans on large table has all capacity to bring counters like PLE,memory grants, free list stalls to value which make syou believe its memory pressure, ya it is but the cause is poor query and missing indexes and incorrect join. You should also consider this.

Edit:

From The output of performance data collector as requested for above counters

Below are some notable points

Target server memory and total server memory remained constant and there value were equal throughout the data collector process( from 10 AM in morning to 3 PM in afternoon). which points to fact that SQL Server database engine was happy with its current memory requirement
Memory grants pending was always zero.
Free List stalls/Sec was always zero. Which means no request had to wait for free   pages
There was considerable decrease in PLE from 2:15 PM to 3:18 PM and at the same time pages read/sec were very high which makes me believe some query/process started after 2:00 PM which required too much pages in memory and hence causing flurry of activity in Buffer pool.

You need to find out what query is running after 2:00 PM and it was still running so it makes me think there is process.job starting at 2:00 PM. This process/job/query is trying to read too many pages which is causing lazywriter to flush too many pages because query is requesting space for such pages

It can be that such query is missing index, can be that its creating a bad plan due to skewed statistics, it can be that query needs to be written to get/read only subset of data not whole data.

it would be incorrect to say that its a memory pressure, its only that particular process is creating scenario which seems like memory pressure.


Choosing what table fields to partition on

By all means, partition by unixtime. Why ???

You can separate data by day
You can archive the data of the oldest partition
This can be scheduled in the MySQL event scheduler for

adding new partitions
deleting old partitions

You drop the data of the oldest partition by DDL (ALTER TABLE tbalename DROP PARTITION partitionname) instead of DML (DELETE FROM tablename WHERE unixtime ...). See my answer to this post Partitioning and freeing disk space
If you are using MySQL 5.6, you can query against the specific partition instead of whole table.

Here is what I am going to do for you

Let say your table is as you gave as an example

USE mydatabase
CREATE TABLE mytable
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    value DOUBLE NOT NULL,
    device_id SMALLINT NOT NULL,
    unixtime INT UNSIGNED,
    PRIMARY KEY (id,unixtime),
    KEY device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(unixtime))
(PARTITION p2015_05_09 VALUES LESS THAN (1431230400) ENGINE = InnoDB,
 PARTITION p2015_05_10 VALUES LESS THAN (1431316800) ENGINE = InnoDB,
 PARTITION p2015_05_11 VALUES LESS THAN (1431403200) ENGINE = InnoDB,
 PARTITION p2015_05_12 VALUES LESS THAN (1431489600) ENGINE = InnoDB,
 PARTITION p2015_05_13 VALUES LESS THAN (1431576000) ENGINE = InnoDB,
 PARTITION p2015_05_14 VALUES LESS THAN (1431662400) ENGINE = InnoDB,
 PARTITION p2015_05_15 VALUES LESS THAN (1431748800) ENGINE = InnoDB,
 PARTITION p2015_05_16 VALUES LESS THAN (1431835200) ENGINE = InnoDB,
 PARTITION p2015_05_17 VALUES LESS THAN (1431921600) ENGINE = InnoDB,
 PARTITION p2015_05_18 VALUES LESS THAN (1432008000) ENGINE = InnoDB,
 PARTITION p2015_05_19 VALUES LESS THAN (1432094400) ENGINE = InnoDB,
 PARTITION p2015_05_20 VALUES LESS THAN (1432180800) ENGINE = InnoDB,
 PARTITION p2015_05_21 VALUES LESS THAN (1432267200) ENGINE = InnoDB,
 PARTITION p2015_05_22 VALUES LESS THAN (1432353600) ENGINE = InnoDB,
 PARTITION p2015_05_23 VALUES LESS THAN (1432440000) ENGINE = InnoDB,
 PARTITION p2015_05_24 VALUES LESS THAN (1432526400) ENGINE = InnoDB,
 PARTITION p2015_05_25 VALUES LESS THAN (1432612800) ENGINE = InnoDB,
 PARTITION p2015_05_26 VALUES LESS THAN (1432699200) ENGINE = InnoDB,
 PARTITION p2015_05_27 VALUES LESS THAN (1432785600) ENGINE = InnoDB,
 PARTITION p2015_05_28 VALUES LESS THAN (1432872000) ENGINE = InnoDB,
 PARTITION p2015_05_29 VALUES LESS THAN (1432958400) ENGINE = InnoDB,
 PARTITION p2015_05_30 VALUES LESS THAN (1433044800) ENGINE = InnoDB,
 PARTITION p2015_05_31 VALUES LESS THAN (1433131200) ENGINE = InnoDB,
 PARTITION p2015_06_01 VALUES LESS THAN (1433217600) ENGINE = InnoDB,
 PARTITION p2015_06_02 VALUES LESS THAN (1433304000) ENGINE = InnoDB,
 PARTITION p2015_06_03 VALUES LESS THAN (1433390400) ENGINE = InnoDB,
 PARTITION p2015_06_04 VALUES LESS THAN (1433476800) ENGINE = InnoDB,
 PARTITION p2015_06_05 VALUES LESS THAN (1433563200) ENGINE = InnoDB,
 PARTITION p2015_06_06 VALUES LESS THAN (1433649600) ENGINE = InnoDB,
 PARTITION p2015_06_07 VALUES LESS THAN (1433736000) ENGINE = InnoDB,
 PARTITION p2015_06_08 VALUES LESS THAN (1433822400) ENGINE = InnoDB,
 PARTITION p2015_06_09 VALUES LESS THAN (1433908800) ENGINE = InnoDB,
 PARTITION p2015_06_10 VALUES LESS THAN (1433995200) ENGINE = InnoDB,
 PARTITION p2015_06_11 VALUES LESS THAN (1434081600) ENGINE = InnoDB,
 PARTITION p2015_06_12 VALUES LESS THAN (1434168000) ENGINE = InnoDB,
 PARTITION p2015_06_13 VALUES LESS THAN (1434254400) ENGINE = InnoDB,
 PARTITION p2015_06_14 VALUES LESS THAN (1434340800) ENGINE = InnoDB,
 PARTITION p2015_06_15 VALUES LESS THAN (1434427200) ENGINE = InnoDB,
 PARTITION p2015_06_16 VALUES LESS THAN (1434513600) ENGINE = InnoDB,
 PARTITION p2015_06_17 VALUES LESS THAN (1434600000) ENGINE = InnoDB,
 PARTITION p2015_06_18 VALUES LESS THAN (1434686400) ENGINE = InnoDB,
 PARTITION p9999999999 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Please note that

unixtime must be part of the PRIMARY KEY.
unixtime should be INT UNSIGNED instead of BIGINT

This will make your table 30 days back and 10 days ahead. Of course, those extra 10 days should be empty all the time.

If you run the following code, this will create the stored procedures and events that will run every night

USE yourdatabase

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Partition_Table_Append` $$
CREATE PROCEDURE `sp_Partition_Table_Append`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64)
)
BEGIN

  DECLARE Target_DBTB,pname VARCHAR(1024);

  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);

  SET @dt1 = DATE(NOW()) - INTERVAL 7 DAY;

  SET @looking_for_opendate = 1;
  WHILE @looking_for_opendate = 1 DO
    SET @dt1 = @dt1 + INTERVAL 1 DAY;
    SET @dt2 = @dt1 + INTERVAL 1 DAY;
    SET pname = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@dt1,'%Y_%m_%d'),11)));
    SELECT COUNT(1) INTO @looking_for_opendate
    FROM information_schema.partitions
    WHERE table_schema   = TargetDB
    AND   table_name     = TargetTB
    AND   partition_name = pname;
  END WHILE;

  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' DROP PARTITION p9999999999');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' ADD PARTITION (PARTITION ',pname,' VALUES LESS THAN ( UNIX_TIMESTAMP(',QUOTE(@dt2),') ) ENGINE=InnoDB)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' ADD PARTITION (PARTITION p9999999999 VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('FLUSH TABLES ',Target_DBTB);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Partition_Table_Rotate` $$
CREATE PROCEDURE `sp_Partition_Table_Rotate`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64),
  DaysToKeep INT
)
ThisStoredProcedure:BEGIN

  DECLARE Target_DBTB,OldPartitionToKeep,partitions_to_drop VARCHAR(1024);
  DECLARE FirstOfTheMonth,OldestMonthToKeep DATE;

  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);

  SET @today  = DATE(NOW());
  SET @oldest = @today - INTERVAL DaysToKeep DAY;
  SET OldPartitionToKeep = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@oldest,'%Y_%m_%d'),11)));

  SELECT COUNT(1) INTO @zapcount
  FROM information_schema.partitions
  WHERE table_schema   = TargetDB
  AND   table_name     = TargetTB
  AND   partition_name < OldPartitionToKeep;

  IF @zapcount = 0 THEN LEAVE ThisStoredProcedure; END IF;

  SELECT GROUP_CONCAT(partition_name) INTO partitions_to_drop
  FROM information_schema.partitions
  WHERE table_schema   = TargetDB
  AND   table_name     = TargetTB
  AND   partition_name < OldPartitionToKeep;

  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' DROP PARTITION ',partitions_to_drop);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

DELIMITER $$

DROP EVENT IF EXISTS Append_Daily_Partitions $$
CREATE EVENT Append_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) - INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
ThisScheduledEvent:BEGIN
  DECLARE srcdb,srctb VARCHAR(64);
  DECLARE FoundCount INT;

  SET srcdb = DATABASE();

  DROP TABLE IF EXISTS PartitionedTablesToRotate;
  CREATE TABLE PartitionedTablesToRotate
  (
    id INT NOT NULL AUTO_INCREMENT,
    table_schema VARCHAR(64) NOT NULL,
    table_name VARCHAR(64) NOT NULL,
    days_to_retain INT NOT NULL DEFAULT 30,
    PRIMARY KEY (id),
    UNIQUE KEY DB_TBL_INDEX (table_schema,table_name)
  );
  INSERT INTO PartitionedTablesToRotate
  (table_schema,table_name)
  SELECT table_schema,table_name
  FROM information_schema.partitions
  WHERE table_schema=srcdb
  AND partition_name='p9999999999';

  SET @ndx = 0;
  SELECT COUNT(1) INTO @lastndx FROM PartitionedTablesToRotate;
  WHILE @ndx < @lastndx DO
    SET @ndx = @ndx + 1;

    SELECT table_name INTO srctb
    FROM PartitionedTablesToRotate WHERE id = @ndx;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.partitions
    WHERE table_schema = srcdb
    AND table_name = srctb
    AND partition_name = 'p9999999999';

    IF FoundCount = 1 THEN
      CALL sp_Partition_Table_Append(srcdb,srctb);
    END IF;
  END WHILE;

END $$

DELIMITER ;

DELIMITER $$

DROP EVENT IF EXISTS Rotate_Daily_Partitions $$
CREATE EVENT Rotate_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) + INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
ThisScheduledEvent:BEGIN
  DECLARE srcdb,srctb VARCHAR(64);
  DECLARE FoundCount,DaysToKeep INT;

  SET srcdb = DATABASE();

  SELECT COUNT(1) INTO FoundCount FROM information_schema.tables
  WHERE table_schema=srcdb AND table_name='PartitionedTablesToRotate';
  IF FoundCount = 0 THEN
    LEAVE ThisScheduledEvent;
  END IF;

  SET @ndx = 0;
  SELECT COUNT(1) INTO @lastndx FROM PartitionedTablesToRotate;
  WHILE @ndx < @lastndx DO
    SET @ndx = @ndx + 1;

    SELECT table_name,days_to_retain INTO srctb,DaysToKeep
    FROM PartitionedTablesToRotate WHERE id = @ndx;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.partitions
    WHERE table_schema = srcdb
    AND table_name = srctb
    AND partition_name = 'p9999999999';

    IF FoundCount = 1 THEN
      CALL sp_Partition_Table_Rotate(srcdb,srctb,DaysToKeep);
    END IF;
  END WHILE;

END $$

DELIMITER ;

DELIMITER $$

SET GLOBAL event_scheduler = 'ON';

The events will do the following:

Append_Daily_Partitions will add new partition at 11:45 PM
Rotate_Daily_Partitions will delete old partition at 12:15 AM

Please install this on a Dev or Staging Server and verify to works for you.

GIVE IT A TRY !!!


ORA-12631 using Database Links with Oracle DBMS 19c on Windows Server 2019

For many years we have adjusted the configuration of our Windows DBMS hosts by revoking the Access this computer from the Network User Right from the Everyone group.  With previous versions of Oracle DBMS this did not cause an issue, but with 19C we found that we needed to grant this user right on each database link target host to the AD service account used to run each database link source host.


Replication Broken, List Out of Sync Tables

With regard to the first point

  check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

  get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working (This one has shell scripting  I made to do the parsing for you)


Can you update a database with transaction logs only, without having to do a full restore?

I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this?

Assuming you run the same version of SQL Server, Yes.  What you're looking for is called RESTORE WITH STANDBY:

RESTORE WITH STANDBY Leaves the database in a standby state, in which
the database is available for limited read-only access. This option is
equivalent to specifying WITH STANDBY in a RESTORE statement.
Choosing this option requires that you specify a standby file in the
Standby file text box. The standby file allows the recovery effects to
be undone.

Every time you apply another log backup users will need to be disconnected briefly.  This is how Log Shipping works, and so long as they send you all the transaction log backups, you don't need to restore the weekly full.  Just apply the next log backup in the chain.


How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?

You're interpreting the semantics of the delete statement incorrectly. When a using clause is used, it doesn't mean that records will also be deleted from those tables. Instead, those tables are purely used to join to in order to determine which rows need to be deleted from Users.

You basically have three choices:

deleting child rows in a before delete on Users trigger.
on delete cascade constraints.
execute multiple delete statements on the various tables involved, in the right order.

My preference, in certain cases, is actually for the on delete cascade constraints, but I don't use them everywhere: just for the situation where it makes sense to be able to remove all of the children of a given parent in one go. I might use it for "invoices" and "invoice_lines".

When you take this approach you need to be sure that only users who really need to be able to delete from the parent table have that privilege -- no users or applications logging in as table owners!


There will be a many-to-many relationship between tasks and users, so you will need a table, perhaps ASSIGNED_TASK, that will have FK to the user and task tables. You will likely have some attributes, e.g. user role (task lead, minion, etc), due date, etc.
I recommend that you make your status columns be a string. Using opaque integers is nice from a computer science perspective, but humans find them annoying.
The updated column can be problematic in its meaning. Often the updated date is actually a property of the event (task completion, change in requirements, etc). I recommend that updated be removed from project and task and you track the date of the action in a child table.
Finally, I recommend that your table names be singular not plural. The name of the table should reflect what is being stored in a row and it is independent of whether the table has 0, 1, or a million rows.


I had to do this for NPI (non-public information) reasons at a previous employer.  Ended up scrambling fields in a script, since a first name / last name pair isn't NPI unless it's tied to something else.

Here's a rough version of the script.  Season to taste.  Also, this assumes you have first and last names in separate fields.

CREATE TABLE #RandomData
(
    DataID int not null,
    ID int not null,
    FirstName varchar(50),
    LastName varchar(50)
)

INSERT INTO #RandomData (ID, DataID)
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), DataId FROM DataTable

UPDATE r SET r.FirstName = x.FirstName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, FirstName FROM DataTable
) x
    ON x.ID = r.ID

UPDATE r SET r.LastName = x.LastName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, LastName FROM DataTable
) x
    ON x.ID = r.ID

CREATE CLUSTERED INDEX PK_RandomData ON #RandomData
(
    DataID asc
)

ALTER TABLE DataTable DISABLE TRIGGER ALL

UPDATE dt
    SET dt.FirstName = r.FirstName,
    dt.LastName = r.LastName
FROM DataTable dt
INNER JOIN #RandomData r
    ON dt.DataId = r.DataId

ALTER TABLE DataTable ENABLE TRIGGER ALL

DROP TABLE #RandomData


There is always a balance. You can get close to zero data loss with the right restore strategy and none of this additional technology - which are more geared to HA than DR. I've stopped calling this a backup strategy, because the key takeaway from any such plan is that you need to test that you can restore your backups, not just that they're running successfully.

Since you can afford a day of downtime, you can take your time retrieving your full and log backups from wherever you store them and restoring them on a new server. With FULL recovery, and taking a log backup every 5 minutes, you should be able to guarantee that you will never lose more than 5 minutes of data (unless, of course, you backup to the local disk and the disk explodes - best practice is to get your backups as far away from the primary server as possible, as soon as possible). You can tighten that window up to three minutes, two minutes, one minute, etc., but backing up the log too frequently can cause a few ripple effects - most notably on your I/O subsystem, your SQL Server Agent history, and of course the complexity of restoring to point-in-time.

I also wouldn't be afraid to use mirroring in addition to a proper backup/restore methodology (but certainly not instead of), to get you as close to zero data loss as possible. Just because it's deprecated doesn't mean you have to worry about it being unavailable "soon." Deprecation announcements are typically three releases out... plenty of time in 6-9 years to come up with an alternative, even if you are staying on top of every new release, which most people don't, and even if this application is still in service, which it may not be. There are a lot of folks still running 2000 and 2005; do you think they're worried about deprecated or discontinued features in current versions?

Besides, Microsoft will most certainly have had to come up with an alternative for Standard / BI Edition customers by the time they actually remove mirroring from the product. I know they have been told loud and clear, from the moment they announced this deprecation, that they need to have a contingency plan for non-Enterprise customers. My guess, with absolutely no internal knowledge on the subject, is that at some point they will introduce some extremely limited form of Availability Groups to Standard Edition, much like they did with backup compression in 2008 R2, which was previously only available in Enterprise.


Ok. I was able to find the root cause (hopefully) and circumvent it. Most likely the reason was type mismatch 'join' was because ACCT_ID was NULL in the source table.

The below worked fine:

IF NOT isNULL(DTSSource("ACCT_ID"))
    varamt = Lookup ("Lookup_tbl1",
        [Lookup_tbl1].[Amount],
        Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID")))
    IF (NOT isEmpty(varamt) or NOT isNULL(varamt)) or varamt<>"" THEN
        DTSDestination("AMOUNT") = varamt
    ELSE
        DTSDestination("AMOUNT") = DTSSource("AMOUNT")
    END IF
ELSE
    DTSDestination("AMOUNT") = DTSSource("AMOUNT")
END IF


Oracle is not using a unique index for a long key

I found the solution! It is so beautiful and I actually learned a LOT about Oracle.

In one word: histograms.

I started reading a lot about how Oracle's CBO works and I stumbled upon histograms. I didn't fully understand so I took a look at the USER_HISTOGRAMS table, and voilá. There were several rows for the sick table, and practically nothing for the cloned table. For the sick table, there was one row for each of the 8 different identifier-starting-parts. And this is the key: they were cut off at 32 characters, before the @ sign. As I said, the first part of keys is highly repetitive, they become different after the @ sign.

It seems that histograms can be more powerful than the simple fact that a unique index always has a cardinality of 0 or 1 for a given value. When I was querying for 2+ rows, Oracle looked at the histogram, it thought that there could be tens of thousands of values for that identifier-starting-part, and it threw the CBO off course.

I deleted the histograms for that column in the old table and the problem went away!

More reading: https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating


sql-server optimize query with lots of null checks

NULL bitmap is indeed used for query optimization that involve columns with NULL values, but there are a few considerations here:

NULL bitmap is not present on nonclustered indexes (you'll see why), on the other hand it is always present on the leaves of clustered indexes, and heap tables.
NULL bitmap cannot be built for index records, but exclusively for data records (this is why you cannot have it for nonclustered indexes). That means you can have a NULL bitmap either on the table (organized as heap) or on a clustered index (leaves).
NULL bitmap is not present for tables with only SPARSE columns.

You should check if you have a NULL bitmap indeed. My guess is that yours is missing. You could use DBCC to check the record attributes:

Open a query window and issue:

DBCC IND (DATABASE_NAME, 'TABLE_NAME', -1)
DBCC TRACEON (3604)
DBCC PAGE (DATABASE_NAME, PageFID, PagePID, 3)
DBCC TRACEOFF (3604)

where PageFID and PagePID are displayed by the first command DBCC IND then check for the Record Attributes and check if NULL_BITMAP is present.

If there isn't any, then create a clustered index on the table, and check again.
You could also give us the execution plan, and the actual definition of the table (with dummy column names if confidential).


That is an index that was automatically generated for a constraint. Example:

SQL> select index_name from user_indexes;

no rows selected

SQL> create table t1(c1 number primary key);

Table created.

SQL> select index_name from user_indexes;

INDEX_NAME
--------------------------------------------------------------------------------
SYS_C006380


Storing multiple tags on analytics database

I'm still convinced that using a many-to-many lookup table (a bridge table) is still your best option here. Your concern about matching multiple rows can be fixed by proper query design. Let's say your tables are:

CREATE TABLE purchases(PurchaseID,CustomerID,PurchaseDate,...)
CREATE TABLE tags(TagID,TagType,TagName)
CREATE TABLE purchasetags(PurchaseID,TagID)

So each purchase can have multiple tags set (no limit), and just for fun, I added the ability to categorized the tags by TagType, maybe that contains things like "ProductType", "Brand", "Color", "Sport", so you have a way to tell that "shoes" is a "ProductType" tag, "Nike" is a brand tag, and "soccer" is a sport tag.

Then if you want to query (and return just single rows), just do:

SELECT *
FROM purchases
WHERE PurchaseID IN (SELECT pt.PurchaseID
                     FROM purchasetag pt
                     INNER JOIN tags t ON pt.TagID=t.TagID
                     WHERE t.TagName IN ('Adidas','Nike'))
GROUP BY whatever...

If you need to do fancier combo-searches (find purchases of Nike shoes or Adidas shoes, your query will have to also be fancier:

SELECT *
FROM purchases
WHERE PurchaseID IN (SELECT pt.PurchaseID
                     FROM purchasetag pt
                     INNER JOIN tags t ON pt.TagID=t.TagID
                     WHERE t.TagName = 'Shoes')
AND   PurchaseID IN (SELECT pt.PurchaseID
                     FROM purchasetag pt
                     INNER JOIN tags t ON pt.TagID=t.TagID
                     WHERE t.TagName IN ('Adidas','Nike'))

Again, that's still returning a single row for each purchase that matches your desired tag combination.


Is it Possible to have Multiple Named Instances with No Default Instance?

Yes, it is.  I have a dev server with multiple named instances (with multiple Sql Server versions) and no default instances.


This is such a quick and dirty question.

This deserves a quick and dirty answer.

Good News : PROCEDURE ANALYSE()

Bad News : It is Deprecated in 5.7.18 and will not be available in MySQL 8.0

I have discussed  PROCEDURE ANALYSE() over the years: See my old posts.

In your case, you would just do this:

SELECT columnA,columnB FROM yourtable PROCEDURE ANALYSE();

and the output will tell you the min value, max value, avg value, and recommended datatype.


how often to run reorganize index & update statistic maintenance task on sql server 2012?

You should intelligently do index reorg and rebuilds. As a general (widely accepted) rule of thumb is

If your indexes are fragmented :

If index has less than 1000 pages - do not perform any index maintenance operation.
Less than 10% - then do nothing
between 10% and 30% - then do a reorg
more than 30% - then rebuild them.

I would highly recommend to use Ola's maintenance solution (index and checkdb)

Since your databases are 1GB to max 10GB, depending on your server spec it should not take very long time.


What is ERROR_STATE() in SQL Server and how it can be used?

The purpose of SQL Server error states is for the SQL Server development team to be able to identify in code the exact place system errors were raised, given that many errors are raised in multiple places.

You as an end user (ie. developer of applications using SQL Server) can similarly use the state passed in to RAISERROR so that your product support can identify the place a procedure raises an error, for example:

create procedure usp_my_proc
as
if <somecondition>
  raiserror(N'Error foo and bar', 16, 0);
if <someothercondition>
  raiserror(N'Error foo and bar', 16, 1);
go

See how the two state allow you to distinguish later which error case was hit. Before you say 'but I can look at the error message' I'm telling you one word: internationalization.


Recover MongoDB data without WiredTiger.wt file

If you perform the recovery by overwriting some collection-*.wt files inside your dbpath, then your database is in an unsupported state. Data loss is quite likely in this case.

If you want to restore the database, you would need to restore the dbpath as a whole and not as individual files. Please see MongoDB Backup Methods for instructions and information on supported backup and restore methods.

The WiredTiger.wt file is a crucial metadata file that keeps track of the state of the whole database and all the *.wt files. Hence, the content of this file is deployment specific, and not transferrable to other deployments. It is not possible to recreate the content of this file.

If you have a known good backup of the whole dbpath, you may be able to restore to a valid state of the database, as long as you restore the dbpath as a whole.


This is a long-standing bug in the GUI tools.

However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect.

connect to sample user test using test!
--> SQL0104N

connect to sample user test using "test!"
--> connection succeeded.

This method will also work if the GUI tools prompt you for a password with a dialog box.

If you have passwords with double quotes in them, you may be out of luck.  You could open a ticket with IBM DB2 support and/or just change the password.


Is it possible to archive table rows of an RDS read replica?

A read replica is basically a read_only slave in normal MySQL speak. RDS does allow you to change the read_only parameter, so you can purge the records you want and RDS won't recopy the data from the master.

However be careful if you go this route. If you run DML statements on the master for rows that don't exist on the slave, you likely will break the replica. If replication breaks, you're only choice is to drop the replica and create a new one from the master. You'd then have to re-purge the rows.

I am curious why you want to remove data from the replicas for report queries. I would expect reports would want more data, not less, than the master.


What you want is called TOP WITH TIES in SQL Server and FETCH FIRST .. WITH TIES in standard SQL. The MySQL LIMIT clause is equivalent to TOP (and FETCH FIRST) but it doesn't have WITH TIES functionality.

Here is one way to do this. The details will differ, depending on what you want the ORDER BY to be based on. I used formID:

SELECT f.*
FROM forms AS f
  INNER JOIN
    ( SELECT formID
      FROM forms
      ORDER BY formID
      LIMIT 1 OFFSET 4        -- offset 5 minus 1
    ) AS fm
  ON  f.formID <= fm.formID
ORDER BY f.formID ;

This will return 6 rows in your example case. If you replace <= with < it will return 3 rows.

In order to try and adapt that to your specific case more closely, you can add the created column to the "equation":

SELECT f.*
FROM forms AS f
  INNER JOIN
    ( SELECT created, formID
      FROM forms
      WHERE created >= '2014-01-01'
      ORDER BY created, formID
      LIMIT 1 OFFSET 4
    ) AS fm
  ON  (f.created, f.formID) <= (fm.created, fm.formID)
WHERE f.created >= '2014-01-01' ;

This Rextester demo shows how it works both with <= and with <.

I am not sure about the id != $lastId predicate in your query, but if it is an actual part of the filter rather than just your attempt at resolving the issue in question, then you will probably need to add it to the above query as well, both to the main WHERE and to the nested WHERE.


Should I put the error handler inside the loop, or the loop inside the error handler?

Error handling should be (IMO) for the whole stored procedure: so loop inside the TRY/CATCH

Not least, what happens when more code is added pre- or post-loop?
Then you need the TRY/CATCH to cover both loop and new code.

Poor maintainability and extra work required.

You can have an inner TRY/CATCH if you want to handle certain inner errors: this is acceptable for, say, "UPSERT" calls (before SQL Server 2008 MERGE of course). See Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting for more


If you want to force every child of every parent to have the same customer then there are two quick solutions that you could use to accomplish this.

The first, and my preference, would be to use a check constraint on the table like so:

-- define a function that performs the logic required
CREATE FUNCTION dbo.fn_CheckCustomersAreEqual(ParentID INT, CustomerID INT)
RETURNS BIT
AS
BEGIN
RETURN CAST(ISNULL(
    SELECT 1
      FROM dbo.CustomerFolders
     WHERE ID = ParentID AND CustomerID = CustomerID
), 0) AS BIT);
END

-- add a check constraint to the table using the function to provide the outcome
ALTER TABLE dbo.CustomerFolders
ADD CONSTRAINT CK_CheckCustomersAreEqual
CHECK (dbo.fn_CheckCustomersAreEqual(ParentID, CustomerID));

The second option would be to add a trigger to the table to enforce the requirements like so:

CREATE TRIGGER CheckCustomersAreEqual ON dbo.Folders
INSTEAD OF INSERT
AS
BEGIN
-- check to see if the number of parents that meet the criteria
-- match the number of records we are inserting
IF (SELECT COUNT(*) FROM INSERTED AS F
                    JOIN dbo.CustomerFolders AS I ON I.ParentID = F.ID
                                      AND I.CustomerID = F.CustomerID) <>
   (SELECT COUNT(*) FROM INSERTED)
BEGIN
-- they don't match so raise an error
RAISERROR('Child customer does not match parent customer', 16, 1);
END
ELSE
BEGIN
-- they match so insert
INSERT dbo.CustomerFolders
SELECT *
  FROM INSERTED;
END
END

You can adjust the error handling and message returned to the client a bit better in the trigger but I personally prefer using the CHECK CONSTRAINT version.

These are just two possibilities that I can come up with, there may be more and you may be able to optimize the function or trigger to perform better. You could also change the design of your database to accommodate this better if you wanted. I just wrote the example above off the top of my head so be aware that they are also untested.

EDIT

I just noticed that the parent can be null (which makes sense) - but the example I gave above does not account for this. Make sure you account for this scenario in your end solution.


Using CREATE ACCESS METHOD in postgres allows you to define a function to handle (or 'access') a type of index.

It exists to allow you to define new ways for postgres to understand a mapping from the data key values to the actual TID that identifies the related value.

Without an understanding of how relational databases work, it's probably not something you'll ever directly deal with.


No, backup / restore will keep all the fragmentation. Probably better to add a filegroup with files in the new location, and recreate all of your user tables on the new filegroup (by recreating the indexes with DROP_EXISTING, and as an online operation if possible). You won't be able to eliminate the original files entirely, but if you've moved all the user objects, you should be able to shrink the primary data file down to system objects only.

If you then want to move the primary MDF file and log file to the new RAID drives, you can do this with ALTER DATABASE - take the database offline, alter the individual file locations, physically move the files, then bring the database back online again.


What's the solution for corrupted MDF files?

Your solution here is to restore the database from backup.

If you have transaction log backups you can restore the logs as well.  If the database is in simple recovery mode you'll be loosing 3 months worth of data.


If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB

If you need deadlock resolution, you will want InnoDB

If you want a storage engine that caches both data and indexes, you will want InnoDB

If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so)

Please refer to my past articles on InnoDB:

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Aug 04, 2011 : Optimizing InnoDB default settings
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Dec 19, 2011 : Which first: upgrade mysql version or convert storage engine?
Jan 09, 2012 : Any gotchas at all with converting from MyISAM to InnoDB?
Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb server
Jan 18, 2012 : Insert-heavy InnoDB table won't use all my CPU
Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You said you heard that MySQL 5.5 is faster than MySQL 5.0. Out of the box, that's not true. YOU MUST TUNE MySQL 5.5 TO GET GREAT PERFORMANCE OUT OF IT !!! Here are my past articles on that subject:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jul 18, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You mentioned MyISAM being faster than InnoDB. If there are a lot of wide VARCHAR columns in the MyISAM with the default ROW_FORMAT=Dynamic (Default), that can degrade MyISAM performance. Saying one storage engine is faster than the other depends on the application, read/write I/O ratio, storage engine tuning.

See my posts around this subject

Sep 20, 2011 : Best of MyISAM and InnoDB
April 12, 2012 : Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)
May 02, 2012 : Which mysql storage engine to choose?
May 03, 2012 : Which is faster, InnoDB or MyISAM?

Addressing your issue of whether to convert now or later to InnoDB:

In the fifth link I provided ( Which first: upgrade mysql version or convert storage engine? ), I recommend converting first. However, in your particular case, I would recommend converting later. The reason is simple: You are moving the data to a different server. MyISAM tables are very easy to port. Doing an rsync or scp of /var/lib/mysql is more expedient (faster) that converting to InnoDB because the data will be slightly (sometimes significantly) big when existing as InnoDB.

There are two things you will need to do:

NEED #1

If you rysnc/scp /var/lib/mysql you will overwrite /var/lib/mysql/mysql. Therefore, I propose you move your data like this:

STEP01) On the MySQL 5.5 machine, mkdir /root/mysql_schema
STEP02) On the MySQL 5.5 machine, cp /var/lib/mysql/mysql/* /root/mysql_schema/.
STEP03) rsync or scp /var/lib/mysql from MySQL 5.0 Server to MySQL 5.5 Server
STEP04) On the MySQL 5.5 machine, cp /root/mysql_schema/* /var/lib/mysql/mysql/.
STEP05) Get the grants from MySQL 5.0 machine as pure SQL and save the file as MySQL50Grants.sql

Jul 26, 2011 : Restoring an old backup to latest MySQL release (See the CAVEAT)
Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb server
Jun 13, 2012 : Fastest way to move a database from one server to another

STEP06) Execute the MySQL50Grants.sql on the MySQL 5.5. Server

NEED #2

You need a straightforward script to convert MyISAM to InnoDB. Please run the following on the MySQL 5.5 DB Server :

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

When you are satisfied with the conversion script, login to mysql on MySQL 5.5 and run

mysql> source /root/ConvertMyISAMToInnoDB.sql

Give it a Try !!!


To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown.

I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one!

A word of advice (and see discussion below), if you have a choice, I would strongly urge you to use PostgreSQL as its dialect of SQL is vastly superior to MySQL's. The -- (double hyphen) is a comment in SQL, insert/remove as appropriate for your server.

CREATE TABLE Customer
(
  Cust_ID SERIAL NOT NULL PRIMARY KEY,                 -- PostgreSQL
  -- Cust_ID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- for MySQL
  Name VARCHAR(15),
  CC_Number VARCHAR(16)
);

CREATE TABLE Cust_Order  -- don't want a table named "Order" - SQL keyword
(
  Order_ID SERIAL NOT NULL PRIMARY KEY,  -- PostgreSQL
  -- Order_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- MySQL
  Cust_ID INT,
  Order_Value INT,
  Order_DateTime TIMESTAMP,  -- PostgreSQL
  -- Order_DateTime DATETIME -- MySQL
  CONSTRAINT Order_Cust_fk FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID)
);

Firstly, to answer your specific question about relating credit card number to the order, the query (for both PostgreSQL and MySQL) to run would be this:

SELECT c.Cust_ID, c.CC_Number, o.Order_ID
FROM Customer c
INNER JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
ORDER BY c.Cust_ID;

which gives the result:

+---------+---------------+----------+
| Cust_ID | CC_Number     | Order_ID |
+---------+---------------+----------+
|       2 | 4656576576767 |        1 |
|       2 | 4656576576767 |        2 |
|       2 | 4656576576767 |        3 |
|       2 | 4656576576767 |        4 |
|       3 | 9999999999999 |        5 |
|       3 | 9999999999999 |        6 |
|       3 | 9999999999999 |        7 |
|       3 | 9999999999999 |        8 |
|       3 | 9999999999999 |        9 |
+---------+---------------+----------+

The reason that I didn't directly answer the specific question in your post was that it is very unlikely that anyone would be performing such queries. Storing credit card numbers as plain text is a recipe for disaster. Since the question related to JOINs and SQL generally, I was more interested in demonstrating JOINing and SQL than in how to display CC numbers.

SQL is all about aggregations and GROUPing and SUMming, so in my original example, I showed the SUM of sales by Customer and CC number (although, as mentioned, you should never be performing SQL against CC numbers directly). Chances are you'll be interested in sales per customer rather than actual CC numbers.

SELECT c.Cust_ID, c.Name, c.CC_Number, SUM(o.Order_Value) AS Total
FROM Customer c
INNER JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
GROUP BY c.Cust_ID, o.Cust_ID                      -- PostgreSQL GROUP BY
-- GROUP BY c.Cust_ID, c.Name, c.CC_Number, o.Cust_ID -- MySQL GROUP BY
ORDER BY o.Cust_ID;

result:

+---------+-------+---------------+-------+
| Cust_ID | Name  | CC_Number     | Total |
+---------+-------+---------------+-------+
|       2 | Jimmi | 4656576576767 | 13736 |
|       3 | Mary  | 9999999999999 | 27775 |
+---------+-------+---------------+-------+

A word of warning about MySQL:

MySQL has a variable sql_mode which is a set of parameters - you can view it by issuing the command 'SELECT @@sql_mode;' in the mysql client (or MySQL Workbench). You can set it in the my.cnf or my.ini (Windows) in the mysqld section:

[mysqld]

sql_mode    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

The ONLY_FULL_GROUP_BY parameter is the one to set, otherwise MySQL can produce (ahem...) anomalous (i.e. wrong) results!

Finally, and to expand, you might also (as someone who's selling widgets :-) ) be interested in clients who haven't bought anything (possibly in a specified period). I wrote this query using a LEFT JOIN (not INNER). This works for PostgreSQL and MySQL.

The LEFT JOIN will bring in everything in the left (or first, i.e. Customer) table - i.e. all clients whether they've made purchases or not, i.e. whether they have a corresponding entry in the Cust_Order table or not. The INNER JOIN will only match records which correspond in both tables - i.e. it will only show clients to whom a sale has actually been made.

SELECT c.Cust_ID, c.Name, c.CC_Number, COALESCE(SUM(o.Order_Value), 0) AS Total
FROM Customer c
LEFT JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
-- WHERE EXTRACT(MONTH FROM Order_DateTime) = 2        -- PostgreSQL
-- WHERE MONTH(Order_DateTime) = 2                     -- MySQL
GROUP BY c.Cust_ID, o.Cust_ID                          -- PostgreSQL
-- GROUP BY c.Cust_ID, c.Name, c.CC_Number, o.Cust_ID  -- MySQL
-- HAVING COALESCE(SUM(o.Order_Value), 0) >= 0
-- ORDER BY SUM(o.Order_Value) NULLS FIRST, c.Cust_ID ASC;  -- PostgreSQL
-- ORDER BY SUM(o.Order_Value) IS NULL DESC, c.Cust_ID ASC; -- MySQL

Result:

 cust_id | name  |   cc_number   | total
---------+-------+---------------+-------
       3 | Mary  | 9999999999999 | 27775
       2 | Jimmi | 4656576576767 | 13736
       4 | Joe   | 2222222222222 |     0
       1 | Billy | 3434343434343 |     0

This is the result with no WHERE, HAVING or ORDER BY clauses.

I would urge you to experiment with the COALESCE function (PostgreSQL and MySQL) and instead just using SUM(o.Order_Value) - instructive for NULLs. Vary it both in the SELECT, the HAVING and the ORDER BY.

Check out the EXTRACT (PostgreSQL) here and MONTH (MySQL) functions and the other functions on those pages. Vary the SUM in the HAVING and the ORDER BY - go mad!! :-)

These servers (as do others) have string functions (PostgreSQL and MySQL) and also maths ones (P and M) - as well as regular expressions (much better supported in PostgreSQL than MySQL - another reason to use PostgreSQL).

This should get you started on the road to SQL mastery! :-)

Take a look at the questions on this forum and make an attempt to answer
them - you'll gradually find that situations that used to baffle
you become clearer.

===================== Data for Queries ===========================

Customer table data:, for both PostgreSQL and MySQL.

 INSERT INTO Customer (Name, CC_Number) VALUES ('Billy', '3434343434343');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Jimmi', '4656576576767');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Mary',  '9999999999999');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Joe',   '2222222222222');

Cust_Order table data: (again, for both PostgreSQL and MySQL).

INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-02-14 11:55:34');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-02-18 14:24:34');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-03-09 09:23:17');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-03-27 16:02:56');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-01-22 12:45:03');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-02-25 13:34:43');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-02-28 17:50:01');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-03-01 18:30:16');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-03-17 11:17:17');


Non-clustered index covering a clustered index

Take a look at this article on SQLShack which gives a great description of what to consider when selecting clustered index keys. To summarise:

Keep it short - the clustered index key is included in all non-clustered indexes, so a wide key means more data space required on disk, and in the buffer when reading pages.
Keep it static - If your clustering key values are constantly changing, SQL will be forever re-sorting the clustered index to insert in the correct place.
Use an incrementing value - An IDENTITY column or sequential value helps speed up the INSERT processes by ensuring the new values are always being added to the end of the index, rather than somewhere in the middle.
Make it Unique - If the key column(s) are not unique, then SQL Server will add a uniqueifier. This only adds more space and requires more effort by the DB engine to maintain the index.
Sorting - The clustering key also defines the sort order for the data as it is stored. This means picking a clustering key that can or will be used reasonably frequently for sorting will only aid performance down the track and eliminate costly sort operators in query plans. NOTE: Character fields aren't great for sorting performance, so your current clustering key (A) doesn't fare well on this point.

So how do we apply this to your example?

You need to answer the following:

Is A+B short enough for a key? Probably, because just A is <= 22 bytes, so A+B is only <=26 bytes. Unless we're talking billions of rows, this is probably fine.
Is it static? Only you can answer this. Does the value in A or B change frequently? If so, then they're probably not good candidates for the clustering key.
Is A or B an incrementing value? I'm guessing A is unlikely to be an incrementing value as it is an NVARCHAR field, however, B might be since it is an INT.
Is A unique? Is B unique? Is A + B unique? If none of those is a yes, then probably not suitable as a clustering key.
What order do your queries normally sort in? If its A or B or A+B then these columns may be a good clustering key.

Once you've answered those questions, you should know if A or A+B is the better choice for a clustering key. If A or A+B still seem like equally valid choices for a clustering key, then considering the fact that you have an NCI with A + B would indicate you have a reasonable number of queries using both A and B as a predicate. Given that and the conditions above, A+B is likely a good candidate for a clustering key over just A if they equally satisfy the points from above.


Maintaining data integrity when one table has multiple relationships, but only one should be used. (is this bad design?)

I've worked with six or seven different laboratory information management systems (LIMS) over my years in IT.

The general structure those systems have used tends to be something like:

Accessions (This is where the case begins.)
AccessionKey PK
AccessionDate
CreatedBy
{other columns as needed}

Patients (This holds information about the case's patient)
PatientName
PatientBirthDate
{other columns, including foreign keys to the address entries, etc.}

AccessionPatients (links an existing patient to a new case)
AccessionPatientKey PK
AccessionKey FK
PatientKey FK
{etc.}

Specimens (this holds information about any specimens captured from the patient)
SpecimenKey PK
AccessPatientKey FK
SpecimenTypeKey FK
CollectionDate
(etc)

SpecimenTypes (this holds information on the different kinds of specimens)
SpecimenTypeKey PK
SpecimenTypeName
SpecimenContainerTypeKey FK (link to a container type table OR just a text field for EDTA container, or specimen cup, or blood tube or...)
{etc}

SpecimenTestRequests (This holds what tests have been ordered against what specimens)
SpecimenTestRequestKey PK
SpecimenKey FK (the specimen that this test was requested against.)
TestKey FK (The test that was requested.)
RequestDate
{etc}

Tests (this stores information about the tests the lab can perform)
TestKey PK
TestName
CreatedDate
EndDate
TestFee
{etc}

TestResultTypes (This tracks the kinds of results a test can have)
TestResultTypesKey PK
TestKey FK
TestResultName  (display name for this answer in this test)
UnitsKey FK (link to a units of measure table)
TestResultDataType (free text, dropdown list name, numeric, etc.)
TestResultListName (FK to a lists table or NULL)
DisplayOrder (numeric, to set where in the results this appears)
{etc.}

TestResultLists (this holds valid list entries for drop-down results)
TestResultListKey PK
TestResultListName (used in TestResultTypes)
TestResultListValue (what shows in the dropdown list)
(Etc.)

TestResults (This is where actual results are stored)
TestResultKey PK
SpecimenTestRequestKey FK (what test was requested for what case?)
TestResultTypeKey FK (what answer is this the result for?)
TestResultAnswer (long text that holds the actual result)
EnteredDate
EnteredBy FK (the user who entered the result)
ReleasedBy FK (the user who released the results)
ReleasedDate
(etc)

Units (Units of measure table for values like mg or mg/dL)
UnitsKey PK
UnitsText
(etc)

There are usually several other tables wrapped up in a LIMS -- history tables to track changes over time (audit trails), user tables, permissions tables, address tables, sometimes fee schedule tables, diagnoses tables, and so on. I'm looking through the schema for one of the LIMS I've supported and it has 457 tables. Another has 465 tables.

Anyway, this creates a case or accession, connects a patient to that case (if your system has multiple patients on 1 case, then you'll need a linking table in between). Then one or more specimens are assigned to the case with specimen types. Next, tests are requested for each specimen. Each test can have 1 or more result values. Then there's a results table to provide the results for each test.

A full LIMS would also have reference ranges (your Blood Glucose was 100. It should be between 80 and 120, so you are within normal ranges). It might also have the idea of Panels (a group of tests performed together, like a CBC panel), which can be defined and ordered as a group rather than as individual tests...)

But hopefully this helps.


What is the the value of float_one at the time of conversion ???

Note this example from MySQL 5.5.12 in Windows

mysql> select convert(20000,decimal(7,2));
+-----------------------------+
| convert(20000,decimal(7,2)) |
+-----------------------------+
|                    20000.00 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select convert(200000,decimal(7,2));
+------------------------------+
| convert(200000,decimal(7,2)) |
+------------------------------+
|                     99999.99 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'convert(200000,decimal(7,2))' at row 1 |
+---------+------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

It may be possible that data was truncated if a number bigger than 99999.99 was in float_one. Perhaps, mysql was converting float_one and float_two to DECIMAL(7,2) individually before performing division. Try using DECIMAL(10,2) or greater to accommodate large values.

UPDATE 2011-07-25 15:05 EDT

There are definite truncation problem going on here

According to MySQL Reference Manual Page 442 Paragraphs 2,3

  DECIMAL and NUMERIC values are stored as strings, rather than as
  binary floating point numbers, in order to preserve the decimal
  precision of those numbers. One character is used for each digit of
  the value, the deciaml point (if scale > 0) and the - sign (for
  negative numbers). If the scale is 0, DECIMAL and NUMERIC values
  contain no decimal point or fractional part.

  The maximum range of DECIMAL and NUMERIC is the same as for DOUBLE,
  but the actual range for the given DECIMAL or NUMERIC column can be
  constrainted by the precision and scale for a give column, When such a
  column is assigned a value with more digits following the decimal
  point than are allowed by the specified scale, the value is rounded to
  that scale. When a DECIMAL or NUMERIC column is assigned a value whose
  magnitude exceeds the range implied by the specified (or defaulted)
  precision and scale, MySQL stores the value representing the
  corresponding endpoint of that range.

You need to accommodate a larger precision and/or scale.

Here is an example as to why

I wrote this stored procedure using your specifications for DECMIAL and NUMERIC.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`NumTest` $$
CREATE PROCEDURE `test`.`NumTest` (num1 NUMERIC(7,2), num2 NUMERIC(7,2))
BEGIN

  DECLARE float_one,float_two,my_result NUMERIC(7,2);
  DECLARE f1,f2 DOUBLE(7,2);

  SET f1 = num1;
  SET f2 = num2;

  SET float_one = num1;
  SET float_two = num2;

  SELECT f1 / f2;
  SELECT float_one / float_two;
  SELECT CONVERT(float_one / float_two,DECIMAL(7,2));
  SET my_result = CONVERT(float_one / float_two,DECIMAL(7,2));
  SELECT my_result;

END $$

DELIMITER ;

I used two values: 290.0 and 14.5 for this test.

Before calling the NumTest stored procedure, I manually calculated 290.0 / 14.5

mysql> select 290.0 / 14.5;
+--------------+
| 290.0 / 14.5 |
+--------------+
|     20.00000 |
+--------------+
1 row in set (0.00 sec)

I divided each number by 100, 10000, and 1000000 and tried again

mysql> select 2.9 / .145;
+------------+
| 2.9 / .145 |
+------------+
|   20.00000 |
+------------+
1 row in set (0.00 sec)

mysql> select .029 / .00145;
+---------------+
| .029 / .00145 |
+---------------+
|    20.0000000 |
+---------------+
1 row in set (0.00 sec)

mysql> select .00029 / .0000145;
+-------------------+
| .00029 / .0000145 |
+-------------------+
|      20.000000000 |
+-------------------+
1 row in set (0.00 sec)

So far, so good !!! Now for the stored procedure.

mysql> call numtest(290.0,14.5);
+-----------+
| f1 / f2   |
+-----------+
| 20.000000 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             20.000000 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       20.00 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     20.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So my orignal two numbers work fine. Let's divide them by 100 and try again.

mysql> call numtest(2.9,0.145);
+-----------+
| f1 / f2   |
+-----------+
| 19.333333 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             19.333333 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       19.33 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     19.33 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num2' at row 2 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

WAIT, we lost some precision. What happened ??? How did this happen ??? You need to accommodate more decmial digits (> 2)

UPDATE 2011-07-25 17:37 EDT

I substituted (7,2) with (10,7) in the stored procedure and got back the proper precision

mysql> call numtest(2.9,0.145);
+----------------+
| f1 / f2        |
+----------------+
| 20.00000000000 |
+----------------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|        20.00000000000 |
+-----------------------+
1 row in set (0.01 sec)

+----------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(10,7)) |
+----------------------------------------------+
|                                   20.0000000 |
+----------------------------------------------+
1 row in set (0.03 sec)

+------------+
| my_result  |
+------------+
| 20.0000000 |
+------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>


SQL database ODBC connection Multi-part identifier could not be bound

I would check if you have "Quoted Identifiers" set in the DSN, it looks like its interpreting [Sat. Current] as the column [Current] in the table [Sat]


Unable to start SQL Server on Ubuntu 16.04

SQL Server did not start because the OS did not have 3250MB of memory. Was tracked by below command

journalctl -u mssql-server.service -b

Nov 30 00:43:21 OraServer sqlservr[4075]:
 sqlservr: This program requires a machine with at least 3250 megabytes of memory.

Memory was added and SQL Server was started:

systemctl status mssql-server


Storing hex values as strings or integers?

I would recommend using the BINARY type; strings waste a lot of space, and integers require conversion.

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

For the other part: why do you need the associative table? If objects and fingerprints are 1 to 1, you could either store them directly in the object table, or just have single table with the id, fingerprint, and object id FK.


CLUSTER using sequential scan followed by sorting

TFM says:

  It is impossible to suppress explicit sorts entirely, but turning this variable off discourages the planner from using one if there are other methods available.

So yes, it will reorder the table rows properly, but will try to chose an alternative, potentially slower, access method, if at all possible.


Are triggers bad for created/timestamp columns?

As far as I know, this is the reason Triggers were made.
To handle an event related to a change running in the DB.
I don't see any reason why you shouldn't be using a Trigger.
P.S. please ask your DBA of why he doesn't like Triggers, and report back. Thank you.


Relational Database design best practice

Problems involving classes and subclasses (or, if you prefer, types and subtypes) come up all the time in database design.  Your situation looks like a case in point.

In your case, here's what I would do, using a technique called Class Table Inheritance combined with a technique called Shared Primary key.

Create three tables:  Contacts, Professors, and Students.  Use the autonumber feature for the ID field of the Contacts table.  Have an ID field in the other two tables, but do not use the autonumber feature.  Instead, whenever you insert a new Contact, obtain the ID field just generated for the new contact, and provide that ID field as the value for ID in either Professors or Students.  The ID field is declared as the primary key in all three tables. You can even create a Contact that is both a student and a professor, if that makes sense in your situation.

You place other attributes in the appropriate table, according to the attribute, as you have suggested.

ID is guaranteed to be unique in all three tables.  If you want data about only students, join Contacts and Students matching on the ID field.  Likewise for data about professors.

Using shared primary key in this way enforces the 1:1 nature of the relationships between Students or Professors and Contacts.

Good luck!


MySQL replication slave using percona and docker

The root cause of this issue was the absence of the relay-log option in the mysql.cnf file (or in this case, due to the docker volume mounts, the docker.cnf file). This lead to the creation and usage of files such as 89726507f176-relay-bin.000002 initially, where 89726507f176 is the host name of the machine (randomly assigned by the docker daemon when an image is created). When the container was stopped, removed and recreated, a new set of files was created and used (e.g. be0c801d95bc-relay-bin.000407) but this caused sync issues.

By explicitly specifying a value for relay-log in the docker.cnf file the container was able to be removed and recreated without problems.

As a side note, I suggested also that there was a problem with the /var/log/mysql directory not being mounted - this is not the case. If however you specify a value of log_bin = /var/log/mysql/mysql-bin.log for example, then this is a requirement. If you do not specify this path, it seems the binary logs are stored locally in /var/lib/mysql which is already mounted outside the container.

My final docker.cnf file is as follows:

[mysqld]
skip-host-cache
skip-name-resolve
bind-address        = 0.0.0.0

binlog-ignore-db = mysql
replicate-ignore-db = mysql

log_bin = /var/log/mysql/mysql-bin.log

relay-log   = replication-1
server_id   = 1

Note: server_id = 2 on the replication slave.

Also note that without the relay-log option the command SHOW MASTER STATUS; returned no results on the master database container.

There is a possible outstanding issue yet which is that by default when you use docker stop it asks the container to terminate (by sending a SIGHUP to the docker entrypoint command) and if it doesn't terminate within 10 seconds it is forcefully stopped. I need to ensure taht this is given sufficient time to shut down as it could take a little while to sort itself out while under load, possibly resulting in data loss as a result.