Reattaching MDF files recovered from RAID 5 We had been running a SQL Server 2005 server for many years at our company when just recently, we ran into some trouble.  The server was configured with RAID 5 and had one hot spare disk in the array.  One of the disks failed, and then a second one failed shortly thereafter.  Unfortunately we did not have backups of much of this data (please spare me the "should have had backups" comments -- I am aware of the importance of a solid backup plan).

So we are two disks down, we have no official DBA, and the database is inaccessible.  One of our IT personnel attempted to recover the RAID array and we believe the RAID began rebuilding but he was ordered to shut it down because there was concern for potential data loss during the RAID rebuild.

We sent the entire server to a data recovery company.  They spent two weeks working on the server, first by imaging all the drives and then by reconstructing the data from the RAID.

I received a hard drive that contains all the "recovered" files.  This is a collection of mostly database files (MDF) and trans logs (LDF).  Each database that was on the server consisted of only a single MDF and a single LDF.  I do not currently have access to the original server, so I have been attempting to attach these databases to another server running SQL Server 2005.  However, when I try to reattach any of the databases I run into problems.  Some of the various errors I have received are shown below.  I have had no luck restoring any of these files.  I followed the steps outlined in this article, and even then was unable to restore any of the files.

We paid a large sum to the data recovery company for the recovery of the data from this server and I have been tasked with proving without a doubt that the data are or are not useful to us.  I simply don't have the experience to know for certain, so I put the question to you, dba.stackexchange:  Is there any way at all to restore these files?

  SQL Server detected a logical consistency-based I/O error: incorrect
  pageid (expected 1:134; actual 0:0). It occurred during a read of page
  (1:134) in database ID 17 at offset 0x0000000010c000 in file
  'G:\Data\Data201309.mdf'.  Additional messages
  in the SQL Server error log or system event log may provide more
  detail. This is a severe error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

  The header for file 'G:\Data\Data201112_log.ldf' is not a valid
  database file header. The PageAudit property is incorrect. (Microsoft
  SQL Server, Error: 5172)

  G:\Data\Data201005.mdf is not a primary database file. (Microsoft SQL
  Server, Error: 5171)

  An error occurred while processing the log for database 'Data201008'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe9b50f3a; actual: 0x162a0f84). It occurred during a read of page (1:511232) in database ID 17 at offset 0x000000f9a00000 in file 'G:\Data\Data200803.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
  Could not open new database 'Data200804'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

Is there any way at all to restore these files?

No.

The long answer is like this:

you first will have to fix the MDFs. Attempt to attach it with ATTACH_REBUILD_LOG
as you encounter errors (like incorrect pageid (expected 1:134; actual 0:0). It occurred during a read of page (1:134)) you have to edit the file and fix it. This, of course, requires you to know in fairly much detail the (undocumented) MDF format. Anatomy of  Page is a decent starting point, but it really just skims the surface. I really hope your MDF boot page is correctly 'restored'.
after you fix all the errors, try to evaluate the consistency of the MDF, if possible with a CHECKDB. Fix any error, some will require again manual repair.
in theory you can try to attempt to see how much of the recovered LDFs can be repaired and applied. I estimate this task something 10x the complexity of fixing the MDF, due to the complexities of the LDF format and because of the LSN chain requirement. I would abandon the idea of trying to rebuild the 'restored' log, just try to 'rebuild' the restored data.

You can use OrcaMDF to attempt to browse the MDFs 'cold' and see if OrcaMDF complains about structure issues, then attempt to fix them.

It is critical that the 'restore' process kept the MDF file size, and restores 0s for bad sectors. If they simply removed the unrecoverable sectors you stand no chance.


Creating logins mapping for a linked server without creating logins for AD users We use windows auth, but we don't create logins for particular AD users. We create logins for AD groups and the users have access by being their members.

I noticed that when I try to create a mapping for some AD users (not the groups) using a command like below, besides creating the mapping the DOMAIN\ADlogin login is created.

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'RemoteName',
  @locallogin = N'DOMAIN\ADlogin',
  @useself = N'False',
  @rmtuser = N'rmtuser',
  @rmtpassword = N'password'

If I drop the login the mapping will be dropped as well.

DROP LOGIN [DOMAIN\ADlogin]

Is there any way to avoid creating logins but get the mapping for AD users? It sounds for me like it's impossible but maybe somebody has good experience in solving this problem.

  Microsoft SQL Server 2017 (RTM-CU9) (KB4341265) - 14.0.3030.27 (X64)
    Jun 29 2018 18:02:47    Copyright (C) 2017 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2016 Standard 10.0
  (Build 14393: )

Is there any way to avoid creating logins but get the mapping for AD
  users?

If you run this code:

sp_helptext 'sp_addlinkedsrvlogin'

You'll be able to find a piece of code responsible for win login creation:

-- IF SPECIFIED CHECK LOCAL USER NAME (NO NT GROUP!)
select @localid = 0
if (@locallogin IS NOT NULL)
begin
    -- share-lock the local login
    EXEC %%LocalLogin ( Name = @locallogin ) . Lock ( Exclusive = 0 )
    IF @@ERROR = 0
        select @localid = principal_id from sys.server_principals
            where name = @locallogin and type in ('S', 'U')
    else
    begin
        -- ADD ROW FOR NT USER LOGIN IF NEEDED --
        if (get_sid('\U'+@locallogin) IS NOT NULL)
        begin
            EXEC @ret = sys.sp_MSaddlogin_implicit_ntlogin @locallogin
            if (@ret = 0)
                select @localid = principal_id from sys.server_principals
                    where name = @locallogin and type = 'U'
        end
    end
    if (@localid = 0)
    begin
        ROLLBACK TRAN
        raiserror(15007,-1,-1,@locallogin)
        return (1)
    end
end

Here sys.sp_MSaddlogin_implicit_ntlogin @locallogin creates the corresponding login and its principal_id will next be retrieved by this code:

select @localid = principal_id from sys.server_principals
                        where name = @locallogin and type = 'U'

(here U indicates WINDOWS_LOGIN)

And then it will be passed to create a mapping:

EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(
            LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

So the answer to your question is NO unless you map all your logins to the same remote_user by passing NULL as @locallogin.


linked server hanging We've been using a linked server to an access database for close to a year now with little issue.  Now when I run a select query against the linked server the query sits there and never finishes.  Killing the session results in the familiar transaction sitting in Rollback/Killed status until the service is restarted.

(SPID 153: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.)

Is there a way to clear out these select statements or perhaps run them such that they don't end up in the rollback/killed status?

Has anyone else seen this sort of issue before?  What did you do to get around it?

I believe I've encountered this sort of problem before, primarily in dealing with Excel.  If it's anything like what I'm referring to, it's probable that during your connection to the Access database you are receiving some form of UI prompt or otherwise "waiting for input" type of event.  The root cause of this could be a number of things, but most likely is that the Access database is being held open by a separate process and the linked server connection attempt is being "warned" about it.

When you run KILL against the spids on the SQL Server 2008 R2 server, I expect you get something along the lines of:

  Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

If that's the case, you will be able to clear these "ghost" sessions by also terminating the pid on the other side of the connection.  Go to the server where the Access database exists and run a tasklist command from the console ( or use the task manager or whatever makes you happy ).  If you have a number of these "ghost" spids, you'll likely notice a number of Access processes as well ( MSACCESS.EXE, [pid], blah blah ).  You can remove these processes by using taskkill /IM "MSACCESS.EXE" /F from the console, or if you're feeling cautious, taskkill /PID [pid] /F.  The termination of these destination processes should allow the "ghost" spids on the server to resolve.

Assuming this is the problem, you'll probably find that your SELECT commands will function as they have in the past as well.


MongoDB Reconnects to Secondaries Too Fast How can MongoDB 4.0.12 be configured to SLOW its rate of re-connecting to its replica members when they are down?

I had MongoDB replica members die. The online replica members get “connection refused” when trying down members, then re-attempt to connect as fast as the CPU and network allow. This crushes the CPU and makes the log files grow huge as Mongo retries it’s connection hundreds of time ps per minute.

I'd rather MongoDB retry to reach replica members only once per minute, rather than immediately after getting a failure. There’s no reason to retry a down server hundreds of times per minute.

Is there a way to configure MongoDB to wait for some seconds before attempting a reconnect when a replica member is down? I can't find any such setting in the server & replica settings, and this seems like it would be an important thing to set.

I don't have a specific answer, however, you can consider the following. Once a member is crashed, you can change it to be hidden. Once it is hidden, clients will not try to connect. Once it is up, let it complete the data from the oplog and change it back.


Determine cause of "MySQL server has gone away" I've got a long running process on a headless server. It is a C program that monitors a modem for Caller Id and hangs up on telemarketers. On startup the program opens a connection to a MariaDB 10.3 database on the local machine.

Each morning at 4:00 AM a separate C program fetches the latest data file from the FTC. It includes about 100,000 complaints from the previous day. I add them to the database, so a blacklist table adds 50,000 to 60,000 rows. The current table has about 600,000 rows.

At 9:00 AM when the calls start my queries fail with the message shown below. It is a debugging trace of the C program. It includes Error: MySQL server has gone away.

I checked dmesg and /var/log but there is no indication of the reason or the problem. I can't find it logged anywhere. It also appears the OOM killer did not kill the MariaDB server.

# cat /var/log/mariadb/mariadb.log
#
# dmesg | grep -iE 'maria|mysql|oom'
#

How can I begin to troubleshoot this problem?

I can find lots of posts regarding MySQL server has gone away (apparently this is a chronic problem that has never been fixed). For example, here and here. They are dev-2-dev "try this" answers and lack troubleshooting to determine the root cause.

Debug: ===== new call =====
Debug: ring count: 1
Debug: modem_read
Debug: Read 68:
"DATE = 0308
TIME = 0936
NMBR = 4103174745
NAME = SPAM? PASADENA"
Debug: Ring: 0, Caller Id: 1
Debug: call_read_name
Debug: name: SPAM? PASADENA
Debug: call_read_number
Debug: Number: 4103174745
Debug: database_number_is_whitelist
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in whitelist
Debug: database_number_is_blacklist_lcl
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in local blacklist
Debug: database_number_is_blacklist_ftc
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in FTC blacklist
...

I had a similar issue to this, and it turned out to be a connection time out. My program sets up a connection pool, and threads grab a connection from that pool when they need to make queries. Every morning when I would come in and try to run my program I would get the MySQL Server has gone away error. A lot of solutions I found said to increase the wait_timeout field in my.cnf, but I found in the C API you can enable automatic re-connection like this:

// set up your connection

// enable automatic reconnect
bool reconnect = 1;
mysql_options(&connection, MYSQL_OPT_RECONNECT, &reconnect);

From the docs:

  The MySQL client library can perform an automatic reconnection to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed. If auto-reconnect is enabled, the library tries once to reconnect to the server and send the statement again.

This gave me a hard time for a while as it was pretty hard to troubleshoot, so hopefully this is helpful to somebody else!

Source: C API Automatic Reconnection Control


How really big applications (such as facebook) scale their database I am curious of how one will scale huge database for example facebook DB. What I know is the application is deployed somehow to multiple data centers which means that each "node" in each data center should have fast access to that big DB.

On first place I can not image how they do deploys but that is another question (I guess?).

So if I am not wrong the question is how so many separate nodes access huge database and everything is consistent, stable, well-performing and scalable?

These companies use a mix of commercial products and new technologies developed in-house. Many are documented. Several are open source. The larger companies maintain separate research organisations to bring along the next generation of products. Many papers are available and make for interesting reading.

Broadly, the NoSQL movement was driven by these organisations' needs that could not be met by products available at the time. Cassandra, DynamoDB and LevelDB, amongst others, came from this movement. More recently NewSQL has emerged, giving global scale and ACID guarantees. One example is Google Spanner.


What's the most appropriate type of index to use for this function-based query? I'm using Postgres 9.5.  I'm going to be running a lot of this kind of query

SELECT *
FROM mydb_article
where regexp_replace(url, '\?.*$', '') = :url_wo_query_string

so I figured I would need to create a functional index on my table for that operation.  So I tried

mydb=> CREATE INDEX my_article_idx ON mydb_article regexp_replace(url, '\?.*$', '');
ERROR:  syntax error at or near "regexp_replace"
LINE 1: CREATE INDEX my_article_idx ON mydb_article regexp_repl...

My question is, what's the most appropriate type of index I should be using and if its the above, what else do I need to do to get things to work right?

The list of expressions for an index must be put between parentheses on table_name (a,b,c). If the index expression is not just a column name, the expression itself must be enclosed with parentheses as well:

CREATE INDEX my_article_idx
   ON mydb_article ( (regexp_replace(url, '\?.*$', '')) );


website performing slow once mysql connection RDS reached 50 Below the rds parameter group config, please help me to resolve this issue.
I am using 4 web server having 2 CPU and 8 GB ram and rds having 4 cpu and 16 GB ram.

Once the connection reached 50 site will stop responding.

join_buffer_size                        150.00 MB
read_buffer_size                        150.00 MB
read_rnd_buffer_size                    300.00 MB
sort_buffer_size                        150.00 MB
Total for max_connections=600           750.00 MB

There may be many causes here are some suggestions:

1) Check slow query log in mysql:

https://www.a2hosting.com/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql

2) Change mysql driver in your application

Even if you downgrade it, you can have better results. Change a major version if possible.

3) Look for locks

mysqladmin debug

It will dump a lot of information, including locks, but  you can get lock information by queries too:

https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html

Hope it helps


SQL Replication across the internet give named pipe error We've had a db replication working between two servers across the internet.

Server A is sitting on our server network (Windows Server 2008 + SQL Server 2008 R2)
Server B is sitting on a different network (Windows Server 2008 + SQL Server 2008 R2)

Ports are still open, TCP and Named Pipes are enabled. Alias configured, TCP and ports open in the firewall, everything was smooth and working.
All SQL Services are also running.

Server A is the publisher and Server B is the subscriber.

It's been using a Push subscription so everything was working on Server A.

All data was synced and everything was fine.

After a server issue on Server A, the DB name changed, the replication and publication was recreated and Server A was upgraded to SQL Server 2008 R2 SP3.

So far that's the only noticeable difference between the two, the publisher is running SQL 10.50.6000.34 and the subscriber 10.50.2500.0.

Now the replication stopped working and I'm not able to connect the two via named pipes..

If I try to create a new subscription from either server and use the server name (for replications you need to use the server name instead of TCP) I keep getting named pipe error

I can ping each Server from the other server.
I can connect from SQL Management Studio using tcp with domain or ip and run queries on the other server.
I can add them as linked servers and see the data as well.

But the moment I go to create new subscription and add the remote subscriber I get the named pipe error.

I've finally managed to connecto both servers!

As it turns out even though both server are x64 and running 64bit os and SQL Server 64 bit, it needs the aliases to be configured both on 64 & 32 Bit Native Client Aliases.

Once I configured the one for the 32 bit driver, it worked.


How to identify unused tables We are planning to merge 2 or 3 region databases who are using almost the same version of SQL Server 2008 R2, have exact tables and most stored procedures for our end users reports.. in the process, we are trying to get rid of all unused objects that may have been created for testing purposes to limit what we see in the production database. So there are 5 or 7 tables that the previous DBA created but is no longer in use (at least the front end application is not using it anymore), how can I make sure that there are nothing else is depending on these tables, triggers or SP?

Is there a checklist for me to use before I attempt deleting them other than backups, like when was the last time was used, by whom, etc...?

Since a table might only be referenced once a year (think annual reports), and since they may be referenced only in some developer's (or your CEO's) code or spreadsheet on their desktop, these approaches do not work:

tracing the database to see if any tables aren't referenced for a day / week / month
finding some reliable way to determine when the table was last used (this is not easy)
searching the codebase

The safest approach would be to rename the tables that you have already identified through normal means (like the ones above) as likely to be no longer used, and stand back to see if anyone complains. This way you can restore them quite easily if anyone should complain (and hopefully any interim breakage is in low-impact areas, otherwise these tables should have been identified better).

However, you still need to wait a full business cycle before assuming that no news is good news, as per my very first sentence.


Function Isn't Excluding Repeat Data I am trying to insert a large amount of data into a table.  The source for the data has the potential to repeat data in what is suppose to be a UNIQUE column.  I created the following to insert the data into my table:

DECLARE @OrderNum VARCHAR(20), @JobNum VARCHAR(20), @Supplier VARCHAR(100), @OldOrderID int, @UserNum int,
        @OrderID int, @JobID int, @SupplierID int, @ContactID int, @UserID int

DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
    SELECT order_id, job_num, supp_name, user_num
    FROM _po_temp_b

OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @OldOrderID, @JobNum, @Supplier, @UserNum
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @OrderNum = order_num
    FROM _po_temp_a
    WHERE order_id = @OldOrderID

    IF NOT EXISTS(SELECT order_number FROM purchase_order WHERE order_number = @OrderNum)

        SELECT @JobID = j.job_id
          FROM job AS j
         WHERE j.job_number = @JobNum

        SELECT @SupplierID = s.supplier_id
          FROM supplier AS s
         WHERE s.supplier_name = @Supplier

        SELECT @ContactID = sc.contact_id
          FROM supplier_contact AS sc
         WHERE sc.supplier_id = @SupplierID

        SELECT @UserID = ul.user_list_id
          FROM user_list AS ul
         WHERE ul.user_order_number = @UserNum

        INSERT purchase_order(order_number, job_id, supplier_id, contact_id, user_list_id, status_id, notes)
        VALUES (@OrderNum, @JobID, @SupplierID, @ContactID, @UserID, 2, NULL);

    FETCH NEXT FROM MY_CURSOR INTO @OldOrderID, @JobNum, @Supplier, @UserNum

END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

Now my thought was that the IF NOT EXISTS line would stop me from getting a bunch of data repetitions.  However, I am still getting repeat data entries, preventing me from setting the column up as UNIQUE, which is what it needs to be.

What exactly is wrong with my setup?  Am I missing something somewhere?

Looking at the code, I don't see a reason for the cursor. It's unclear what column is supposed to be unique, but you can handle your EXIST in a WHERE clause (i used NOT IN) and remove all the variables, and cursor as well.

insert into purchase_order (order_number, job_id, supplier_id, contact_id, user_list_id, status_id, notes)
SELECT
    a.order_id
    ,j.job_id
    ,s.supplier_id
    ,sc.contact_id
    ,ul.user_list_id
    ,2
    ,null
FROM
    _po_temp_b b
inner join
    _po_temp_a a on a.order_id = b.order_id
inner join
    job j on j.job_number = b.job_num
inner join
    supplier s on s.supplier_name = b.supp_name
inner join
    supplier_contact sc on sc.supplier_id = s.supplier_id
inner join
    user_list ul on ul.user_order_number = b.user_num
where
    a.order_number not in (SELECT order_number FROM purchase_order)

You may need LEFT joins... it all depends.


Help with Galera Cluster Auto_Increment We're currently running a Galera cluster of 3 DB nodes. The way we're set up (due to ORM and automagical locking), we only have one of the 3 DB nodes being written to, and the other two provisioned for reads.

Galera's set to auto_increment on a value of 3, since there are three nodes in the cluster, in order to ensure that auto_increment values across the cluster are always unique. See this article (this is a Galera default) - https://blog.mariadb.org/auto-increments-in-galera/

If we plan to always only have 1 write DB, would it be dangerous or risky to set auto_increment to 1 globally across the cluster?

Each node defines auto increment equal to the total number of nodes. This is for avoiding id collisions, because each node in the cluster have their unique sequence of numbers.

Like @jersten said, let galera handle it ;)


Percona-server-5.6.41 and sql_mode When setting

sql_mode=""

in  /etc/mysql/my.cnf  server leaves this variable in it default value:

mysql> show variables like 'sql_mode';

+-------------------+------------------------------------------------------------------------+
| Variable_name | Value                                                                                |
+-------------------+------------------------------------------------------------------------+
| sql_mode         | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION  |
+-------------------+------------------------------------------------------------------------+

However, if i set it manually in mysql shell:

set global sql_mode="";

server sets it as desired - to empty value. Till next server restart, when server sets this variable to default value again.

Tried in single and double quotes - no matter.
No errors in error log. No other default configuration files of mysql loaded
What's wrong?

Why?

Found: another MySQL config is /usr/my.cnf and it contains:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

so, setting sql_mode="" did the thing I need.

Debian stretch. Percona server was installed after removing installed MySQL-5.6 community.

Intrecting thing: log doesn't mention this config for reading during server's startup...


Why am I getting table does not exist error? so I'm trying to creating a database table using pg admin. I created a table in pg admin and then I was supposed to run the table using putty. But when I run it it says error table does not exist. The table does exist though so I don't know why I'm getting that error!

--Dropping tables clear out any existing data
DROP TABLE IF EXISTS automobiles;

CREATE TABLE automobiles(
    id INTEGER PRIMARY KEY,
    make VARCHAR(15)NOT NULL,
    model VARCHAR(20)NOT NULL,
    year INTEGER NOT NULL,
    owner CHAR(25) NOT NULL,
    msrp DECIMAL NOT NULL,
    purchase_date DATE NOT NULL
);

INSERT INTO automobiles(id, make, model, year, owner, msrp, purchase_date) VALUES(
    1,
    'Ferarri',
    'F40',
    '1987',
    '',
    '1690000',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date)VALUES(
    2,
    'Tundra',
    '4X2',
    '2019',
    '',
    '39625',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date) VALUES(
    3,
    'Mercedes-Benz',
    'AMG GT',
    '2014',
    '',
    '145000',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date)VALUES(
    4,
    'Chrysler',
    '300',
    '2004',
    '',
    '800',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date) VALUES(
    5,
    'Jeep',
    'Wrangler',
    '2019',
    '',
    '33695',
    '3/12/2019');

SELECT make, model, year, msrp FROM automobiles ORDER BY year ASC;

UPDATE automobiles
SET owner = 'Deanna Slotegraaf'
WHERE make = 'ferarri';

DELETE FROM automobiles WHERE make = 'Chrysler';

This is the full set of code that I have.

Last login: Mon Mar 11 18:02:19 2019 from 108.170.191.53
slotegraafd@opentech:~$ cd /var/www/html/webd2201/slotegraafd/sql
slotegraafd@opentech:/var/www/html/webd2201/slotegraafd/sql$ psql -d slotegraafd_db -f lab7_auto_records.sql
Password:
psql:lab7_auto_records.sql:9: NOTICE:  table "automobiles" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:lab7_auto_records.sql:30: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:38: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:46: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:54: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:62: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
 make | model | year | msrp
------+-------+------+------
(0 rows)

UPDATE 0
DELETE 0

This is the putty session

Thanks...

The table isn't missing. The message "table "automobiles" does not exist, skipping" stems from the DROP TABLE IF EXISTS statement and just informs you that there wasn't such a table to be dropped.

If you look at the message it starts with NOTICE: which tells you that it's not an error. Error messages start with ERROR: (as you can see later on in your script)

Your table was successfully created.

However, your INSERTs are failing as you are providing the values for the date column in the wrong format. The most robust way to supply a date constant is to use the ANSI SQL syntax for dates: DATE '2018-03-12', so your inserts should look like this:

INSERT INTO automobiles(id, make, model, year, owner, msrp, purchase_date)
VALUES
(   1,
    'Ferarri',
    'F40',
    '1987',
    '',
    '1690000',
    DATE '2019-03-12'),
(   2,
    'Tundra',
    '4X2',
    '2019',
    '',
    '39625',
    DATE '2019-03-12'),
...;

If you don't like the ISO formatted date literals, you can also use the to_date() function instead: to_date('3/12/2019', 'mm/dd/yyyy').

The message

date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.

suggests to change the DateStyle setting in order to allow the implicit conversion of a string value like '3/12/2019' to a proper date.

I strongly discourage the use of the DateStyle parameter as that makes your SQL statements dependent on the environment and they might still fail when run on a different computer.


Long running statement not monitored by Oracle We are trying to find out why a (pretty complicated) statement has an extremely high variance in runtime. Sometimes it just takes "minutes" (around 40-50) and sometimes it does not finish even after 12 hours.
My usual approach to problems like that is to check if the statement is monitored and if it is, get the "real-time" execution of it using dbms_sqltune.report_sql_monitor() so I can pin-point the bottleneck in the execution.
However that specific statement is never monitored by Oracle. According to the Oracle manual a statement is monitored, if:

By default, SQL monitoring is automatically started when a SQL statement either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution

As this statement runs for substantially longer then 5 seconds and uses parallel execution it should be monitored.
We even added the hints gather_plan_statistics and monitor to the SQL to force monitoring of the statement, but still it's not monitored.
According to "My Oracle Support" (DocID: 1613163.1) monitoring is limited to statements with a plan with less then 300 lines. When I generate the plan, it has 282 lines (=highest line ID in the plan), so that should not prevent monitoring.
There is a hidden parameter ("_sqlmon_max_planlines") that controls this, but this is a hosted database server and changing something like that is quite complicated.
So my question is:
Are there any other (maybe undocumented) restrictions on why Oracle would not monitor a statement?

This is an Oracle 11.2.0.3 Enterprise Edition (upgrade to 11.2.0.4 is scheduled, but that might take some time).

The statement in question had multiple plans (13 to be precise) which for some reason were not returned by dbms_xplan.display_cursor(). The plan returned by that function was one with less then 300 lines, but other plans had more then 300 lines.

After the hosting partner increased the "_sqlmon_max_planlines" parameter to 500, the statement is now monitored.


Unable to connect oracle as sysdba tables have been dropped I have a script which lists all tables belonging to the user and executes DROP for all of them.
By mistake, I logged in oracle with 'sys as sysdba' and ran above script. Due to which all sysdba tables are dropped.
Now i can not startup database instance. In alert log, it gives following error:

Sat Jul 20 15:28:21 2013
Errors in file orcl_ora_4276.trc:
ORA-00942: table or view does not exist
Error 942 happened during db open, shutting down database
USER: terminating instance due to error 942

I tried to flashback one droppd table, but it is giving error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> FLASHBACK TABLE MAP_OBJECT TO BEFORE DROP;
ERROR:
ORA-03114: not connected to ORACLE

Please suggest if there is any way to restore all these tables.
Or if creating new database is the only way?

This is probably as complete a way of killing an Oracle database as you could wish for. The sys tables contain all the metadata about every object in the database -- objects, segments, extents ... so the database now contains no information on what user tables it stores, including the tables that store the data about that.

New database, I think.

And no more sys connection accidents.


SQL Server- Table partitions on SSD I have a table in my database that has about 250m rows. I'm new with databases but I have read up about partitioning the table to increase performance. One of the important points that I seem to notice is that the partitions should be placed in different file groups in different drives. But my server has a single ssd that is partitioned. Is there any advantage in placing the files in individual partitions of the storage device even though physically it is just one. Does partitioning still help if all the file groups are in the same physical drive?

Thanks in advance.

Performance is only one of several benefits that can be gained from partitioning.  The benefits from partitioning are typically realized from partition elimination, where the query engine can rapidly narrow down the data set you're querying against.  This allows SQL Server to read in and operate on just a specific portion of the data, greatly reducing work time and I/O.  However, as discussed in the linked post, this relies heavily on your how your query is written and whether or not the index it is using can leverage the partitioning scheme you have in place.

It's partition elimination that also allows you to leverage the benefits of placing partitions on different disk systems.  The idea is fairly simple: if you can place partitions that are more actively queried against on faster disk, you can help your query out even more because the subset of data it's accessing can be retrieved faster.  At the core, though, the concept remains the same.  The reason you're increasing query performance is because you're helping the engine reduce the amount of data it is working with.

Two other benefits of partitioning include the ability to rapidly move data in and out of table using SWITCH and reducing index rebuild time by rebuilding indexes by partition.  The first is something very specific and can only be used when properly designed, but can be very useful on extremely large data sets.  The second is actually one of the main reasons I will typically leverage partitioning, as maintaining indexes on large tables can be very consuming, both from a resource and time perspective.

That being said, 250 million rows is not typically considered a large data set.  SQL Server can easily handle tables of this size and greater.  When I consider partitioning, I try to think more in terms of data volume.  I don't usually think about partitioning until my table is over 250 GB.  This isn't to say that your table doesn't fit this profile if your rows are very wide, but chances are you're not anywhere near that threshold.  You might want to consider other options to increase your performance.  Usually this means proper indexes.  A quick win, if you're using Enterprise Edition, could be to instead use data compression, which will reduce your I/O to disk and can be a nice boost to performance.


MySQL 5.5.8 InnoDB Foreign Key, JOIN performance We are using aws/rds, MySQL 5.5.8. all InnoDB.

For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; de-normalized some tables to behave almost like key-value stores; shareded tables containing user generated rows etc.

We are also looking to further improve insert performance by omitting some possible foreign key relationships between large tables (2+MM rows) since our app layer does a lot of similar integrity checking and prevention.

My question is about theoretical join performance between two tables A and B, using a primary key on one side A.F1 and single row index on side B.F2. Since I could also define the B.F2 as a foreign key (over A.F1), I would like to know if such a join would be any "faster/better etc." with or without the foreign key constraints - all other things being equal (indexes, no other fields, no order-by etc.).

We expect a lot of read queries over such situations, and fewer inserts that would benefit from foreign key omissions.

Foreign key relationships are to enforce data integrity, not for query performance, that is what indexes are for. Also note that InnoDB creates an index on each column with a foreign key relationship.

However I would recommend having the foreign key relationships to ensure that the data is always valid, especially when updating and deleting, which may become significant when you have to start archiving data.


SQL "Standard" guide? I am currently learning TSQL and am learning that were possible, I should use "standard" SQL dialect. Where can I find out which commands are "standard" and which are TSQL?

The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree.

While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This almost always means using DBMS-specific versions of SQL and DBMS-specific features.

Your employer/client has paid good money for a license for their DBMS, and often gone through a selection process to do so. Not using the DBMS's features is a waste of money.

I like to say it's like buying a Ferrari and always sticking to the speed limit...

On the other hand, if you're just learning SQL, and T-SQL just happens to be the dialect you have at your disposal, then, yes, try to learn standard SQL as your knowledge will be more broadly applicable. But don't discount learning your particular DBMS's features if you are in a commercial (or non-academic) environment.


Bind variable vs. string-concatenated variable I've made an anonymous block that has dynamic SQL:

01    DECLARE
02        l_table_name VARCHAR2(100);
03        l_field_name VARCHAR2(100);
04        l_domain_name VARCHAR2(100);
05
06    BEGIN
07        FOR list_fields IN (
08            SELECT
09                TABLE_NAME
10                ,FIELD_NAME
11                ,DOMAIN_NAME
12            FROM
13                ENG.FIELD_DOMAIN_ENG_VW
14            WHERE
15                TABLE_NAME NOT LIKE '%ANNO%'
16            )
17        LOOP
18            l_table_name := list_fields.TABLE_NAME;
19            l_field_name := list_fields.FIELD_NAME;
20            l_domain_name := list_fields.DOMAIN_NAME;
21
22            EXECUTE IMMEDIATE
23            'INSERT INTO ENG.CV_ERRORS
24            (TABLE_NAME, FIELD_NAME, ERROR)
25            SELECT
26                :bv1 AS TABLE_NAME
27                ,:bv2 AS FIELD_NAME                 --<<-- Lines in
28                , ' || l_field_name || ' AS ERROR   --<<-- question.
29            FROM ' ||
30                l_table_name ||
31                ' LEFT JOIN
32                (
33                SELECT CODE
34                FROM  ENG.D_CV_ENG_VW
35                WHERE DOMAIN = :bv3
36                )
37                ON ' || l_field_name || ' = CODE
40            WHERE
41                ' || l_field_name || ' IS NOT NULL
42                AND
43                CODE IS NULL'
44
45            USING l_table_name, l_field_name, l_domain_name;
46
47        END LOOP;
48    COMMIT;
49    END;

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |    ERROR    |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
| TABLE_1    | FIELD_2    | zzzz        |
| TABLE_2    | FIELD_1    | asdf        |
+------------+------------+-------------+

There's a part of it that I don't understand how/why it works.

27        ,:bv2 AS FIELD_NAME
28        , ' || l_field_name || ' AS ERROR

Line 27 uses a bind variable that is tied to the l_field_name variable. The value that is returned is the name of the field (example: FIELD_1, FIELD_2, FIELD_1).

Line 28 breaks up the string, and uses the l_field_name variable directly (via concatenation). The value that is returned is the value of the field (example: BLACK SHEEP, zzzz, asdf).

Why do the bind variable (line 27) and the string-concatenated variable (line 28) return different values, when they reference the same thing (l_field_name)?

You ask, referring to the variable l_field_name:

  Why do the bind variable (line 27) and the string-concatenated variable (line 28) return different values, when they reference the same thing?

One way to look at this is to consider when and how the value of l_field_name is used.

The first (in terms of time) use is when you compose the string value that represents the INSERT statement. At that time the value of l_field_name is concatenated, along with other string literals and variable values, into the resultant string and becomes a part of it. The complete statement string is then passed to EXECUTE IMMEDIATE for parsing, compilation, and eventual execution.

The second use is when the value of l_field_name is assigned to the host (bind) variable :bv2 just prior to the statement execution. In this case that value is not concatenated or "injected" into the statement text: by this time the statement is already compiled into a plan, and :bv2 behaves like a variable it is -- it holds its value (that is now equal to the value of l_field_name) and passes it on to be inserted into the corresponding column.


mysql 5.5 strange bug I am trying to create a database in MySQL Server 5.5. The database is created, I know this as when I do

SHOW DATABASES;

It shows me my database in the list of all databases from the commandline.

But when i go inside

  C:\Program Files\MySQL\MySQL Server 5.5\data\

my database is not visible there. In fact with

SHOW DATABASES;

it shows me 6 databases in the list but inside

  C:\Program Files\MySQL\MySQL Server 5.5\data\

shows only two databases.

Its really strange error I m experiencing, the database is created but not actually created
feels like the commandline is cheating with me!

thanks for the help in advance!

Please check the my.cnf in the mysql installment folder.

like this:

datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

it will show you the right folder.


How do I verify the uninstall of Grid Control and WebLogic? I installed Weblogic, the required patch and then Grid Control on a Linux (RHEL 5.5) server.  Unfortunately, my instructions did not have the -d64 tag to allow for 64 bit installation and I have to uninstall Grid Control and WebLogic then reinstall.

Their documentation for uninstalling leaves a lot to be desired and I had no less than 5 errors during the process.  My basic question is "How can I verify that I have deleted everything from WebLogic and Grid Control?"  This includes repositories.

Below is a basic overview of what I was given as instructions:

    1. <ORACLE_HOME>/bin/omsca delete -full   Errored

OMSCA-ERR:Admin server is down. Full option requires admin server up

/bin/emctl stop agent    No Error

/sysman/admin/emdrep/bin/RepManager
 -action dropall -dbUser  -dbPassword
 -dbRole  -reposName  -reposPassword
 -mwHome  -mwOraHome  -oracleHome

Could not get above command to work at all

Ran the following:
DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

emca -deconfig all db -repos drop

Manually delete the data files mgmt.dbf and mgmt_ecm_depot1.dbf from the Oracle home of the database.
Done

$/oui/bin/runInstaller -removeallfiles
Done

/etc/rc.d/ directory. For example, /etc/rc.d/rc3.d/S98gcstartup.
No clarity on which files to delete!
Note that instructions show no information on how to uninstall WLS!
Ran:
USER=oracle1 SID=emrep PATH=/home/oracle/Middleware/wlserver_10.3/uninstall
$./uninstall.sh

Be really, really careful when mucking around in /etc/rc.d/; this is where your startup/shutdown programs live, so removing the wrong file will put you in to a world of hurt. (Unfortunately, I can't tell you which file is the one Oracle tends to use.)

Second, Oracle's uninstall instructions always leave a lot to be desired, as does the actual process, which leaves just about everything hanging around on the file system even when it isn't necessary anymore.

For removing Grid, IIRC (that is a big IF), I think you're in the clear -- pretty much exactly what I would have done. And, as to weblogic, same deal -- I'd have run the uninstall.sh and see what happens. I suppose you could nuke the entire directory at this point so that there'd be no chance of an old file coming back to bite you, so that'd be just about the only other thing I'd do.

Good luck with the re-install...


SQL Server: Backup failed because there is a mismatch in file metadata We have a problem with one of our databases that has FileStream enabled. When we try to backup, we get the error

Backup failed because there is a mismatch in file metadata for file 65537

When we do a DBCC, no errors get reported, DBCC CHECKDB ('MyDB', repair_allow_data_loss) also does not return any issues. Users are not experiencing any issues when accessing the database, it is only the back-up that is a problem.
We have tried copying data to other tables, etc. but nothing has helped yet. We have even tried 3rd party repair tools but nothing has delivered results.
This is a very big database and creating a new DB and migrating the data is not really an option (will probably take many days).
Does anybody have any ideas on how to fix this issue or suggestions on what we can try?
I am now investigating undoing the FileStream...
Thanks!

When we do a DBCC, no errors get reported, DBCC CHECKDB ('MyDB', repair_allow_data_loss) also does not return any issues

This is really interesting as DBCC should report errors if metadata is corrupted. Have you tried to run a DBCC CHECKCATALOG; ?

Backup failed because there is a mismatch in file metadata for file 65537

I know it sounds basic but have you checked what database does file 65537 belongs to? Make sure the DBCC CHECKDB and DBCC CHECKCATALOG are run against the good database. But judging from the fact that you have problems with your backups, I guess you are looking at the right database. Unless it would be msdb? Since backup sets are in msdb so when you do a backup it inserts rows in msdb also.
You should check Paul Randall's blog, he has a post on fixing metadata corruption without a backup :
https://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-metadata-corruption-without-a-backup/
Did you try to restore your backups to see if they are corrupted?
Do you have filestream enabled? From what I'm reading, file 65537 would be related to filestream.
https://www.sqlshack.com/sql-server-filestream-database-corruption-and-remediation/
There's also a great blog by Paul Randal on filestream you should check it out.
https://www.sqlskills.com/blogs/paul/filestream-directory-structure/
-------------- EDITED for FileStream corruption -------------------------
You should check the answer on this question that explains what happens when Filestream is corrupted.
https://stackoverflow.com/questions/15302673/handle-sql-filestream-data-corruption-and-backup
And in the link on sqlshack, there are steps to recover a database from filestream corruption.


why do I have huge log files (SQLFT000060005.LOG.x, 3+Gb) with recent timestamps when I haven't used SSMS for 4 months? ...plus several ERRORLOG.x files ranging from 200MB to 2.1GB ...

I installed SQL Server 2017 in January for an assignment, but haven't used it since then.
Now the logs files are hogging 22.8GB of space, with recent datestamps - how is that possible?
What can I do to remove them - just delete them?

Log files are created by the SQL Server service, not by SSMS. If you installed with the default settings, you can stop and restart the service multiple times (or just call sp_cycle_errorlog repeatedly), and that should cycle away the big log files that are there. They'll come back, though, over time - even if you don't open SSMS.

If you don't need SQL Server to be running anymore, disable the service or, if you don't need it at all, uninstall it. You can also uninstall full-text search if you aren't using it (or never needed it). Those FT logs might be from that service.


Select Into with typed alias column I'm using SQL Server 2012 Express and I'm trying to run a command to create a new table from another

I'd like to do something like this

SELECT
   [RowID], [Account], [Broker], [AlgorithmName], [AlgorithmId],
   [Time], [XMLValue], 'Status' AS [ReportType] AS [varchar](100) NOT NULL
INTO [dbo].[temp_AlgorithmLog]
FROM [dbo].[AlgorithmLog_OLD]

The alias column is the part that doesn't work. 'Status' AS [ReportType] AS [varchar](100) NOT NULL

I would like to use a SELECT INTO rather then CREATE TABLE and INSERT INTO SELECT

Any suggestions on how to do this if it can be done this way?

Thanks in advance

Not sure where you guessed at that syntax, or why you are trying to put single quotes around a column definition (which makes it look like a string literal). Here is one way to make sure that the new column has a specific type:

SELECT ...other columns... ,
  [Status] = CONVERT(VARCHAR(100), ReportType)
INTO dbo.temp_AlgorithmLog
FROM dbo.AlgorithmLog_OLD;

The new column will be nullable regardless of the old column's nullability. If the old column is nullable and you don't want the new column to be nullable, first you need to ensure that there are no NULL values, and then you can alter the table after the fact:

ALTER TABLE dbo.temp_AlgorithmLog
  ALTER COLUMN [Status] VARCHAR(100) NOT NULL;


Combine rows to remove nulls while using latest value based on time I have a table that has multiple rows associated with each ID but sometimes containing differing info:

Key, ID,  Name,  Title,  Time
4,   AA,  Abe,   NULL,   2018-01-05
3,   BB,  NULL,  Mrs,    2018-01-04
2,   AA,  NULL,  Mr,     2018-01-03
1,   BB,  Cat,   Ms,     2018-01-02

Some of the fields are Nulls, some of the row have differing info for the same columns and ID but there will always be an ID and a timestamp.

I want a query where I can squash Nulls but when there are multiple differing fields choose the latest one. Something like this:

Result =
ID,  Name,  Title,  Time
AA,  Abe,   Mr,     2018-01-05
BB,  Cat,   Mrs,    2018-01-04

I have something using "SELECT DISTINCT ON (id, name, title)" but I'm missing the next step so it's not working.

SQL Fiddle: http://sqlfiddle.com/#!17/72ba3/1

SELECT id
     , SUBSTRING(MAX(time || name) FROM 11) "name"
     , SUBSTRING(MAX(time || title) FROM 11) title
     , MAX(time) "time"
FROM test_table
GROUP BY id

The FROM 11 part extracts the data (name or title) from the concatenated aggregate values. First 10 characters is the date (time column).


Space Management in SQL Database This question has been moved from Game Development Stackoverflow

I am trying to create a new type of MMORTS game.

Current Situation

Everyday each user deletes and creates :

1,500 rows of data in diff. tables but in 1 database (all tables data are inter-related to data of other tables)
Each Row estimated size : 1KB
Max user per database: 100,000 users
Total Space Consumed everyday : 0.15 TB

Actual Users in the Game : 10,000,000 users (split in different databases)

Problem

According to me if i delete a row in SQL it is not being deleted permanently and will still consume the data in the database. If this happens, then in near future all my server are likely to crash.

Can anyone tell me if i am going wrong anywhere or suggest me any solution to this problem? I know this question cannot be understood easily so please feel free to ask any doubts you have regarding this question.

If the tables in question are not using a clustered index, they are what is referred to as "heaps".  Heaps do not automatically release the space consumed by deleted rows.

To determine if your tables actually have clustered indexes, you can right-click the table name from SQL Server Management Studio, then click "Script table ... -> CREATE to -> New Query Editor Window".  If you see the word "CLUSTERED" in the definition produced, then the table has a clustered index.

If your tables are not using clustered indexes, I would recommend adding clustered indexes on appropriate key columns (probably the id column), since rows deleted from clustered indexes are automatically freed by the ghost cleanup process referred to in the answer posted by @RLF.  Clearly, you're going to do this in the development environment, NOT production, correct?

For further info, I recommend reading Paul Randall's excellent post on the ghost cleanup process.  One salient sentence from that post is:

  do ghost records occur in heaps? The answer is no, not during normal processing.


How to determine if it is necessary to increase the sga and the pga in Oracle database How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary to increase the pga or the sga?
Thank you

Tuning usually starts with a reason.  Oracle has made great strides in reducing the need to tune for many use cases.  Most tuning I have done has been necessitated by poorly written application code or trying to mix an OLAP application and reporting on the same instance. Without knowing your operating system and specifications, how the database is used, size of the OS paging file, number of users only general advice can be given.  The documentation is excellent and worth your time.

If you don't have a specific issue then set the aggregate PGA target parameter to 0 and let Oracle do the work. The existing settings can be seen here

SELECT *
  FROM V$PGASTAT;

Official documentation is here.

The SGA can also be automatically managed by Oracle with these settings:

STATISTICS_LEVEL to TYPICAL or ALL
SGA_TARGET to a nonzero value
SGA_MAX_SIZE to a value larger than SGA_TARGET but small enough that there is enough memory free on the server for the OS and other running processes.

You can query the existing settings from the SQL command line with

SHOW SGA

A very rough rule of thumb is to allocate at least four to six GB for the OS on a dedicated server.

Official documentation is here


Which of these structures are good for maintaining history data? Iam creating an application which is being used to send reminders. Here is the requirements.

We have set of Students.
Every student has a Student Pass, this need to be renewed in every month. Student Pass has Pass ID, monetary value, date of expiry, etc.
Before 7 days of Student Pass expiry, we have to send notifications to the system admin.
We have to monitor the Student Pass History of every student as well. Which means how many notifications were given to the admin, what is the date the pass got expired and so on.

Now we need to create tables for this. We thought of creating a table called StudentPass and maintaining the student pass history and current student pass in the same table. It is like below

However maintaining the current student pass data and student pass history seems to be
mixed up work because we have to send notification for the expiration of current student pass. So we decided to divide this into 2 tables. It is like below, the current student pass data will be maintained in StudentPass table and the history data will be maintained in StudentPassHistory.

With your experience in database, please let me know which method is better. In case you need more information about why we divided the data into 2 tables, that is because our notification system can simply identify that it should send notification only for the items in StudentPass table, We do not have to do DB comparison to find out which data is the latest and current, we have history and current data separated and it will be easy to generate reports. Your advice is highly appreciated.

One thing missing from the sample tables above is BeginDate and EndDate (or whatever you want to call them).  Since you are tracking history, the dates are obviously valuable and can be used when querying older entries.

Having a StudentPass and a StudentPassHistory table does split out the information, but that is a mixed blessing, since the student's full StudentPass information is split across two different tables.

You might consider implementing StudentPass like this:

CREATE TABLE dbo.StudentPass
(idStudentPass INT,
 Student_idStudent INT,
 BeginDate Date,
 EndDate Date
 PassActive INT);

This way, the BeginDate and EndDate clearly identify not only whether the StudentPass is active, but could also preview for you the approaching EndDate.  Likewise, a future BeginDate could also support setting up a StudentPass that will not be activated for a few more weeks.

The PassActive is not strictly needed, but can be a useful shortcut for querying active StudentPass data without using dates. You just need a process to update the PassActive column setting as defined to be in the inclusive range of the BeginDate and EndDate.

Using a design like this keeps all the information in one table, which I assume will not overwhelm your query processing, so that you can simply filter the data according to your needs.

Of course, if you feel that the StudentPassHistory would server you better for some other needs, then make the choice to also create that table.


AWS Aurora Mysql different instance types for writer/reader I have a db.r5.large for the master/writer.
I want to add some readers.
Do they need to be the same instance type as the master? Or can i add for example 3x db.t3.medium ?

All the writes to a Primary are sent to each Replica and then applied there.  For this reason, the Replica should (in general) be at least as beefy as the Primary.
Often, you need to do reads on the Primary in support of what you then write.

Hence, the characteristics of the Primaries and Replicas are similar.
If the database is sort of "write once and read many times", then you might want the Replicas to be beefier in general, but still a "database" type, as categorized by the cloud provider.
You may find that db.r5.large is overkill for your Primary, especially if you move a lot of the read traffic off to at least 2 Replicas.  (Based on my first bullet item, having only one Replica is not very helpful.)
If you want a deeper review of your application, provide RAM size, SHOW GLOBAL STATUS and SHOW VARIABLES.


Which folder I use for recovery mysql database Accidently deleted mysql database and reload again.But ı cant find my old database and no have backup.This is picture that mysql ProgramData folder. Which folder should ı use for recovery? Thanks advice

Accidently deleted mysql database ...

It happens.
It shouldn't be the End of the World.

... and reload again.  But I cant find my old database ...

Not sure what you mean by "reload" but if you did anything that involved running "create database" for that particular database then anything and everything about the old database has now been obliterated.

... and no have backup.

This is hard lesson for any DBA to learn and, sadly, you've learned it the Hard Way:

Any Data that you don't have at least two copies of is Data that you do not care about and are perfectly content to lose.

You must take Backups of your databases, for the very reason that, sometimes, when things go badly wrong, you have to Recover them.
If you have backups of the machine itself, you might be able to get something back by restoring the data files from that but do not expect to get everything back.  Databases do a lot in memory, so any files copied out from "under" a running database instance are not consistent.


case versus isnull versus coalesce I am just reading through the documentation for the SQL Server 2012 exams and I saw the following point:

  case versus isnull versus coalesce

Now, I know HOW to use each one but I don't know WHEN to use each one. Please could some body clarify. Thanks.

Ps. Please could we have a tag for exam questions?

ISNULL - available only in SQL Server. Allows for the testing and replacement of a NULL value with one another one.

COALESCE - ANSI standard. Allows for the testing and the replacement of a NULL value with the first non-null value in a variable length set of arguments. It is important to note that data type precendence factors into this

-- Conversion failed when converting the varchar value 'a' to data type int
SELECT COALESCE(CAST(NULL AS varchar(10)), 'a', 1)
-- Returns 1
SELECT COALESCE(CAST(NULL AS varchar(10)), 1, 'a')

In the above example, 'a' is the first non-null value but character data has lower precedence than integer.

Another consideration between ISNULL vs COALESCE is the resultant of ISNULL is determined to be NOT NULL while the outcome of a COALESCE call is NULLable. See JRJ's post ISNULL() <> COALESCE() While that might seem a trivial thing, the query optimizer can make different plans based on the nullability of a column.

You can easily test the nullability of your isnull/coalesce/case expressions by running it through the dmo  sys.dm_exec_describe_first_result_set

-- these all evaluate to not nullable (is_nullable = 0) because I'm not clever enough
DECLARE @sql nvarchar(4000) = N'
SELECT ISNULL(NULL, 1) AS I
, COALESCE(NULL, 1) AS C
, CASE WHEN NULL = NULL THEN NULL ELSE 1 END AS C1
'
SELECT
    DMO.*
FROM
    sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS DMO

CASE - Also an ANSI standard scalar function. I'd look at using CASE over the previous two when I have a test that can't be expressed in a simple scalar but that's a pretty weak answer, I admit.


Using timestamp plus integer as primary key In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is:

Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between 0 and 100?

Why would this be preferred to using an Integer Auto_Incremented field? (Unless it wouldn't, then I want to know that too.)

I would love reasons why this is a really bad design practice. Casting everything in order to get back that integer value is also a total pain in the ass.

I basically stand once chance at getting my small team of developers access to help improve our applications' database, which it needs badly-To avoid situations like primary key Varchar(50) fields with left-padded zero's, or to just start improving the non-normalized database, or comma-separated lists in one field.

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak.

The reasons to have auto incrementing surrogate int PK's are many:

You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works well with little overhead. If you do it the way you suggest, it causes much more overhead with the need to have a UDF to create the key which involves a lot more CPU cycles to create than an auto increment int.
They are the most efficient storage solution. The way you suggest would require a varchar(20)/char(20) or so meaning 20 bytes per row just for the key. A normal int is only 4 bytes. It might not seem like much, but put 100 million rows in there and you increase the size by around 1.5GB. Not cool. Then you have to put it as the FK in another table, another 1.5GB. Or in 3 other tables and your DB is needlessly almost 5GB larger. You get the idea...
Tables should really be linked together with something the users don't care about or understand. This is so that you have a low granularity of control as a DBA, a value that you can refer to which the users know nothing of which becomes important when doing highly granular changes, fine tuning the table or managing the data structure. I can just tell you this from experience of working with DBs that have composite keys like you suggest. They become frustratingly annoying and you are always tempted to just put in an auto incrementing int and be done with the complexity and frustration. Imagine for example, you wish to quickly insert a whole bunch of records that have come from a different source. With an auto increment, easy with minimal hit on server resources. With a composite key... you have to run a UDF which creates the key for each insert... and in your example the randomiser for 100 keys per ms might not be enough, SQL Server may work too fast for you and give you the same ID for 2 records. Damn, you need a different approach which doesn't violate the unique constraint. Or imagine the user wishes to start "reusing" a key for some reason (it happens, sometimes on some mistaken idea of "recycling making the database faster"). Users end up trying to tune the DB themselves, a recipe for disaster. Telling them (and having) behind the scenes things occurring that they don't understand gives a level of separation that often avoids the situation. They don't try to optimise anything, taking their sticky little fingers out of your side of the business, meaning you can optimise the way it should be done. Much like a teenager with no idea attempting to change the electronic timing of a modern car with their smart phone and a hammer... a job much better done by a professional with the right knowledge and tools.
SQL Server expects int PK-FK auto increments and is optimised for it. It will mean faster processing overall due to FK checks when inserting records.
Lastly, I find natural keys to be absolutely fine with small databases with few users that need little optimisation and management. They can also make for cleaner looking queries (by using the composite key in WHERE statements) when the queries are simple, meaning you have less joined tables etc. But as soon as the database starts getting large and complex... managing auto incrementing ints surrogate keys becomes a lot easier.

My final thought: Composite keys are FINE for lookup tables which have a low number of members (meaning a shorter composite key and therefore low space requirements) that are not subject to a lot of change (meaning low management overhead). An example is two letter state codes in the US. They will mean potentially one less join and over many stored procs, this could make a small but measurable performance improvement.


Mysql - generating trends of rank I've a table of students score

      ID_STUDENT | SCORE
     ------------------
           1     |  90
           2     |  80
           3     |  99
           4     |  80
           5     |  70
           6     |  78
           7     |  90
           8     |  50
           9     |  90

So lets say on first day I'll compute the rank and store it in one column say RANK -

      ID_STUDENT | SCORE  | RANK
     ----------------------------
           3     |  99    |  1
           1     |  90    |  2
           7     |  90    |  2
           9     |  90    |  2
           2     |  80    |  3
           4     |  80    |  3
           6     |  78    |  4
           5     |  70    |  5
           8     |  50    |  6

on second day I'll refresh the score and will recompute the rank. However, here is the thing, I need the records of prompt past rank as well, like this -

      ID_STUDENT | SCORE  | RANK | OLD_RANK
     ---------------------------------------
           2     |  99    |  1   |  3
           8     |  92    |  2   |  6
           1     |  90    |  3   |  2
           9     |  90    |  3   |  2
           3     |  80    |  4   |  1
           4     |  80    |  4   |  3
           6     |  78    |  5   |  4
           5     |  70    |  5   |  5
           7     |  40    |  6   |  2

So using this outcome, I would able find the rank trend,much the same as you can see the songs position going up/down over a week as shown here - https://www.billboard.com/charts/hot-100

How can I achieve this using straightforward DML queries?

In MySQL 8.0, there is a "straightforward" way using the windowing functions of RANK or DENSE_RANK.

If 5.7, there is no practical SQL way.  It would be better to pull the data into your app to compute the ranks.

I have a quibble with 1-2-2-2-3-... -- The guy with rank 3 is really 5th in the list.  The 3-guy will be disappointed when he drops to 4 or 5 tomorrow when the 2-guys' scores change a little.


Missing indexes dmv count varies every day I have a question to the missig index dmv.

BOL says:

  Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.

I basically do this using the following (slihghtly simplified) query:

SELECT <SomeFieldsAndCalculations> FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
INNER JOIN Database1.sys.objects o on d.[object_id] = o.[object_id]
INNER JOIN Database1.sys.schemas sch on sch.[schema_id] = o.[schema_id]

The results are written to a table once per day from ten sql servers (with correct grouping options...) .

Looking at this table I see that the number of missing indexes varies a lot.
These are the total number of missing indexes (over multiple instances) in a row:

  3091 3133 3090 3135 3131 2506 2750 2028 2032 2195 2274 2269 2272 2373

The services have not been restarted.

From the introduction from BOL I could understand a very slight variation from one day to another. Assuming a missing index popping up a day should still be in the dmv the next day, how can the total count ever vary that much? For example decreasing from 3131 to 2506 wihtin 24 hours? That's a change of about 60 per instance... Same with the raising number from 2272 to 2373 - suddenly 10 additional indexes missing per instance wihthin 24 hours...?

No indexes have been created during that period.
But unused indexes have been removed during that period.
I can't see a relation between removing unsused and missing indexes...

Questions:

How long will missing indexes information be returned from the above query if the Sql Service is runnung without interruption?
What could have influenced the number of missing indexes?

Missing index info will be cleared from the view if the referenced table has changed. Added columns, new indexes would clear out this data. When you remove indexes, any missing index info for that table will also be removed.

Index rebuilds also clears out this info, so if you are doing nightly index maintenance, this may contribute to the change in numbers. (edit: rebuilds only clear missing index info on 2012+)


Need to divide single column into multiple columns My table has data with a single column:

1
2
3
4
5
6
7
8
9
10

And I want output like this:

1 2 3 4 5-first row
6 7 8 9 10 - second row

Another has two columns like this:

Column 1           Column 2
1                  a
2                  b
3                  c
4                  d
5                  e
6                  f
7                  g
8                  h
9                  i
10                 j

And I want it to display like this:

1 a 2 b 3 c 4 d 5 e-first row
6 f 7 g 8 h 9 i 10 j - second row

For the first case you can use ROW_NUMBER then PIVOT to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.

WITH CTE AS
(
SELECT C,
       (ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row

SQL Fiddle

For the second case Oracle has a straight forward syntax to achieve this with PIVOT. SQL Server doesn't but you can do this as below.

WITH CTE AS
(
SELECT *,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)/5 AS Row
FROM YourTable
)
SELECT MAX(CASE WHEN Col = 0 THEN Column1 END),
       MAX(CASE WHEN Col = 0 THEN Column2 END),
       MAX(CASE WHEN Col = 1 THEN Column1 END),
       MAX(CASE WHEN Col = 1 THEN Column2 END),
       MAX(CASE WHEN Col = 2 THEN Column1 END),
       MAX(CASE WHEN Col = 2 THEN Column2 END),
       MAX(CASE WHEN Col = 3 THEN Column1 END),
       MAX(CASE WHEN Col = 3 THEN Column2 END),
       MAX(CASE WHEN Col = 4 THEN Column1 END),
       MAX(CASE WHEN Col = 4 THEN Column2 END)
FROM CTE
GROUP BY Row
ORDER BY Row

SQL Fiddle


DBCC Shrinkfile not working on SQL 2016 Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank.

Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file 2. File 2 is in the same file group that file1 was in. Result 2555 error:

  Cannot move all contents of file "file2" to other places to complete the emptyfile operation.

Restarted SQL, executed script from point 2, with same result.

I am ultimately trying to remove the file group that the file is a member of and the file group has only one remaining file in it. The file is has current allocated space of 1 MB and available free space of .81 MB

If it's saying the file is not empty, it's not empty. You need to figure out what's there and either rebuild it on another filegroup manually, or drop it. EMPTYFILE only works on the last file if there are no objects at all, and of course this is not possible on the PRIMARY filegroup.

My first guess is there's a table, index, or partition scheme still associated with this filegroup:

DECLARE @fgid int;

SELECT @fgid = data_space_id
  FROM sys.filegroups
  WHERE name = N'smoochies';

SELECT [object] = o.name, [index] = i.name
  FROM sys.objects AS o
  INNER JOIN sys.indexes AS i
  ON o.[object_id] = i.[object_id]
  WHERE i.data_space_id = @fgid
    OR i.data_space_id IN
    (
      SELECT partition_scheme_id
        FROM sys.destination_data_spaces AS ds
        WHERE data_space_id = @fgid
    );

SELECT ps.name
  FROM sys.destination_data_spaces AS ds
  INNER JOIN sys.partition_schemes AS ps
  ON ds.partition_scheme_id = ps.data_space_id
  WHERE ds.data_space_id = @fgid
  GROUP BY ps.name;

When you find the thing (it could be an index on an empty table, an empty heap, or just a partition scheme still referencing the filegroup you want to remove), you'll have to drop / rebuild / shift objects to a new partition scheme. If the partition currently referenced is the "permanent" partition (first in RANGE RIGHT or last in RANGE LEFT), see this answer.


Better understand table indexing best practices I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider:

The tables are not that big, I think (1000 - 10,000 rows).
The searches are made from the CMS admin pages - So they include more types of filtering/sorting options/combinations than the frontend.
I'm wondering if there is such thing as over-indexing where I'll add too much indexes that the searches might be quicker, but it will impact dramatically on the performances of inserting/updating data to the table, or that the DB engine will choose the wrong index for a query.
I'm adding Fulltext index when it come to textual searches.
I'm also adding asc/desc indexes for DATETIME/INT/BIGINT for what I believe will give better performances when it comes to sorting/filtering based on columns.

I'm just not sure what will be the optimal point in which adding an index will improve the performances of my App, when I take into consideration the above parameters, and my tendency to be an "Index Nazi" (Pardon my French) - I'm finding myself adding more and more indexes for every possible scenario I can think of.

I do hope that this question is not too broad, and I didn't include an example because it's more general behaviour of good and bad practices when it come to indexing.

Don't randomly add indexes.  Look at your queries to decide which indexes are needed.  See my cookbook.

InnoDB really needs a PRIMARY KEY.  Keep in mind that a PK is, an index, is UNIQUE, and is clustered.  So don't add any index(es) that start with the same column(s).

WHERE a=2 AND b=4 begs for a "composite" index:  INDEX(a,b) or INDEX(b,a).  Those are different than two separate indexes: INDEX(a), INDEX(b).

Don't use BIGINT unless you really expect to have huge numbers.

DESC is ignored on an INDEX declaration.  However, that does not stop the optimizer from running through an index in reverse order to have the effect of 'descending'.

FULLTEXT is useful only if you use MATCH(...) AGAINST(...).

"Too many" indexes slows down inserts some, and slows down UPDATEs that change indexed columns.

Don't index "flags"; such indexes will 'never' be used.


postgresql 9.1 failed insert occupies increasing disk space I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and a few web interface that show inserted data (refreshing every few minutes).
There is an error in the data reported of a specific table that generates a foreign key violation and overnight i find that all disk space is filled up; looking at the relation size i find that all the space is associated to a table and its primary key but no entries are present in that table (see FK error above).
If i reindex the pkey and vacuum table the space is correctly freed.
But i wonder, what is filling up space on error? is there a way to prevent that?

Error on inserting data due to FK constraint violation will leave dead tuple on the target table. That's the nature of Postgres. This dead tuple of course will occupy disk space. Things to prevent this filling up disk space :

A well tuned autovacuum can handle this wasted space.
Do a manual FK constraint check (on application side perhaps) prior to inserting data to the target tables.


SQL developer - procedure just for a session Is it possible to create a bunch of procedures just for the SQL developer session, so when the session ends the procedures are deleted?

The closest thing you can do is defining procedures in anonymous blocks, for example:

Declare
    val number;
    Procedure P1(abc in number, def out number) is
    begin
         def := abc*abc;
    end;
begin
    For i in 1..10 loop
    P1(i,val);
    dbms_output.put_line(val);
    End loop;
end;
/

Another feature is defining PL/SQL functions for a single SQL call in 12c.

Using a PL/SQL Function in the WITH Clause

For example:

WITH
 FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
   pos BINARY_INTEGER;
   len BINARY_INTEGER;
 BEGIN
   pos := INSTR(url, 'www.');
   len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
   RETURN SUBSTR(url, pos + 4, len);
 END;
SELECT DISTINCT get_domain(catalog_url)
  FROM product_information;
/


Auto Increment Primary ID inserting random values Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird IDs. So the sequence goes as follows:

Some more random ids

You can see between 27243 and 27245, and 27324 and 27325 there are some different numbers inserted. This has caused a lot of problem for us since there are FK in another table which are now missing or messed up. Even today, there was one similar incidence.

In my many years of dealing with MySQL, this is the first time ever I am seeing something like this. What could have been the reason and how to correct it?

There are no triggers. Currently table create statement shows:

CREATE TABLE `txn` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(11) DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27368 DEFAULT CHARSET=latin1;

Thanks.

I'm having a very difficult time thinking of any possible misbehaving mechanism inside MySQL or InnoDB that could cause this behavior as the result of a bug, without throwing errors or causing a crash.  It seems much more likely that the table is being updated by the application.  Instead of writing new rows, something is finding and updating old ones.

If that's not supposed to be happening, I would put a trigger on this table to block those updates.

mysql> CREATE TRIGGER txn_bu BEFORE UPDATE ON txn FOR EACH ROW
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'this table does not support updates';
Query OK, 0 rows affected (0.14 sec)

mysql> UPDATE txn SET `timestamp` = NOW() WHERE id = 1;
ERROR 1644 (45000): this table does not support updates

mysql>

This makes directly updating rows impossible.  Note that you don't need the usual DELIMITER change and BEGIN/END blocks because the trigger is comprised of a single simple statement.

If the table is never updated then the trigger would never fire, so there's no significant overhead associated with having it defined.  If it sees an update query then it's going to throw an exception back to the application which should generate exception reports or support tickets.  Arguably, if your application has a defect that's stomping data, you want it to fail hard, instead of soft.


"Active Directory - Password" login in SSMS for Azure AD - Password expired I'm trying to connect to an Azure SQL DB through SSMS (v17.9.1), using an Azure AD user, that is an Active Directory Admin for my SQL Server that hosts my Azure SQL Database.

I'm able to login with my Server Admin "azureuser", using SQL Server Authentication. But now I'm trying to use an Azure AD user (by password) to login through SSMS. I've created this user ("sqladmin") into Azure Active Directory:

And then added this AD user as an AD admin to my SQL Server:

Now, I don't know if this is the correct way to do this, but I'm resetting [sqladmin] password to know it, and then trying to connect with SSMS, but I keep getting the password expired, even when Azure Portal states that the password will be valid until the next login (where it'll be required to be changed):

Am I doing something wrong? I can't create EXTERNAL PROVIDER logins on this server either as it requires to be connected with an AD user (executing with azureuser - server admin):

CREATE USER [sqladmin@myuserhotmail.onmicrosoft.com] FROM EXTERNAL PROVIDER

  Msg 33159, Level 16, State 1, Line 5
  Principal 'sqladmin@myuserhotmail.onmicrosoft.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

When you reset a password in Azure AD, it automatically forces the user to change the PW on the next login.  If you immediately attempt to login to Azure SQL it will give you an error that the PW is expired.

Log into the Azure Portal as sqladmin@myuserhotmail.onmicrosoft.com, set a new password and this will resolve your issue.


Timeseries: SQL or NoSQL? I don't care about the general differences between SQL and NoSQL (or their traditional differences).

I am currently looking at altering the storage of our internal time series. They all contain financial data from a number of different sources. Currently, we are storing our data in a proprietary database. It's very much NoSQL, that has its own query language.

I'm interested in the community input: How would you store the data in a SQL database? What merits are there for using SQL over a NoSQL, specifically for time series? Am I insane for considering storing this in SQL?

Our data set consist of millions of time series, with around 10% of these containing millions of records each.
The time series are organised hierarchically: /Market/Instrument/Value/Frequency, where:

Market is a securities exchange, etc, basically a collection of instruments, usually similar instruments.
Instrument is an instrument. This could be an indicator (Brent Crude), an equity (GOOG), etc
Value is one of multiple types of data for an instrument. This could be a close, high, low, etc
Frequency is the frequency of a particular time series values. Weekly, daily, monthly, tick, arbitrary, etc.

How would the data be stored in a SQL db? One big table (maybe partitioned by something), one table per market or instrument, one table per time series.

Thank you in advance.

In general, for such a structured dataset I suspect you could write a custom data format which was faster for most daily operations (i.e. small data pulls from an arbitrary time).  The benefit of moving to a standard DB tool is likely in some of the extras, for example ad hoc queries, multiple access, replication, availability etc.  It's also easier to hire help to maintain a standards based data store.

If I were asked to set up a database to store that data, I would do the following:

Proposed schema

(1)  Core data is placed into numerous (1000's) of individual tables, each containing two columns:

time: either a SQL DATETIME data type or a numeric type from some epoch (this is the primary key)
value: typed as appropriate for your data. I would default to single precision float, however a fixed-point data type may be more appropriate for financial transactions.  This is probably unindexed.

These tables will get quite large, and you may want to manually partition them by (for example) year.  But you'll have to check system performance and tune as appropriate.

These tables need unique names, and there are a couple of options.  They could be human readable (e.g. nyse_goog_dailyhighs_2010) or (my preference) random.  Either way a set of metadata tables is required, and random table names prevent developers from inferring anything into the name that was not meant to be inferred.

(2)  Meta data is stored in separate tables, as required by the application:

An additional table or set of tables is required to keep track of the metadata.  These tables will contain data about exchange, instrument, value, frequency, date ranges, provenance (where did the data come from), plus anything else you need.  These are mapped to data table names.

If there is enough data, this lookup could actually provide a table name and database name, allowing a sort of self-implemented data sharding (if that is the correct use of the term).  But I would hold that in reserve.

Then at the application layer I would query the metadata tables to determine where my data was located, and then perform relatively simple queries on the big data tables to get my data.

Advantages:

My (relatively limited) experience is that databases can generally handle a large number of small tables easier than a smaller number of large tables.  This approach also enables easier maintenance (e.g. purging old data, rebuilding a corrupt table, creating/reloading from backups, adding a new entity).  This completely decouples the different kinds of data, if (for example) you have data at different rates, or requiring different data types.
This skinny table concept should also allow fast disk access for what I suspect is the most common query, a contiguous range of data from a single entity.  Most data applications are disk I/O limited, so this is worth considering.  As a commenter has already implied, this my be an ideal application for a column-oriented database, but I have yet to find a column oriented product that is mainstream enough for me to bet my career on.  This schema gets pretty close.

Disadvantages:

About half of your disk space is dedicated to storing time stamps, when quite frankly 100's or 1000's of the tables will have the exact same data in the timestamp column.  (In fact this is a requirement if you want to perform easy table joins).
Storing table names and performing the dynamic lookup requires a lot of application complexity and string operations, which kind of makes me cringe.  But it still seems better than the alternatives (discussed below).

Considerations:

Be careful of rounding in your time field.  You want your values round enough to enable joins (if appropriate), but precise enough to be unambiguous.
Be careful of time-zones and daylight savings time.  These are hard to test.  I would enforce a UTC requirement on the data store (which may make me unpopular) and handle conversions in the application.

Variations:

Some variations that I have considered are:

Data folding:  If the timeseries is equally spaced, then use one timestamp column and (for example) 10 data columns.  The timestamp now refers to the time of the first data column, and the othe data columns are assumed equally spaced between that timestamp and the next one.  This saves a lot of storage that was previously used to store timestamps, at a cost of significant query and/or application complexity.  Contiguous range, single entity queries now require less disk access.

Multi-plexing:  If multiple time series are known to use the same time series, then use one timestamp and (for example) 10 data columns as described above.  But now each column represents a different time series.  This requires an update to the metadata table, which is not a lookup into table and column name.  Storage space is reduced.  Queries remain simple.  However contiguous range, single entity queries now require significantly more disk access.

Mega-table:  Take the "multi-plexing" concept to the extreme, and put all data into a single table, once time series per column.  This requires large amounts of disk access for contiguous range, single entity queries, and is a maintenance nightmare.  For example adding a new entity now requires a MODIFY TABLE command on a many TB table.

For additional discussion on this format, see the various answers in:
Too many columns in MySQL

Fully normalized table:
Instead of using many 2-column tables, you could use one, three-column table, where the columns are time, dataid, and value.  Now your metadata tables only need to lookup ID values, rather than tablenames or column names, which enables pushing more logic into the SQL queries, rather than the application layer.

Approximately 2/3 of Storage is now consumed with the normalizing columns, so this will use a lot of disk space.

You can use a primary key order of (dataid, timestamp) for fast contiguous, single entity queries.  Or, you can use a primary key order of (timestamp. dataid) for faster inserts.

However, even after considering these variations, my plan for my next development is lots of tables, two-columns each.  That, or the method soon to be posted by someone wiser than I :).


Why a dropped index is still there, and does it prevent a table from being altered? I have a column (named RequestDate) which I need to change from NULL to NOT NULL, but there's a clustered index on that column, so I first need to drop that index.
Originally, the index was created using:
CREATE CLUSTERED INDEX IX_RequestDate
    ON [MyDB].[dbo].[MyTable] (RequestDate);

To drop it, I executed:
Drop Index IX_RequestDate On [MyDB].[dbo].[MyTable]

And then tried to change the column nullity using:
ALTER TABLE [MyDB].[dbo].[MyTable] ALTER COLUMN [RequestDate] DATETIME NOT NULL

But got an error:

The object 'MyTable' is dependent on column 'RequestDate'.

I then listed MyTable's indexes using this query, and the index is still there, though now it doesn't have a name and its type is HEAP (originally CLUSTERED):
TableName   IndexName   IndexType   ColumnOrdinal   ColumnName     ColumnType
MyTable        NULL       HEAP         0            RequestDate     datetime

I have 2 questions:

Why the dropped index is still there?
Did I got the dependency error above because of this still-existing index?
If so, how can I remove it?
If not, is there something else I should check? I'd like to avoid dropping the table.

Note that there are no constraints on this table.
I'm using SQL Server 2014, OS Windows Server 2012 R2, and the following is the output of SCRIPT TABLE AS -> CREATE TO (I changed some of the column names):
CREATE TABLE [dbo].[MyTable](
    [RequestDate] [datetime] NULL,
    [UserName] [nvarchar](50) NULL,
    [HostName] [nvarchar](20) NULL,
    [RequestContent] [ntext] NULL,
    [ResponseContent] [ntext] NULL,
    [RequestStatus] [int] NULL,
    [ErrorMessage] [ntext] NULL,
    [Duration] [float] NULL,
    [ServiceName] [nvarchar](100) NULL,
    [Direction] [int] NULL,
    [RequestId] [uniqueidentifier] NOT NULL,
    [IsRetry] [bit] NULL,
    [CallerId] [nvarchar](100) NULL
)

Why the dropped index is still there?

In fact that index is no longer there, the column is the one still there and what was once a Clustered Index is now a Heap.

Did I got the dependency error above because of this still-existing index?

No, because the index no longer exists.

If not, is there something else I should check? I'd like to avoid
dropping the table.

You can View the Dependencies of a Table as suggested by Erik Darling.

As you mentioned in your comments, you found a VIEW using this method and if you check the CREATE VIEW doc, you'll see the option SCHEMABINDING could cause the error you mentioned:

SCHEMABINDING Binds the view to the schema of the underlying table or
tables. When SCHEMABINDING is specified, the base table or tables
cannot be modified in a way that would affect the view definition. The
view definition itself must first be modified or dropped to remove
dependencies on the table that is to be modified. [...]