Ora - 00088 error: command cannot be executed by shared server I get this error from the database when I run the query:

select status, error from v$archive_dest where dest_id=2;

It's a dataguard environment. I have never seen this problem before.

I'm not quite sure what this means or how it can be fixed.

What is generally the cause of this error?

Try adding (SERVICE=Dedicated) to your TNS entry on the client computer that you are using and then run your query again.

There could be other causes but this is the most common:  you are trying to access data using a shared connection when a dedicated connection is required.

Edit: Are there any other log or trace file entries when this error happens?


How to use msdb.dbo.backupset and dbatools to gather all of the historical backup size information for all instances in an environment I have too many servers to manage to want to do this manually on each server so I'd like to somehow automate this using the dbatools module and the below query for gathering the backupset information.
  WITH BackupsSize AS(
  SELECT TOP 1000
         rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
    , [Year]  = DATEPART(year,[backup_start_date])
    , [Month] = DATEPART(month,[backup_start_date])
    , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
    , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM
    msdb.dbo.backupset
WHERE
    [database_name] = N'A1000370D1'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY
    [database_name]
    , DATEPART(yyyy,[backup_start_date])
    , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month])
--SECTION 1 END

--SECTION 2 BEGIN
SELECT
   b.Year,
   b.Month,
   b.[Backup Size GB],
   0 AS deltaNormal,
   b.[Compressed Backup Size GB],
   0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT
   b.Year,
   b.Month,
   b.[Backup Size GB],
   b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
   b.[Compressed Backup Size GB],
   b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
   SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
   FROM BackupsSize bs
   WHERE bs.rn = b.rn - 1
) AS d

I have a list of servers sitting in table on a db and plan to load a $sqlinstance variable with my list of instances using invoke-dbaquery.
What I'm not sure about and need help with is how to use the above SQL script to gather the historical backup data for all databases on all servers in one go. How can I loop through each database while also looping through my list of instances?

You can capture this all with Get-DbaDbBackupHistory from dbatools, no loops required (it loops internally).
Get-DbaDbBackupHistory -SqlInstance $instancelist -Type Full -Since (Get-Date).AddMonths(-13) -ExcludeDatabase master,model,msdb |`
 Select-Object SQLInstance,Database,Start,Totalsize,CompressedBackupSize |`
 Sort-Object -Property SQLInstance,Database,Start;

You may want to delve into the Totalsize and CompressedBackupSize properties to get the raw number of MB or GB  without formatting.
Get-DbaDbBackupHistory -SqlInstance $instancelist -Type Full -Since (Get-Date).AddMonths(-13) -ExcludeDatabase master,model,msdb |`
 Select-Object SQLInstance,Database,Start,@{n='TotalSizeMB';e={$_.TotalSize.MegaByte}},@{n='CompressedBackupSizeMB';e={$_.CompressedBackupSize.MegaByte}}|`
 Sort-Object -Property SQLInstance,Database,Start;

You should be able to write this output to a table with Write-DbaDbTableData


How to distinguish the current operation is done from system or from sql server management studio We want to record who insert or update a table, so we add "CreatedBy" and "ModifiedBy" columns.

When the data is changed through the application, we can easily get the person. But when someone uses SQL Server Management Studio, how can I get the right person who operated on it?

Can I use trigger to catch it?

In most cases, you should be able to use some of the built-in functions:

SELECT @ModifyUser = SUSER_SNAME();

SELECT @ModifyUser = ORIGINAL_LOGIN();

This will work if they are Management Studio on their own machine and running queries remotely. If they are all using the same SQL authentication login, then in that case you should be able to tell them apart by grabbing the host name or IP address (then you'll have to figure it out based on that):

SELECT c.client_net_address, s.[host_name]
  FROM sys.dm_exec_connections AS c
  INNER JOIN sys.dm_exec_sessions AS s
  ON c.session_id = s.session_id
  WHERE s.session_id = @@SPID
  AND c.session_id = @@SPID;

In some cases, you're not going to be able to tell who it is. For example, if you let everyone log onto a common machine, access the instance through a local copy of SSMS, and they all use the same SQL authentication login, they will all look the same. There is no magic that will give SQL Server information it doesn't have. If auditing is important to you, there's a simple solution: stop letting them do that.


How to see percentage of existing values for all columns in mysql table? I have three large-ish tables (~20 columns each) and I want to know what percentage of rows have a value for each column.

For instance, this table:

ID   | TITLE               | SERVING     | NOTE
-------------------------------------------------------------------
6716 |  Yummy Molasses ... | 1 cookie    |
3765 |  Rosemary-Red Wi... |             |
5178 |  Stuffed Avocado... |             |
6025 |  Amazing Pea Sou... | about 1 cup |
4412 |  Overnight Oatme... | 1 cup       | Note: Steel-cut oats...

Might yield results similar to this:

ID   | TITLE               | SERVING     | NOTE
-------------------------------------------------------------------
100% | 100%                | 60%         | 20%

This is for an internal report, so the format isn't too important.  If I only have the count() instead of the percent, that's fine.  Having the column name would be nice, but not necessary.  Etc.

I'm not very familiar with queries that involve more meta questions like gathering table names or working over all the columns in a table, so I'm interested to see what people suggest.

I have a Dynamic SQL solution

PROPOSED QUERY

SET group_concat_max_len = 1048576;
SET @GivenDB = 'mydb';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
')*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

EXAMPLE

Let's use a sample table I made up for another question

mysql> use timyash
Database changed
mysql> show tables;
+-------------------+
| Tables_in_timyash |
+-------------------+
| mytable           |
+-------------------+
1 row in set (0.00 sec)

mysql> desc mytable;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| transmitted  | int(11) | NO   |     | NULL    |                |
| connect_time | int(11) | NO   |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY DISPLAYED

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT COUNT(id)*100/COUNT(1) id_pct,COUNT(transmitted)*100/COUNT(1) transmitted_pct,COUNT(connect_time)*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.02 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |         100.0000 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED WITH NULL DATA

Let's change two rows to have NULL column data

mysql> alter table mytable modify column connect_time INT NULL;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update mytable set connect_time = 1/0 WHERE id in (2,5);
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |         NULL |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |         NULL |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

Let's run it again

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |          71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2014-12-29 15:25 EST

In response to your last comment, use this version

SET group_concat_max_len = 1048576;
SET @GivenDB = 'timyash';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',
GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
SELECT @sql\G
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

You do not need to check the datatype with this one.

To show you that it works ...

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',
    -> GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
    -> ','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT SUM(IF(IFNULL(id,'')='',0,1))*100/COUNT(1) id_pct,SUM(IF(IFNULL(transmitted,'')='',0,1))*100/COUNT(1) transmitted_pct,SUM(IF(IFNULL(connect_time,'')='',0,1))*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |          71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>


Slower queries after upgrade from mysql-5.5 to mysql-5.6 There is lots written about how MySql 5.6 is faster than MySql 5.5 (e.g. http://dev.mysql.com/tech-resources/articles/mysql-5.6.html) but we upgraded our Amazon RDS instance (using the new upgrade procedure) and found that the average time spent in the database per page view got worse, from 30ms to 50ms - a significant slow down.

This is a chart (from newrelic) of the Time spent in the database per page view (approx 1000 queries per second):

The slow down seems to be on simple UPDATE statements. I wouldn't expect them to be a problem because the PK is used. This example shows a statement that took 6 seconds to execute but the explain plan is optimal (rows in table: 700k):

mysql> EXPLAIN UPDATE users SET type='dual', updated_at=NOW() where user_id=1234;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users   | range | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

updated_at=NOW() seems to be a common theme in the newly slow statements.

Is there any reasons UPDATEs are slower with MySQL 5.6? Does anyone have any clues about what I could check to debug this slow-down?

Other info: instance size: db.m2.4xlarge , query_cache is off. performance_schema is off. exact versions: was: 5.5.27, now: 5.6.16.

The problem was that our new RDS instance was being throttled on writeIOPS, we could see queued disk operations (DiskQueueDepth of 10 or greater) and we could see writeIOPS averaging 300, whereas Amazon only provide 100 reliable iOPS (source).

The solution was to fork out approx $200 per month for "provisioned iOPS". 1000 is the smallest you could buy (which easily covers us). After this change which took 5 hours to complete on 350G of storage (but only 30s of downtime with a multi-AZ instance) everything was fine and our MySQL 5.6 instance was running better than the original MySQL 5.5 instance like all the literature promises.

The problem was that the new RDS instance that we were given since migrating to 5.6 was just not as good as our original, which was just bad luck. We asked Amazon support "Q: Why would there be a difference in mysql5.5 than mysql5.6?"
From Amazon:

  Your previous underlying host may have been benefiting from unusually light use on the underlying volumes. You may have grown accustomed to very light use which allowed you to consume additional IOPs without accumulating as much DiskQueueDepth. However, when you moved to a new underlying host by upgrading you may now be experiencing more typical use of the underlying volumes. With typical use of the underlying volumes it's not recommended to consistently exceed the amount of reliable IOPs while expecting to maintain the lower DiskQueueDepth that you've grown accustomed to. However, there is no way to confirm or deny that this was the cause.


Getting Only What Changed from SQL Server 2005 We have a 17 store system which are on SQL Server 2005 Standard Edition and on Windows 2003 server. We currently have the following process:

At each store we have an SQL job which runs a procedure to populate 1 table. Procedure basically first Truncates the table and then does the Insert from the system. This has records from day our store was open till now, approximately 2million records in this table. There is no way to archive it.
We then have an SSIS package which gets this table data from store's back to Home Office every night. It runs for 4-7hours depending upon connectivity, which is slow most of the time.

We would like to do the following:

We would want to create a separate table at the store, which would record ONLY the changes occurred to table its populating every night (above Step-1)
This way, when SSIS package pull the data, it doesn't have to pull back 2million records, instead it can only pull back the changes

Due to SQL Server 2005 we cannot use MERGE command and as table gets Truncated and Inserted is not the good candiate for replication. How should we get ONLY change back to the home office?

Thank you,
Hiren Patel

I would change the process to be more like the following.  Not the best solution, but there aren't that many good options to begin with.

Import Data Process:
a) - Drop yesterday's table
b) - rename current table to *_yesterday
c) - Import from system to current

Create view in Store's SQL Server that will return only rows that are new/different between current and _yesterday's tables. Something like the below.

SELECT Current.*
FROM Current
LEFT OUTER JOIN Yesterday ON Yesterday.PK = Current.PK (or list each field out seperately)
WHERE Yesterday.PK IS NULL (so the new ones)
OR ISNULL(Yesterday.Field1, '') <> ISNULL(Current.Field1, '')
<repeat>

2) - Move data to HQ
a) - Use view created to just pull across only the new records (possibly into staging table).  Then use merge? (is HQ a higher version of SQL?) to push the newly updated data into HQ's copy of the data.


What does "('*') = '*' mean in T-SQL I have inherited maintenance of an in-house application along with a couple of views in our ERP system's database.

I have begun to suspect that my predecessor was drunk as a skunk when writing these views, or was making the queries deliberately complicated as a form job protection (if so, the joke is on me, because the guy quit).

One of the things that I find highly confusing about the views is that the WHERE-clause contains a sub-clause that looks a little bit like this:

SELECT ...
WHERE ...
OR ( (('*') IS NULL)
     AND Project.Invalid = 0
     AND ... )
OR ...

From my understanding, that ('*') IS NULL part should basically be a no-op. Yet, when I comment that line out, the number of rows returned by the query jumps from 460 to ~350,000.

I am fairly confused by this, I would have expected no change at all in the number of results.
In the same query, another branch of the WHERE-clause contained a similar sub-clause, ((-255) IS NULL), and when I commented that one out, the number of results stayed the same.

What is going on here? Is there something funky going on, or am I missing something obvious?

Thank you very, very much for any input!!!

('*') IS NULL evaluates to false. As a result the OR part also evaluates to false. We can use the simple rule:

(FALSE AND Something AND Something_Else AND ... )
= FALSE

Of course as a result, if you comment out (('*') IS NULL) the result changes.

Try commenting out the whole OR part:

SELECT ...
WHERE ...
-- OR ( (('*') IS NULL)
--      AND Project.Invalid = 0
--      AND ... )
OR ...

This should have no effect, due to the other rule:

(FALSE OR Something OR Something_Else OR ... )
=
(Something OR Something_Else OR ... )


Home sensors database - optimising model I am trying to develop a home automation/monitoring system.  This involves a bunch of sensors distributed around the house/garden/garage. I'd like to store the values for the sensor readings.  I am trying to make the database design flexible and general so that I don't have to change it around once it is set up.  However, I seem to have ended up with two possible options and I can't quite see which is the most optimal.  The tables that are common to both are:
tbl_region = id, region_name - (e.g 1, first)
tbl_room = id, region_id, room_name - (e.g 1, 1, lounge)
tbl_sensor = id, sensor_name - e.g (1, dht11)
tbl_measurement = id, measurement_name - e.g (1, temperature)

Since each sensor can have many measurement types (e.g temp, humidty, pressure) and a measurement can be done by many sensors (e.g dht11, bme280) there needs to be a junction table to define the many to many relationship
tbl_sensor_measurement = id, sensortype_id, measurementtype_id - (e.g 1, 1, 1) for a dht11 sensor's temperature measurement (it does other things too)

I can potentially have multiples of the same sensor in the same room so there is a many to many relationship between rooms and sensors. Here is where my two ideas diverge:

I could have a junction table between tbl_room and tbl_sensor_measurement:

tbl_room_sensor_measurement = id, room_id, sensor_measurement_id

this would give me mutliple rows for a sensor in a room (one for each type of measurement) and allow the same type of sensor to be housed in that room.  This would mean that my final data table would only have four columns
tbl_value = id, room_sensor_measurement_id, value, timestamp

I could have a junction table between tbl_room and tbl_sensor

tbl_room_sensor = id, room_id, sensor_id

this would give me one row for a sensor in a room and allow the same type of sensor to be housed in that room.  This would mean that my final data table would now have five columns
tbl_value = id, room_sensor_id, measurement_id, value, timestamp

I think that the model 2 is going to be easier to retrieve data later on (I can pick a sensor in a room and get all of the measurements easily.  I am new to rdb design and i'm not sure which to go for (or if there is an entirely different way altogether) and would appreciate any pointers.
Thank you for your help,
Martyn

Quick Note
You probably want to ensure that a measurement of 760 mm Mg is not accidentally identified as a Temperature measurement.  As such, you really want to keep the two values in different columns (with appropriate CHECK constraints).
Now What?

Temperature IS A measurement
Humidity IS A measurement
Pressure IS A measurement

There are a few ways to build "IS A" relationships in a database.
The method I am recommending has a one "master" measurement table and a "specific" table for each type of measurement.  You'll need to decide if you can group some measurements together; in which case, they'll go into the same "specifics" table.
-- pseudo code.  Actual would be RDBMS specific

create table measurements_meta (
  measurement_id  int primary key, -- "Specifics" tables get the same ID.  therefore, the same column name.
  sensor_id       int not null references sensors(id), -- I dropped the unnecessary TBL_ prefix
  room_id         int not null references rooms(id),
  time_of_measure datetime not null default now()
);

create table measurements_temp (
  -- this enforces the 1:0 or 1 relationship
  measurement_id int primary key references measurements_meta( measurement_id ),
  temperature    number not null check ( temperature between -40 and 150), -- F
);

create view temperature
as
select a.*, b.temperature
from measurements_meta a
  join measurements_temp b on a.measurement_id=b.measurement_id;

-- RDBMS Specific
create trigger temperature_tr
instead of insert or update or delete
on temperature
before each row
-- fill in code here to do the actual DML

grant select, insert, update, delete on temperature to my_app_user_role;

In this design, the data Model remains hidden from the application.  The application will View the data through the VIEW. Control of the data (DML) is done through simple CRUD operations on the VIEW or through APIs (stored PROCEDURES).  TL;DR - I suggest you use the MVC design pattern.
If you can take multiple (simultaneous) readings (eg 2 thermometers for 1 device), the individual readings would go into a child table under that "specifics" table.  The "specifics" table would record the average.  You would need to use an API (instead of CRUD on the VIEW) which can take in the required number of reading in one call (eg an array of temperatures).


Bus time schedule database design Hi I'm trying to figure out how to design a database for bus time schedule.

There are several lines, each line have two routes (forward and back), each route have several stations (stops) and each station may belong to several routes.

The main goal of the database is to easily select the next arrival time for a specific bus station with a specific line.

I googled and came up with next database design:

lines (id, name, ...)

routes (id, name, line_id, ...)

stations (id, location)

route_station (id, route_id, station_id)

times (id, station_id, line_id, time_forward, time_back)

So, for instance

times Table
id  station_id   line_id   time_forward  time_back
1       10          100         9:15         9:18
2       10          100         9:30         9:33
3       10          100         9:45         9:48
4       10          100         10:00        10:03
5       10          100         10:15        10:18
6       10          100         10:30        10:33
7       10          100         10:45        10:48

8       20          100         9:20         null
9       20          100         9:35         null
10      20          100         9:50         null
11      20          100         10:05        null
...
45      40          200         9:55         null
46      40          200         9:09         null
...

In the first 7 rows, the line with line_id=100 stops at different times in the station with station_id=10 (The null in time_back indicates that there is no backward route that passes by station_id=20).

My questions are:

I'm not sure if this design is right at all. I'm no looking for performance. I just want to know if I will have problems with this design in future time (For example: is easy to insert a new line station? Is easy to select the next arrival time for a bus station with specific line station?
How I deal with different time frequencies? Because is different for weekdays and weekends. If my design is ok, then the example above will work fine with arrival times in weekdays but no for weekends. I need a second table or add a column in times table indicating the type of the day?

Thanks.

P.S. I've taken a look at this design, but I think is too much for my system.

P.S.2 I've read about using NoSQL databases may fits better for this kind of systems; but my knowledge is limited to relational databases.

Since you really only have to keep track of times between stations on each route, you only need to keep the start time of each route, the rest can be calculated easily by storing the time delta value for each line stop (the time between the current station and the last station ), instead of keeping time data for each route_stop. You also need to maintain the order of the stops on the line, and if it's a circular route you simply put the stops twice into the chain with different ordering numbers ( so each stop on a circular route is inserted twice into the route_station relation table with different order number ).

You can of course keep the time for each stop if you want, but that seems redundant and makes it harder adding stops to a route, since you'd then have to recalculate all bus stop times, instead of simply adding the new stop and updating the delta time of the next stop.

I'd probably start with a data model like this ( but of course this needs to be expanded if you want to add information about the buses and drivers etc ) :

lines (id, name, ...)

routes (id, name, line_id, ...)

stops (id, location)

line_stops (id, line_id, stop_id, order, time_delta)

route_start_times (id, route_id, start_time)


What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each placed on separate disks. As an added rule-of-thumb from a colleague, the number of files should be equal to the number of CPU cores.

My understanding is that this setup is ideal for mechanical spinning disk drives where there is a performance boost by streaming data from multiple heads because a spinning disk drive is much slower than a solid-state drive. Is this understanding correct?

If yes, then my question is whether the cost-based optimizer takes into account the newer solid state drives? It seems like the performance bottlenecks from the spinning hard drives goes away when switching to the new solid-state drive. Our IT Operations groups has informed me that while I am currently allocated one virtual drive, the data is really stored on an ISCSI SAN, which has multiple solid-state drives.

This question is geared for trying to answer what is the most optimal setup for a large database of this magnitude:

Should I have a default secondary file group with just one file?
Should I have a default secondary file group with a number of files that equals the number of cores on the CPU?
Is there a performance boost by partitioning a database table when using solid state drives?

The current project I am working on requires a scaled database that will be a few terabytes in size that stores massive amounts of log data. A one week sample has approximately 150 million records and we need to store a rolling 3 years of logs. So, I am now looking at long running queries to find data. I have tuned the indexes to the point where nearly all of the work is attributed to non-clustered index seeks; the optimizer does not recommend adding a missing index.

Note

The licensing on Microsoft SQL Server is currently by CPU core. So, there is sensitivity to throwing more cores at the problem, especially if that will not boost performance.

Additionally, I am currently developing on SQL Server 2014, but will be migrating to SQL Server 2017 for both development and production.

Update 1

The project will be loading logs nightly where I expect few (perhaps none) updates or deletes because the logs are not expected to change at all - so they will not be re-loaded. Everything else will be reads for analytic purposes.

The PRIMARY file group for the system tables where the SECONDARY default file group is for everything else. The reason for doing this is explained by the link that is referenced at the bottom of this question.

Separate file groups will be created for the table partition. There are other tables within the database that are small enough where they will reside within the SECONDARY file group - I am only partitioning two tables where one exceeds 100 million records (partitioned by IDENTITY row number) and the other will go into the billions of records (partitioned by time [monthly]).

I plan on partitioning by month over 3 years. So, there will be 36 partitions. I will create file groups for each year and then place 12 files into the corresponding yearly file groups. The partition strategy is to reduce read times as there will be a lot of data scanning for analytic purposes. The annual file group strategy is strictly for ease of maintenance for the DBAs where they can remove a year's worth of data by removing a single file group.

Reference:

SQL Server Best Practices Setting a Default File Group

The cost-based optimizer does not (currently) change its estimated IO costs based on your hardware.

You say this question is geared towards answering what filegroup and partitioning strategy is best for your environment. In that case, who cares what the optimizer does? The question is what works well in your environment, and the real answer is to test it.


Tips about MySQL installation I'm starting to prepare my server for a classic configuration Apache PhP MySQL.

I installed Apache 2.2.24 without problems. (Output "It works").
I installed php 5.2.1 - win32 - installer. (launching info.php I see I see all configuration details).

Now I have to install MySQL.

Question:

Which version do you recommend suitable for Apache 2.2.24 and PHP 5.2.1.
You could attach the link?

P.s.: only classic configuration (no wamp or others).

Load them all at once - WAMP.  Their current version has 64-bits (or 32-bits) with

Apache 2.4.9
MySQL 5.6.17
PHP 5.5.12
PHPMyAdmin 4.1.14
SqlBuddy 1.3.3
XDebug 2.2.5

That should be easier and better than installing end-of-life software.


MySQL - Query records between yesterday and today but with a specific time I seem to be having a case of the Fridays. I am new to MySQL (few weeks now) and I just can't seem to get a report automated. I need to be able to query data from yesterday 8 am until today 8 am. I am going to schedule the report to run at 10 am. What is the best way to do this? I have been running it manually with BETWEEN but this will not do. I tried to use the interval feature but no luck. I think I am almost there.
One try:
purchase_date BETWEEN '2020-11-18 09:00:00' and (DATE(NOW()) - INTERVAL 16 hour)

Another try:
purchase_date BETWEEN BETWEEN (DATE(NOW()) - INTERVAL 40 hour) and (DATE(NOW()) - INTERVAL 16 hour)

If you can just point me to the function or best option in this case, I should be able to figure out the arguments. Ideas/suggestions/thoughts? Thank you!

USE CURDATE() and INTERVAL
SELECT CURDATE() - INTERVAL 1 DAY + INTERVAL 8 HOUR, CURDATE() + INTERVAL 8 HOUR

RESULT
# CURDATE() - INTERVAL 1 DAY + INTERVAL 8 HOUR  CURDATE() + INTERVAL 8 HOUR
2020-11-19 08:00:00                             2020-11-20 08:00:00


Convert list of dates in a date range in SQL Server Convert list of dates in a date range in SQL Server

For ex:

It's classic "islands" problem.

Here is "classic" solution:

declare @t table (dt date);

insert into @t(dt) values
  ('20180202'),
  ('20180203'),
  ('20180204'),
  ('20180205'),
  ('20180209'),
  ('20180212'),
  ('20180213');

with c as
(
  select dt, dateadd(day, -1 * dense_rank() over(order by dt), dt) as grp
  from @t
)
select min(dt) as start_range, max(dt) as end_range
from c
group by grp;


Script works but Im not sure this is the best format I have a survey table which contains mostly integer. I want to make it readable for end user so I use select statement as below:

SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two'
        WHEN 3 THEN 'Three' ELSE 'Your message.' END
        ,(SELECT CASE DoYouWishToP When 0 Then 'Yes'  When 1 Then 'No' END)
        ,(SELECT CASE Housingoptions When 'rb0' Then 'Lease'  When 'rb1' then 'im lazy' when 'rb2' Then 'Rental' END)
        from tblSurvey s

Above script does work. My question is why the 2nd and 3rd SELECT statments need to be inside the parenthesis. ( ). And do you see any issues in my script?

If I Use below I get Error:

 SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two'
            WHEN 3 THEN 'Three' ELSE 'Your message.' END
            ,SELECT CASE DoYouWishToP When 0 Then 'Yes'  When 1 Then 'No' END

(SELECT CASE DoYouWishToP When 0 Then 'Yes, urgently'  When 1 Then 'Not peeing' END)

is a Sub-select of the main select. It's simply a rule of SQL that it should be surrounded by parenthesis.
Ypercube is also right: if the columns, like "DoYouWishToP", are from the same table, you don't need the second and third SELECT. In that case it will work without the parenthesis. You normally only do a Sub-select using:

, (Select xxx **from** another_table where ...)


What is a basic model for making a database with users and groups? I'm trying to find the best way for a basic security system for a website.  I know I want users and groups.

I thought I'd have:

user_table
user_id
user_name
...

group_type
group_id
group_name
parent_id
...

group_table
id
user_id
group_id

First one is the user, second one is the group, and the third one is the intermediary table that connects the two.  One user has many groups.

Does this sound ok?

The traditional way to model this is using a pattern called Role-Based Security.

The idea is not just to have groups of users, but also groups of permissions. Here is how the pattern looks:

Note that you want to avoid reserved words for table names, so don't name your tables exactly as shown in the diagram.

The way it works is that your groups or Roles have not only a list of users assigned to them but also a list of permissions assigned to them.  This allows you to table-drive both who can do what, but also what it is they can do, if you follow me.


Why does imported txt table consume so much space I have a .txt file with storage size 1253 MB. After importing it to SQL Server 2016, the table takes 1680 MB on disk. Why does the storage space grow so much?

I didn't do any data transformation and use pure char format to store data. I read the data dictionary of the txt file and if the length of a variable is at most n, then I use varchar(3). If the file is fixed length n, then I used char(n).

About 4 to 5 columns are densely populated and all the other columns are very sparse. For the sparse columns, only 1% rows are not null.

Here is the code creating the table and importing data:

CREATE TABLE table1 (
var1 char(12),      var2 char(6),      var3 varchar(12),       var4 varchar(3),
var5 varchar(3),    var6 varchar(4),   var7 char(1),           var8 char(1),
var9 char(2),       var10 char(6),     var11     varchar(8),   var12 varchar(12),
var13 char(6),      var14 varchar(12), var15 varchar(14),var16 varchar(12),
var17 varchar(12),  var18 varchar(12), var19 varchar(12),var20 varchar(12),
var21 varchar(12),  var22 varchar(12));

How can I reduce the storage space?

Here is the storage information from command sp_spaced:

name       table1
rows       22260960
reserved   1721240 KB
data       1721128 KB
index_size 24 KB
unused     88 KB

There is a metadata storage overhead for columns in SQL Server that you do not have to pay for in a flat file. Using compression can reduce this overhead. From Row Compression Implementation:

  It reduces the metadata overhead that is associated with the record. This metadata is information about columns, their lengths and offsets. In some cases, the metadata overhead might be larger than the old storage format.

Try compressing your table with row or page compression and see if that reduces the space required in SQL Server.

ALTER TABLE table1 REBUILD WITH (DATA_COMPRESSION = ROW);

In my experience, compression can both improve and degrade query performance. It depends on the data and the query that you're running. It's possible that you'll need to make a trade off between space and query performance.


Execution plan for COMPUTE BY I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint:

How does the query processor execute COMPUTE BY and how can I obtain an accurate execution plan?

Here is some T-SQL to play with:

 create table employee(
    ID          int,
    name        nvarchar (10),
    salary      int,
    start_date  datetime,
    city        nvarchar (10),
    region      char (1))
GO
insert into employee (ID, name,    salary, start_date, city,       region)
              values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')

insert into employee (ID, name,    salary, start_date, city,       region)
              values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')

insert into employee (ID, name,    salary, start_date, city,       region)
              values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (6,  'James', 70060,  '09/06/99', 'Toronto',  'N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (7,  'Alison',90620,  '08/07/00', 'New York', 'W')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
GO

select * from employee
GO
/*
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
*/

-- COMPUTE BY
SELECT salary, Name
FROM Employee
ORDER BY ID, Name
COMPUTE SUM(Salary)
GO

/*
salary      Name
----------- ----------
      40420 Jason
      14420 Robert
      24020 Celia
      40620 Linda
      80026 David
      70060 James
      90620 Alison
      26020 Chris
      60020 Mary
sum
===========
     446226
*/

GO

drop table employee

(Source)

The results from this single SELECT are:

How does the query processor execute COMPUTE BY?

It is a hard-coded sequence of operations at the top of the execution tree. There is a simple stream accumulation (hence the ordering requirement), some logic to detect the start of a new group, a little computation and data value copying, and direct calls to construct the alternate TDS result sets per group. Some example call stacks from a query using COMPUTE BY are shown below:

  and how can I obtain an accurate execution plan?

You can't, because COMPUTE BY is not implemented in the same way as other plan operations. The replacement ROLLUP support does use the regular execution framework, automatically providing showplan information. An additional benefit of ROLLUP is that it does not use alternate TDS result sets, providing better client compatibility. In short, no one will miss COMPUTE.


Can Postgres inherited tables overlap Is it possible to have single row in a parent table be visible in more than one inherited (child) table?

Perhaps by inserting a row directly into the master table and then "making it visible" in one or more than one inherited table?

To clarify:

I want to insert into the parent table, then make that row visible in one or more child tables.  If the row is updated in one child table, the changes are immediately present in all tables where it is visible.

It does seem like this is not how Postgres inherited tables work, they appear to be more of a partitioning scheme.

I was thinking if I have a generic table with person information, then I could have a child table with that "person" and additional attributes, but if that person also belonged to a different class I could "make it visible" in another child table which would add different attributes.  If however I changed the person's contact number, which is generic and comes from the parent table, then that would be reflected in all the child tables where this row is seen.

I'm not sure exactly what you're asking. The table which inherits has all of its rows show when you query the parent.

CREATE TEMP TABLE foo ( id int );
CREATE TEMP TABLE bar () INHERITS (foo);
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1);

test=# TABLE bar;
 id
----
  1
(1 row)

test=# TABLE foo;
 id
----
  1
  1
(2 rows)


Result of a query in SQL Let us say that we have to write a query for:

  Find the distinct names of all students with percent>90 in courseno=107

given three relations:

Students (rolno:integer,sname:string)
Courses (courseno:integer,cname:string)
Registration (rollno:integer,courseno:integer,percent:real)

According to an exam solution,the query solution is:

SELECT DISTINCT S.name
FROM Students as S,
     Registration as R
WHERE R.rollno=s.rollno
  AND R.courseno=107
  AND R.percent>90

I think that if two students(say there are two students having same name so its name would be eliminated) so I am worried about use of DISTINCT here.

I see the point you are making: if there is more than 1 student with the same name, enrolled in course 107, with a percentage greater than 90%, then your query will not distinguish between them.

Given the limited amount we know about the dataset, the solution provided does do the job, but I would have added the caveat about duplicate student names, or proposed an alternative solution:

SELECT S.name,
       count(*)
FROM   Students as S,
       Registration as R
WHERE  R.rollno=s.rollno
  AND  R.courseno=107
  AND  R.percent>90
GROUP  BY S.name

and in this way, we list distinct names, but are aware when there are multiple records selected for the same name.


character set, not set correctly I have two database with a table with utf8 table, I wanted to change the encoding of a column, I used two scenarios but a problem happened to me.
at first the table was like this :

CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text  NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE,
  KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

First scenario

in the first one I change the row encoding with bellow command :

ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

and then I use show create table spool; like this one :

 CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE,
  KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

second scenario

In the second table I first changed the table encoding and then I changed the xml column encoding like this :

ALTER TABLE spool
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

and then I changed column encoding like below :

ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

and then we can see table like this :

CREATE TABLE `spool` (
  `username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30849368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

as you can see in the second scenario in front of the "`xml` text" we don't see the CHARACTER SET utf8mb4 and I think it causes error for me because when Ejabberd wants to insert a query in the second one I see bellow error but in the other table I don't have this error:

HY000Incorrect string value: '\\xF0\\x9F\\x98\\x8F\\xF0\\x9F...' for column 'xml' at row 1"
** Stacktrace: [{ejabberd_odbc,sql_query_t,1,[{file,"src/ejabberd_odbc.erl"},{line,173}]},{lists,foreach,2,[{file,"lists.erl"},{line,1336}]},{ejabberd_odbc,outer_transaction,3,[{file,"src/ejabberd_odbc.erl"},{line,443}]},{ejabberd_odbc,run_sql_cmd,4,[{file,"src/ejabberd_odbc.erl"},{line,380}]},{p1_fsm,handle_msg,10,[{file,"src/p1_fsm.erl"},{line,582}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,237}]}]

in the first scenario we don't see this error but in the second one the error exists. how to solve this problem?

Interesting question. It looks like in second scenario you firstly convert default charset for table:

  To change only the default character set for a table, use this
  statement: ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

So then you try to MODIFY charset of your xml column. But database
may think that this column is already in the desired charset (remember you already change default charset)

Try to use one query like this (see the same page of mysql manual):

ALTER TABLE spool
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  To change the table default character set and all character columns
  (CHAR, VARCHAR, TEXT) to a new character set, use a statement like
  this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

  The statement also changes the collation of all character columns. If
  you specify no COLLATE clause to indicate which collation to use, the
  statement uses default collation for the character set. If this
  collation is inappropriate for the intended table use (for example, if
  it would change from a case-sensitive collation to a case-insensitive
  collation), specify a collation explicitly.


How to design table for role-based security with special Update actions I am trying to design a table that will contain the following information. Basically this is a table to lookup security roles. The catch for this is that for each ObjectType, you might have special actions they can or cannot do.

For example, ObjectType A, you can Assign, Complete, Close, for ObjectType B you can Recieve, Release, and Reject. For each of these special actions they can or cannot do them.

UserID
ObjectTypeID
Create (yes or no)
Read (yes or no)
Delete (yes or no)
Update (yes or no, in addition to a list of ActionID's that are
specific to ObjectType)

note: int? = nullable integer

I came up with the following table structure, but the problem is that I would have to add another column if I wanted to I realized I don't have enough columns to contain all possible actions for that specific ObjectType. Also, that just seems kinda of sloppy, and not very Normalized.

How I can make this more Normalized?

UserID|ObjectTypeID|Create|Read|Delete|Update|Action1|Action2|Action3|...
int   |   int      |  bit |bit | bit  | bit  | int?  |  int? |  int? |...

ObjectTypeID|ObjectType|Description
  int      |    int   |  varchar

ActionID|ObjectTypeID|ActionDescription
  int   |    int     |    varchar

Just in keeping with the KISS principle, first, make a list of all actions:

create table Actions(
  ID     int auto_generate primary key,
  Name   varchar( 16 ) not null -- "Create", "Read", etc.
  ...    -- other data describing the action
);

Now create an intersection table between the user, the object and the actions. Nothing fancy, if a user is tied to an action, they can perform that action. If not, they can't.

create table UserActions(
    UserID   int not null references Users( ID ),
    ObjectID int not null references Objects( ID ),
    ActionID int not null references Actions( ID ),
    constraint PK_UserActions primary key( UserID, ObjectID, ActionID )
);

The PK prevents multiple entries of the same User/Object/Action combination. If there in an entry for a particular user and a particular object with, say, the id of READ, then that user can perform that action on that object. Otherwise, they can't. Simple and scalable. And thoroughly normalized.


Handling expiration in a course registration or ticketing system I want to restructure the database for a web application I inherited for handling summer camp registrations, but my question is relevant to any event registration or ticketing system.

My question is about how to best handle the need for registrations to expire if unpaid for X amount of time.

Currently the database design is something like this (some columns omitted for brevity):

PK = primary key
FK = foreign key

sale_header (
    PK id
    FK account_id
    payment_status
    total
    payment_method
    payment_time
)

sale_item (
    PK (sale_header_id, camper_id)
    price
)

class_registration (
    PK (camper_id, class_id)
    time_added
    registered (boolean)
)

When payment is completed, the registered flag is set to true. There is a dump script that runs regularly to clear out unpaid registrations by checking for registrations where `class_registration.registered = 0 AND sale_header.payment_status='not paid'" and for which the configured time allowed until expiration has elapsed.

There are currently some bugs related to this, and anyway it seems to me to be a somewhat strange design given that the registered flag is only set to true when payment is completed, so the payment_status column seems to be unnecessary; it seems like it would be much cleaner to have the sale_header table only contain transactions that were actually completed. Also, even if I kept this approach, it seems it would be better if there were a FK linking sale_item and class_registration.

I've been researching potential database designs for this scenario and one thought I had was to add price info to the class_registration table and get rid of the sale_item table, since the system is only for selling class registrations, nothing else:

sale (
    PK id
    FK account_id
    total
    payment_method
    payment_time
)

class_registration (
    PK (camper_id, class_id)
    FK sale_id
    price
    time_added
)

In this design, the FK sale_id would initially be null while the parent was still choosing classes for their camper(s), so finding expired, unpaid registrations would be done by querying for registrations where sale_id is NULL, and which had exceeded the expiration time.

Another option I thought of, inspired by this example (scroll down to "Data Modeling in the Sample Application"), would be to have a separate class_availability table, which might be good for querying performance when determining available capacities:

class_registration (
    FK camper_id
    FK class_id
    FK sale_id
    price
    time_added
)

class_availability (
    FK class_id
    FK camper_id
    FK registration_id
)

Finally, I considered what it would look like if I kept the sale_item table, although I'm not sure what would be the advantage of doing so:

class_registration (
    PK (camper_id, class_id)
    FK sale_item_id
    time_added
)

sale_item (
    PK sale_item_id
    FK sale_header_id
    price
)

So my specific concerns are:

What are the pros and cons of these approaches?
Should expired registrations stay in the database and just be excluded when querying, or actually be deleted?
A requirement to keep in mind is that it should be possible to change the configured expiration time in a live system (in case of bugs, which has happened before with this system), so having a column like expiration_time in the database isn't an option; it needs to be calculated based on time_added.
(Bonus :) Feel free to skip this one and focus on the above questions.) In my new proposed designs (or some other design), how could I handle the possible need for admins to be able to extend the expiration time for a particular camper in case there was some issue with payment that needed to be worked out, and their registration should be held in the meantime?

There are two principals that should govern your thinking about you data modeling options for this application:

1. Never Throw Away Important Information

Discarding data that you might need is a terrible idea.  It's up to you to decide whether a failed registration is something you might need.  I would think it probably is.  What would you do if someone showed up for a class saying "hey I registered!"?  Wouldn't you want to be able to say "oh, I see your cheque bounced so your registration failed."?

2. Model Close to Reality

If you try to abstract your entity types too much, you will find your model gets brittle and hard to adapt to changing business rules.  Your system is tracking a couple of kinds of events.  In particular, the act of someone registering for a class and the act of someone paying for a registration.  You should have a table for each of these events, along with tables for the people and classes.

Keeping separate tables for registration (which you already have) and for payment (which you don't really have in any of your options) allows you to handle situations like payment by multiple methods, e.g. some cash, some credit card, some promotional coupon, etc.  It also lets you deal with the situation of failed payments, credit notes processed, and other real-world exceptions that are probably important to know about.

Instead of keeping columns for registration status, which is ultimately a calculated value, you should be keeping columns (and records) for the various components of the registration status calculation.  If your system turns out to have peculiar performance demands during real runtime testing, then consider using denormalization of the calculated registration status value, but beware of all of the potential issues that will raise for data integrity and plan to deal with those issues.

Bonus:

If you keep a REGISTRATION table and a PAYMENT table, as I suggest, and treat registration status as a calculated value, then you can add a column to REGISTRATION that allows you to bend the payment time limit rule.  You can do this different ways.  For example you could have a flag or code that means "consider this registration valid, even without payment".  Alternatively, you could have a numeric or date column that gives a payment extension allowance in number of days or by a different, arbitrary deadline.


how login other account by sysdba in oracle? I forget hr's password, but I have sysdba role.

Can I use su hr like in linux.

I don't hope alter hr's password.

It is really easy to log in as another user, just use the proxy user feature that is available since 8i. Unfortunately it does not work with SYS, but works with any other user. Basically you can grant the privilege to users to connect as another user.

SQL> alter user system identified by Oracle123;

User altered.

SQL> alter user hr identified by password account unlock;

User altered.

So the password of SYSTEM is Oracle123, and the password of HR is password. Now allow SYSTEM to connect as HR:

SQL> alter user hr grant connect through system;

User altered.

Finally, connect with SYSTEM and its password, providing HR in the brackets:

SQL> connect system[hr]/Oracle123
Connected.
SQL> show user
USER is "HR"

You can use any user for this, except SYS:

SQL> alter user hr grant connect through sys;
alter user hr grant connect through sys
                                    *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYS'


MySQL query performance using index 77 million records in the Campaign Code table.

Index statement :

  INDEX IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId

  (CampaignId, CampaignCodeDateId, PKCampaignCodeId, CustomerId)

First query,
execution time : 0:00:0.546

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 18
AND cc.CampaignCodeDateId = 19325
ORDER BY cc.PKCampaignCodeId LIMIT 20000 OFFSET 0;

Second query,
execution time : 0:01:11.597

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

Do not the second question need to be faster? What should I do?

Edit :

I created index for second query.

INDEX IDX_Test_Index

(CampaignId, CustomerId,PKCampaignCodeId)

After index create the second query is faster but first query slower

First query execute plan changed :
execution time : 0:01:17.236

CREATE TABLE CampaignCode (
  PKCampaignCodeId int(11) NOT NULL AUTO_INCREMENT,
  CampaignId int(11) NOT NULL,
  Code varchar(255) NOT NULL,
  CreatedBy int(11) NOT NULL,
  CreatedOn datetime NOT NULL,
  CustomerId int(11) DEFAULT NULL,
  IsActive bit(1) NOT NULL,
  IsUsed bit(1) DEFAULT NULL,
  ModifiedBy int(11) DEFAULT NULL,
  ModifiedOn datetime DEFAULT NULL,
  OrderNumber varchar(255) DEFAULT NULL,
  CampaignCodeDateId int(11) NOT NULL,
  PRIMARY KEY (PKCampaignCodeId),
  INDEX IDX_CampaignCode_CampaignId (CampaignId),
  INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId
            (CampaignId, CampaignCodeDateId, PKCampaignCodeId),
  INDEX IDX_CampaignCode_Code (Code),
  INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId)
)
ENGINE = INNODB
AUTO_INCREMENT = 114306664
AVG_ROW_LENGTH = 61
CHARACTER SET latin5
COLLATE latin5_turkish_ci
ROW_FORMAT = DYNAMIC;

There is no perfect index for this:

WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId

It is because the index cannot get past IS NOT NULL to get to the ORDER BY.

In general,

WHERE a=1 AND b=2 ORDER BY c

can make good advantage of INDEX(a,b,c) or (b,a,c)

But, this cannot:

WHERE a=1 AND b!=2 ORDER BY c

(IS NOT NULL is similar to != 2 in that it is a "range", not a single value.)

To optimize this:

WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

there are two possibilities.  The Optimizer will pick one, sometimes the less optimal one:

INDEX(CampaignId, CustomerId)
INDEX(CampaignId, PKCampaignCodeId)

The first will do the filtering, but still have to sort and limit.
The second will do some of the filtering, avoid the sort, but might have to look at a lot more than 25 rows.

There is no benefit of having all 3 columns in an index; the third column  will not be used.  (And, apparently, it causes trouble with your first SELECT.

More on creating indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql  (though it does not fully address all your questions)


Reading and writing the common data I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application gets timeout exceptions because of locks this shared data. What the common or maybe non-ordinal solution to separate intensively modified data and fast reading the same one. By the way, data can be consistent but can be delayed in time for 10-20 seconds (for example). OLAP cannot be used.

Use Snapshot or Read Committed Snapshot isolation for your reads.

  When a row versioning-based isolation level is enabled, the Database
  Engine maintains versions of each row that is modified. Applications
  can specify that a transaction use the row versions to view data as it
  existed at the start of the transaction or query instead of protecting
  all reads with locks. By using row versioning, the chance that a read
  operation will block other transactions is greatly reduced.

Alternatively:

Read less. If the nature of the application and data is such that you can tolerate a 10-20 seconds delay, cache the response and returned the cached result until your next successful read.
Use replication to distribute data to a set of read-only slaves.
Fix your application. Timeout implies long running transaction; occasionally necessary but typically a design flaw.


Get the last statement execution date on an MSSQL Database In an MSSQL Database, I am trying to know when was the last statement executed for that database.

So, is there any log that I can use to see the required activity records (could be the Transaction Log, but I am not sure how to get dates from it)?

You will want to use the sys.dm_exec_query_stats dmv. If you simply want to see the last statement that was executed, this should get you started (note: you need to execute this in the context of your database e.g. by using the USE <db_name> command):

SELECT
deqs.creation_time
,   deqs.last_execution_time
,   dest.text
,   SUBSTRING(dest.text,
        (deqs.statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                WHEN -1
                    THEN DATALENGTH(dest.text)
                ELSE deqs.statement_end_offset
                END
                    - (deqs.statement_start_offset/2) + 1)))
AS statement_text
, deqp.query_plan
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY
sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY
sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE deqs.last_execution_time = (SELECT MAX(last_execution_time)
                                FROM sys.dm_exec_query_stats)


How to express a "some of which are" and "not all are" relationship with enhanced entity-relationship diagrams? For example:

Gardens have attribute gate, Condos have attribute door. Some of the Gardens and Condos are Private_Properties. On the other hand, Private_Properties are not restricted to only Gardens or Condos.

How can these non-exclusive relationship be illustrated with an enhanced entity-relationship diagram (EERD)?

You can represent a 1..* relationship

Notation is quite standard but it may vary a little depending on your EER tool... Since I work with Oracle, I'm used to Oracle Data Modeler notation...

Check the picture... and good luck!


Will MySQL redirect a read request to the master after a certain time? I can't find what variable controls this or any information about the time limit.

Thanks!

Why do you mean by "certain time"? Replication Lag? Query Response Time?

In a simple Master/Slave(s) infrastructure, the distribution of the reads/writes operations should be done with a load balancer or directly in your apps.

Max.


Naming conflict between function parameter and result of JOIN with USING clause Given this setup in current Postgres 9.4 (from this related question):
CREATE TABLE foo (ts, foo) AS
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle here (also from the previous question).
I wrote a SELECT with a FULL JOIN to achieve the objective of the referenced question. Simplified:
SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);
As per specifications, the correct way to address the column ts is without table qualification. Either of the input values (f.ts or b.ts) can be NULL. The USING clause creates a bit of an odd case: introducing an "input" column that's not actually present in the input. So far so elegant.
I put this in a plpgsql function. For convenience (or requirements) I want the same column names for the result of the table function. So we have to avoid naming conflicts between identical column names and function parameters. Should best be avoided by picking different names, but here we are:
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;
Bold emphasis to highlight the problem. I can't use ts without table qualification like before, because plpgsql would raise an exception (not strictly necessary, but probably useful in most cases):

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

I know I can use different names or a subquery or use another function. But I wonder if there's a way to reference the column. I can't use table-qualification. One would think there should be a way.
Is there?

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or at the start of the function definition, declaring how you want such conflicts to be resolved.
The 3 possible settings are error (the default), use_variable and use_column:
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;


Huge disk space usage increase when altering a column I hope this question is easy for someone to answer :)

I have a table that looks like this (unimportant columns hidden)

CREATE TABLE [dbo].[Log](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    --several other columns
    [Name] [nvarchar](512) NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now I did some indexing on that table and that also involved shortening the Name-column to 256 width.
But when I change this column

ALTER TABLE Log ALTER COLUMN Name NVARCHAR(256) NOT NULL
GO

The database grows by a non trivial amount. (And yes I double checked - this is the only change I make here)
In total that table has 90746 entries and before altering the table SSMS said the size was 247.56MB.

But after this update the database grew to 336.13MB.

Don't know if that matters here but SELECT @@VERSION gets Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:11:52
    Copyright (c) 1988-2008 Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

How can this be? Can someone explain?

UPDATE: I tried DBCC SHRINKDATABASE (myyDB, 0); that led to a smaller database of 268.81MB, but that is still bigger than the original database size with a wider column, I still do not understand :)

SQL Server will create a new column, copy the data over, and drop the old column. The table will increase in space by some factor larger than just the size of the new or old column and/or the average or max length of the data. The reason is that temporarily the copy of the data for all rows on a page can't possibly fit on the page, so many new pages will have to be generated.

It doesn't reclaim this space after dropping the column. In order to reclaim the space, you'll need to rebuild the table / clustered index.


Looking for a database that supports fulltext but does not lock table on insert or update I have a program that needs to insert and update a table very frequently (200+ insert or update queries per minute), and this table should also support fulltext search.

I am currently using MySQL MyISAM, but it will lock the table when some data is inserted. So the fulltext queries go in a queue, slowing them down and eating a lot of memory.

What can I do to get both fulltext indexing and no table locks on inserts or updates?

Database fields:

id | title |  content | url | date | image | source | imagesource

With the current MySQL version this is not possible. Apparently the next version (5.6) will support full text indexing on InnoDB tables. I have no idea when that version will officially be released.

You might also want to consider an upgrade to PostgreSQL which has pretty decent full text indexing (I haven't really used that, but it appears it has some additional features like stemming that MySQL does not have).

And PostgreSQL copes very well with concurrent reads and writes.


create incremental number in oracle sql query how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g

here is the code that I try,it seems not working:

WITH
TABLE3 AS ( SELECT 2008 YEARS FROM dual WHERE 1=1
union all
select t3.YEARS+1 from TABLE3 t3
WHERE 1=1 AND t3.YEARS < 2011
)

select YEARS from TABLE3

expected result I want is :

2008
2009
2010
2011

I think this will work (based on  this page ( http://psoug.org/definition/LEVEL.htm ) as a starting point):

WITH counter
AS ( SELECT LEVEL seq
       FROM DUAL
     CONNECT BY LEVEL <= 4 )
SELECT (2008 + seq - 1) myYear
  FROM counter
 ORDER BY 1
;

This should return:

myYear
------
  2008
  2009
  2010
  2011

Adjust 2008 and 4 to get different results.


Is there an effective way to create extended event data that includes stacktraces in SQL Server on a local db instance? I read Paul Randal's article on getting the symbols for SQL Server. I have a theory that I could get the extended events to read the symbols from the symbol server catch directory by setting the _NT_SYMBOL_PATH environment variable. The only problem is I can't create the right conditions for any data to show up when I create an extended events session with sqlos.spinlock_backoff. I tried the script in this pdf.

I tried HammerDB and I tried this script with adventure works. I don't get any spinlocks. I tried setting max server memory at 256 megs (my default setting on my laptop) and I tried it at 2 gigs. The Extended event session was running, but no data showed up.

The original Extended Event script from that article has some filters on it ( DBCC_CHECK, INDEX_CREATE and SE_REPL_PARTITION_BY_KEY ) which means they won't fire for just any old spinlocks.  I have adapted the script to remove the filters and have successfully captured the callstacks.  This demo script makes a number of assumptions:

you are not trying to do this on your production server
you know how to use SQLCMD mode ( SSMS > Query > SQLCMD Mode )
you know how to use ostress and have it installed
I'm working with a named instance called sql2012 - you can configure that
I'm using c:\temp\temp.sql as a scratch file - you can configure that

Demo script.  WARNING!! Do not run on production server
-- Spinlock simulation and callstack tracking with Extended Events
:on error exit
USE tempdb
GO

SET NOCOUNT ON
GO

CREATE TABLE spinlock_sim ( id INT PRIMARY KEY )
GO
INSERT INTO spinlock_sim VALUES( 1 )
GO

-- Create the sql file to run
!!bcp "SELECT N'SELECT id FROM spinlock_sim'" queryout c:\temp\temp.sql -S.\sql2012 -T -c

-- Reset spinlock stats (SQL 2012 only)
DBCC SQLPERF( 'sys.dm_os_spinlock_stats', CLEAR );
GO

-- Create the event session
CREATE EVENT SESSION [spin_lock_backoff] ON SERVER
ADD EVENT sqlos.spinlock_backoff( ACTION(package0.callstack) )
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlos.spinlock_backoff',
    source=N'package0.callstack',source_type=(1))
WITH (MAX_MEMORY=51200 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE=START
GO

-- Attempt to create the spinlock contention with ostress
-- REM Parameters -E Windows Authentication -S server -Q query -o output directory -n number of connections -r iterations
-- REM eg 50 connections 200 times
!!"C:\Program Files\Microsoft Corporation\RMLUtils\ostress.exe" -E -S.\sql2012 -dtempdb -ic:\temp\temp.sql -q -oc:\temp\ostress.log -n50 -r200 -mstress
GO

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1)
GO

--Get the callstacks from the bucketize target
select event_session_address, target_name, execution_count, cast (target_data as XML)
from sys.dm_xe_session_targets xst
inner join sys.dm_xe_sessions xs on (xst.event_session_address = xs.address)
where xs.name = 'spin_lock_backoff'
go

-- Look at spinlocks
SELECT * FROM sys.dm_os_spinlock_stats
ORDER BY backoffs DESC
GO

-- Cleanup
DROP TABLE spinlock_sim
GO
DBCC TRACEOFF (3656, -1)
GO
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP
GO
DROP EVENT SESSION spin_lock_backoff ON SERVER
GO

If your pdb is not in the right place you will only see hex values instead of method names (like mine), but you get the idea.  Here's what I captured:

HTH


Replicate multiple times a set of rows with unique column in a table I wish to replicate a set of rows in a table but with handling unique column in SQL Server 17.
The table is:
CREATE TABLE test1 (
    id int CONSTRAINT PK_test1 PRIMARY KEY  CLUSTERED IDENTITY,
    name nvarchar(10) CONSTRAINT UK_test1 UNIQUE NONCLUSTERED,
    value int,
    type varchar(20)
);

The table contents are:
INSERT INTO test1 VALUES ('e_1',1,'A');
INSERT INTO test1 VALUES ('342A',34,'ABC');
INSERT INTO test1 VALUES ('Tame_33',277,'Test10');

Now I wish to replicate those rows multiple times but the problem is with NAME column which is UNIQUE.
The solution I did so far is just use of a cursor, but this is inefficient and I'm curious if this can be done in more efficient way?
DECLARE @id int;
DECLARE @name nvarchar(20);
DECLARE @value int;
DECLARE @type varchar(10);
DECLARE @cnt int = 0;stackoverflow
DECLARE @intcnt int = 0;

DECLARE temp_cursor CURSOR FOR
SELECT * FROM test1
OPEN temp_cursor;
FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @intcnt < 5
    BEGIN
        INSERT INTO test1 VALUES
        ('ins_' + CONVERT(varchar,@intcnt) + '_'  + CONVERT(varchar,@cnt), @value, @type);
        SET @intcnt = @intcnt + 1;
    END

    SET @intcnt = 0;

    FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;
    SET @cnt = @cnt + 1;
END

CLOSE temp_cursor;
DEALLOCATE temp_cursor;

WITH gen AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=5
)
INSERT INTO test1 (name,value,type)
SELECT 'ins_' + CONVERT(varchar,num) + '_'  + CONVERT(varchar,id), value, type
FROM gen
CROSS JOIN test1

Some explanation provided by the OP, added so others can adapt the solution:

Actually used CTE as a generator of integers (called
'fnTally') which he later used in CROSS JOIN to produce cartesian
product of those two tables - the source one which is 'test1' and
generated by CTE called 'gen' (a combination of every row from two
tables). The only further optimization here would be to use cCTE
(cascade CTE introduced by Itzik Ben-Gan).


SQL Managment doesn't use all memory, queries fail due insufficiënt memory In Microsoft SQL Server Management Studio 12.0.4100.1 I have a table with about 12.600.000 records. If I try to show the TOP 1000 of these records with an ORDER BY [bigint column], the query runs for a few minutes, then fails with a message of "There is insufficient memory available in the buffer pool."

However, if I look in the task manager, I see that the SQL Server Windows NT - 64 Bit process only uses a small part of the 32 GB that the computer has. If I run this query after a reset of the local server, I first see the proces using a fair amount of CPU and Disk capacity, while the memory usage steadily climbs. But after a few seconds the memory usage stops growing. Exactly at what level it stops changes every time I try it. Sometimes it's at 1.5 GB, sometimes around 3.5 GB. In either case, the memory usage no long changes, the Disk and CPU usage drop to 0, and the SQL query will keep executing for 2-4 more minutes before failing.

I checked the Max Memory of the local server in SQL Managment Studio, and it was set to the highest value. I actually tried lowering it to 16 GB, but the query still doesn't use nearly what I think it should be allowed to.

Am I missing something? Is this a limitation of SQL Server Express, which I am using at the moment?

I believe memory usage in Express edition is limited to 1Gb per instance: http://sqlknowledge.com/2014/04/sql-2014-express-edition-limitations/

So setting Max Memory any higher than that will be ignored by an Express instance. With that in mind I'm a bit surprised that you are seeing memory usage hitting 3.5Gb

You mention the ORDER BY in your question - does the query run ok without it?


How can I make a database backup go faster? I recently attended an interview for my next job and was asked this:

How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server?

My Answer: We can use Database Compression or use a third-party tools like Light Speed.
But they expected this answer which I was not aware of:

Solution : Increase throughput by adding more destination files

Example
I created this sample script by adding more backup destination files:
DBCC TRACEON (3605, -1)
DBCC TRACEON (3213, -1)

BACKUP DATABASE [AdventureWorks2008] TO
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full.bak',
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full-File2.bak',
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full-File3.bak'
WITH
    NOFORMAT, INIT, NAME = N'AdventureWorks2008-Full Database Backup',
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

DBCC TRACEOFF(3605, -1)
DBCC TRACEOFF(3213, -1)

Can you please answer the below questions:

What is the use of following this method instead of using a single file?
Will there be any time difference if I do like this?
How can I restore the DB using the multiple split files?
Will it really increase the speed of the backup?

Theory

Multiple targets for the backup can be very useful, but only in certain situations.

Your source database can be read faster than your current single file backup can write.
Your backup targets for each database are on separate spindles.
You aren't using shared SAN HBA or iSCSI or other bandwidth between the SQL Server instance and the media.
i.e. the IOPS from writing Backup File A DO NOT use the same disks as writing Backup File B.

If all of these are true, then it's possible that some degree of parallelism (many output files) will increase the speed.  If all of these are not true, more than likely you'll cause one or more sets of disks to thrash, and your multi-file backups will actually be slower and also may cause OS filesystem or storage level fragmentation, because you're writing Backup File A and Backup File B at the same time!

Note that many of the same things apply that I covered in my answer to Transaction Log Backups Serial or Parallel?, since this is about writing in parallel (though not necessarily reading, depending on how your filegroups and files are laid out).

Experiment

To really understand this, you may need to see about borrowing some equipment from close friends.

Obtain one (1) fast solid state disk

Any of the good SATA2 or SATA3 or mSATA Samsung, Crucial, Mushkin, etc. internal drives will do
You need to ensure that you can read faster than you can write.

Beg, borrow, or buy at least two disk drives in addition to the drive your SQL Server data and log files are on.

One (1) of these can be a USB2 external drive (or a USB3 drive) plugged into a USB2 port
One (1) of these can be a USB2 external drive plugged into a USB3 port

We can be reasonably sure the USB2 and USB3 ports aren't sharing a bus different controllers
You can't put a USB3 drive into a USB3 port here, because we WANT our backup devices to be slow individually, but not contend with each other for bus bandwidth

One or more drives can be SATA spinning disks

Log into your local SQL Server Developer or SQL Server Express instance

Create a reasonably big database with data and log files on the fast SATA SSD.
Now try backing up twice in a row (two BACKUP DATABASE commands in the same SQL batch) to each backup device, one device at a time
Record the speed of backup and verify on only the second backup to each, i.e. after caches have been filled.
Now you know how fast each is by themselves; one is likely better than the others.
Now try backing up twice in a row (two BACKUP DATABASE commands in the same SQL batch) to ALL backup devices at once (i.e. with multiple DISK arguments)

See the speed difference?

Now play around with BUFFERCOUNT, MAXTRANSFERSIZE, and if you're on developer edition, COMPRESSION.

ALWAYS use the CHECKSUM option!


How to benchmark Microsoft SQL Server In my company we have multiple virtual machines hosting SQL Server 2008 R2 and some of these machines perform differently than others, some because of very busy Vmware hosts some because of slower connections to NAS.

Is there a way I can run some SQL code in a test SQL database or other known best practices I could use to run some performance tests in each VM and baseline/benchmark performance so that I can understand which machine behaves better than others prior to moving those machines to Prod or UAT environments? Thanks, Davide.

You could use one of the free SQL benchmarking tools such as Dell's Benchmark Factory for Databases (BFD) or Hammerora. Benchmark Factory has a free trial and Hammerora is opensource.  They both run industry-known TPC benchmarks, and you can do custom workloads with BFD.  I have only used BFD and each test 'run' can be saved as a report which I found really useful for comparisons.  However Hammerora is also well supported ( eg here and here ).

You could also record one of your own known workloads with a Replay trace via Profiler / server-side trace then restore the backup and replay it in each environment.  There's a bit more work to this option but would make for a more familiar test.

I also have a soft-spot for ostress which is part of the RML Utility Suite which had an update in April 2014 and I still use all the time.  More of a learning curve with this free Microsoft tool, but it's really powerful.  For example you can do things like "run all the .sql scripts in this directory 20 times, simulating 50 concurrent users".

In summary, if you're after a free tool and a quick start look at one of the free tools I mentioned above.  If you want to invest a bit more time, have a look at Replay traces, RML and ostress.

HTH


How to implement a dynamic referral program? Example: Currently, we will provide 100 credits each to both the referrer and referred.

Then in the future, we might want to implement a different referral program. Such as change credits to 200 instead of 100.

Then again in the future, give each 100 credits as well give referrer 50 if the referred spends 100 with us for the first time.

Then again in the future, give each 100 credits as well give referrer 50 each time the referred spends 100 with us.

Also, sometimes restrict the program to a few users only.

"Dynamic referral": We should be able to create different referral programs in the long course. Should be dynamic and able to take stats from the data.

Is it OK to change the database every time we come up with a new referral program?

I'm confused. How to create a schema/design/table(model) for such? Please help.

Note: I didn't include any design attempt because I couldn't think of it. I'm confused. I want it to be generic. I could build models for different referral programs. But I'm looking for something more generic. Is it even possible? I wonder.

Separate data and business logic.

A database is a repository for data, so put those numbers in a table (let's call it Creds), possibly with date ranges of when they are applicable.

Your application code is where you build the "business logic" that interprets "referrer", "referree", "first time", etc, based on the data.

For example "first time" would come from some other table that, for example, has the entire list of "referred spends" for a user.  You would write a suitable query to determine "first time", then use Creds to and the current date to decide how much credit to give a user.


Warehouse storage system (FIFO by BBD and date) I have inventory table.

CREATE TABLE inventory
(
InventoryID int(11) unsigned PRIMARY KEY AUTO_INCREMENT,
InventoryDate   datetime,
Quantity    decimal(15,4),
BBD date NULL,
ItemID  int(11) unsigned NULL,
MoneyValuePerUnit   decimal(20,4) NULL);

and rows inside

INSERT INTO inventory (InventoryDate, Quantity, BBD, ItemID, MoneyValuePerUnit) VALUES
('2019-12-01 16:23:39', 100, null, 1, 1.23),
('2019-12-02 16:27:11', 200, null, 1, 1.55),
('2019-12-03 16:31:58', 100, null, 1, 1.23),
('2019-12-10 16:23:39', -100, null, 1, 1.23),
('2019-12-11 16:23:39', -137, null, 1, 1.55);

It is about warehouse managment system. Some product (ItemID=1) is taken to warehouse 1,2,3 dec (with different prices)
and some quantities left warehouse (10 and 11 dec) with corresponding price.

Warehouse is working FIFO (or, if we have BBD (best before date) then the worst BBD first)

So, my problem is, when I'm creating outgoing order in my system, the corresponding quantity of goods must left the warehouse = I need to insert one+ rows to table inventory with negative Quantity (like 2 last rows in this table).

But, I need to calculate what BBD and price will left warehouse.
In this example, I have no BBD's at all (just for example I have product without BBD), but I have different purchase price (1.23 and 1.55 per item)

So, when I added first row, I took 100 quantities of item per 1.23/item
Second row: +200 quantities but different price 1.55
Third row: +100 quantities but price is also 1.23

Then I received first sales order for 237 items.
And I need to "reduce" warehouse sequentially, starting from first item in stock.
In this example I see, that I have 100 items with price 1.23, so I insert row N 4, to deduct this amount by this price
next I have to deduct 137 items, and I have 200 items by price 1.55, so I insert row n 5 to deduct 137 items by 1.55

Now I have in warehouse 100 items by 1.23, and 63 items by 1.55. But order is very important. Because items per 1.55 (row 2) came first than items by 1.23(row 3), and row 1 is "empty".

And finally, I received second sales order for 100 items.
So I have to somehow calculate, what I need to reduce firstly 63 items by price 1.55, and only then 37 items by price 1.23

Question is: How can I calculate it? I have that products left warehouse obey the following rules: worst BBD first. If BBD is the same, then first come left first (by InventoryDate)

SQLFiddle is here:
http://sqlfiddle.com/#!9/99cf48/4

Check this solution:

WITH
cte1 AS ( SELECT *,
          SUM(CASE WHEN Quantity > 0
                   THEN Quantity END) OVER (PARTITION BY ItemId
                                            ORDER BY InventoryDate) incomeQuantity,
          SUM(Quantity) OVER (PARTITION BY ItemId
                              ORDER BY InventoryDate) balance
          FROM inventory ),
cte2 AS ( SELECT ItemID, -SUM(Quantity) spent
          FROM inventory
          WHERE Quantity < 0
          GROUP BY ItemId )
SELECT cte1.ItemID,
       cte1.InventoryID,
       cte1.InventoryDate,
       cte1.Quantity,
       cte1.MoneyValuePerUnit,
       CASE WHEN cte1.incomeQuantity - cte2.spent <= cte1.Quantity
            THEN cte1.incomeQuantity - cte2.spent
            WHEN @amount + cte2.spent >= cte1.incomeQuantity
            THEN Quantity
            ELSE GREATEST(@amount +
                          cte2.spent -
                          COALESCE(LAG(cte1.incomeQuantity) OVER (PARTITION BY ItemId
                                                                  ORDER BY InventoryDate), 0), 0)
            END expense
FROM cte1
JOIN cte2 USING (ItemId)
WHERE cte1.incomeQuantity > cte2.spent
  AND cte1.Quantity > 0
ORDER BY cte1.InventoryDate

where @amount - the amount of good (itemId) which must be sold. The variable is used because only one itemId is present in data, really this must be a joined table order (itemId unique, amount), which contains the amount for each good to be sold in the order.

fiddle - test it carefully in all possible datasets. But please note - the query does not show that the requested quantity is greater than the stock balance. It also does not take into account the value of BBU if it is not NULL, add the appropriate sorting level.


Can't connect to ODBC database with pyodbc. Which SQL Server driver should I install on the Raspberry Pi? I'm trying to connect to a SQL Server database using a python script. I'm using the pyodbc library to connect to it.

cnxn = pyodbc.connect('DRIVER={SQL Driver}; SERVER=192.168.80.30 ;DATABASE=users;UID=data;PWD=xxxx')

When running the script with the above connection configuration I get:

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

It seems that I don't have the SQLDriver installed. Doing some research I haven't found a driver that works. Which ODBC driver should I install on the Raspberry Pi? I'm using Raspbian.

I believe you are looking for FreeTDS or for pymssql. From pymssql docs:

  A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server.


Does the order of keys in compound Primary key make any difference? Package

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)
and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

When I define PackageVersion (read PackageVariant) table, I mark VersionNumber (aka idPackageVariant) as Auto Increment and a part of Primary Key in the order defined above:

  PRIMARY KEY (idHolidayPackage, idPackageVariant)

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  **PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,**

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

However, due to this bug in mySql, I need to have AutoIncrement Key as the first part of the Primary Key in a compound key.

So, does the order of keys in compound Primary key make any difference ?

Just for reference, the above design comes from here.

The order of keys can make a difference depending on your access pattern.

Example...

You want to find MAX(idPackageVariant) for a singleidHolidayPackage

With idHolidayPackage as the second column, then the index will be scanned. With idHolidayPackage as the first, it can be seeked which is more efficient.

This is a specifiy MySQL problem of course. IIRCl you'll need to do this. InnoDB uses the first unique key as the clustered index which may or may not be the primary key. However, this may not be the case

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,

  -- not needed INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Alternatively, you can just have idPackageVariant as the primary and clustered key for efficiency.


Column that must be unique only for rows with matching foreign key I'm trying to enforce a uniqueness constraint on a table and I'm not sure the best way to go about it. I have two tables: Parent and Child. Child.parent_id is a foreign key referencing Parent.id. I would like for Child.name values to be unique, but only for members of Child where the Child.parent_id value is the same. That is,

child_one.parent_id == child_two.parent_id && child_one.name == child_two.name should be impossible
child_one.parent_id != child_two.parent_id && child_one.name == child_two.name should be possible.

If it's a great deal of trouble I may be willing to relax the second constraint and simply say that Child.name must simply be globally unique, but I'm not very experienced with databases so I thought I would ask for advice.

I'm writing the application using Laravel, so the exact database technology is more of an implementation detail, but we happen to be using MySQL.

Thank you for any help.

I would like for Child.name to be unique, but only for members of Child where Child.parent_id is the same.

This mean you need NOT (Child.name) to be unique.
You need in (Child.name, Child.parent_id) to be unique.