Restore Standard/Enterprise Dump to Express I have a need to restore a dump (which is less than 1GB).  Can I restore a dump that was made in either enterprise or standard to express?  I'm asking because I know from experience with SQL that this is impossible.  I've been looking and I can't see to locate if Oracle has the same limitations.

As far as I can tell, yes - you can import a DMP file whose origin is Standard or Enterprise edition into an Express edition database.

However, there are certain restrictions. One of them seems to be OK - size of user data (you have less than 1 GB, while 11g XE accepts up to 11 GB).

If tables are partitioned in the source database, they won't be imported as XE doesn't support partitioning. (Though, if you pre-create such a table and import only data, that should also be OK.)

The same goes for other features that aren't supported by XE (such as internal Java virtual machine, materialized view query rewrite) - they won't be imported.

If tablespace names are different in the source and XE database, don't forget to include the REMAP_TABLESPACES parameter.

I suggest you give it a try; XE is a nice tool to play with. Review the log file - it'll tell you which objects weren't imported and why. Good thing is that - even if something fails to import - it won't stop other objects from importing. Then you'll know whether the final result suits your needs or not.


Calculate Num_Index_Pages for sizing design I don't understand Step2 #8 Calculate the number of number of non-leaf pages in the index.  I am not sure I understand the "Level".

I have Num_Leaf_Pages=508 & Index_Rows_Per_Page=476

I don't understand how they arrived at
Num_Index_Pages = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43
"which is the number of pages described in the example."

1000/253 does not give 1, and 1000/252 does not give 2...What am I missing with regard to the "level" and how can I plug my values of 508 and 476 in and be confident with the result.

Attempting to implement the same, I ended up using this to approximate at a decent level of accuracy:
WHILE (@NonLeafLevels > 1)
BEGIN
    DECLARE @TempIndexPages FLOAT;

    -- TempIndexPages may be exceedingly small, so catch any arith overflows and call it 0
    BEGIN TRY
        SET @TempIndexPages = @NumLeafPages / POWER(@IndexRowsPerPage, @NonLeafLevels);
        SET @NumIndexPages = @NumIndexPages + @TempIndexPages;
        SET @NonLeafLevels = @NonLeafLevels - 1;
    END TRY
    BEGIN CATCH
        SET @NonLeafLevels = @NonLeafLevels - 1;
    END CATCH
END;


SQL server 2014 Transaction Log Backup fails I have setup a Maintenance Plan for my SQL server 2014 Database. This Plan Generates Full Backup every Sunday, and Differential backup every Day and Transaction Log every hour.

This Maintenance plan worked fine initially, but now after few successful backups the Transaction Log backups are failing (specially during Production Hours).

When i restart the SQL server instance, then the Backups are generated fine again until the production hours (work hours). My Database is around 40Gb.

So what is causing this Backups to fail during Production hours? Any suggestions

I Installed SQL Server 2014 Service Pack 1 (SP1) and that resolved the issue.


How did this statement update 3 rows in Postgres? I am curious how this statement updated 3 rows in Postgres. All the other times I ran it, it would update 0 or 1. Is there a way to find out which rows?

bestsales=# update keyword set revenue = random()*10 where id = cast(random()*99999 as int);
UPDATE 3

id is the primary key.

 id               | integer                        | not null default nextval('keyword_id_seq'::regclass)
    "keyword_pkey" PRIMARY KEY, btree (id)

I tried to run it as SELECT:

bestsales=# select * from keyword where id = cast(random()*99999 as int);
  id   |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | google_violation | revenue | bing_violation
-------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+------------------+---------+----------------
  6833 | vizio m190mv        |         | GOOGLE |            0 |         |          |     70 | 0.38 |        0.90 |             |                  |         |
 65765 | shiatsu massage mat |         | SPYFU  |            0 |         |          |    110 | 0.69 |             |             |                  |         |
 87998 | granary flour       |         | SPYFU  |            0 |         |          |     40 | 0.04 |             |             |                  |         |
(3 rows)

And sometimes it would return more than one. How is that possible?

PostgreSQL 9.5.3

It seems that random() is executed once per row and not once for all the statement. So each id value is checked against a different random value. You may get 0, 1, 2 or more, even all the rows of the table, although that possibility would be very small even with 10 rows.

If you want it to be executed once and thus get only one value and update only zero or one rows, you could use a CTE, which are known to be evaluated before the main query:

with rnd as
  ( select random() as random )               -- get just one value
select *
from keyword, rnd
where id = cast(rnd.random * 99999 as int) ;  -- and use it everywhere

and the same for the UPDATE:

with rnd as
  ( select random() as random )
update keyword
set revenue = random()*10                    -- different random value
from rnd
where id = cast(rnd.random * 99999 as int);


auto set value of a specific column after 24 hours and to delete record after one week: Oracle SQL Here is my table:
create table passwordResetLink(
link_id number(5) primary key,
link_date timestamp(0) default sysdate NOT NULL,
isExpired number(2)
email varchar2(110) UNIQUE NOT NULL,
linkUsed number(2)
);

How can I auto update isExpired column? I mean when record is inserted its value should auto set to 0 and when 24 Hours passed the 0 should be updated as 1
link_date is timestamp of inserted record. The record that has passed one week should auto be deleted from the table

Thanks

Rather than query a table, why not query a view that has your rules built in?
CREATE VIEW vwPasswordResetLink
    AS
SELECT link_id,
       link_date,
       CASE WHEN sysdate > link_date + INTERVAL '1' DAY THEN 1
            ELSE 0 END AS isExpired,
       email,
       linkUsed
  FROM PasswordResetLink
 WHERE link_date >= sysdate - INTERVAL '7' DAY;

Then you can write a scheduled job to delete records older than 7 days to clear out expired records.
Alternatively, you could just use the above query to filter results and skip the creation of the view altogether …


Why can't I get the available free space after shrinking? My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image.

Once I ran the shrink for data file, though it completed successfully I didn't get the 5GB space as indicated. Infact the disk size remained the same. Nothing was shrunk. For log file shrink it shows available space of 200MB and when I run a log file shrink it does free up 200MB as indicated. So why is it not working with data file shrink? What am I doing wrong? Please advice.

In shrink action, you need to choose:

"Reorganize pages before releasing unused space"

And choose desidered size.

Actually you don't have released space because you have still used pages ad the end of your file.

Edit:
You can do it via query, with instructions like this:

DBCC SHRINKFILE (N'MATRIX24X7' , 449000)


What are the SET options that affect Plan Reuse and how to get their values in T-SQL? I'm troubleshooting the case when a stored procedure runs great from the management studio at any time, but the same stored procedure can run terribly badly in one of the webservers, even for the same parameters. There could be a few things causing this, including blocking, etc.
I would like to leave out the possibility that 2 different plans were created with different SET options, and at least one of those plans was optimized using a combination of parameters that produced a bad plan.
Quoting Benjamin Nevarez:

"in general, query optimization is an expensive operation, and in
order to avoid this optimization cost, the plan cache will try to keep
the generated execution plans in memory so they can be reused.
However, if a new connection running the same stored procedure has
different SET options, it may generate a new plan instead...."

In order to tackle this I would like a T-SQL query that would show me all the values that have been set for the session.
Is this possible?

the following SET options will affect the reuse of execution plans:
(some of them)
DATEFORMAT
LANGUAGE
NUMERIC_ROUNDABORT
FORCEPLAN

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason.

A list of plan cache keys can be found in the following answer by Martin Smith:

What would cause parameter sniffing one one computer and not another?


Stored procedure with ROW instead of a list of parameters I have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement.

I would like to know if it is possible to declare the list of IN parameters of the stored procedure as a RECORD structure instead of 15 parameter, so when calling this stored procedure, I will only pass a RECORD structure instead of 15 single variables.

Is that possible in PL SQL? If no, is there any shorthand for doing this kind of declarations? Thanks

Sure.  You can even use the predefined %rowtype record.

CREATE OR REPLACE PROCEDURE insert_row( p_rec IN table_name%rowtype )
AS
BEGIN
  INSERT INTO table_name
    VALUES p_rec;
END;


Index for inequality on multiple columns (col1 >= 'const_1' AND col2 <= 'const_2') I have the following query:

SELECT * FROM plane_tracks
    WHERE created_at >= '2016-12-29 08:00:00' AND updated_at <= '2016-12-30 00:00:00';

I've created the following indexes:

create index plane_tracks_on_created_at on plane_tracks(created_at);
create index plane_tracks_on_updated_at on plane_tracks(updated_at);
create index plane_tracks_on_created_at_updated_at on plane_tracks(created_at,updated_at);
create index plane_tracks_on_created_at_updated_at_desc on plane_tracks(created_at,updated_at DESC);

Yet when I run the query, it scans all elements in plane_tracks.

# EXPLAIN ANALYZE SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00' AND updated_at <= '2016-12-30 00:00:00';
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on plane_tracks  (cost=0.00..582.78 rows=5425 width=471) (actual time=1.507..3.435 rows=1978 loops=1)
   Filter: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
   Rows Removed by Filter: 15941
 Planning time: 0.366 ms
 Execution time: 3.618 ms
(5 rows)

When I drop the updated_at part, like this:

SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00';

Then I get this:

# EXPLAIN ANALYZE SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00';
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on plane_tracks  (cost=0.00..537.99 rows=8595 width=471) (actual time=0.022..3.261 rows=8593 loops=1)
   Filter: (created_at >= '2016-12-29 08:00:00'::timestamp without time zone)
   Rows Removed by Filter: 9326
 Planning time: 0.093 ms
 Execution time: 3.901 ms
(5 rows)

At least this last part makes sense.

But I don't understand why

the first query isn't at least as efficient as the second one.
why first query isn't much better than the second one, given that there are also indexes on updated_at.
what index to create to make it really efficient

Tom

Whether PostgreSQL will use one index or not depends (a lot!) on the actual values of your tables, the conditions on your queries, whether statistics are up-to-date for the values in the table, and your configuration settings. So... whatever comes next depends a lot on your actual data. If you can provide a better description of your actual values, I guess we'll be able to better help you.

Anyhow, a first guess:

I assume your table has a structure similar to this:

CREATE TABLE plane_tracks
(
    plane_track_id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITHOUT TIME ZONE NULL,
    some_pay_load TEXT
) ;

I populate the table with some simulated data:

-- Generate data for a few days (60.475 rows).
INSERT INTO
    plane_tracks (created_at, some_pay_load)
SELECT
    generate_series(timestamp '2016-12-25 00:00:00',
        timestamp '2016-12-31 23:59:00', interval '10 seconds'), 'payload' ;

I simulate that (about) half your rows are updated, and that changes "udpated_at":

-- Update randomly some of these data (just simulate updated_at)
UPDATE
    plane_tracks
SET
    updated_at = created_at + ((random()*10) || ' minutes')::interval
WHERE
    plane_track_id IN
    (SELECT plane_track_id FROM plane_tracks ORDER BY random() LIMIT 30000) ;

I make sure PostgreSQL has good statistics:

ANALYZE VERBOSE plane_tracks ;

If I use under these conditions an index on (created_at, updated_at) with your query:

CREATE INDEX
    plane_tracks_on_created_at_updated_at
    ON plane_tracks(created_at,updated_at);

The index IS really used:

explain analyze SELECT * FROM plane_tracks
    WHERE created_at >= '2016-12-29 08:00:00'
      AND updated_at <= '2016-12-30 00:00:00';

Bitmap Heap Scan on plane_tracks  (cost=890.34..1679.47 rows=12209 width=28) (actual time=4.611..5.194 rows=2834 loops=1)
  Recheck Cond: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
  Heap Blocks: exact=22
  ->  Bitmap Index Scan on plane_tracks_on_created_at_updated_at  (cost=0.00..887.29 rows=12209 width=0) (actual time=4.596..4.596 rows=2834 loops=1)
        Index Cond: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
Planning time: 0.147 ms
Execution time: 5.443 ms

The reason why the index is used: PostgreSQL thinks it will help it select 12.209 out of the 60.475, and this will take less time than a sequential scan. This is giving good selectivity.

If you just change your query, and you make the condition broader, see what happens:

explain analyze SELECT * FROM plane_tracks
    WHERE created_at >= '2016-12-26 08:00:00'   /* 26 instead of 29 */
      AND updated_at <= '2016-12-31 00:00:00';  /* 31 instead of 30 */

Seq Scan on plane_tracks  (cost=0.00..1963.12 rows=31066 width=28) (actual time=12.890..21.236 rows=19993 loops=1)
  Filter: ((created_at >= '2016-12-26 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-31 00:00:00'::timestamp without time zone))
  Rows Removed by Filter: 40482
Planning time: 0.086 ms
Execution time: 22.441 ms

The reason: PostgreSQL has decided that, if the number of rows returned is going to be much higher than before, it makes more sense to scan the whole table than to use the index. Using an index means: first check the index, then gather the data from the main table. The savings on data gathering (SELECT *) would not be compensated by the expense of also checking the index.

In practice: I'd probably have one ("reasonable") index, and let PostgreSQL decide whether it is worth using it or not. The way you use your index (one comparison >=, the other <=), I think the best suited should be:

CREATE INDEX
    plane_tracks_on_created_at_updated_at
    ON plane_tracks (created_at, updated_at DESC);

Anyhow, it's normally good to check a few variants. You could also try to use a GIST index:

CREATE INDEX
    plane_tracks_gist_idx
    ON plane_tracks USING   gist   (created_at, updated_at);

... and see if that helps. GIST indexes tend to be good when you're going to use ranges, but you need to try which combination fits best your use-case.

NOTEs:

My test were done on 9.6.1 on Mac OS X 10.12, with "out of the box" settings. If you perform the same tests with other settings, results might change.
The choice of sequential versus index scans depends not only on your data, but also on a number of PostgreSQL settings. You can actually force PostgreSQL to not use index scans, or set some cost parameters for random access so high that, in practice, the usage of indexes will be heavily restricted. Check the Query Planning documentation.
When you're not sure about which indices to use, you can start by creating (a few) more than you actually need, analyze the index usage statistics after the system has been running in practice for a period that you think is long enough, and remove the seldom used ones.


MySQL taking too much CPU Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that the remote server's open a connection, read/write/update and then close it. Also the remote server reads a lot.

What can I do to reduce my cpu consumption. FYI, I am using 2 core CPU with 4GB RAM.

[client]
port          = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice          = 0

[mysqld]
user          = mysqluser
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port          = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

Also pasting output from mysqltuner.

>>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 2G)

Ignore the "fragmented" alarm; it's bogus.  Ignore the max "possible" memory; it's bogus.

Focus on the slow queries; they are causing the high CPU.  What are the slow queries?

Plan A:  Glance at SHOW FULL PROCESSLIST; frequently.  You will see one or two queries there most of the time.

Plan B:  Turn on the slowlog; set long_query_time = 2; and (after a day), run pt_query_digest.  It will tell you the worst queries.

Given a bad query (or two), show us

SHOW CREATE TABLE
EXPLAIN SELECT ...

We should be able to help you dramatically shrink the CPU load.

(Contrary to what @dborba suggested, I ruled out swapping and I/O.  Those tend to show up as low CPU, but high latency for queries.  We may need to address it again after adding some INDEXes, etc.)


Window Azure SQL Server DB Backup to a local-Premise One of my customers stopped subscribing to our services and they INSIST to have their data stored in their local SQL Server. Their data size is quite some amount though.

We did try to use the Azure Sync (Preview) and the process is painful and troublesome.

Question came in mind, is there a traditional way that backup to .bak and restore to their SQL Server? Is there any other way of doing this?

I would ditch Azure Sync by all means .. buggy and still in preview since ages.

We have exactly same requirement and what I did was create SSIS package and load the data from Azure to OnPremise on a nightly basis. It works just fine once you set it up. Make sure to load the data as per FK relationship.

On other side, you can explore Windows Azure SQL Database Import/Export Service.


Postgresql - Get Oid and relation name for relations created by the query Consider the following TPC-DS benchmark query 50.

with ss as
(select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
from store_sales,date_dim,customer_address
where ss_sold_date_sk = d_date_sk
and ss_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year),

ws as
(select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
from web_sales,date_dim,customer_address
where ws_sold_date_sk = d_date_sk
and ws_bill_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year)

select /* tt */
    ss1.ca_county
   ,ss1.d_year
   ,ws2.web_sales/ws1.web_sales web_q1_q2_increase
   ,ss2.store_sales/ss1.store_sales store_q1_q2_increase
   ,ws3.web_sales/ws2.web_sales web_q2_q3_increase
   ,ss3.store_sales/ss2.store_sales store_q2_q3_increase
from
    ss ss1
   ,ss ss2
   ,ss ss3
   ,ws ws1
   ,ws ws2
   ,ws ws3
where
   ss1.d_qoy = 1
   and ss1.d_year = 2000
   and ss1.ca_county = ss2.ca_county
   and ss2.d_qoy = 2
   and ss2.d_year = 2000
   and ss2.ca_county = ss3.ca_county
   and ss3.d_qoy = 3
   and ss3.d_year = 2000
   and ss1.ca_county = ws1.ca_county
   and ws1.d_qoy = 1
   and ws1.d_year = 2000
   and ws1.ca_county = ws2.ca_county
   and ws2.d_qoy = 2
   and ws2.d_year = 2000
   and ws1.ca_county = ws3.ca_county
   and ws3.d_qoy = 3
   and ws3.d_year =2000
   and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end
     > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end
and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end
     > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end
order by web_q2_q3_increase;

While executing this query inside the postgres code, I want to be able to get the relation name from relid or Oid.
I am able to do that for relations web_sales, date_dim, customer_address and store_sales, i.e. I have their relids, from which I am able to get Oid and relation names.

But for relations ss and ws, which are created in the query, I have a relid, but I get their Oid as zero and thus relation name as null.

is there some way for ss and ws also I can get their names from the relids?
is ther no RangeTblEntry for ss and ws ?

I got the solution.

I have the relid of the CTE. (in the example ss1, ss2 etc.)

From the relid I can get RangeTblEntry for each CTE using

RangeTblEntry *rt1 = ((RangeTblEntry *) list_nth(root->parse->rtable, (relid)-1));

For a CTE RangeTblEntry has a field ctename, which stores the name it's a CTE.

You can use rt1->ctename to get the CTE name.


Does SQL Server 2017, including older versions, support 8k disk sector sizes? Disk (loosely worded to include not only rotational media but non-rotational media [SSD, NVMe, etc.]) drives are continuing to evolve in their underlying formats and hardware. Part of this was an "enhancement" from 512 byte physical sector sizes to 4k physical sector sizes, which changes the on disk layout (512n, 512e, 4kn).

This next evolution is in using 8k physical sector sizes, which some manufacturers are starting to produce and setup in production. Given this next step, is the 8k sector size disk supported in Windows? Does SQL Server care about sector sizes?

Is the 8k sector size disk supported in Windows?

Currently, no, it is not supported by Windows and has been documented.

  Does SQL Server care about sector sizes?

Yes, SQL Server does care about sector sizes. In fact, SQL Server checks the underlying physical disk information as it has data structures and algorithms that work with current disk sector sizes for various reasons (data integrity, optimizations, etc.).

If you attempt to use a physical sector size above 4k (4096) you'll receive an error:

Msg 5179, Level 16, State 1, Line 1
Cannot use file 'S:\Folder\TestDB.mdf ', because it is on a volume with sector size 8192.
SQL Server supports a maximum sector size of 4096 bytes.
Move the file to a volume with a compatible sector size.

In this case, you can see that the error message is specifically telling you that the maximum sector size is 4096 (4k). This means that volume can't be used for SQL Server, and as noted above it wouldn't be supported in Windows either.

Solution

If you're receiving this error/issue, unfortunately you'll need to use a supported disk sector size such as 512 bytes or 4k. Other formats are unsupported and can't be used. You'll need to contact your hardware vendor for the Disk subsystem used and ask if there are any other options.

As eckes mentioned in a comment, on Linux you can emulate a 8k drive with the new logical blocksize option of losetup -b with 4.14 kernels.


Unable to add primary key to innodb table without one I've inherited myself a fun table with some peculiar indexes (and no primary key) on it, that looks as follows:

CREATE TABLE `my_wonky_table` (
  `id` bigint(20) unsigned NOT NULL,
  `login` varchar(127) DEFAULT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `ip` varchar(32) CHARACTER SET ascii DEFAULT NULL,
  `val_1` int(10) unsigned DEFAULT NULL,
  `val_2` varchar(127) DEFAULT NULL,
  `val_3` varchar(255) DEFAULT NULL,
  `val_4` varchar(127) DEFAULT NULL,
  `val_5` int(10) unsigned DEFAULT NULL,
  KEY `my_wonky_table_id_idx` (`id`),
  KEY `my_wonky_table_timestamp_idx` (`timestamp`),
  KEY `my_wonky_table_val_1_idx` (`val_1`,`id`),
  KEY `my_wonky_table_val_2_idx` (`val_2`,`id`),
  KEY `my_wonky_table_val_4_idx` (`val_4`,`id`),
  KEY `my_wonky_table_val_5_idx` (`val_5`,`id`),
  KEY `my_wonky_table_ip_idx` (`ip`(16),`id`),
  KEY `my_wonky_table_login_idx` (`login`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION pdefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Now, because it has no primary key, nor a fully unique index, MySQL adds a GEN_CLUST_INDEX (that's taking up quite some space, but that's a different matter).

Note that even though there is a column called id, it's not actually a unique entry. For this reason, I want to add an (auto incremented) column, that, combined with the id column, should act as the primary key.

However, I appear to be unable to add an auto_incremented column; I suspect this is because of the 'secret' column MySQL adds, because of the error message saying I already have an auto-incremented column, even though I don't believe I do.

The following:

ALTER TABLE my_wonky_table
    ADD COLUMN count SMALLINT NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (id, count);

yields:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Any ideas?

You can have an auto-Incrementing column, as long as there is an index (key) on it, to fix this issue you need to execute those commands:

ALTER TABLE my_wonky_table ADD COLUMN count SMALLINT NOT NULL;
ALTER TABLE my_wonky_table ADD INDEX (count);
ALTER TABLE my_wonky_table CHANGE count count SMALLINT NOT NULL AUTO_INCREMENT;
ALTER TABLE my_wonky_table ADD PRIMARY KEY (id, count);


Revised Question: How do I prevent Oracle 12c Database from starting on machine boot I'd posted previously about srvctl, didn't realize it was only used if a Grid Infrastructure was installed, which in my case, is not.

Anyways, I have Oracle 12c Database installed on my Windows 8.1 pro personal machine, used for testing purposes (school work actually).  As such, I don't want it to be up every time I log into my PC.  And being forced to run SQL shutdown every time I've logged in is a hassle (I realize I could write a batch script but that's a last resort).

I can't figure out what service is starting an Oracle Instance at boot.  Does anyone know of what does?  I've been combing through the Oracle Doc's and can't seem to find out what it is.

TL;DR:  Just trying to stop my Oracle Database from initializing on a PC boot.

You'll want to prevent the OracleService<<SID>> from starting automatically.  Assuming you used the default SID of ORCL, that would be OracleServiceORCL.

You'll likely also see a number of other services that start with Oracle that are for various other components (i.e. your listener, your MTS recovery service, etc.)  You can set all those to not start automatically if you want.  Given that those services tend to use many fewer resources, it's generally easiest to let them run to make it easier to start the database later on.


#1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on.
For this tried to create same table with create SQL but it says

#1005 - Can't create table 'mydb.table1' (errno: 150)

Now I created same schema table with table name table2, this was successfully created
but as my application code is written for table1, I need to rename this table to table1 again on which gives me following error.

#1025 - Error on rename of table errno: 150

Also  tried to run alter table query with set foreign_key_checks = 0. But same error.

While searching I came accross below SQL,

SHOW INNODB ENGINE STATUS;

Which displays following message

141223 11:30:10 Error in foreign key constraint of table myahd/table1:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:,
CONSTRAINT "fk_key9" FOREIGN KEY ("updated_by") REFERENCES "users" ("userid") ON DELETE NO ACTION ON UPDATE NO ACTION

While there is no such table listed in my database with name table1.
Nor any such key is found with fk_key9.

May be I can delete this table1's idb or frm file, but unable to locate it.
Or may be there is some other solution.

Thanks in advance.

You have a weird situation, but it has happened others.

Some worked around this

(Bug?) InnoDB MySQL error 1025, errno 150 Foreign Key
What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

Others have tried to explain it

MySQL's ERROR 1025 explained
SQLSTATE[HY000]: General error: 1025 Error on rename of

I think the problem stems from the InnoDB Architecture (Vadim Tkachencko's Picture)

See the data dictionary at the top of ibdata1 ? Although the physical file (table1.ibd) to the InnoDB table is gone, the data dictionary still thinks the table exists.

I have addressed this data dictionary issue before

Jul 03, 2014 : Can't create table, but table doesn't exist
Jun 09, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

You may need to mysqldump the database and load it into a new server (as suggested by the OP since it was unresolved but worked around), or look for a tool that can flush out the bad data dictionary entry.


Does reporting webservice url contain the function that helps to export the report to the client version? The reporting webservice url contains the webservice url that contains all the information of the Reports which are created in SSRS. As the report has been created on reporting service, i want to export/send that report to my client using the SOAP feature.

http://localhost/reportserver_INSTANCE2012/reportservice2005.asmx?wsdl

While creating the project under soapUI using the above wsdl, it returns 105 functions like

Function Name

CreateBatch()
CancelBatch()
CancelJob()
CreateDataDrivenSubscriptionMethod()
CreateDataSource
CreateFolder
CreateLinkedReport
createModelReport
createReport
createReportHistorySnapshot
createResource
CreateRole
CreateSchedule
CreateSubscription
DeleteItem
DeleteReportHistorySnapshot
DeleteRole
DeleteSchedule
DeleteSubscription
DisableDataSource
EnableDataSource
ExecuteBatch
FindItems
FireEvent
FlushCache
GenerateModel
GetCacheOptions
GetDataDrivenSubscriptionProperties
GetDataSourceContents
GetExecutionOptions
GetExtensionSettings
GetItemDataSourcePrompts
GetItemDataSources
GetItemType
GetModelDefinition
GetModelItemPermissions
GetModelItemPolicies
GetPermissions
GetPolicies
GetProperties
GetRenderResource
GetReportDefinition
GetReportHistoryLimit
GetReportHistoryOptions
GetReportLink
GetReportParameters
GetResourceContents
GetRoleProperties
GetRoleProperties
GetScheduleProperties
GetSubscriptionProperties
GetSystemPermissions
GetSystemPolicies
GetSystemProperties
GetUserModel
InheritModelItemParentSecurity
InheritParentSecurity
ListChildren
ListDependentItems
ListEvents
ListExtensions
ListJobs
ListModelDrillthroughReports
ListModelItemChildren
ListModelPerspectives
ListReportHistory
ListRoles
ListScheduledReports
ListSchedules
ListSecureMethods
ListSubscriptions
ListSubscriptionsUsingDataSource
ListTasks
Logoff
LogonUser
MoveItem
PauseSchedule
PrepareQuery
RegenerateModel
RemoveAllModelItemPolicies
ResumeSchedule
SetCacheOptions
SetDataDrivenSubscriptionProperties
SetDataSourceContents
SetExecutionOptions
SetItemDataSources
SetModelDefinition
SetModelDrillthroughReports
SetModelItemPolicies
SetPolicies
SetProperties
SetReportDefinition
SetReportHistoryLimit
SetReportHistoryOptions
SetReportLink
SetReportParameters
SetResourceContents
SetRoleProperties
SetScheduleProperties
SetSubscriptionProperties
SetSystemPolicies
SetSystemProperties
UpdateReportExecutionSnapshot
ValidateExtensionSettings

I would like some one to help me out which function is used to send the existing report of SSRS reporting to my client. I am using the wsdl feature to connect directly to the report server and want to use function listed on above, to send the report in any format like pdf etc.

follow the given instruction, and should be able to print the report in format passed format:

https://surendragurjar.wordpress.com/2015/07/11/ssrs-wsdl-web-service-endpoint-using-java/


VLF, Shrink and Simple Recovery We currently have a transaction log thats been growing incrementally and has too many VLF files.  I am going to follow a process of shrinking and then re-growing the log file to a suitable size to alleviate this problem.

My question is shrinking will only reduce from the end of the file to the last active portion of the log.  Therefore should I change to simple recovery first, then perform the shrink?

Following this I will change back to full perform a full backup to start the backup chain again.

No, do not switch to SIMPLE recovery model since that will result in a break in your backup chain.  If you are doing in FULL recovery model, do the following:

CHECKPOINT
BACKUP the LOG
DBCC SHRINKFILE (logfile, targetsize)

Repeat until you shrink the log file as small as possible. (My experience is that two or three times is usually needed.)

Then set the preferred default log size and the increment of growth for the log. Do not use percentage log growth, but have a defined increment.

If you follow this, then the backup chain is preserved.

If there are reasons that you need to switch to SIMPLE recovery model, then once you return to FULL recovery model you should BACKUP DATABASE to reestablish the start point for your following log backups.


Data Deplucation and relationship integrity I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion.

This is the scenario: We have Salesforce and a multiple local provisioning systems (short history - multiple companies were merged). As part of an effort to standardize, we setup an ETL that pulled the data from all of the local systems, transformed it and uploaded it to Salesforce. Currently we use Salesforce mainly as a Ticketing system and there is no write-back to the Local Provisioning system. The data is refreshed in Salesforce every 2 hours.

The function that the data in Salesforce provides is that as customer calls or emails come in, they are matched against Contact information (pulled from the local provisioning systems).

Our Marketing team is currently wanting to implement Salesforce Marketing cloud, which pulls data from the Contacts table.

Now you have the background - here is the issue:

Since we have a lot of 'duplicate' data - the 2 most common scenarios are:

Person A has an account with Brand X and Brand Y. The ETL sees 2 unique contact objects, assigned to 2 different brands, 2 different accounts with 2 different products and imports them as 2 separate contacts, even though the relevant details (Name, Email, Phone number) is the same.

Person A has multiple accounts within a Brand. Sometimes this can be due to having multiple businesses or they are a reseller but aren't using a Reseller login or whatever - in which case the ETL sees multiple unique contacts (as within the Provisioning system, the contacts have a unique and different primary Key) - and as above, the ETL sees this is multiple unique contacts, belonging to different accounts, having different product sets.

This causes an issue with the Match rates for case generation and it also potentially causes an issue with the Marketing tool that we want to use - in particular an Individual with Multiple accounts might receive multiple copies of Marketing material.

The Marketing team want to just De-Dupe the Contact data, however whilst I have a few theories as to how to do that, that would either:

1: Lose relationships between the local systems and Salesforce - There are some ancillary issues that may be generated here since Products are bound to contact information and when a case is created, the associated products are presented to the Agent.

2: Create some form of relationship mapping to try and preserve things - this may get extremely messy and may end up crossing Brand/Account boundries, which IMO could create Legal/Privacy issues if anything went wrong. Not to mention it creates something that is not pulled from the Source provisioning system.

Currently, I've designed this process with the mantra that what is in the source system must be replicated to Salesforce, but with what I'm being asked to do, will break that.

So - to sum up and ask a question: Are there any 'rules' about De-duplication and Data Integrity I should be aware of or reference? How would you architect such a requirement? Any Pitfalls or 'gotchas' or similar I should be aware of? Should I even pursue this course of action?

Edit:

I'm looking more for some guiding principles as opposed to a specific answer - I appreciate that it's a bit broad, however Breadth is what I need right now, I should be able to work on the detail, but right now I need some help on the big picture.

First answer of find out what the business needs is a good one - so I'm going to sit down with the Marketing team and other stakeholders and see what we get.

More information and general principles/advice like that is appreciated.

There's no real hard rules when it comes to data cleanup/deduplication as it usually depends on these factors:

The quality of your dataset(s)

You don't mention quality being an issue so I won't say any more on this but usually matching data from different systems can be the biggest hurdle to tackle. e.g. David in one system might be Dave in another.

What the business needs

You already have a list of requirements and that's guided you so far. Now you've hit something that will impact the quality of the output. What would be beneficial is to sit down with the process and data owners and tell them the concerns you have with either method. It's not a solely DBA/Developer issue to solve and the other stakeholders can then be part of the solution. It help will avoid those "Why did you do that TheDemonLord! That's not what we wanted" accusations down the line when you thought you were doing the right thing but someone else disagrees!

You can then define the logic and rules around the dedupe effort together e.g. If a person has an account in with Brands X & Y then we will use the details from the most recent purchase etc...

How much effort can you devote to cleaning or transforming the data

Like any task there's a clear time/quality equation to deal with. You can easily get trapped in an ever growing list of manual fixes to have a perfect dedupe / extract process that captures all you need it to. This is another one to solve with the business. It's not your job to find every error and fix every issue. Define a process for highlighting and logging any accounts that might not fit your extract model. Then decide who fixes it, where and when.

As you first said, it is quite theoretical, and that's why the questions you uncover as you build it should be solved by everyone who is using the system.


fetch all data having a Price BETWEEN 100000 AND 10000000 I have a table named articles with a column named price.

When a user on my web app selects both Price Min and Price Max, I need to perform a query like this:

$priceMin = 100000;
$priceMax = 10000000;

$query = 'SELECT * FROM articles WHERE price BETWEEN $priceMin AND $priceMax';

However, when the selected Price Min is <= 100000 and Price Max is >= 10000000 (10 Million), I don't have any output with a Price greater than 900000 even though rows with values above 900000 do exist.

The table is defined like this:

CREATE TABLE articles (
 id int(11) NOT NULL AUTO_INCREMENT,
 name varchar(100) NOT NULL,
 price varchar(100) NOT NULL,
 source varchar(255) NOT NULL, PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

Storing numeric data in a varchar column is causing your difficulty.

You can see this effect with a very simple test.

First a table with two columns where we'll store identical data; one column is a varchar, the other is an integer.

CREATE TABLE test
(
    v varchar(100) NOT NULL
    , n int NOT NULL
);

Insert some sample data:

INSERT INTO test (v, n)
VALUES (0, '0')
    , (1, '1')
    , (2, '2')
    , (100, '100')
    , (99, '99');

Show the data, ordered by the varchar column:

SELECT *
FROM test
ORDER BY v;

Results:

╔═════╦═════╗
║  v  ║  n  ║
╠═════╬═════╣
║   0 ║   0 ║
║   1 ║   1 ║
║ 100 ║ 100 ║
║   2 ║   2 ║
║  99 ║  99 ║
╚═════╩═════╝

Show the data, ordered by the integer column:

SELECT *
FROM test
ORDER BY n;

╔═════╦═════╗
║  v  ║  n  ║
╠═════╬═════╣
║   0 ║   0 ║
║   1 ║   1 ║
║   2 ║   2 ║
║  99 ║  99 ║
║ 100 ║ 100 ║
╚═════╩═════╝

The varchar column is sorted alphabetically, whereas the integer column is sorted numerically.

You should consider altering the price column to a numeric data type that supports the precision and magnitude you require.  Refer to the MySQL developer guide for fixed-point data types for more details.  I'd suggest considering decimal(18, 4) as a starting point for prices.  18 digits is the precision, i.e. the total number of digits that can be stored.  4 is the scale, which is the number of digits to the right of the decimal point.  If the price column was defined as decimal(18, 4), it would be able to store values such as 99999999999999.9999, and allow them to be sorted correctly.  Defining the column as a varchar is problematic since it will allow you to store hedgehog as well as 10000, which will lead to a variety of problems, not least of which will be the inability to obtain a total price of articles in a group.


Oracle 12c upgrade - backwards compatible with 11g? Are there any application code changes required for SQL or PL/SQL to continue to work in 12c, or is it backwards compatible with 11g?  Are there any queries that would work in 11g that break in 12g?

I remember having to change some queries when upgrading 10g to 11g, but it turned out to be a bug in 10g that was fixed in 11g - the queries should have failed in 10g to begin with.  (See: https://stackoverflow.com/questions/4269205/why-doesnt-oracle-raise-ora-00918-column-ambiguously-defined-for-this-query)

Usual rules apply. Code changes should not be required but something will break if code is more complex. Also there will be some SQL plans regressions.

Official documentation about deprecated features is there: Deprecated and Desupported Features for Oracle Database 12c

Also you can check Metalink note 567171.1 about _fix_control parameter. This parameter is used to enable or disable certain bugfixes which affect query behaviour. In 11.2.0.4 the view V$SYSTEM_FIX_CONTROL contains 854 rows which means that there are so many potential quirks while upgrading to the newer version of Oracle Database. I am sure that 12c contains much more rows.


How do I merge three update query into one single update query I am trying to update a promotion table with a single update statement. Although the three update statements below can do it, I want to merge the three statements into one. How can I do that?
UPDATE promos SET next_promotion = (last_promotion + 4) WHERE grade_level IN (14,15,16,17,18)
UPDATE promos SET next_promotion = (last_promotion + 3) WHERE grade_level IN (7,8,9,10,11,12,13)
UPDATE promos SET next_promotion = (last_promotion + 2) WHERE grade_level IN (1,2,3,4,5,6)

You could do it with a CASE statement like so:
UPDATE P
SET P.next_promotion =
     CASE
         WHEN P.grade_level IN (14,15,16,17,18) THEN P.last_promotion + 4
         WHEN P.grade_level IN (7,8,9,10,11,12,13) THEN P.last_promotion + 3
         WHEN P.grade_level IN (1,2,3,4,5,6) THEN P.last_promotion + 2
         ELSE P.next_promotion
     END
FROM Promos AS P
WHERE P.grade_level >= 1
AND P.grade_level <= 18 -- Ensures we only update the records we want to change and doesn't do any extra work updating

And this equivalent query minimizes the code (if you're counting characters :).
UPDATE Promos
SET next_promotion =
     CASE
         WHEN grade_level BETWEEN 14 AND 18 THEN last_promotion + 4
         WHEN grade_level BETWEEN 7 AND 13 THEN last_promotion + 3
         WHEN P.grade_level BETWEEN 1 AND 6 THEN last_promotion + 2
         ELSE next_promotion
     END
WHERE grade_level BETWEEN 1 AND 18 -- Ensures we only update the records we want to change and doesn't do any extra work updating


Implementing something similar to "pointer arrays" with MySQL While building a site I came across the following issue:

I want to be able to have multiple containers and within each container have ids pointing to multiple items that lie on another table. Scheme:

I am using MySQL and would like to stick with it. However I am open to other ideas.

My questions are:

Would this be a reasonable data structure, or is there any better way to organize this data?
If So, How would I go about implementing this idea of "array of pointers" to elements.

Possible Solution: http://tutsbook.com/mysql-foreign-keys/

You are conceptualizing this from something other than a relational database perspective, but it sounds like what you're looking for is a junction table (which also has several other names, all describing pretty much the same construct).

This structure allows multiple "this" to reference multiple "that" with unlimited flexibility with regard to which or how many of "this" references which or how many of "that"... you create a third table that is conceptually "between" the existing "this" and "that" tables.  The juction/map/link table often has just two columns, "this_id" and "that_id" which combine to make up its primary key... and you create the "pointers" by inserting rows in that third table containing each this_id/that_id pair that reflects the way the records should be related.

You typically then would retrieve the records with a join.


ORDER BY clause with Alias I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market values.

Works:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY SUM( MKT_VAL ) DESC;

Fails:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY MarketVal DESC;

It appears when I try to ORDER BY the Alias (MarketVal), Access asks for MarketVal parameter value. However, when I ORDER BY SUM( MKT_VAL ), the code executes fine. I can't understand why this is, can anyone tell me why?

There is no issue in both the query they will run properly.

you must inspect any expressions Or find the identifier that is causing this.


SQL Server, how to log client PID of failed login attempts? I'm trying to figure out which process is trying to connect to my SQL Server instance with a wrong password. The log file only contains the IP address from where the connection is initiated. Example:

Date      4/05/2021 9:43:34 AM
Log        SQL Server (Current - 5/05/2021 10:11:00 AM)
Source     Logon
Message
Login failed for user 'SA'. Reason: Password did not match that for the login provided. [CLIENT: 10.120.1.99]

It does not log which PID from the client machine made the attempt.
I know that I can use profiler to find out which PID from which machine is responsible for this. But I do not want to keep a profiler running for this. (especially if this happens rarely, in which case I'll need to keep the profiler running for days before I can catch such an attempt).
Is there a way to log the PID as well as the IP for such failed logons?

Default Server Trace
Try to get more info from the default server trace, as Eitan Blumin's article Finding the Details Missing from the SQL Server Failed Logins Audit shows using this query:
SELECT  trc.*
FROM fn_trace_getinfo(default) AS inf
CROSS APPLY fn_trace_gettable (convert(nvarchar(255), inf.value),default ) AS trc
WHERE inf.property = 2 AND inf.value IS NOT NULL
AND trc.EventClass= 20
ORDER BY trc.StartTime DESC

XE Session
Alternatively, Dave Bland's article Find Failed Logins Using Extended Events has an example of how you can achieve it using an XE Session:
CREATE EVENT SESSION [FailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username)
WHERE ([severity]=(14) AND [error_number]=(18456) AND [state]>(1)))
ADD TARGET package0.event_file(SET filename=N'C:\temp\FailedLogins.xel')
GO

Don't forget to create the folder listed on the path (or change it). With that XE Session you'll be able to capture the client_app_name which is what you're actually looking for instead of just the PID.
SQL Server Audit
You could also configure a SQL Server Audit, but the doc says:

SQL Server audit uses Extended Events to help create an audit.

Therefore, using an Audit would cost about the same as creating the XE Session.


Transitive dependency with foreign keys For an assignment, I had to design a database from a given problem (based on a fictional health service organisation with 3 medical centres).

Three of the entities I had (among many others) were:

Branch (the actual medical centres; primary key branchNo)
BranchSection (the sections within each medical centre; primary key sectionNo; includes branchNo as foreign key to Branch table.)
Staff (the people working at the medical centres; primary key staffNo; includes sectionNo as foreign key to BranchSection)

One of the requirements of the given problem was that the staff entity hold information about the section and branch of each staff member, and so I added the SectionNo attribute as a foreign key in the staff entity (see below). I intentionally didn't put the BranchNo attribute into the staff entity because the SectionNo determines the BranchNo attribute and so I thought there would be a transitive dependency if I had both SectionNo and BranchNo in the staff entity. However, when I got my result back I was marked down for not having BranchNo in the staff entity. Could someone please clarify whether I was right in assuming this would cause a transitive dependency or whether my tutor was right to mark me down.

Here is a visual representation of these 3 entities (to make it easier to understand what I'm saying).

Branch (BranchNo, address, telephoneNo)
BranchSection (sectionNo, sectionName, branchNo)
Staff (staffNo, firstName, lastName, position, birthdate, telephoneNo, sectionNo)

Note: sectionNo is a true primary key; it is unique across all Branches.

While I would say you are definitely correct, and this was the correct design, your tutor may be technically correct (it's difficult to tell, especially, if you're translating form another language - can't tell if you are or not (congrats!)).

You are correct, that a given sectionNo determines the branchNo, since that value exists in BranchSection. Including branchNo in Staff in addition to sectionNo is redundant, and would indeed lead to having to make sure that all linked branchNo values in Staff are updated if the branchNo in BranchSection were ever changed.

However, you said:

  One of the requirements of the given problem was that the staff entity hold information about the section and branch of each staff member

(Emphasis added)

Technically, Staff does not (directly) hold information about the branch; only about the section.

If you haven't already argued too much with the tutor, I would note the maintenance (and normalization) issues, and ask him why he wants you to start off with a denormalized design.

And I mean ask him, seriously. There may be a reason. At the conceptual level, you do need to tie a staff person to the branch they're located at; perhaps he believes that, for a high-level design, you need to leave that sort of thing in place, even if it's not literally going to be implemented that way in the actual database. Maybe they wanted everyone to include it so they can explain the importance of normalization (a bad reason to mark you down still, but if you've got tutors with an actual professor over them, they may be following instructions overly literally). Or, maybe they're training you for the real world; customers sometimes have specific requests that defy best DB practices, and that you can't talk them out of.

It's even possible that this implies a deeper issue with the design. Perhaps BranchSection should be just Section, and should apply to all instances of a given section, regardless of the branch. If so, then branchNo wouldn't belong, and each Staff row would need both branchNo and sectionNo. That's not how I read the question as presented, and it seems rather unlikely to me, but it's not impossible.

So, especially if you'll have further assignments based off this one, I'd say you have a valid reason to ask for further explanation, to valid your reading of the assignment.

If all else fails, and there is a class professor over the tutor, talk to them. They may have the latitude to accept a "more correct" design than expected, if that's what happened, and may have a better view on the class overall, and might have the explanation you need.


Investigate peak in MySQL throughput Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the MySQL throughput. However we were expecting to see a corresponding increase in number of MySQL queries which didn't happen:

We would like to find out what caused this peak in MySQL throughput. Here is the list of bin logs from the crash:

101M Apr 17 01:27 drupal_master-bin.001270
106M Apr 17 03:00 drupal_master-bin.001271
101M Apr 17 04:05 drupal_master-bin.001272
104M Apr 17 05:53 drupal_master-bin.001273
104M Apr 17 06:39 drupal_master-bin.001274
101M Apr 17 07:02 drupal_master-bin.001275
104M Apr 17 07:22 drupal_master-bin.001276  # 100M filled up in 1 min
106M Apr 17 07:23 drupal_master-bin.001277
101M Apr 17 07:33 drupal_master-bin.001278
101M Apr 17 07:43 drupal_master-bin.001279
104M Apr 17 07:46 drupal_master-bin.001280
102M Apr 17 08:29 drupal_master-bin.001281
102M Apr 17 08:46 drupal_master-bin.001282
105M Apr 17 08:54 drupal_master-bin.001283
 13M Apr 17 09:26 drupal_master-bin.001284  # crash of server around 09:50
# prior to crashing load went very high (we saw 45) and server was extremely slow (few min delay when typing in an SSH session)
101M Apr 17 10:54 drupal_master-bin.001285  # server up again, nothing wrong since then

I've been looking for tools to analyze those bin logs. So far I found:

binlog-analyze.pl: gives me an overview of the of queries processed, with breakdown on select, insert, update.. (FYI I replaced select into with select in the script as it seemed wrong).

$ mysqlbinlog /path/to/bin.log | binlog-analyze.pl -v

pt-query-digest: gives me statistics on query size (min, max, avg...). That utility has plenty of options but I don't know what to look for.

$ mysqlbinlog /path/to/bin.log | pt-query-digest

What we would like to find out is what queries generated the increase in MySQL output.

Can someone give directions as to how reviewing the MySQL bin logs in order to identify queries generating a sudden increase in MySQL throughput?

Anaylzing binlogs may not give you an accurate picture because binlogs contains queries that were completed and are inserted in the binary logs like a FIFO queue.

What you really need to look for is what does the historgram of actively running queries looks like. In other words, you need to catch the processlist in the act of revealing what queries and what performance was like when the unique circumstances came into existence.

I highly recommend using the pt-query-digest but you need to use itt differently. Instaed of have the query digest process the binlog entries, have it process the processlist LIVE !!!

I wrote a past post (Nov 24, 2011) on how to use the pt-query-digest (My post uses mk-query-digest) as a replacement for the slow query log (I have posted the actual script I used plus how to read the query digest output) : MySQL general query log performance effects. My past post was based on a YouTube Video showing how to do it. I just simply emulated myself using mk-query-digest.


group by left(col, x) text column - possible to index? I'm looking for a possible index for the following:
select left(name,10), count(*)
from tab
group by left(name,10)

This is the table config:
create table tab(
id int(11),
name text,
primary key(id),
key name(name(10)))

The optimizer does not uses the index for the select. Any idea if that possible in MySQL?
In Oracle for example I can create index on functions, which is not supported in MySQL.

You may want to try something like this:
create table x
(
  y varchar (25),
  z varchar (10) generated always as (left(y, 19)) stored, -- also virtual
  key (z)
);

Or you could name the index as follows (see here):
CREATE INDEX x_z_ix ON x (z);

I find that giving explicit names to indexes is preferable - it gives more meaningful error messages.
Just because there is an index doesn't mean that the optimiser will actually use it! The table might be too small, too many nulls, skew in values &c.
I'm not exactly sure what your own create table syntax is actually doing - could you post the output of show create table tab\G.


SQL Server on ssd seems slow We recently moved SQL Server to SSD and the performance gain is not that great.

SQLIO shows enormous gains (15-100 times faster), getting sequential read/write speed up to 6GB/s.

But when we do a simple query:

select *
into table2
from table1

it gets roughly like 60MB/s. 2GB table takes 30 seconds to finish.

Is this normal? What speed is considered average for SSD for such a simple query?
Don't believe it's a RAM issue, 50GB of RAM..?

Problem solved, there were IO limits on that VM.

As for SQLIO, it was lieing on hyper-v:

https://social.technet.microsoft.com/Forums/azure/en-US/b8e1674d-f1c7-4e46-b1f1-09fb0d75d82e/strange-blazingfast-io-performance-inside-hyperv-vm?forum=winserverhyperv

https://serverfault.com/questions/287311/does-sqlio-lie-when-run-from-a-hyper-v-guest-on-a-vhd


Mysql auto insert record in primary table if not exist Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key?

For example, assume these tables:
 - user(id, name, age)
 - topic(id, name)
 - post(userId, topicId, text, createdAt, updatedAt)

Now i am pulling posts from some source and saving the records in the post table. But sometimes the data that is being returned contains a userId or a topicId that is not yet in my database. So everytime i would have to check if the user and topic records exist then save if not. Only then my post record would be valid and saved.

I want to be able to save the post even if its related user or topic does not exist, and add an empty row with the in these tables having the ids that have been stored in the post table.

Example:

Current User Table

+----+------+-----+
| id | name | age |
+----+------+-----+
| 15 | Paul | 26  |
+----+------+-----+
| 56 | John | 31  |
+----+------+-----+

current Topic Table

+----+----------+
| id | name     |
+----+----------+
| 5  | Business |
+----+----------+
| 12 | General  |
+----+----------+

current Post Table:

+--------+---------+----------------+-------------+-------------+
| userId | topicId | text           | createdAt   | updatedAt   |
+--------+---------+----------------+-------------+-------------+
| 15     | 12      | blah blah blah | *timestamp* | *timestamp* |
+--------+---------+----------------+-------------+-------------+
| 56     | 5       | lorem ipsum... | *timestamp* | *timestamp* |
+--------+---------+----------------+-------------+-------------+

So then i fetch post from some sources an get a new 1 This is a new topic posted by a user with id 72 in a topic with id 2. The source only returns the id, and to obtain the rest of the details of the user, i should make another request to their api.

Post Table after:

+--------+---------+---------------------+-------------+-------------+
| userId | topicId | text                | createdAt   | updatedAt   |
+--------+---------+---------------------+-------------+-------------+
| 15     | 12      | blah blah blah      | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+
| 56     | 5       | lorem ipsum...      | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+
| 72     | 2       | This is a new topic | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+

User Table After:

+----+------+-----+
| id | name | age |
+----+------+-----+
| 15 | Paul | 26  |
+----+------+-----+
| 56 | John | 31  |
+----+------+-----+
| 72 |      |     |
+----+------+-----+

Topic Table after

+----+------------+
| id | name       |
+----+------------+
| 2  |            |
+----+------------+
| 5  | Business   |
+----+------------+
| 12 | General    |
+----+------------+

So now that i have this, i can make my request to their api and look for data for user with id 72 and data for topic with id 2.

You cannot get it automated via FOREIGN KEY.

Plan A:  Use a TRIGGER.

Plan B:  Use INSERT .. ON DUPLICATE KEY UPDATE ...


How to view the table structure in workbench in mysql database without quotes I need to view the structure(i.e table scripts without quotes) of tabes from mysql work bench

Thanks

You can get the DDL via the context menu for an object. So, right click on e.g. your table and let it send the DDL to your current editor or to the clipboard:


Can I construct a trigger that deletes existing rows before inserting new ones in SQLite, which lacks FOR EACH STATEMENT? Here’s my SQLite schema:

CREATE TABLE IF NOT EXISTS deps (
    target TEXT NOT NULL,
    user TEXT NOT NULL,
    dependency TEXT NOT NULL);

CREATE INDEX IF NOT EXISTS targetUser ON deps (target, user);

In words, the deps table (a dependency tree) is intended to have multiple
rows for each (target, user) pair—all rows with the same (target, user) pair are dependencies of target for some user.

In my application, a user only batch-updates the dependencies of a target. By “batch” I mean that I would (1) DELETE all rows containing the (target, user) and then (2) INSERT the new rows fresh.

I would like a trigger to combine these two steps in the SQLite database, so that before I insert new rows for a given (target, user), the database deletes all old rows for this pair. I came up with this:

CREATE TRIGGER IF NOT EXISTS clearOldDeps BEFORE INSERT ON deps
    BEGIN
        DELETE FROM deps WHERE target = NEW.target AND user = NEW.user;
    END

but alas! SQLite triggers are all FOR EACH ROW, and it lacks FOR EACH STATEMENT, so if I use this trigger above and insert some rows for a given (target, user) pair, each row inserted deletes the rows that were inserted just before!

Observe:

sqlite> INSERT INTO deps VALUES ("t1", "u2", "e1"), ("t1", "u2", "e2"),("t1", "u2", "e3");
sqlite> select * from deps;
t1|u2|e3

I went to add three rows, and wanted any pre-existing rows for ("t1", "u2") target–user pair to be deleted, but I only got the last row inserted, because it deleted all previous ones.

If SQLite had FOR EACH STATEMENT triggers, I suspect this trigger could be made to work using that, but since it doesn’t, is my only recourse to first DELETE then INSERT? Is there any more concise way?

Remember that this is a lite version of SQL and does not support all operations. See the following link:

https://www.sqlite.org/lang_createtrigger.html

SQLite supports a BEFORE TRIGGER which can delete rows before further action is taken. The link also provides some cautions about how that works:

  If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
  that was to have been updated or deleted, then the result of the
  subsequent update or delete operation is undefined. Furthermore, if a
  BEFORE trigger modifies or deletes a row, then it is undefined whether
  or not AFTER triggers that would have otherwise run on those rows will
  in fact run.

See the link to learn other details about the SQLite triggers.

Of course you could write your SQLite code to delete the rows that you no longer need in your database before inserting new rows into your database. I would prefer that approach over a trigger since your are just getting rid of unwanted data.


Estimating tablespace size I'm trying to estimate the size of a tablespace using these steps: Space Estimations for Schema Objects

and I was little confused about some of the steps. In step 3 for calculating the rowsize I first need to calculate the column size and then the sum of all column sizes in the table, I was wondering how I get the column size value without having actually created the table yet do I have to create sample data and then use vsize()? or am I missunderstanding what the column size is..

Also these steps in the link are for estimating the size of a table (?) so if I wanted to estimate the size of the tablespace would I just take the sum of all the estimated table sizes?

Are you using Oracle 7? If not, please make sure to always use current versions of documentation. Oracle is already on 12C, and while some concepts still the same, a huge part of features has changed.

You actually won't get column size. This is why it is only an "estimate". You need to have an idea of what you are going to need, and then you can use the package dbms_space with the create_table_cost procedure to get an idea of space required.

For example:

I need to create a table with 3 columns (val1, val2, val3), and I know each one is going to take up 4bytes (3 of literals, like abc, and the leading byte that varchar2 takes). I also estimate that this table will have approximately 1000 rows. Now just think about cartesian graph:

row_count
^
|
|
|
|-----------> avg_row_len

and use the following to estimate the size:

 set serveroutputon
    DECLARE
      estimated_used_size   NUMBER;
      allocated  NUMBER;
    BEGIN
        DBMS_SPACE.create_table_cost (
        tablespace_name => 'USERS',
        avg_row_size    => 12,
        row_count       => 1000,
        pct_free        => 10,
        used_bytes      => estimated_used_size,
        alloc_bytes     => allocated);

      DBMS_OUTPUT.put_line ('the table (TS=USERS): will use =' || estimated_used_size ||' bytes  to be allocated =' ||  allocated);
end;
/

Regarding your last question, to estimate the size of an existing table, you can check the view dba_segments (also to query index and other objects size).

Example:

SQL> SELECT bytes/1024/1024 "Size in MB" FROM dba_segments WHERE segment_name = 'TABLE_NAME_HERE' AND segment_type = 'TABLE';


Conflicting 'LIKE' conditions in MySQL CASE statement I have two conditions that are causing the CASE statement not to work as intended. I've tried changing which condition comes first, but it still only proceeds with one of them. Any suggestions?

CASE statement that returns only the value before the first '(' and replaces all instances of 'Metallic' with 'M':

CASE
WHEN `order_option`.`value` LIKE '%(%)%' THEN SUBSTRING_INDEX(`order_option`.`value`,' (', 1)
WHEN `order_option`.`value` LIKE '%Metallic%' THEN REPLACE (`order_option`.`value`,'Metallic','M')
ELSE `order_option`.`value`
END AS `Option`,

Current data

`value`
--------
Red Metallic (999)
Blue (745)

Expected results

`Option`
--------
Red M
Blue

Thank you in advance!

You have no chance,  besides change the metallooc text first  and also do a substing on it

Schema (MySQL v8.0)

CREATE TABLE Option1 (
  `Text1` VARCHAR(18),
  `num` INTEGER
);

INSERT INTO Option1
  (`Text1`, `num`)
VALUES
  ('Red Metallic (999)', '0'),
  ('Blue (745)', '0');

Query #1

SELECT
CASE
WHEN `text1` LIKE '%Metallic%' THEN SUBSTRING_INDEX(REPLACE (`text1`,'Metallic','M'),' (', 1)
WHEN `text1` LIKE '%(%)%' THEN SUBSTRING_INDEX(`text1`,' (', 1)

ELSE `text1`
END AS `Option1`
FROM Option1;

| Option1 |
| ------- |
| Red M   |
| Blue    |

View on DB Fiddle


Problem translating MySQL to PostgreSQL We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work.

In the below, all I did this far is replace IFNULL() with COALESCE() and UNIX_TIMESTAMP() with TO_TIMESTAMP()::TIMESTAMP

I don't know what the error means. The query worked just fine in MySQL, and I'm not sure, after looking at pages discussing differences between it and PostgreSQL, what the problem is (I also don't know what to replace the SEC_TO_TIME line with, but removing that line doesn't fix the error).

SELECT IFNULL(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
    (CASE
      WHEN t.id > 0 THEN (CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END)
      ELSE 'font-weight: bold'
    END) AS __style__,
    t.id AS ticket,
    IF(t.id > 0, t.summary, 'Total') AS summary,
    SEC_TO_TIME(SUM(IF(w.endtime, w.endtime, TO_TIMESTAMP(NOW())::TIMESTAMP) - w.starttime)) AS total
  FROM ticket t
  INNER JOIN work_log w
  WHERE t.id = w.ticket
  GROUP BY t.component, t.id, t.summary, t.status
  WITH ROLLUP HAVING IFNULL(id, -1) = -1 OR (t.summary IS NOT NULL AND t.status IS NOT NULL);

ERROR:  syntax error at or near "WHERE"
LINE 11:  WHERE t.id = w.ticket

Edit: I tried changing the WHERE to ON, and that seemed to fix that error; however, I don't know if this is the correct solution. Also, now I get an error with the "WITH ROLLUP HAVING" part. What's the way to do it in PostgreSQL?

Also, I don't know what to replace SEC_TO_TIME with above, or the SUM / IF combo (as IF() is MySQL only--if I swap to CASE, do I need to test separately for 0 and null?). Using TO_CHAR as per http://www.verious.com/qa/sec-to-time-function-in-postgre-sql/ doesn't work because the minutes are always zero with the code given there (i.e. 90 seconds ends up as 00:00:30...)

Edit 2: What I'm trying to end up with, which was the case with MySQL:

Edit 3: Thanks to a_horse_with_no_name and efesar, I've ended up with the following working query:

WITH base AS (
  SELECT t.component AS component, t.status AS status, t.id AS ticket, t.summary AS summary,
      SUM((CASE WHEN w.endtime > 0 THEN w.endtime ELSE CAST(EXTRACT(EPOCH FROM current_timestamp) AS bigint) END) - w.starttime) * INTERVAL '1 second' AS total
    FROM ticket t
    INNER JOIN work_log w
    ON t.id = w.ticket
    GROUP BY t.component, t.id, t.summary, t.status
)
SELECT CONCAT('Component ', component) AS __group__,
    (CASE status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END) AS __style__,
    ticket, summary, total
  FROM base
UNION ALL
SELECT CONCAT('Component ', component) AS __group__,
  'font-weight: bold' as __style__, null, 'Total', SUM(total)
  FROM base
  GROUP BY __group__
UNION ALL
SELECT 'All Components' AS __group__, 'font-weight: bold' as __style__, null, 'Total', SUM(total)
  FROM base
ORDER BY __group__, ticket

Something like this:

SELECT coalesce(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
       CASE
          WHEN t.id > 0 THEN CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END
          ELSE 'font-weight: bold'
        END AS __style__,
        t.id AS ticket,
        case when t.id > 0 then t.summary else 'Total' end AS summary,
        SUM( coalesce(w.endtime, current_timestamp) - w.starttime) AS total
FROM ticket t
  INNER JOIN work_log w ON t.id = w.ticket
GROUP BY t.component, t.id, t.summary, t.status

The sum() will sum up the difference in milliseconds between endtime and starttime not sure what the intention behind that is.

I'm not entirely sure what the rollup does, but something along the lines:

with base_data as (
   ... the above query goes here ...
)
select __group__, ticket, __style__, summary, total
from base_data
union all
select null, null, null, null, sum(total)
from base_data
group by __group__, ticket, __style__, summary

might get you started.


SQL INSERT INTO SELECT query for updating on same table with some modification I want to update this table
Table name: Student

Name      Department

Alex         CSE
Bob          EEE
Briyan       ME

by copying all rows with some modification like this table:
Table name: Student

Name      Department

Alex         CSE
Bob          EEE
Briyan       ME
Mr. Alex     SCIENCE(CSE)
Mr. Bob      SCIENCE(EEE)
Mr. Briyan   SCIENCE(ME)

What should be the SQL query ... can you help please?
I have tried this far:
INSERT INTO
    Student (Name, Department)
SELECT Name,Department FROM Student;

So you're close you just need to add the data modifications you want to each field in your SELECT list like so:
INSERT INTO
    Student (Name, Department)
SELECT CONCAT('Mr. ', Name), CONCAT('SCIENCE(', Department, ')') FROM Student;

You can use the CONCAT() function to concatenate strings in MySQL


Does AWS RDS support MEMORY storage engine? Title says it all.  Strangely can't find a result on this.

This answer applies to AWS Aurora not straight MySQL RDS.

Just because SHOW ENGINES says the database has the MEMORY storage engine doesn't mean you can actually use it. We have found that in AWS Aurora the SHOW ENGINES; call will tell you that the MEMORY engine is supported. However, if you try to change the engine to MEMORY or create a non-temporary table with MEMORY engine you'll get an InnoDB engine, because Aurora only supports InnoDB. When you run the engine change you'll get a warning that the InnoDB engine has been used.

Here's what I got from AWS support:

  Aurora is designed to provide support for only InnoDB storage engine for regular tables. Engines other than InnoDB are only supported for session-level temporary tables. With default configuration, when you try to create a table using a storage engine other than InnoDB, Aurora will implicitly create the table as InnoDB. Session level temporary tables are not persistent and they will be dropped once the session is completed.


The conversion of a varchar data type to a datetime data type resulted in an out-of-range value I have two users in my database. When I execute following query under user ONE it successfully executes; however when I execute it under user TWO it generates a error message:

Query:

select cast ('2015-04-28 16:00:00.000' as datetime)

Error Msg:

  Msg 242, Level 16, State 3, Line 11
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

User ONE has English as default language and its a simple Server login
User TWO has British English as default language and is also used as Application User

DB Properties

Since in my real query columns are in Where clause to get result set for a specified date range:

    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) >= CAST(CONVERT(VARCHAR(10), StartDate, 101) AS DATETIME)
    AND
    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) <= CAST(CONVERT(VARCHAR(10), EndDate, 101) AS DATETIME)

I verified columns Createdate, StartDate and EndDate have a DateTime datatype in database.

What I have tried so far:

Removed casting cast(columnname as datetime) and its working fine.

CONVERT(VARCHAR(10), Createdate, 101)  >= CONVERT(VARCHAR(10), StartDate, 101)
AND
CONVERT(VARCHAR(10), Createdate, 101) <= CONVERT(VARCHAR(10), EndDate, 101)

This is not the solution there are more than 3 thousand SPs and we can't change all in a single click? is there any other solution?
I changed language of user 'Two' to English by following steps Security->logins->User'two'->Default Language to verify is this the only reason but query still failed on Casting

Any idea what settings are we missing? Do I have to verify date format for both users?

If removal of casting or use of T is the only solution then it require time for modifications and then QA, do we have any temporary solution so nothing effect with our deadline and we have enough time to change SPs according to best practices?

The condition that is responsible for the error:
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) >=
CAST(CONVERT(VARCHAR(10), STARTDATE, 101) AS DATETIME)
AND
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) <=
CAST(CONVERT(VARCHAR(10), ENDDATE, 101) AS DATETIME)

is trying to do this comparison:
CAST(createdate AS DATE) >= CAST(STARTDATE AS DATE)
AND
CAST(createdate AS DATE) <= CAST(ENDDATE AS DATE)

but is using the 101 style code (US, mm/dd/yyyy) to first convert the datetime to varchar(10), effectively chopping off the time part and then converting back to datetime - but without explicitly specifying* a style code for the second cast.
Frankly, I'm surprised that it worked at all. It's quite possible that the various queries and procedures that use such conversions are producing false results, depending on server or client settings. (i.e. converting '2015-12-08 16:00:00' (2015-Dec-08) to '12/08/2015' and then to '2015-08-12 00:00:00' (2015-Aug-12)).
What you can do is to change these conversions to not depend on the client settings. The easiest would be to remove the second cast. But that would also require that  you change the 1st cast to use a sane format (and the responding style code). "Sane" meaning a format that preserves the order of dates, i.e. style codes 102, 112, 120, 126. (Do not use styles 101 or 103 that do not preserve order. That would make the issue much worse.) Examples of codes to use:
-- style code 120 (ODBC canonical):   yyyy-mm-dd hh:mi:ss(24h)
CONVERT(VARCHAR(10), createdate, 120) >=
CONVERT(VARCHAR(10), STARTDATE,  120)
AND
CONVERT(VARCHAR(10), createdate, 120) <=
CONVERT(VARCHAR(10), ENDDATE,    120)

-- style code 126 (ISO8601):   yyyy-mm-ddThh:mi:ss.mmm (no spaces)
CONVERT(VARCHAR(10), createdate, 126) >=
CONVERT(VARCHAR(10), STARTDATE,  126)
AND
CONVERT(VARCHAR(10), createdate, 126) <=
CONVERT(VARCHAR(10), ENDDATE,    126)

-- style code 112 (ISO):   yyyymmdd
CONVERT(VARCHAR(8), createdate, 112) >=
CONVERT(VARCHAR(8), STARTDATE,  112)
AND
CONVERT(VARCHAR(8), createdate, 112) <=
CONVERT(VARCHAR(8), ENDDATE,    112)

But the best in my opinion is not to convert to varchar at all and remove the time part using another way. If you are on 2005 and no DATE type is available, you can use this method to strip the time from a DATETIME:
DATEADD(day, DATEDIFF(day, 0, createdate), 0)
  >= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0)
AND
DATEADD(day, DATEDIFF(day, 0, createdate), 0)
  <= DATEADD(day, DATEDIFF(day, 0, ENDDATE), 0)

or simplified to (possibly use indexes):
createdate >= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0)
AND
DATEADD(day, DATEDIFF(day, 0, createdate), 0) <= ENDDATE

And finally the best  (and assuming all these columns are DATETIME) and that your version of SQL Server is 2008 or later (and so has the DATE type) is to change these conversions to cast to DATE so they are self-explanatory of the intention, to either:
CAST(createdate AS DATE) >= CAST(STARTDATE AS DATE)
AND
CAST(createdate AS DATE) <= CAST(ENDDATE AS DATE)

or even better, simplify to (possibly use indexes):
createdate >= CAST(STARTDATE AS DATE)
AND
CAST(createdate AS DATE) <= ENDDATE

What happens when a char or varchar value is implicitly converted to a date/time type, is nicely explained by Aaron Bertrand in the blog post Bad habits to kick : mis-handling date / range queries:

.., the only truly safe formats for date/time literals in SQL Server, at least for DATETIME and SMALLDATETIME, are:

YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

About the question, why a user gets these errors while another user doesn't, this (thank you Paul White) technet page: Using Date and Time Data, explains:

Using Date and Time Formats
String literal formats affect the presentation of data in applications to users but not the underlying integer storage format in SQL Server. However, SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.


What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow? Let's say we have a transaction running 50,000 inserts which dirties approximately 600 MB of the database. If the buffer pool size is 128 MB, there will be dirty pages in the buffer pool while the transaction is running, and these will need to be evicted to make space. My question is, what does MySQL/InnoDB do with these dirty uncommitted pages?

I just encountered a question 5 days ago : InnoDB errors and crashes with MyISAM database?

When troubleshooting the issue in a live chat session, I discovered that dirty pages from  Temporary InnoDB Tables were occupying the InnoDB Buffer Pool. The file ibtmp was 482MB with 75% of the buffer pool being dirty since innodb_max_dirty_pages_pct was 75. The Buffer Pool was only 128M. So, then, what happens ?

The Buffer Pool has to flush very frequently when the Buffer Pool is too small (which is what I encountered with a virtually empty database).

If you run SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';, you will see something like this:

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 190925  9:50:58 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 247                                              |
| Innodb_buffer_pool_bytes_data         | 4046848                                          |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 36                                               |
| Innodb_buffer_pool_pages_free         | 7945                                             |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 8192                                             |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 1090                                             |
| Innodb_buffer_pool_reads              | 214                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 325                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

mysql>

You can monitor Innodb_buffer_pool_pages_dirty with

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_dirty%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql>

This number may fluctuate with pages being flushed and pages coming in, but this will definitely bottlenck InnoDB. Your solution is just to increase the Buffer Pool size.

I talked about other ill effects before (See my post what happens when InnoDB logspace is too small)


how to create double entry book keeping for member purchases, fees, charges My application records member fees, purchases, overdue charges and payments.

I have read many articles that suggest the use of double entry accounting.

Currently here's my table and I want to convert it to double entry or better database schema.

MembersAccount(id, name, address)
Purchases(member_id, amount, product_id, date)
Fees(member_id, amount, fee_type, date)

PurchasePayments(purchase_id, amount, date)
FeePayments(fee_id, amount, date)

Any suggestion is much appreciated.

Double entry accounting is a specialist skill by itself. To do this properly you'll likely need the assistance of someone trained in accountancy.

The basic principal is that every bit of money is associated with an account. Choosing what accounts and how they relate to each other is where the expertise lies. Money only ever moves between accounts via matching transactions - one to debit the source and the other to credit the destination.


Compare Strings After Character I need to check email addresses against an internal known bad domain database.  My internal database has domains in the form of myfreepaysite.com just no @ sign.  I have been using this syntax to check, but it locks up my SSMS for roughly 90 seconds then unfreezes.  Is there a better way to do such?

Create Table #Testing
(
  emaildomain varchar(Max)
)
INSERT INTO #Testing VALUES ('myfreepaysite.com')

Create Table #DataToCheck
(
  fullemail varchar(max)
)
Insert Into #DataToCheck VALUES('rr1234@myfreepaysite.com')

Select Top 1
fullemail
,SUBSTRING(fullemail, CHARINDEX('@', fullemail) + 1, LEN(fullemail))
FROM #DataToCheck
WHERE SUBSTRING(fullemail, CHARINDEX('@', fullemail) + 1, LEN(fullemail)) IN (Select emaildomain FROM #Testing)

Something that I've done in the past for searching emails by domain name is to reverse the email address and the domain. Then I can use the LIKE operator with a wildcard and the resulting query should be sargable. You can read this Brent Ozar post about why this is helpful. The short version is reversing our email address and search term will allow us to use indexes. I don't think you'll need to do this based on the table sizes you mentioned in the comments but just in case you're curious you can create indexes on temp tables.

Disregarding indexes on the temp tables reversing things would make your original query look like this:

Create Table #Testing
(
  emaildomain varchar(100) -- You can't index max and it isn't needed
);
INSERT INTO #Testing
VALUES (REVERSE('@myfreepaysite.com')); -- Reversed & prepended the @ sign
                                        -- to ensure full domain matching

Create Table #DataToCheck
(
  fullemail varchar(200) -- You can't index max and it isn't needed
);
Insert Into #DataToCheck
VALUES(REVERSE('rr1234@myfreepaysite.com')); -- reversed

Select Top 1
  REVERSE(d.fullemail) -- Changed
  ,REVERSE(t.emaildomain) --Changed
FROM #DataToCheck AS d
  INNER JOIN #Testing AS t ON d.fullemail LIKE t.emaildomain + '%'; -- Changed

That should be easier to read and perform better as well. Also note that I reduced the size of your EmailDomain and FullEmail columns. I did this because you can't index a VARCHAR(MAX) but more importantly you should scope your columns to the data sizes and types they'll actually contain.


Slow query that times-out my application, any advice? I have a query that it seems taking about 50 seconds to execute from SQL Management Studio, but when a user tries to run it as a report from a front end application (that was built in VB), the application seems to through an error of "timing-out expired (after 20 seconds),The timeout period elapsed prior to completion of the operation or the server is not responding"
So it's not waiting for SQL to finish executing the stored procedure.

It was running fine up until today, where one of the developers had to update (several 100's) rows for a column in one of the joined tables for that stored procedure, could that affected indexes, statistics..? I'm not sure where to start checking, please advice.

This report only acting up for one division (Central America), so when I log in as a West division user or any other division, it executes within 2 seconds for the same amount of rows (about 200 rows)...but I remember that it ran successfully couple of times this morning when I log back in from Central America. I checked it back again after peak hours, errors still the same!

I'm using SQL Server 2008 r2, Standard edition, not using any tools unfortunately.

The most likely case is either parameter sniffing or, less likely, bad statistics, with a very unlikely side of index split pages (thousands of rows is pretty small for that).
To prove parameter sniffing:

Turn on profiler or a trace for your particular Session_ID; watch Reads, Writes, CPU, Duration for events SQL:BatchCompleted and RPC:Completed

Invalidate the query cache for your particular query, which you can do by either DBCC FREEPROCCACHE  sql_handle
a. Note that if you do DBCC FREEPROCCACHE without any arguments, it frees the entire procedure cache, which has serious though short lived bad performance impacts on production boxes, since EVERY query plan now needs to be rebuilt.

Run your query for the most common set of parameters

Run your query for the most common set of parameters (again; ignore the results from #2, since they may include caching data)

Run your query for the least common, most unusual (index statistics wise) set of parameters.

Run your query for the least common, most unusual (index statistics wise) set of parameters (again; ignore the results from #4, since they may include caching data)

Do steps 1-5 again, EXCEPT do 1,2,5,6,3,4 - i.e. do the least common first, and the most common second

If your results on the second run of each set are significantly different on reads, writes, and/or CPU, you've proved parameter sniffing!  If not, you may still have parameter sniffing issues on other combinations, or you may not.

If it takes arguments that vary from division to division, it's probably parameter sniffing.  Read Erland Sommarskog's article Dynamic Search Conditions in T‑SQL which covers several ways of handling the various execution plans required.  WITH RECOMPILE is the quick band-aid, multiple stored procedures is the cadillac solution, and very careful dynamic SQL is another option (NEVER concatenating parameters into the SQL statement, only using them as actual parameters and as part of IF statements that put blocks of code in).
You can ALTER INDEX ALL on  REBUILD and it'll also update statistics, and see if that makes a difference.  Use ONLINE=ON if you're on Enterprise edition and need to, otherwise it's going to lock those tables.
Oh - make sure all tables have high cardinality clustered indexes first!


Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items? Let's say I have some table A which have json-field items:

A:
 foo: 'foo'
 items: [
  {a: 1, b:2},
  {a: 3, b:4},
  ...
 ]

There could be couple of thousands of items in one A object.

Is there any sense to introduce another table Item instead of having json field?

Item:
 a: 1
 b: 2

Both methods allow me to query stuff that I need, but it's much easier to work with separate table and it also provide out of the box validation for data.
My only concern is that it might be much slower than dealing with all items that already stored in one place.

Should I worry about performance?

You will need to measure the two ways against your performance requirements.

That said, in the world of RDBMS (which Postgresql is), generally it's better to keep tables rather than JSON/XML/Blobs etc.

The databases are optimised and designed for relational data and while it's true they have supporting features for JSON/XML etc, often these are not as mature, performant or generally as well thought out as the relational side.

Additionally there are more third party data analysis, reporting, ETL, ORM systems for relational than JSON.

Any or all of those statements may well become more or less true over time so you really need to decide for yourself as there's not generally a right way for all cases.

Me personally?  If it's data in a known, structured format and your DB and requirements tend towards relational then go relational and get rid of your JSON storage completely.


Is it ok to let users add new columns and tables to your database? I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can then go in and, through an interface, add entries to the database. The point is to create a management system for information within the company. As in, the administrator could create a "Employ" database to store workers with the correct columns and such.

I have it in my head that I should never let users create new tables (which is what a "database" would be) or even columns. I created two tables to store the information that defines what a database/table should be from the users perspective, and two more tables to store the entries and data for those entries.

I feel a bit like I'm reinventing the wheel. Is it ok just to let users add new tables.

Database design is both an art and a science. End users usually understand neither the art nor the science.  I don't let end users add tables or alter tables; I've never worked anywhere that allowed anything like that.

End users will almost invariably build things that look like either Word documents or Excel spreadsheets. They will only rarely build things that look like databases.

  The point is to create a management system for information within the
  company.

That's what a database is for. If your database is basically a table, as you seem to be saying, I think your users would be better off with a database.

As a database designer, I find these features to be indispensable. I'll give you the SQL keywords and concepts; you can look them up with Google if you like.

CHECK constraints
GRANT and REVOKE
Updatable views
Materialized views
Stored procedures
Foreign key constraints
"Exotic" indexes (function-based indexes, partial indexes, clustered indexes)
PRIMARY KEY and UNIQUE constraints

If you're not going to provide similar features, you should think hard about what might happen. If you don't provide something similar to GRANT and REVOKE, somebody will drop a table or delete rows they shouldn't touch sooner or later.

If you are going to provide similar features, you're reinventing the wheel.