SQL Temporal Tables Begin and End Date We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.

For our BeginDate and EndDate, we want them to be date, not datetime. Always on beginning of date. So if table changes at 5/3/2018 2:30pm,  SQL Temporal tables will automatically will place datetime, however, we just want date 5/3/2018. Is there anyway to only implement dates, without using Views (which convert the datetime to date), and without modifying/updating the Temporal history table? Is there a property or setting to only use regular date? If no other options, what is best out of the two, views or updating temporal history table?

CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
      CONSTRAINT DF_Department_SysEndTime
    DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Since you're only wanting to use DATE values to determine your historical changes, I'm assuming the data will only be updated once per day, otherwise you will potentially have multiple historical entries with the same day value that you cannot identify an order for with only a DATE value.

Given this assumption, you can possibly rely solely on the SysStartTime for beginning and end information by querying your table and history table. To this end, if you add a computed column to your table like below:

CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
    ValidFromDate AS CAST(SysStartTime AS DATE), -- This is a computed column
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
      CONSTRAINT DF_Department_SysEndTime
    DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Your history table does not have the column as computed but rather persists the value at the point of any change to the record. This won't work for SysEndTime because it will not recalculate the value when it is changed by system versioning, so it is always 9999-12-31.

Queries to Department will return the computed value, which is simply the SysStartTime datetime2 value converted to a date, and queries to DepartmentHistory will return the persisted value which is the computed value as it was at the point of being versioned (which is the SysStartTime datetime2 value converted to a date).

If you can rely only on SysStartTime for your Type 2 SCD, then this should meet your requirements.


Microsoft SQL Server equivalent to Oracle Enterprise Manger Top Activity Interface? My DB performance management experiences is primarily with Oracle and I am looking for an equivalent set of tools in MSSQL. In particular something to easily spot historical SQL peaks and identify the culprit SQL. It seems most of the built-in tools (Activity monitor/DMV) offered by MS only caputure active issues, but cannot retrive and chart issues that occurred much longer than a few minutes in to the past.

For Oracle, we have Oracle Enterprise Manager which views the AWR repository and by default can see, 7 days worth of Activity including the exact Sessions/SQL statements that caused an issue and why.
Example screen shot:
https://docs.oracle.com/cd/B28359_01/server.111/b28275/tdppt_sqlid.htm#TDPPT142

For MSSQL aka SQL Server, the only thing I have been able to find is a 3rd party utility from Solar Winds.
Example screen shot:
http://logicalread.com/wp-content/uploads/2017/05/meritsSQLblogpost5.png

Does Microsoft have something native that offers this capability?

I think what you are looking for is Query Store, but this is available only for SQL Server 2016 or higher.

For SQL Server 2014 or lower, you can set up an Extended Events (XEvents) session to capture the queries running longer than X amount of time. XEvents are really easy to use and quite intuitive, the only thing you should pay attention to is how much data you are capturing as it can have some impact on performance or fill up the disk quickly. The only draw back of XEvents is that it will not provide you with a chart.


Performance on updating row When updating in SQL Sever, it is my understanding that the entire row is deleted and then recreated (which is why you get a new timestamp).  So then, if you update 1 field or 500 fields, does the performance of the update change?

I suspect that if there are indexed columns, as it has to update the index, there would be more "work" for the server to perform.  When answering, assume that there are no triggers.

If you are trying to decide whether to build a dynamic or CASE expression, omitting columns whose values haven't changed, I'd say that it depends on your index structure, and you will need to test. In this article, I found that with a single, simple, clustered primary key, the blind update was always faster; this changed in some scenarios when multiple indexes were added.

http://www.sqlperformance.com/2012/10/t-sql-queries/conditional-updates

Where the breaking point is, I'm not sure; nor did I test with dynamic SQL.


how to configure influx properly I am storing some data in influx and it is quite confusing that influx is 4-5 times slow as Mysql. I try to test by inserting 10000 rows in mysql and then in influxdb.
and the stats are below.

Mysql

real: 6m 39sec
user: 2.956sec
sys: 0.504sec

Influxdb

real: 6m 17.193sec
user: 11.860sec
sys: 0.328sec

my code for influx is given below, I used the same pattern to store in mysql.

#!/usr/bin/env python
# coding: utf-8
import time
import csv
import sys
import datetime
import calendar
import pytz
from influxdb import client as influxdb
from datetime import datetime

host = 'localhost'
port = 8086
user = "admin"
password = "admin"
db_name = "testdatabase"
db = influxdb.InfluxDBClient(database=db_name)

def read_data():
    with open(file) as f:
        reader = f.readlines()[4:]
       for line in reader:
            yield (line.strip().split(','))

fmt = '%Y-%m-%d %H:%M:%S'
file = '/home/rob/mycsvfile.csv'

csvToInflux = read_data()
for metric in csvToInflux:
    timestamp = datetime.strptime(metric[0][1: len(metric[0]) - 1], fmt)

    new_value = float(metric[1])
    influx_metric = [{
        'measurement': 'mytable1',
        'time': timestamp,
        'fields': {
             'col1': metric[1],
             'col2': metric[2],
             'col3': metric[3],
             'col4': metric[4],
             'col5': metric[5],
             'col6': metric[6],
             'col7': metric[7],
             'col8': metric[8],
             'col9': metric[9]
        }
        }]
    db.write_points(influx_metric)

Can someone give me an idea how can I improve it. I think it might be due to cache. is cache option is off by default in Influx db? and can someone guide me to do batch processing in influx. I try to look over SO and google but couldn't solve my problem. I am newbie to influx db. I am trying to make it faster.

InfluxDB:  3% cpu; MySQL: 1%.  Do you have something else to use the CPU for?

Can you get InfluxDB to perform a MySQL LOAD DATA INFILE?  That will do all that work in a single SQL statement, and probably do it faster than 6 minutes elapsed.


How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times? Problem

We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year.

The SQL-Server instance in question has very high CPU usage (higher than 90% on all 16 cores all the time) during business hours.

We have also noticed very high wait times: The combination of CXPACKET & LATCH_EX accounts for about 97% of all waits. This is split about 50/50 between CXPACKET & LATCH_EX.

The non-buffered latch wait accounting for the vast majority (>95%) of LATCH_EX is ACCESS_METHODS_DATASET_PARENT.

This suggests the problem is to do with parallelism.

An example of the scale of wait times is:

CXPACKET : 332,301,799 ms
LATCH_EX : 267,955,752 ms
PAGEIOLATCH_SH : 2,955,160 ms

This was for the period between 08:00-16:24 on Jan 11th.

Options under consideration

1) Change MAXDOP from 0 to something between 4 and 8

2) Modify the cost threshold of parallelism from 50 to a higher number

Suggestions most welcome on how to ease the very high CPU load we are seeing, and reduce timeouts, in particular whether the proposed course of action is wise, and which numbers to change MAXDOP and cost threshold of parallellism to.

Background information

SQL-Server 2008 R2 running on AMD Opteron 6180 SE, of which 16 cores are given to this instance of SQL-Server.
Type of workload: something of the order of 800 connections at the same time during business hours; majority OLTP type workload with some OLAP mixed in.
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) ... Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1). Memory is appx 128 Gigs between 24 cores. 16 of the cores are available to this instance

majority OLTP type workload with some OLAP mixed in

This is the problem, not the CXPACKET. Parallelism is a symptom, not a cause. Your 'some' OLAP workload is doing scans which trigger parallelism, which cascades into exchange wait times, likely buffer pool pollution and possible blocking (OLTP workload blocks behind OLAP scans).

If the OLAP workload is well understood and absolutely critical, then you can consider adding appropriator covering indices for it. But is an ever uphill battle. I would much rather see the OLAP workload, with its damaging scans, move onto a dedicated box. Newer versions (SQL Server 2014) have readable secondaries and columnstores, both exquisite at serving analytical/ad-hoc/OLAP workloads.

For SQL Server 2008 R2 I would consider log shipping or replication (although I reckon none is 'perfect').

Short term: you have a performance problem and you need to analyse it appropriately. Read How to analyse SQL Server performance. Identify the query or queries that cause most damage (see identifying problem queries. Only then, after you identified the actual problem, can solutions be recommended.

NB: LATCH_EX on ACCESS_METHODS_DATASET_PARENT is not about IO at all. It is strictly related to parallelism, as is the latch the parallel scans 'child' threads must acquire on the parent thread to allocate the scan range for that child. Contention on it indicates the parallelism is inefficient (is doing more 'homework' than actual useful work). Partitioning would exacerbate this symptom, specially unaligned partitioning (as parent/child datasets are set per partition). Bad cardinality estimates (outdated stats?) may also be to blame, conducting to parallelism when not necessary. On and all my advice is the same: identify the actual problem query(s).


How does SQL Server handle the data for a query where there is not enough room in the buffer cache? My question is how does SQL Server handle a query that needs to pull more volume of data into the buffer cache than there is space available? This query would contain multiple joins, so the result set does not exist in this format already on disk, and it would need to compile the results. But even after the compilation, it still requires more space than is available in the buffer cache.

I will give an example. Suppose you have a SQL Server instance that has 6GB total of Buffer Cache space available. I run a query with multiple joins that reads 7GB of data, how is SQL Server able to respond to this request? Does it temporarily store the data in tempdb? Does it fail? Does it do something that just reads data from disk and compiles segments at a time?

In addition, what happens if I am trying to return 7GB of total data, does that change how SQL Server handles it?

I am already aware of several ways to address this, I am just curious how SQL Server handles this request internally when it runs as stated.

Also, I am sure this information exists somewhere, but I have been unsuccessful in finding it.

Pages are read into memory as required, if there is no free memory available, the oldest unmodified page is replaced with the incoming page.

This means if you execute a query that requires more data than can fit in memory, many pages will live a very short life in memory, resulting in a lot of I/O.

You can see this effect by looking at the "Page Life Expectancy" counter in Windows Performance Monitor.  Look at https://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy for some great details about that counter.

In the comments, you asked specifically what happens when the results of the query is larger than available buffer space.  Take the simplest example, select * from some_very_big_table; - assume the table is 32GB, and max server memory (MB) is configured at 24GB.  All 32GB of table data will be read into pages in the page buffer one at a time, latched, formatted into network packets, and sent across the wire.  This happens page-by-page; you could have 300 such queries running at the same time, and assuming there was no blocking happening, the data for each query would be read into page buffer space, a page at a time, and put onto the wire as fast as the client can request and consume the data.  Once all the data from each page has been sent onto the wire, the page becomes unlatched, and will very quickly be replaced by some other page from disk.

In the case of a more complex query, say for instance aggregating results from several tables, pages will be pulled into memory exactly as above as they are required by the query processor.  If the query processor needs temporary work space to calculate results, it will know that upfront when it compiles a plan for the query, and will request work space (memory) from SQLOS.  SQLOS will at some point (assuming it doesn't time out), grant that memory to the query processor, at which point query processing will resume.  If the query processor makes a mistake in its estimate of how much memory to ask for from SQLOS, it may need to perform a "spill to disk" operation, where data is temporarily written into tempdb in an intermediate form.  The pages that have been written to tempdb will be unlatched once they are written to tempdb to make room for other pages to be read into memory.  Eventually the query process will return to the data stored in tempdb, paging that in using latching, into pages in the buffer that are marked free.

I'm undoubtedly missing a load of very technical details in the above summary, but I think that captures the essence of how SQL Server can process more data than can fit in memory.


Do I have to worry that root user can login into MariaDB 10.5 without password I remember when installing MySQL Server 8.0, or in some database setups, there are warnings about no password login. Should I worry about this? I think if someone gets access to root user on my debian they can reset root password of MariaDB. What is the real answer here? I appreciate doc reference.

Only if you have only localhost access.
When  the attacker has somehow direct baccess to your server, it isn't that much important, but will still remain a nuisance for the hacker.
When you let access from outside the computer, it is vital that there is a strong password, which lets casual users not gain full access to the hole database.
Overall, yes make a passowrd


How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first? Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute.

Your expert advice: How can I write the C# insert code for SQL Server, such that there is a guarantee that SQL caches everything immediately in its own RAM, without blocking my app for more than it takes to feed data into said RAM? To achieve this, do you know of any patterns for setup of the SQL server itself, or patterns to set up the individual SQL tables I'm writing to?

Of course, I could do my own version, which involves constructing my own queue in RAM - but I don't want to reinvent the Paleolithic Stone Axe, so to speak.

Have you tried just writing and see what happens? Do you have a known bottleneck?

If you need to prevent your app being blocked then you one way would be to queue the writes to defer the database call. However, I'd expect the queue to clear in a second or 2: so do you need an queue if this is OK?

Or you can spool to a staging table and then flush later? We use this technique to deal with sustained writes of millions of new rows per minute (we actually use a staging DB with Simple recovery): but we didn't implement it until we had experience of just writing rows.

Note: Every write in SQL Server will go do disk as part of the Write Ahead Logging (WAL) protocol. This applies to the t-log entry for that write.

The data page with the row will go to disk at some point (based on time, use, memory pressure etc) but generally your data will be in memory anyway. This is called "Checkpointing" and doesn't evict data from memory, just flushes changes (edited 24 Nov 2011)

Edit:

For throughout considerations, based on ther last paragraph above, shift your LDF for this database to a dedicated set of disks for more performance. Ditto a staging database (one each for MDF/LDF). It's quite common to have a dozen or 3 different volumes (via a SAN normally) for your database server


How can i write a stored procedure which will perform a calculation and store the result in a table? I have two tables:

CREATE TABLE `siteArea` (
  `length` decimal(6,2) NOT NULL,
  `breadth` decimal(6,2) NOT NULL,
  `Area` decimal(6,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `roadArea` (
  `Area` decimal(6,2) NOT NULL,
  `roadWidth` decimal(6,2) NOT NULL,
  `roadArea` decimal(6,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Where

Area = length * breadth;
roadArea = Area * road_width;

The Column Area from siteArea table has to be updated into Area column of the roadArea table.

Any changes to the Area of siteArea table has to be updated to the roadArea table and roadArea has to perform the calculation automatically without input from user.

You'll need to put a trigger on the siteArea table so that whenever it's updated or inserted, it'll call a stored procedure with an argument representing the new site area and that procedure will calculate the needed values and insert them in a table.

Your question is too ambiguous for me to describe further.


mysqldump dumps different data with and without --no-create-info mysqldump dumps different representations of data when called with/without --no-create-info.
Test case
First, create the test table and populate it with interesting data.
CREATE TABLE `test` (
  `value` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- insert 'crème'
insert into test values(UNHEX('6372C3A86D65'));

Then, dump the data using with and without --no-create-info:
mysqldump test_db test > dump_test.sql
mysqldump --no-create-info test_db test > dump_test2.sql

These files encode crème differently:

dump_test.sql encodes it as 0x6372C3A86D65 (è is 0xC3A8)
dump_test2.sql encodes it as 0x63725CE86D65 (è is 0x5CE8)

Restoring the dump from dump_test.sql works correctly, a complete row is restored:
mysql test_db < dump_test.sql

Restoring the dump from dump_test2.sql produces an incorrect result where data is truncated to just cr (0x6372). Note that structure of the table is the same:
mysql test_db < dump_test2.sql

No error nor warning is given. These two files differ - besides encoding of crème - only by:
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
  `value` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--

Question
Why does this happen? Is it documented anywhere? Can I/how can I correctly restore the dump created with --no-create-info data?
Additional info
# mysqldump used to dump the database
$ mysqldump -V
mysqldump  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
# Server
version_component: Percona Server (GPL), Release '35', Revision '5688520'
version: 5.7.32-35

Try dumping using --hex-blob
mysqldump --hex-blob test_db test > dump_test.sql
mysqldump --hex-blob --no-create-info test_db test > dump_test2.sql

Please let us know if the hex value remains the same between the two dumps.
UPDATE 2021-01-08 14:15 EST
Try changing character set
The MySQL Docs say that mysqldump will use utf8 if not specified

Internationalization Options The following options change how the
mysqldump command represents character data with national language
settings.
--character-sets-dir=dir_name
The directory where character sets are installed. See Section 10.15,
“Character Set Configuration”.
--default-character-set=charset_name
Use charset_name as the default character set. See Section 10.15,
“Character Set Configuration”. If no character set is specified,
mysqldump uses utf8.
--no-set-names, -N
Turns off the --set-charset setting, the same as specifying
--skip-set-charset.
--set-charset
Write SET NAMES default_character_set to the output. This option is
enabled by default. To suppress the SET NAMES statement, use
--skip-set-charset.

Just add -N to ignore character sets or use --set-charset to explicitly name the character set you want to use when loading the dump in.


What does role db_owner allow I have been trying to troubleshoot a login that is not able to view certain tables in a SQL Server 2012 database. In doing so I find I don't quite understand what membership in the db_owner role allows. I can understand the other roles such as db_datareader and db_datawriter but I remain confused as to what db_owner allows.

Please see the reference on BOL for Database-Level Roles:

  db_owner

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

The easiest way to see all of the permissions is to use the sys.fn_my_permissions() function.  First verify you are a member of db_owner:

select
    rol.name
from sys.database_principals mem
inner join sys.database_role_members drm
on drm.member_principal_id = mem.principal_id
inner join sys.database_principals rol
on drm.role_principal_id = rol.principal_id
where mem.principal_id = user_id();

Now list out all of the effective database permissions:

select *
from sys.fn_my_permissions(null, 'database');

That should be granular enough for your purposes.  Something to note, sys.fn_my_permissions() returns the effective permissions, so think of it as an aggregation.


Multiple-View Query Optimisation We have a monster view that contains joins to many (many) tables in order to return the data that is required. In some cases, to get a column of information, we've have to join onto 4 tables, just to get to the value we need (the database structure isn't great, and we can't change it).

In order to improve the performance, I am extracting certain components and constructing them into their own View, in order to simplify the execution plan, and hopefully optimise the queries.

If I write a query that joins these views together, do I lose the benefit of the query optimisation for each view? i.e. do I end up back where I started by combining the views into a single select, or will it inherently perform better because they are acting atomically from one another?

Views are just stored queries: you don't execute views, but you SELECT data out of them. When you combine multiple views joining them, you are doing nothing different from inlining the view definition in the query. In fact, that's what the optimizer does: it's called view expansion.

If your views will never be used in isolation, but always joined together instead, I suggest that you stop tuning them individually, but you concentrate on the overall query that will use them. The optimizer will expand the views and generate a plan that will have nothing to do with the plan used when selecting from the individual views.

On the other hand, what makes your individual views faster (well-thought T-SQL code, indexes, statistics) won't probably hurt the performance of the overall query. Just don't expect the resulting plan to be a simple merge of the individual views' plans.


How do I use the MYSQL_TEST_LOGIN_FILE environment variable to change the mysql_config_editor file? I'm using MySQL 5.7 on Ubuntu 16.04.
I want to set up a mysql_config_editor login path.  The default file for this information is ~/.mylogin.cnf, but I would prefer to have it in the file ~/.config/mylogin.cnf.
The documentation for mysqli-config-editor says

To specify an alternate login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable.

I tried doing this:
$ MYSQL_TEST_LOGIN_FILE=~/.config/mylogin.cnf

$ echo $MYSQL_TEST_LOGIN_FILE
/home/username/.config/mylogin.cnf

$ mysql_config_editor set --login-path=mysqldump --host=localhost --user=root --password
Enter password:

As you can see, I confirmed that the environment variable set correctly.  However, running the set command above created the file ~/.mylogin.cnf, which I did not want, and did not create the file ~/.config/mylogin.cnf, which I do want.
$ ls ~/.mylogin.cnf
/home/username/.mylogin.cnf

$ ls ~/.config/mylogin.cnf
ls: cannot access '/home/username/.config/mylogin.cnf': No such file or directory

How do I use the MYSQL_TEST_LOGIN_FILE environment variable to change the mysql_config_editor file?

Evidently mysql_config_editor set spawns a shell subprocess, so to keep the environment variable available to it, you have to set it with export.  This works:
$ export MYSQL_TEST_LOGIN_FILE=~/.config/mylogin.cnf

$ echo $MYSQL_TEST_LOGIN_FILE
/home/username/.config/mylogin.cnf

$ mysql_config_editor set --login-path=mysqldump --host=localhost --user=root --password
Enter password:

$ ls ~/.mylogin.cnf
ls: cannot access '/home/username/.mylogin.cnf': No such file or directory

$ ls ~/.config/mylogin.cnf
/home/username/.config/mylogin.cnf


Setting Up Multi-Server Administration---SQL Server Account I am trying to set up Multi Server Administration so that I can easily update multiple instances of the same database across our network with ease. Where I am getting lost is how to set up the SQL SERVER AGENT service on the target machines.
Right now the SQL SERVER AGENT services on the machines are set to log on as NTSERVICE\SQL SERVER AGENT.
My SQL Server Agent process is set to log in as LocalSystem
My overall goal here is to figure out why I can't set any target servers from my machine, as I always run into the following error:
TITLE: Microsoft.SqlServer.Smo
------------------------------

MSX enlist failed for JobServer 'MYPC'.

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'MYPC'.) (Microsoft SQL Server, Error: 22026)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

When looking at the log on my machine I see the following error when trying to enlist the target machine:
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

One weakness of MSX is that target servers uses Windows authentication and you can't change that.
I.e., your target Agent need a domain account as a service account and that account need to exist as a Windows login on the master SQL Server (and also be a user in the msdb database there and a member of the TargetServersRole).


Database free space not being reused At the company I work for, we have encountered a problem where it seems that SQL Server 2008 R2 is not correctly calculating the free space in databases. As such databases on that server are growing rapidly in disk space usage when they should not be.

We have two database servers running SQL Server 2008 R2 10.50.1600.1 (RTM) on Windows Server 2008 R2 called Server A and Server Z. To our knowledge both servers are identical including the SQL Server configuration.

The databases held on each server are very similar though no two are identical.
All the databases are all set to Simple recovery mode.

Each night on the worst affected databases, a data import is run which deletes around 3 GB of data from the database and inserts around 3 GB of data, maybe a little more. Normally, the size of the database only grew if the inserted data was larger than the deleted data e.g. if 3GB was deleted and 3.1GB was inserted the database grew by 0.1GB.

On Server A, this has recently changed so that in the above example the database is growing by 3.1GB each time the data is imported. The old data is definitely still being deleted. It is the MDB file which is growing, not the transaction logs.

Most of this occurs in one table. When calculating the size of the data in that table it does not add up to the size reported by SQL Server when looking at the table Properties Data space value. The table has a Clustered Index, which we have both Rebuilt and Reorganised to no significant effect.

In the course of investigating this issue we took a backup of the database and restored it under a different name to Server A and Server Z using the same bak file. Server A reported 9% free space, Server Z reported 75% free space and we were able to shrink the databases accordingly.

Based on this it appears that there is an issue specifically with SQL Server on Server A as the same database is reporting a different amount of free space on  SQL Server on Server Z. We have now tried this with two of the affected databases with similar results.

We are however unaware of any differences between these servers, and are unaware of any changes made around this time this issue came to our attention.

Is anyone aware of what could be causing this?

The Fill Factor for the Index is 0 for that table on all the databases on both Server A and Server Z.
There are no active traces on the databases.
DBCC CHECKDB is reporting no errors.

We aim to be upgrading to SQL Server 2016 by the end of the year.  The issue we have is that this is causing us to run out of disk space so we cannot wait that long.  Cannot understand why this only just started happening, or why only one server is affected.  Will ask about patching SQL server.

Rebooting the server has fixed the issue

After rebooting Server A  the correct amount of free space was showing on the affected databases on Server A.
e.g. for the example in the question the affected database that was showing 9% free on Server A and 75% free on Server Z is now also showing  75% free on Server A after rebooting Server A.

We have now shrunk the affected databases on Server A back to having a normal amount of free space and run the imports.
The databases have only grown by the amount of new data that has been added.
e.g. if 3GB deleted and 3.1GB inserted the database has grown by 0.1GB instead of 3.1GB.

For reference Server A had been rebooted 3 weeks before and we are now planning to upgrade to SQL Server 2016 in the next couple of months.


Putting a Select statement in a transaction What is the difference between these 2 queries:

START TRANSACTION;
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;
COMMIT;

And without transaction:

SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;

What is the effect of having a SELECT inside a transaction?

If DELETE FROM orders WHERE id=1 was called from another session right after the SELECT in both cases, when will it be processed?

A SELECT query inside a transaction, in itself, is not properly shielded from UPDATEs and DELETEs.

What you need to use the following:

SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE
Disable autocommit by doing one of the following:

start transaction; (Which you have in your first query)
set autocommit = 0;

If you issue Delete From orders Where id=1, it will happen once the rows in the orders table have released their locks at the end of the transaction. You could experiment (on a Dev/Staging Server, please) with using READ UNCOMMITTED transaction isolation level to make the delete logically happen, but only on commit will it become visible and recorded permanently.

In the second transaction, basically all bets are off. If you run

select * From orders Where id=1;
UPDATE orders SET username="John" Where id=1;

running Delete From orders Where id=1 will commit immediately. Depending on the order MySQL executes these statements, will you see (or not see) the delete rows.

CAVEAT

MySQL 5.6 now has the following:

START TRANSACTION READ WRITE;
START TRANSACTION READ ONLY;

  The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.


Best way to store HTML dropdown options I'm developing a web site for a model agency. I have a "model" table with the details about each model (this is just an example):

CREATE TABLE model {
    model_id INT PRIMARY KEY,
    name VARCHAR(100),
    hair_color_id TINYINT,
    eyes_color_id TINYINT,
    skin_color_id TINYINT,
    city_id TINYINT,
    another_id TINYINT,
    blabla_id TINYINT,
    ...
}

The hair_color_id is a FK of the following table:

CREATE TABLE hair_color {
    id INT PRIMARY KEY,
    name VARCHAR(100),
    visible TINYINT
}

The same applies to eye_color_id and most of the other fields. This way I keep my "main" table small while I can use the other tables to store the available options and list them dinamically in the HTML dropdown lists. They are also handy when adding new models to the "model" table, I don't want people can store wrongs ids either by accident or maliciously, so the constraints keeps my model table with valid data. Of course, when I want to show the models profile I have to make many many JOINs and this will happen a lot.

Having that in mind, my question is: which storage engine would you recommend me to use for these tables (eye colors, hair colors, etc)? I'm using InnoDB (because of its cache function) but I think that maybe it's better to use MEMORY storage engine. Or MyISAM because they will be practically read only. Do you think that I should change this schema and store these values in a PHP file so it's not neccesary to make the JOINs?

Use InnoDB.  At some point, it will be the 'only' one supported.  Anyway, it is actually faster in many benchmarks.

You are talking about dozens or maybe hundreds of rows in all the the tables combined.  You will have trouble noticing sluggishness until you get millions of rows.  "Eschew premature optimization."

On the other hand, if you have a query that is demonstrably slow, let's see EXPLAIN SELECT ... and SHOW CREATE TABLE.  There could be some trivial fix for it.


Setting up AlwaysONAG in SQL server 2016 on windows 2016 I am going to set up AG's for the very first time, so with little or no experience, please guide me on below understanding:

If i am setting up 4 node AOAG with below scenario:

Node1-NY Data Center - Primary replica

Node2-NY Data Center - Secondary replica

Node1---Node2 Synchronous mode

Node 3 - Michigan Data Center secondary replica acting as DR site with different subnet with ASYNC mode

Node 4 -- Readable secondary in NJ data center for reporting purpose using ASYNC mode

Doubts/Questions:

In above Multi Subnet configuration can i still use a different storage across 4 nodes or do i need to use SAN replication as i have read few blogs and confused if that is a requirement for multi site env
What should be ideal Quorum setup? -- Can i have that in NJ site as file share witness?

Also if there is better design than above, please suggest as i have never implemented or worked on AOAG before.

RPO/RTO for critical apps varies between 2 hrs to 4 hours

While for mid critical its between a day and 2

I can add up information as needed.

Thanks

This is generally a consulting engagement since there are lots of minutiae. But to answer your techincal questions:

The AG handles the data synchronization process. There is no storage-based replication needed or involved with AGs whether it is local or multi-site.
You can use the NJ site as a FSW, but arguably the best option for multi-subnet in WS2016+ tends to be cloud witness.Figuring out the right witness for quorum needs a bit more informaiton about your infrastructure, which is again, generally a much deeper consulting engagement.

Also, it's not AOAG. Just AG. Always On (which has a space) is just a marketing umbrella term which covers both AGs and FCIs. The ON is not captialized.


Is index performance on a TIMESTAMP(6) column impacted when converting it to a number If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision (YYYYMMDDHHmmss) and still realize the index performance?

For example:

 SELECT * FROM MYTABLE
 WHERE TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') > 20110622105738
   AND TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') <= 20150622105738
 ORDER BY MYCOLUMN

This is for Oracle 11

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows.  In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse

 SELECT * FROM MYTABLE
 WHERE my_column > to_timestamp( to_char(20110622105738), 'YYYYMMDDHH24MISS' )
   AND my_column <= to_timestamp( to_char(20150622105738), 'YYYYMMDDHH24MISS' )
 ORDER BY MYCOLUMN

Now, you could also create a function-based index

create index idx_my_fbi
    on mytable( TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') );

which your original query could use.

Of course, in either case, whether Oracle actually uses the index will depend on how selective it expects the predicate to be.  Since you are returning 4 years worth of data, it seems unlikely that an index would be beneficial even if it was eligible to be used.  Unless your table has hundreds of years worth of data in it, a table scan (or a full scan of one or more partitions if the table is partitioned) would seem like a more appropriate query plan.


Why do we not store names in a separate table? Practically, I would never store names for customers in a separate table.  But why not?  It's a one to many relationship (a person can have one name but that name can be used by multiple people).  Isn't it proper design to separate one to many relationships over multiple tables, or am I missing something?

No, it is absolutely not proper normalization to move a name out to another table.

Normalization reduces redundancy based on functional dependencies, not based on coincidental repetition of data values.

Let's say there are 100 Joe Smiths in your database.  If you move the name "Joe Smith" out to another table and have 100 records point at this name, what happens when one of the Joe Smiths decides to change his name to "Bob Smith"?  Unless they all change their names in unison, you can't work with the data that way.


If the query for database too complicated, does it means the design of Database is bad? I am new to database, and below is one of my query with it's explanation, I feel it's too complex. It works, but I am worried the design is bad.

Provide a list of customer information for customers who purchased anything written by the most profitable author in the database.

SELECT E.id,NAMES.fname, NAMES.lname, E.address, E.city, E.state,E.country,    E.email, E.phone, E.postalcode
FROM ENTITY AS E, (SELECT DISTINCT T.customer_id
               FROM (SELECT  DISTINCT O.order_id
                     FROM ORDERITEM AS O
                     WHERE O.bid IN (SELECT DISTINCT A.b_id
                                     FROM AUTHOR AS A
                                     WHERE A.name_id IN (SELECT DISTINCT NA.name_id
                                                         FROM (SELECT A.name_id, A.b_id
                                                               FROM (AUTHOR AS A JOIN NAMES AS N ON (A.name_id = N.id))
                                                               GROUP BY A.name_id) AS NA,(SELECT B.b_id AS bid, max(C.total_quantity * B.price), B.title
                                                                                FROM BOOK AS B, (SELECT bid, SUM(quantity) AS total_quantity
                                                                                FROM ORDERITEM AS O
                                                                                GROUP BY O.bid) AS C
                                                                                WHERE B.b_id = C.bid) AS BD
                                                         WHERE NA.b_id = BD.bid))) AS OID JOIN ORDER_TRANSACTION AS T ON (OID.order_id = T.order_id)) AS TID, NAMES
WHERE TID.customer_id = E.id AND
NAMES.id = E.name_id

And This is my design, look very simple.

The answer is: divide to conquer! Each sub-query should become a query (or a view) of its own. Use sub-queries sparingly. Use JOINs instead.

You would need at least one query (using JOINs) for:

AUTHOR
CUSTOMER
BOOK
...

Then you can test your queries or views independently (very useful if you are having trouble).


Building new version of a table and replacing the old one by rename, is it a good idea? We have a slightly heavy table which is semi-frequently (every ~5 minute) being recreated by a procedure. I wondered if there was something that could be done in order to minimize the downtime during the table rebuild, and have thought of two possibilities so far (let's call the table "Device"):

Option A
1) Create the new table in the procedure, name it Device_new
2) Rename old table from Device to Device_old
3) Rename new table from Device_new to Device

Option B
Same as Option A, except do it using Partition Switching.
Caveat: Partition switching is significantly more complex to setup, and has quite strict requirements for usage.

Are these options poor solutions, if yes, how would you solve this use-case? Thank you.

You mentioned that partition switching is more complex but that is not the case when the table is not partitioned.  One can use SWITCH to efficiently replace the entire contents of one non-partitioned table with another as a meta-data only operation.  The requirement for SWITCH is that the source and target table must have the same schema, indexes, constraints, and be storage aligned.  These requirements are presumably the same as the rename technique so the effort is similar.

Note that unlike table partitioning with a partition function and scheme, SWITCH of a non-partitioned table can be used in any SQL Server edition.

Below is a sample DDL script for option B:

CREATE TABLE dbo.Device(
      DeviceID int NOT NULL
        CONSTRAINT PK_Device PRIMARY KEY CLUSTERED ON [PRIMARY]
    , DeviceName varchar(30) NOT NULL
    );
CREATE INDEX idx_Device_DeviceName ON dbo.Device(DeviceName) ON [PRIMARY];
GO

CREATE TABLE dbo.Device_new(
      DeviceID int NOT NULL
        CONSTRAINT PK_Device_new PRIMARY KEY CLUSTERED ON [PRIMARY]
    , DeviceName varchar(30) NOT NULL
    );
CREATE INDEX idx_Device_new_DeviceName ON dbo.Device_new(DeviceName) ON [PRIMARY];
GO

CREATE PROCEDURE dbo.RefreshDevice
AS

SET XACT_ABORT ON;

BEGIN TRY

    TRUNCATE TABLE dbo.Device_new;

    --load dbo.Device_new with new data here

    BEGIN TRAN;

    TRUNCATE TABLE dbo.Device;

    ALTER TABLE dbo.Device_new
        SWITCH TO dbo.Device;

    COMMIT;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;

    THROW;

END CATCH;
GO


Order by where conditions SELECT * FROM table
WHERE
key REGEXP 'some long regular expression'
OR key REGEXP 'some other long regular expression'

ORDER BY
key REGEXP 'some long regular expression' DESC,
key REGEXP 'some other long regular expression' DESC

Is there a cleaner way to get the same result as above, without repeating the where conditions in order by?

Use a subquery, something like:

SELECT * FROM (
  SELECT
    t.*,
    key REGEXP 'some long regular expression' AS a,
    key REGEXP 'some other long regular expression' AS b
  FROM table t
  WHERE a OR b
)
ORDER BY a DESC, b DESC;


Coalesce on right hand table field with left join, or a better way? I have two SQL Server 2014 tables one has "en-GB" cultured names and descriptions, and a second has translations for other cultures.

The "en-GB" table...

CREATE TABLE [dbo].[HomePages]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Key]         NVARCHAR (25)  NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK.dbo.HomePage] PRIMARY KEY CLUSTERED ([Id] ASC)
);

The translation table:

CREATE TABLE [dbo].[HomePageTranslations]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [HomePageId]  INT            NOT NULL,
    [CultureName] NVARCHAR (5)   NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
    CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

There will always be values in en-GB table, but may or may not be equivalent values in the translation table. The query I am using should return the translated values if they exist or fall back on the en-GB values if they don't. The query is in the stored procedure here:

CREATE PROCEDURE [dbo].[HomePage_GetForKeyAndCulture]
(
    @Key NVARCHAR(25),
    @CultureName NVARCHAR(5)
)
AS
BEGIN
    SELECT      [base].[Id],
                [base].[Key],
                COALESCE(translation.[Name], base.[Name]) [Name],
                COALESCE(translation.[Description], base.[Description]) [Description],
                [base].[Active]

    FROM        [dbo].[HomePages]               [base]
    LEFT JOIN   [dbo].[HomePageTranslations]    [translation]
            ON  [translation].[HomePageId]  =   [base].[Id]
            AND [translation].[CultureName] =   @CultureName

    WHERE       [Key]                       =   @Key;
END

As you can see I am using a LEFT JOIN and the COALESCE function to achieve this. I know LEFT JOINS are not overly performant, so is there a more performant way to achieve this?

EDIT: Following advise tables have now had indexes added and look like...

The HOMEPAGE table...

CREATE TABLE [dbo].[HomePages]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Key]         NVARCHAR (25)  NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK.dbo.HomePages] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX.HomePages_Key]
    ON [dbo].[HomePages]([Key] ASC);

And the TRANSLATION table...

CREATE TABLE [dbo].[HomePageTranslations]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [HomePageId]  INT            NOT NULL,
    [CultureName] NVARCHAR (5)   NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
    CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_HomePageTranslations_HomePageIdCultureName]
    ON [dbo].[HomePageTranslations]([HomePageId] ASC, [CultureName] ASC);

no one has bothered with an answer

an index on [HomePages] [Key]

an index on [HomePageTranslations] [CultureName], [HomePageId]
and make it a unique constraint if appropriate

the query itself looks fine to me

you could probably just use a view and an index on [HomePageTranslations] [HomePageId] and have fine response
10s of thousands is not that much


How to fix CRC error when doing a SQL Server database backup? I'm in a pickle and any help would be greatly appreciated. I'm running our office's ERP Software (Acumatica) on a local server with Windows Server 2016 and SQL Server 2017.
Acumatica is currently running fine, we are not getting any errors whatsoever while using the app. We have 20+ employees constantly accessing Acumatica imputing data and so for. I have not receive any complains.
The problem that I'm having is that I'm getting an error when trying to backup the database using SQL Server Management Studio. When the backup process reaches 90% I get a SQL Server database cyclic redundancy check (CRC) error, and the backup fails.
I tried manually copying the .MDF and .LDF files to an external hard drive but the .MDF file fails just at the very end (99% with 120MB left to copy) with an error

Cannot read from source

I was able to copy the .LDF file. The .MDF file is around 140 GB in size and the .LDF file 180 GB.
What can I do to fix this and be able to do a full backup?.. The latest successful backup that I have is from 3 weeks ago. I really need to be able to backup the data before this gets any worse.
I have been thinking about running chkdsk with the repair command but I'm afraid it will make it worse and loose the database and/or data.
Any advice would be very highly appreciated.
Regards.
Carlos

I'm having is that I'm getting an error when trying to backup the database

That's potentially bad.

I have not done a Transaction Log Backup ... I'm in Full Recovery Model

That's actually good. Then backup the log.  Somewhere safe.

In my test server I restored the backup that I have from 3 weeks ago

Almost home.  On your test server restore that old full with NORECOVERY, then restore the log backup.  If that works, your data is safe.  And even if your database is corrupt you can restore from the old full, the big log backup, and a Tail Log backup from the production server.


How do I tactfully bring up database shortcomings? A little background:

I recently inherited responsibility for support and development of a database application after the original developers left. Looking through the application, the database appears to have several... issues.

Weekly shrink and re-index plan
Database account used for web application access has db_owner role
No foreign keys
All tables use GUID column as primary key
Queries built in C# are not parameterized

How can I bring these issues up with management?

Normally, this should be no trouble, but I have a problem: I have less than six months of SQL experience, whereas the managers and former developers were working on this project for several years.

Here's the line I like to use - it preserves everyone's dignity and lets everyone escape without finger-pointing:

"Yes, that used to be a best practice, and..."

It’s almost easier to explain that line in terms of what it DOESN’T do.  It doesn’t dispute the speaker’s claim, because you don’t want to go down the rathole of arguing about whether or not the point was right at the time.  You could spend the entire day arguing about why USB has the word Universal as part of the name, but that’s beside the point.  You need to get to the right solution as fast as possible, and sometimes that just means ignoring something that’s wrong.

It doesn’t chastise the other guy for not keeping up with the latest tips and tricks.  It’s hard enough to just do our jobs, let alone keep up with blogs and training, and real life interferes.  It lets the other person save face.

It doesn’t divide you against the other guy.  I try to use the word “we” whenever I use this line, because I want to take the other guy along with me on the journey to fix this problem.

I've written more about it in my Consulting Lines series.


How to summarize Bridge results? I am trying to summarize Bridge results for each pair (north/south pair and east/west pair).

Match points for pair north/south is MPNS, for pair east/west is MPEW. Is there a way to summarize all match points for pair from MPNS and MPEW given the following data?

Boards results for sample are:
board ns ew score MPNS MPEW
1     1  2  110     0   4
1     3  4  120     2   2
1     5  6  150     4   0
2     1  4  150     4   0
2     2  6  120     2   2
2     3  5  100     0   4
3     1  6  100     1   3
3     4  2  150     4   0
3     5  3  100     1   3

and how get result for pairs:

pair sum
  1     5
  2     6
  3     5
  4     6
  5     9
  6     5

Based on the comments, this is one possible solution:

select a.NS, Sum(a.MPNS) from
(select ns, MPNS from match_points
union all
select ew, MPEW from match_points) a
group by a.ns

which returns the required results.

SQLFiddle version of the data here - http://www.sqlfiddle.com/#!2/7e473/11


mySQL Master not getting data from slave I have followed the instructions on the Web on how to set up master-slave mySQL replication and I have spent a day and half trying to get it to work.

The master is simply not getting new data at all. I looked at phpMyAdmin and I see "replication" enabled on both systems - slave/master. But nothing shows up. The DB structure is identical on both systems.

It has never generated any errors. The slave is able to connect to the master as shown in show slave status command. The PROCEESSLIST showed no errors. I used the replicate_do_db set on slave. If I unplug the ethernet cable, it will generate error. Plug it back in, and it just does nothing (except that the error went way). I changed the password and it generated error as expected but put it back to right password and it just went silent with no errors.

It's so aggravating as it doesn't tell me WHY it's not updating at all or why it's not pushing data to master. I am under the impression that any new data that's entered in slave DB should automatically be replicated to the master and the master should be showing data. Is this correct?

Is there any way that I can find what went wrong considering that the replication setup tells no errors whatsoever?  How do I force the master to get data from the slave? Is there a way to do it?

I think you what you are describing is a replication topology called a Star Topology

I wrote about that before

Apr 18, 2013 : Single slave - multiple master MySQL replication (Approach #1)
Apr 28, 2011 : MySQL in star topology (ServerFault)
Apr 26, 2011 : MySQL in star topology (Stack Overflow)

In that event, the Central Server must serve as a Slave in MySQL Replication paradigm until all changes in the binary logs of the Master (outside server) are pulled and executed in the Central Server.

If you pull the plug, replication breaks. It will lose its place.

MySQL does have a setting to control this in the CHANGE MASTER TO command. By default, the retry interval for MySQL 5.6 is 86400 seconds. That's 24 hours. Before MySQL 5.6, it was 60 seconds. To change the retry interval simply run,

STOP SLAVE;
CHANGE MASTER TO MASTER_CONNECT_RETRY = 60;
START SLAVE:

CAVEAT

Even if you pull the plug, it is possible for replication to lose its place and not reconnect because of incomplete transmission of binlog events. When that happens, you must reset the replication coordinates manually.

Here is how to fix replication manually in this regard:

Step 1: STOP SLAVE;

Step 2: SHOW SLAVE STATUS\G

Suppose the display looks like this

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000255
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Step 4: Record Relay_Master_Log_File and Exec_Master_Log_Pos

In this case, 'mysql-bin.000254' and 858190247

Step 5: Use Replication Coordinates from Step 4

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000254',MASTER_LOG_POS=858190247;

Step 6: Start Replication

START SLAVE;

Give it a Try !!!


Find substrings within between 2 string fragments I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code.

CREATE OR REPLACE VIEW vw_actions AS
 SELECT ls.f_table_schema, ls.f_table_name,
 (SELECT substr(ls.attribute_actions_text,
 strpos(ls.attribute_actions_text, 'name="')+6,
 strpos(ls.attribute_actions_text, '"/>') -
 strpos(ls.attribute_actions_text, 'name="'))) AS actions
   FROM layer_styles ls;

The outcome is that it doesn't like minus numbers when using strpos. I can get it go forward 6 characters to remove the 'name="' from the returned substring but cannot remove the '"/>'.

It returns the following:

View SHED Database"/>

where I want it return:

View SHED Database

Any suggestions would be greatly appreciated.

ADDITION:I have found out that if I was using 9.1 I could have used strposrev and i think the following code would have worked:

CREATE OR REPLACE VIEW vw_actions AS
 SELECT ls.f_table_schema, ls.f_table_name,
 (SELECT substr(ls.attribute_actions_text::text,
 strpos(ls.attribute_actions_text::text, 'name="'::text)+6,
 strposrev(ls.attribute_actions_text::text, '"/>'::text)+3 -
 strpos(ls.attribute_actions_text::text, 'name="'::text))) AS actions
   FROM layer_styles ls;

Use substring() with a regular expression instead:

substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')

The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned.

Like your code, this selects the first string matching the pattern and does not look further.

Also, you don't need to make your expression a subquery, that adds nothing but overhead:

CREATE OR REPLACE VIEW vw_actions AS
SELECT ls.f_table_schema
     , ls.f_table_name
     , substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') AS actions
FROM   layer_styles ls;

Quick test case (you should have provided):

SELECT *, substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')
FROM  (
   VALUES
     ('bar name="View SHED Database"/> foo')
   , ('bar name="View SHED Database"/> fooname="View SHED Database"/>xx')
   , ('name="buntch a bull"/> fooname="View SHED Database"/>xx')
   , ('xxname="bla foo grr"/>')
   , ('')
   , (NULL)
   ) ls(attribute_actions_text)


Postgresql install extension I installed Posgresql version 10 (stable) through the source package (compiling manually) in the directory opt/pgv10/ .. on my OS LInux Debian Version 8.

I would like to use the HIPOPG extension 1, 2 to enable the creation of hypothetical indexes in postgresql.

I checked on document (link) you have to have the Dev package from postgresql, but I believe my installation does not have this (I think).

I tried to download the file extension .zip but can not proceed with the compilation and installation (with make and make install), because it gives the following message:
    make: pg_config: Command not found
    make: Nothing to be done for 'all'.

On .zip file that I downloaded has the files: HANGELOG.md hypopg - 1.0.0.sql hypopg.control hypopg_import.h META.json test typedefs.list expected hypopg.c hypopg_import.c Makefile README.md TODO.md

In tutorials on the internet, they talk to install via apt-get, but as I installed it manually I believe it will not work.

I tried to run the command:
   create extension hypopg; in psql program, but gave the message below even though I put the hypopg.control files in opt/pgv10/share/postgresql/extension/

ERROR: could not access file "$ libdir / hypopg": No such file or directory

Would anyone know how to manually install an extension and if it would have to have the -dev package and how would I install that package.

Best Regards
Neto

THe solution was:

$ export PATH=/opt/pgv10/bin:$PATH
-- go to hypopg directory
$ USE_PGXS=1 make install

Best Regards
Neto


Getting Running Total from user defined function I have the following query.

Select x1.TotalAmount, [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount,
       x2.DocumentType
FROM table1 X1
  INNER JOIN table2 x2 on t1.id = t2.t1id

Now how do I get the running total of self-declared TaxAmount? I wish to do something like this

Select x1.TotalAmount,
       SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount) OVER (PARTITION BY x3.InvId
          ORDER BY x3.createddate
          ROWS BETWEEN UNBOUNDED PRECEDING
          AND 1 PRECEDING) AS TaxAmount,
       x2.DocumentType
FROM table1 X1
  INNER JOIN table2 x2 on t1.id = t2.t1id

[ReturnTaxAmount] is a function that will return calculated decimal value.

This is the method I tried but failed which suspected is because of the table hasn't really being generated yet.

Is it possible to do so or must I create a new declared/temp table to store the table and select the table again to get the running total of it.

You got the syntax slightly wrong. It should be:

SELECT
    x1.TotalAmount,
    SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate))    -- no AS TaxAmount here
        OVER (PARTITION BY x3.InvId
              ORDER BY x3.createddate
              ROWS BETWEEN UNBOUNDED PRECEDING
              AND 1 PRECEDING) AS TaxAmount,           -- it's ok here
    x2.DocumentType
FROM table1 x1
  INNER JOIN table2 x2 ON t1.id = t2.t1id
--- the rest of the code, that joins x3, etc
  ;

The alias should be only once, after the whole column expression, which includes the SUM(...) OVER (...)

You could use a derived table or a CTE as your idea (no need for temporary table). It would be equivalent:

SELECT
    t.TotalAmount,
    SUM(t.TaxAmount)                    -- here we use the alias, defined in
        OVER (PARTITION BY t.InvId      -- the derived table, below
              ORDER BY t.createddate
              ROWS BETWEEN UNBOUNDED PRECEDING
              AND 1 PRECEDING) AS TaxAmount,
    t.DocumentType
FROM
    ( SELECT
          x1.TotalAmount,
          [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount,
          x2.DocumentType,
          x3.InvId,
          x3.createddate
      FROM table1 x1
        INNER JOIN table2 x2 ON t1.id = t2.t1id
      --- the rest of the code, that joins x3, etc
    ) AS t ;


Is it possible to write an SQL query that automatically reconciles/"transactionalizes" payments and charges I'm still working on the project mentioned here (http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system).

The system is to give the user the option to either pay specific amounts on specific charges, or make generic "you figure it out" payments.  Given the table structure we went with (PAYMENTS, CHARGES, PAYMENTS_TO_CHARGES) I'm looking to cheat a bit.  Basically I'm looking for an amazingly snazzy "reconcile"  SQL query that will do the following:

STEP 1) Grab all Payments with remaining balance (basically credits)

STEP 2) Grab all Charges with remaining balance (partially paid, etc)

STEP 3) Insert portions of payments in the PAYMENTS_TO_CHARGES table until there is no more available credit or there are no more charges.

…I guess technically this isn't reconciliation so much as creating transactional data, but you get the idea.

Steps 1 and 2 are obviously very easy.  It's Step 3 that's the killer.  If there's no snazzy way to do this in SQL, I suppose I'll go with the old hand-coded step-by-step loop-through-each transaction and post payment_to_charge…just thought I'd ask.

Thanks in advance!

EDIT 1:
I've come up with this query to determine which charges have remaining balance, but it's giving me an error saying "Unknown column 'remaining_balance' in 'where clause'":

SELECT
       charges.*
     , (charges.amount - transactions.total_paid) as remaining_balance
FROM charges
   , (SELECT
             charge_id
           , sum(amount) as total_paid
      FROM payments_to_charges
      GROUP BY charge_id) as transactions
WHERE charges.member_id = 123
  AND charges.id = transactions.charge_id
  AND remaining_balance > 0
  AND charges.active_on < NOW()

I'm sure certain items are simply out of order, but I can't figure out what's generally wrong with this particular query.  Should I be using HAVING instead of WHERE?  Am I missing something else completely obvious?

You might be able to do the INSERT into Payemnts_To_Charges in one SQL statement, but I'm not sure it would be worth it.  It seems like this would be easier to build, debug, and maintain in procedural code.  Something like this:

CREATE TABLE Payments (PaymentId Number(10), Amount Number(6,2));
CREATE TABLE Charges  (ChargeID Number(10), Amount Number(6,2));
CREATE TABLE Payments_To_Charges
   (PaymentID Number(10), ChargeID Number(10), Amount Number(6,2));

INSERT INTO Payments VALUES (1,4);
INSERT INTO Payments VALUES (2,4);

INSERT INTO Charges VALUES (1,2);
INSERT INTO Charges VALUES (2,5);
INSERT INTO Charges VALUES (3,6);
INSERT INTO Charges VALUES (4,4);
INSERT INTO Charges VALUES (5,10);

Declare
   vPaymentAmount Payments.Amount%Type;
   vAppliedAmount Payments_To_Charges.Amount%Type;
Begin
   For vPayment In (SELECT PaymentID, Amount FROM Payments) Loop
      vPaymentAmount := vPayment.Amount;
      For vCharge In (
            SELECT ChargeID, Amount FROM
            (
               SELECT ChargeID, Amount -
                  NVL((SELECT SUM(Amount) FROM Payments_To_Charges pc
                       WHERE pc.ChargeID = c.ChargeID),0) Amount
               FROM Charges c
            ) WHERE Amount > 0
         ) Loop
         vAppliedAmount := LEAST(vPaymentAmount, vCharge.Amount);
         INSERT INTO Payments_To_Charges (PaymentID, ChargeID, Amount)
            VALUES (vPayment.PaymentID, vCharge.ChargeID, vAppliedAmount);
         vPaymentAmount := vPaymentAmount - vAppliedAmount;
         If (vPaymentAmount = 0) Then
            Exit;
         End If;
      End Loop;
   End Loop;
End;
/

SELECT * FROM Payments_To_Charges;

Update:

remaining_balance is the issue.  You can't reference the aliased value in the WHERE clause.  You could either make the query a sub-query and add that condition on a higher level or change remaining_balance in the WHERE clause for (charges.amount - transactions.total_paid).


Cannot CAST '33.33' to INT I have encountered a problem where the WHERE clause is causing a problem.  The varcharColumn is of datatype VARCHAR and contains numbers as well as text.  The query looks to be averaging this column, but it has now hit a problem when a number has a decimal, e.g. 33.33.

SELECT AVG(CONVERT(INT,[varcharColumn]))
  FROM View
 WHERE [varcharColumn] > 0
   AND [varcharColumn] <> 'NA'
   AND CreatedDate > @StartDate AND CreatedDate <@EndDate

Now, I have tried to CAST and CONVERT the varcharColumn in the WHERE clause, but this returns no results.  Any ideas?

Try this

    SELECT AVG(cast(cast([varcharColumn] as decimal) as int))
      FROM View
     WHERE isnumeric([varcharColumn]) = 1
     AND cast(cast([varcharColumn] as decimal) as int) > 0
     AND CreatedDate > @StartDate AND CreatedDate < @EndDate


Location of RMAN backup I have two servers. One is DB server another is RMAN Catalog DB server. From my RMAN Catalog, I connect to my remote DB server and I run the backup. Where will the backup files be stored? Will it be in my RMAN catalog db server or the source db?

The backup operation will run on the database server, and the backup pieces may be created on the database server, attached storage disk, a backup server through a 3rd party library (Networker, TSM, etc.), or any other server through network shares (NFS, samba), depending on your configuration.


Execution plan push sort operator to avoid too many PK lookups For a query like this:
select top 40 ColumnNotInIdexCausingPKLookup from table
where ColumnIncludedInIndex=A
order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex

Any way to push up a sort/filter operator, so the PK lookup isn't that heavy (40 records vs 4.797)?  All columns that are being used in the filter/sort are in the index "included column" list so there is enough information in the index to not have 4797 PK lookups
+- 20K reads

If i change the index so the columns are in de index fields instead of the "include" fields then the execution plan does filter first, using the index itself. However, that only works with that specific set of orderby/filterby. Which changes if the user filters/sorts on another field.
+- 700 reads

SQL Version: Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64)   Nov  2 2020 19:19:59   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 18363: )
Steps to reproduce
create database ExecutionPlanTestDB
go
use ExecutionPlanTestDB

create table ExecutionPlanTest (
    id int not null identity(1,1) primary key,
    ColumnInIndex bigint,
    ColumnInIndex_Include bigint,
    ColumnNotInIndex bigint,
)

insert into ExecutionPlanTest(ColumnInIndex,ColumnInIndex_Include,ColumnNotInIndex)
select
    top 10000000
    o1.object_id+o2.object_id+o3.object_id+o4.object_id,
    o1.object_id+o2.object_id+o3.object_id+o4.object_id,
    o1.object_id+o2.object_id+o3.object_id+o4.object_id
    from sys.objects o1
inner join sys.objects o2 on 1=1 and o2.object_id<100
inner join sys.objects o3 on 1=1 and o3.object_id<100
inner join sys.objects o4 on 1=1 and o4.object_id<100
where o1.object_id<100

create index IX_EPT_ColumnInIndex on ExecutionPlanTest(ColumnInIndex) include(ColumnInIndex_Include)

-- execute with execution plan from here
select top 5 ColumnNotInIndex from ExecutionPlanTest
where ColumnInIndex=307 --should have +-2k results
order by ColumnInIndex_Include

Query plan: https://www.brentozar.com/pastetheplan/?id=S1odVbOgu
One way to solve this seems to be changing it to this structure. Although I would rather not convert all the queries like this. It seems to be only logical that sql server first sorts/filters/tops the data in the index itself before sending it further in the execution plan?
select ColumnNotInIdexCausingPKLookup  from table where tableID in (
    select top 40 tableid from table
    where ColumnIncludedInIndex=A
    order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex
)

This results in the better plan (+-700 reads now instead of 20k)

I think you answered your own question, SQL Server will only use an index for sorting if the columns are in the index definition keys (the sorted part) not in the included column list (as then the values are not sorted). If your users can choose one of multiple sorts you will need multiple supporting indexes, and some carefully written queries.


What steps should I perform to prepare a MDF for distribution as a (mostly) read-only database? I am on a team that develops a product that uses several SQL Server databases. Some of them are mostly read-only. Tables do not get changed, but some views and stored procedures get changed as a way of configuring the app. These views lack indexes. They are not materialized views. Changes to the tables only happen during upgrades and involve shipping the clients new MDFs that are attached. What steps should I perform to put these databases in a "ship ready" state? UPDATE Note that there is a LOT of free space in these MDFs, therefore a shrink followed by re-indexing.

We are doing the following currently:

Databases are in simple recovery mode (and we generally recommend keeping the original copy of the MDFs as the backups anyway)
We make the databases owned by sa and remove all logins (the deployment process adds appropriate logins)
We run DBCC SHRINKDATABASE on the databases
We run Ola Hallengren's Integrity and Maintenance scripts:

EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'DB_IN_QUESTION',
    @CheckCommands = 'CHECKDB',
    @ExtendedLogicalChecks = 'Y',
    @TabLock = 'Y'
GO

EXECUTE dbo.IndexOptimize
    @Databases = 'DATABASE_IN_QUESTION',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @SortInTempdb = 'Y',
    @MaxDOP = 0
GO

Should we use other parameters for Ola's scripts? Should we take any other steps? Making the database read-only would be a pain because the views and stored procedures are often modified by the end user.

If you want to distribute a copy of your database, why don't you use LocalDB?

Shrinking the data file causes very high fragmentation. Running index maintenance grows the file back out. Why are you shrinking it?

Furthermore, if you're only going to ship the mdf file, why are you using DBCC SHRINKDATABASE? SHRINKDATABASE calls DBCC SHRINKFILE for every file in the database. If you're not shipping the log file, why are you shrinking it at all? Seems like a waste of time.

Delivering just an mdf file is not a supported method for sharing a database. There's always a risk that the file cannot be reattached. Shipping a backup or at the very least the full set of files is the best way to ship a database.


Oracle 11gR2 expdp table converted to CSV from Linux With Oracle expdp,  I can export the tables I want with

expdp INCLUDE=TABLE:table1,table2,table3

I need a way to convert this into CSV on a linux machine. I've seen Windows tools but don't know how good they are and this needs to work from linux command line/ bash script.

Is this possible?

No its not possible with expdp, you need to use utl_file to solve this

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:88212348059


Find Sessions Connected by IP Is there anyway to see which address connections are connecting on?

More info:

I've repeatedly found people setting up connectionstrings using IP instead of by DNS name, and with SQL2012 and above, connecting to instances by server name without including the instance name (i.e. where this is on port 1433).
I'd like to audit this so that within hours of something having connected using a non-compliant connection string, I can be alerted and so quickly find the culprit and fix the issue; as opposed to waiting for changes in infrastructure (e.g. us moving to a non-standard port, or switching to a different host on a different IP whilst maintaining the same underlying DNS entry) to cause more serious surprises as a result of non-compliant configurations.

Try this:

SELECT session_id, client_net_address
    FROM sys.dm_exec_connections


Very tricky: Can't update a sequential list SQL-92 ( Filemaker) I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months:

There is a column "SortID" that needs to get updated with sequential integers, starting from 1. The column may look like this (without ORDER BY):

SELECT SortID FROM Beleg

2
3
.5
4
5
9
10
10.000001
10.000002
11
13

and needs to look like this.

2
3
1
4
5
6
7
8
… and so on

I have tried everything that I can think of, but no luck.

I am using the Execute SQL function from within Filemaker and I can just use plain SQL-92, nothing fancy.

For example, the following tricks all don't work (for example: Update existing rows with sequence number…). All produce syntax errors:

ALTER TABLE MyTable ADD MyColumn int IDENTITY(1, 2) NOT NULL

and:

ALTER TABLE MyTable ADD MyColumn uniqueidentifier NOT NULL
    CONSTRAINT DF_MyTable_MyColumn
        DEFAULT (NEWSEQUENTIALID())

and (no variables allowed):

DECLARE @id INT
SET @id = 0
UPDATE X
SET @id = cn = @id + 1
GO

and (ROW_NUMBER () doesn't work):

update T
set cn = rn
from (
    select cn,
        row_number() over(order by (select 1)) as rn
    from TableX
    ) T

and (this gets close, but no):

update invoice set RecNo = (next value for seq_invoice_recno)
where invoiceid in (select top 100000 invoiceid from invoice where RecNo is null
order by invoiceId)

. . .
However, I have come a little closer: I can use a Filemaker function "RecordNumber" that returns a sequential number for each line. Using this statement, I can get the correct order and get the correct integer for that line:

SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

.5  1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10  10
10.00001    11
10.00002    12

But when I try to write the values, I get an error, as soon as include the ORDER BY statement:

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

=> ERROR

Only, when I remove the ORDER BY, the values are written — but in the wrong order!

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210

1
2
3
4
5
6
7
8
… and so on

The SortID "1" should have been written into line 3 and the "2" into the first line, the "3" into the second line, like this:

2
3
1
4
5
6
7
8
… and so on

So close, but still no luck.

Does anyone have any idea?

Thanks a lot!

Gary

Here is another approach, which, however, is likely to perform worse than the other suggestion:

UPDATE
  Beleg
SET
  SortID = (
    SELECT
      COUNT(*)
    FROM
      Beleg AS Beleg2
    WHERE
      Year ( Beleg2.Valuta ) = Year ( Beleg.Valuta )
      AND Beleg2.Ursprungskonto = Beleg.Ursprungskonto
      AND Beleg2.SortID <= Beleg.SortID
  )
WHERE
  Year ( Valuta ) = 2016
  AND Ursprungskonto = 1210
;

On the plus side, not only does this method reset SortID values in a single step, it can also do that for multiple (Year(Valuta), Ursprungskonto) subsets at once, if need be. That is, you can specify multiple values for either attribute in the main WHERE clause, e.g. like this:

WHERE
  Year ( Valuta ) BETWEEN 2011 AND 2016
  AND Ursprungskonto IN (1210, 2101, 1012)

or even just remove the WHERE clause altogether – and each subset will be re-numerated separately from the others, all with the single query.

Important note: this method assumes that SortID values are unique across any single (Year(Valuta), Ursprungskonto) subset.


How do I restore a SQL Server 2000 database to a SQL Server 2008 instance? How do I restore a SQL Server 2000 database to a SQL Server 2008 instance?


Distribute n questions to m students with all students having the same questions but in different orders I am building an online exam platform with PHP and MySQL. I have the following requirements for the database:

The exam questions are of MCQ type and there are 20 questions.
There are 30 students taking the test.

Is there a way I can have these 30 students receive the 20 questions but in different orders? E.g. that question 1 for Student A would be the question 16 for Student B?

TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) thought processes!

Update: there's an alternative answer provided below.

This proved to be quite tricky. To solve this, I did the following (see fiddle here)

I created tables:

CREATE TABLE test_question
(
  tq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  q_text VARCHAR (256) NOT NULL
);

the text of the questions doesn't really matter for the purposes of this demonstration:

INSERT INTO test_question (q_text) VALUES ('q1');
INSERT INTO test_question (q_text) VALUES ('q2');
INSERT INTO test_question (q_text) VALUES ('q3');
INSERT INTO test_question (q_text) VALUES ('q4');
INSERT INTO test_question (q_text) VALUES ('q5');
INSERT INTO test_question (q_text) VALUES ('q6');

Then the assigned question part:

CREATE TABLE assigned_question
(
  aq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  st_id INTEGER,  -- Student id
  qu_id INTEGER,  -- Question id
  q_text VARCHAR (256) -- text of the question
);

Then, to generate a random list of student_question combinations, i.e. filling the assigned_question table, I used two RECURSIVE WITHs (or CTEs):

INSERT INTO assigned_question (st_id, qu_id)
(
  WITH RECURSIVE student AS (
    SELECT 1 AS s_no
    UNION ALL
    SELECT s_no + 1 AS value
    FROM student
    WHERE student.s_no <= 5 -- 6 students
),
question AS (
    SELECT 1 AS q_no
    UNION ALL
    SELECT q_no + 1 AS value
    FROM question
    WHERE question.q_no <= 4 -- 5 questions
)
SELECT * FROM student
CROSS JOIN question
ORDER BY s_no, RAND()
);

The important bit here is the RAND() function which randomly orders the question part of the result set. You assign a student a random student number and then tell them to select from the master table according to their id number.

Now, I can't guarantee that every student will have a completely different set of questions, but the majority of them should have a different list of questions - if you don't allow them to speak to each other and tell them to answer all questions, in order, even if the answer is blank, then you have a reasonable way of checking that they were answering their own set of questions. The few students who do have matching question lists won't be able to communicate anyway!

then, join this assigned_question table to the test_question table thus:

SELECT aq.aq_id, aq.st_id, aq.qu_id, tq.q_text
FROM assigned_question aq
JOIN test_question tq
  ON aq.qu_id = tq.tq_id;

Result (sample, see fiddle for full results):

aq_id   st_id   qu_id   q_text
    1       1       2       q2
    2       1       5       q5
 ... results omitted for brevity
    6       2       4       q4
    7       2       3       q3

So, we can see that student 1 will have to answer questions 2 and 5 first whereas student 2 will be answering questions 4 and 3 first (full results below).

This is probably the best you can do in pure MySQL.

Update - single pass query:

I've "fiddled" with this a bit more (pardon the pun! :-) ) and it can in fact be done in one pass, as follows:

(Aside: also see this more elegant fiddle which more closely resembles my second answer).

INSERT INTO assigned_question (st_id, qu_id, q_text)
SELECT s_no, q_no, q_text FROM
(
SELECT * FROM
(
  WITH RECURSIVE student AS (
    SELECT 1 AS s_no
    UNION ALL
    SELECT s_no + 1 AS value
    FROM student
    WHERE student.s_no <= 5 -- students - 6 questions
),
question AS (
    SELECT 1 AS q_no
    UNION ALL
    SELECT q_no + 1 AS value
    FROM question
    WHERE question.q_no <= 4 -- students - 5 questions
)
SELECT * FROM student
CROSS JOIN question
ORDER BY s_no, RAND()
) AS z
JOIN test_question tq ON z.q_no = tq.tq_id) AS a
ORDER BY a.s_no, RAND();

Result:

aq_id   st_id   qu_id   q_text
    1       1       3       q3
    2       1       2       q2
    ... results again omitted for brevity...
    6       2       4       q4
    7       2       3       q3

So, student 1 will answer questions 3 and 2 first and student 2 will answer 4 and 3 first. On inspection, the results I generated with this last fiddle show that every one of the 6 students has the first 2 questions different, so it appears to be working well. Remember however that the RAND() function does not guarantee this and there may be students with the same question order - but with 30 students and 20 questions, this is highly unlikely! HTH! p.s. welcome to the forum! :-)

Alternative Answer:

What you can do is "stagger" (see explanation of results below) the questions asked of the students. All the necessary DDL table construction and DML data is at the bottom of the post - I'll just go through the "core" of the SQL below. Fiddle available here.

SELECT
  ROW_NUMBER() OVER () AS "Num",  -- all records - not strictly necessary
  t.s_id,
  CONCAT
  (
    t.q_id,
    CASE
      WHEN t.q_id % 100 BETWEEN 11 and 13 THEN "th"
      WHEN t.q_id % 10  = 1               THEN "st"
      WHEN t.q_id % 10  = 2               THEN "nd"
      WHEN t.q_id % 10  = 3               THEN "rd"
      ELSE "th"
    END
  ) AS "Q_ID_2",
  ((s_id + q_id - 2 ) % (SELECT COUNT(*) FROM question )) + 1 AS "Question_id",
  q.question_text
FROM test t
JOIN question q ON ((s_id + q_id - 2 ) % (SELECT COUNT(*) FROM question )) + 1  = q.question_id
ORDER BY s_id, q_id;

The key bit here is the modulus (%) operator and adding the s_id and q_id. The whole CONCAT bit to get the ordinals is just showing off! :-) Ordinal snippet found here.

Results:

Num     s_id    Q_ID_2  Question_id     question_text
  1        1       1st            1     question_1_txt
  2        1       2nd            2     question_2_txt

So, as we can see, the first student's first question is question_id 1 which is question_1_text. "So what?", you might say, that's not what the original question was aksing!

But, when we look at the 2nd student's questions we get this:

Num     s_id    Q_ID_2  Question_id     question_text
  9        2       1st            2     question_2_txt
 10        2       2nd            3     question_3_txt

Now, the 2nd student's first question is question_id 2, i.e. question 2 which is not the same as his neighbour's 1st question - which is what the OP asked for!

The cool thing is, if we look at the "interface" between, say students 4 and 5, we get this:

Num     s_id    Q_ID_2  Question_id     question_text
 31        4       7th            2     question_2_txt
 32        4       8th            3     question_3_txt
 33        5       1st            5     question_5_txt
 34        5       2nd            6     question_6_txt

The last questions answered by student 4 are 2 & 3 - i.e. the questions have "wrapped around" - so no student misses any question.

This was really just an intellectual exercise. For (close to) true randomness, my first answer is better - the only advantage I can think of is that it would make distributing the papers easier - all the invigilators (people who supervise exams) would have to do is move one sheet to the back at each new desk. Hope you enjoyed! :-)

=============================== DDL AND DML ================

CREATE TABLE question
(
  question_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  question_text VARCHAR (30) NOT NULL
);

INSERT INTO question (question_text) VALUES ('question_1_txt');
INSERT INTO question (question_text) VALUES ('question_2_txt');
INSERT INTO question (question_text) VALUES ('question_3_txt');
INSERT INTO question (question_text) VALUES ('question_4_txt');
INSERT INTO question (question_text) VALUES ('question_5_txt');
INSERT INTO question (question_text) VALUES ('question_6_txt');
INSERT INTO question (question_text) VALUES ('question_7_txt');
INSERT INTO question (question_text) VALUES ('question_8_txt');

CREATE TABLE student
(
  student_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR (50) NOT NULL,
  UNIQUE (student_name)
);

INSERT INTO student (student_name) VALUES ('student_1');
INSERT INTO student (student_name) VALUES ('student_2');
INSERT INTO student (student_name) VALUES ('student_3');
INSERT INTO student (student_name) VALUES ('student_4');
INSERT INTO student (student_name) VALUES ('student_5');
INSERT INTO student (student_name) VALUES ('student_6');

CREATE TABLE test
(
  test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_id INTEGER NOT NULL,
  q_id INTEGER NOT NULL
);

INSERT INTO test (s_id, q_id)
SELECT * FROM (
SELECT s.student_id, tab1.question_id
FROM student s
CROSS JOIN (SELECT q2.question_id FROM question q2)
AS tab1) AS tab2
ORDER BY student_id, question_id;


Finding integer database columns that may have their data type changed to reduce size I have a database schema that I've inherited where all the integer columns have been defined as int but because of business rules many could be replaced by tinyiny and other data types that would require less storage. While I don't expect much of a performance improvement by using the smaller types my reasons for wanting to do so are:

The application uses SQL Server Express so I'd like to make the most of the 10GB database size before needing to purge data.
The application is shipped with a full initial copy of the database and updates are synced using the Microsoft Sync Framework. Some client PCs are on relatively slow Internet connections so the smaller the database size the better.

I was wondering if there was some way to quickly identify columns that may be 'oversized'? I realize that the results will need to be carefully reviewed to make sure that the column will never exceed the limits of the new data type.

The following stored procedure will help identify those columns. It starts by creating a temporary table that stores the minimum and maximum values that each integer type can hold and then runs a dynamic query to find the minimum and maximum value for every table / column that defines one of those integer types. Once the min/max values have been determined it looks up the smallest type that can hold the value current values and gives a result such as the following:

table_name      column_name         current_type    min_val max_val proposed_type   proposed_min    proposed_max    space_saved
RideLegVetting  RideNumber          int             1       21      tinyint         0               255             2025654
RideLegVetting  LegNumber           int             0       99      tinyint         0               255             2025654

For each column it shows the curent type and min/max values found in the table along with a new proposed type and the min/max value it can hold. Based on the cardinality it also indicates an estimate of the amount of space that will be saved. The stored procecedure is as follows:

CREATE PROCEDURE OptimizeIntSizes AS
BEGIN
    SET NOCOUNT ON
    CREATE TABLE #DataTypeRanges
    (
        name nvarchar(128),
        size tinyint,
        low bigint,
        high bigint
    )
    INSERT INTO #DataTypeRanges VALUES (N'tinyint', 1, 0, 255)
    INSERT INTO #DataTypeRanges VALUES (N'smallint', 2, -32768, 32767)
    INSERT INTO #DataTypeRanges VALUES (N'int', 4, -2147483648, 2147483647)
    INSERT INTO #DataTypeRanges VALUES (N'bigint', 8, -9223372036854775808, 9223372036854775807)
    CREATE TABLE #Results
    (
        table_name nvarchar(128),
        column_name nvarchar(128),
        current_type nvarchar(128),
        min_val bigint,
        max_val bigint,
        proposed_type nvarchar(128),
        proposed_min bigint,
        proposed_max bigint,
        space_saved bigint
    )
    DECLARE @table_name nvarchar(128)
    DECLARE @column_name nvarchar(128)
    DECLARE @current_type nvarchar(128)
    DECLARE @proposed_type nvarchar(128)
    DECLARE @low bigint
    DECLARE @high bigint
    DECLARE @size tinyint
    DECLARE @cardinality bigint
    DECLARE @min_val bigint
    DECLARE @max_val bigint
    DECLARE @proposed_min bigint
    DECLARE @proposed_max bigint
    DECLARE @proposed_size tinyint
    DECLARE @space_saved bigint
    DECLARE @sql nvarchar(max)
    DECLARE @params nvarchar(max)
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE FindCursor CURSOR STATIC FOR
        SELECT tables.name AS table_name, cols.name AS column_name, types.name, dtr.size, dtr.low, dtr.high
        FROM sys.columns cols
            JOIN sys.tables tables ON tables.object_id = cols.object_id
            JOIN sys.types types ON types.system_type_id = cols.system_type_id
            JOIN #DataTypeRanges dtr ON dtr.name COLLATE DATABASE_DEFAULT = types.name COLLATE DATABASE_DEFAULT
    OPEN FindCursor
    FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'SELECT @cardinality_out = COUNT(1), @min_val_out = MIN([' + @column_name + ']), @max_val_out = MAX([' + @column_name + ']) FROM [' + @table_name + ']'
        SET @params = N'@cardinality_out bigint OUTPUT, @min_val_out bigint OUTPUT, @max_val_out bigint OUTPUT'
        EXECUTE sp_executesql @sql, @params, @cardinality_out = @cardinality OUTPUT, @min_val_out = @min_val OUTPUT, @max_val_out = @max_val OUTPUT
        SELECT TOP 1 @proposed_type = name, @proposed_size = size, @proposed_min = low, @proposed_max = high
            FROM #DataTypeRanges
            WHERE @min_val >= low  AND @max_val <= high
            ORDER BY size
        IF @proposed_type <> @current_type AND @max_val IS NOT NULL
        BEGIN
            SET @space_saved = (@size - @proposed_size) * @cardinality
            INSERT INTO #Results VALUES (@table_name, @column_name, @current_type, @min_val, @max_val, @proposed_type, @proposed_min, @proposed_max, @space_saved)
        END
        FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    END
    CLOSE FindCursor
    DEALLOCATE FindCursor
    SELECT * FROM #Results ORDER BY space_saved DESC
END


Disable TTL thread in MongoDB Is there any way to disable TTL deletion thread in mongoDB?

I know that you can modify the ttl index expireAfterSeconds value to a high number in order to "technically" stop ttl from deleting, but i am interesting in disabling the ttl thread completely for some time and enable it later.

Thanks in advance

You can use the admin command:

db.adminCommand({setParameter:1, ttlMonitorEnabled:false});

To re-enable, use the same command with true.


Unable to access sql database with login and password I'm writing Windows Service in C# and inside of it I'm unable to connect to the database.
This is the question I've asked recently:
SqlException: Cannot open database "path\db.mdf" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'

What I've done so far:

1) In server properties set flag "Identity verification with Sql Server and Windows"
2) Created login for sql server. Added user to the database with that login.
3) Added all available roles to this login.

Part 1:
If I connect to the server using Sql Server 2012 Identity Verification and than trying to attach database that is in some folder I get:

CREATE FILE encountered operating system error 5(Access is denied.)
while attempting to open or create the physical file 'path\db.mdf'.
(Microsoft SQL Server, error: 5123)

But I can attach the database if it's in this folder:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

That's in SSMS.

Part 2:
What do I get when trying to attach database from Visual Studio 2012:

The attempt to attach to the database failed with the following information:
Login failed for user test_user

If I use this connection string in my code:

<add name="ConnectionName"
         connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;
     provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\v11.0;
     attachdbfilename=&quot;path\db.mdf&quot;;
     connect timeout=30;
     User Id=test_user;Password=1;
     MultipleActiveResultSets=True;
     App=EntityFramework'"
         providerName="System.Data.EntityClient" />

I get from my Windows Service app (written in C#) the following exception:

EntityException: The underlying provider failed on open.

Inner excetion: SqlException: Login failed for user test_user.

I'm sure I typed the login properly. I get this error whether the database is in MSSQL\DATA folder or not.

There is something wrong with the permissions to access some folders on my computer, but I don't know why, I've given this user all the permissions!

Also, one time after computer restart the windows service started properly and was able to connect to the database, but after some time it lost this ability. And I don't know why.

Please help, I'm struggling with this issue for the last 4 days. Thanks!

The error indicates that the SQL Server Service does not have permissions to the folder that the mdf file is located in.

Since SQL Server 2008 the account running SQL Server has by default a minimal permissions set. If you want it to access files in non default directories, you need to explicitly grant access to that location.

In SQL Server 2012 things are complicated even more by Service Isolation.

In 2008 you can just grant access to the folder to the account is setup to start the sql server service. You can find that account in the SQL Server Service Manager.

In SQL 2012 you need to grant access to an account that looks like this: NT SERVICE\MSSQLSERVER or like NT SERVICE\MSSQL$InstanceName.

In your case, as you are using a default instance of SQL Server 2012, granting NT SERVICE\MSSQLSERVER to have write access to the path that contains db.mdf should make things work.