How to model changing OrderLine rows in fact table In our business, we have Orders that are made up of OrderItems. Over time, these Orders can change status (e.g. Received, Challenged, Planned, Completed). During this process, the OrderItems for an Order can be changed, added, or removed.

For example, when an Order is finally fulfilled, the OrderItems might actually be for different Items and different prices to the OrderItems on the original Order.

I'm wondering how to model this. Any suggestions welcome!

I should maybe create a list only with OrderNumber and Status column. Then I would create a mechanism that will delete all changed non-completed orders and load them again and another mechanism which will load newly completed Orders. I think the specific structure really depends on the structure of you Dw (how often do you perfrom loading, how many layer your solution has etc.)


How to connect to Oracle 11g using "Navicat for Oracle"? My Oracle 11g Enterprise Manager runs like this:

I have taken the following screen-shot after my Oracle installation has been completed:

I have the following Oracle-related services running:

I am able to connect to Oracle 11g with SqlDeveloper like this:

But, this is not working:

It gives the following message:

Now, how to connect to Oracle 11g with Navicat?

You need to change port 1158 to 1521 in your connection properties.

Oracle's listener for database connections runs on port 1521 by default.

Port 1158 is the web listener for Database Control, a webgui for managing the database.

In addition, the servicename should be something like "database.domain.com", or you can try using SID and specifying ORCL.


Is an SQL Server trace 'considered harmful' in production? Context: Careful DBAs

We've been bought out a few times and our latest overlords have DBAs who keep carefully guard their production database servers.

Overall this is good.

However, we have run into one point of contention between developers and DBAs: they will not run traces against production database servers.

Our experience as developers: SQL Server trace + RML Utilities rocks

In the past, when application instances ran sluggish, we would run a trace via an .sql script, zip up the output, and process it with Microsoft's RML Utilities.

Note that we do not run the GUI-based profiler tool as that dramatically slows down performance (the database needs to wait on updating the GUI). These traces simply "log activity" to the database server's local filesystem. Our scripts run a trace for a prescribed number of minutes at a time (for example, 10 minutes).

The combination of trace and RML Utilities works great: The report is easy to use. It shows hotspots (see below), the reports give helpful information, they allow drilling down to the actual execution plans, etc. It's much better than anything Oracle offers (or at least a few years ago).

(Hotspot: if a query takes 1/10 second, but executes 400,000 times, it wil appear higher in the report, higher than the once-executed query that takes 30 seconds.)

I've done a bit of performance work over the years. My experience is that capturing 'actual data' from 'real databases under real load' trumps programmers 'taking a guess at' and simulating the problem they imagine. This is why I think that the SQL Server trace is pretty much  'silver bullet' for killing the problem.

My Questions

What are the main, legitimate concerns against running SQL Server traces in production (filling up the disk, slowing down performance, etc.)?
Are these concerns valid? If so, how can we address them?
Taking a trace and profiling the "actual problem" seems like the clearest step toward understanding and solving the situation. What are the alternatives?

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing.  A screenshot from the help file:

They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, SP:StmtStarting, SP:StmtCompleted), ie every statement in every proc.  This could easily bring a server to its knees under the wrong conditions, eg scalar function in a SELECT statement running across a big table.

Extended Events (XE) were a bit limited in versions prior to 2012, but it is more lightweight.  You still need to be careful as SQL statement-level events will still incur overhead for very intensive workloads (sqlserver.sp_statement_starting, sqlserver.sp_statement_completed, sqlserver.sql_statement_starting, sqlserver.sql_statement_completed).  Execution plan capture (sqlserver.query_post_execution_showplan) can also be intensive.  Consider using the XETraceCaptureDefLightWeight.sql template which is provided and includes less events.

In summary I suggest moving to XE and using the lightweight XE definition provided with the latest versions of RML.


Normalization Excercise Clarification Have a bit of confusion regarding a normalization from 1NF to BCNF.  The teacher gave us this excercise to illustrate a normalisation all the way up to BCNF.  I think I have found a problem with the given solution and would like to clear this up.

The way I have solved it up to 3NF is as follows:

My understanding is that there are TWO partial dependencies here

The Primary key is made up of (SID, InstructorID)

The Subject is dependent only on the the InstructorID and not on the entire primary key
The Name is dependent only on the SID only and not on the entire primary key

Finally, 3NF where the transitive dependency has now been removed and refactored into it's own table.
No BCNF violations here!!!

QUESTION:

Is my solution correct?

have I correctly performed the 2NF step?

So The question has been answered in great detail in chat by ypercubetm https://dba.stackexchange.com/users/993/ypercube%e1%b5%80%e1%b4%b9

The short anwer is yes the solution above is one possible correct solution

Many thanks to ypercubetm for taking the time breaking it down for me!


On Prem SQL Server: Log shipping via Azure Blob Storage: Possible? This is about on premise, regular SQL Server 2014, Standard edition.

This is NOT about SQL Azure. The only part of Azure in this conversation is Azure Blog Storage, as a transport for some bits (SQL backups and logs)

Scenario:

Primary SQL Server is in data center 1.

Backup is in data center 2.

In SQL 2014, is it possible to use the "backup to url/azure blob storage" to be the log shipping pipeline?

We like the idea of backup of db and logs to azure blob storage. Great, easy, automatic off site storage of backups.

Would be awesome to use the same pipeline for log shipping to backup db server.

Doable?

(Notes: Neither server is member of a domain. These are two stand alone servers, with no AD or other relationship with each other.)

Yes, but only manually - Azure Blob Storage isn't integrated into the log shipping wizards. You would need to do your own backups to Azure Blob Storage, and then have the secondary server(s) restore from ABS using the same certificates.

When other shops have done similar projects with AWS's S3 storage (writing their SQL Server backups there using 3rd party tools), I've seen them either log backups to a text file that the other SQL Servers check for backup files, or log entries into a centrally accessible table (like SQL Azure or Amazon RDS) and fetch the backup file names from there.


What are the best practices for running SQL Server in a Virtual Machine? What are the best practices for running SQL Server in a Virtual Machine? My on-line transaction activities are very low, but there is a high amount of data processing for the purpose of providing reporting data to multiple web sites.

As with physical machines IO is king. There will be a noticeable IO hit from virtualisation (how much of a hit depends on the technology you choose), so make sure that you do nothing that can exacerbate this and harm IO performance further.

Always used fixed size, not dynamic virtual disks, and ensure that the underlying drives/arrays are sufficient. Dynamic vdisks are less performant than fixed size ones.
If the data in the VMs is not mission critical, turn on the VM solutions ability to cache writes (effectively lying to the guest OS about when things are physically written to disk) for extra performance. This is useful for a development and testing machine where the data is easily replaced, or read-only replicas local to the master that can be recreated if they experience any trouble, but it is recommended that such options stay off for production and backup systems as they increase the chance of data loss (or worse, corruption) if the machine powers off unexpectedly.
Ensure that each VM has enough RAM dedicated to it such that the normal working set of each database fits into memory with a fair amount of room to spare to avoid unnecessary IO. Also, ensure that the hypervisor will not page out the VMs RAM at any time (for instance most VMWare products can do this, which allows you to run a larger set of VMs than you otherwise could on the same hardware, but the performance degradation can be massive - there is an option to tune or turn off this behaviour).
If the host uses RAID5 arrays, consider moving to RAID10. This will reduce the space available but remove the write performance problems that can be apparent with RAID5 (or 6).
For VMs that are likely to see a lot of IO activity, consider giving them their own drive or array, or only have them share their drive(s)/array(s) with data that sees low activity.

Of course if your entire DB (and everything else the VM is running) fits into the RAM allocated to the VM and it sees very little write activity, IO performance can be much less of an issue.

Edit: a few more points:

If using virtual disks make sure that the best controller type is used for performance. Some virtualisation solutions offer several virtual controller types for better guest compatibility and you might find some less efficient than others due to protocol limitations in the design they are emulating and limitations in the guest OS's drivers.
If your virtualisation solution offers specific drivers for the virtual drive controller, make sure your guest OS is using them - these can speed up bulk IO by reducing the number of memory-to-memory copies and host<->hypervisor<->guest context switches involved in some operations.
The two points above apply if you are using network storage such as simple SMB shares or iSCSI too: make sure that you are using the most efficient virtual network controller on offer and that your guest OS is using the recommended driver for it, otherwise both latency and bandwidth may be more limited than needs be by the virtualisation layer.


Stop pg_restore from returning false errors? How do you stop pg_restore from returning an error code if it encounters a meaningless non-error like “schema "public" already exists”?

I'm trying to automate a database transfer, and after I upgrading PostgreSQL to 12, pg_restore throws an error condition for things that aren't actually errors.

For example, my database setup script is basically:

sudo psql --user=postgres --no-password --host=localhost   --command="DROP DATABASE IF EXISTS mydb;"
sudo psql --user=postgres --no-password --host=localhost   --command="CREATE DATABASE mydb;"
sudo psql --user=postgres --no-password --host=localhost --dbname mydb  --command="DROP USER IF EXISTS myuser; CREATE USER myuser WITH PASSWORD 'supersecretpassword';"
sudo psql --user=postgres --no-password --host=localhost --dbname mydb  --command="GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;"
sudo pg_restore -U postgres --format=c --create --dbname=mydb /tmp/mydb_snapshot.sql.gz

However, even though the last line logically succeeds in loading the database snapshot, it returns an error code, reporting that:

pg_restore: error: could not execute query: ERROR:  database "mydb" already exists

It seems like a change in PostgreSQL v12 is that creation of a new database also now populates it with a default public schema, which conflicts with the public schema in any snapshot. I never ran into this issue in v11 or v10.

I could modify my script to ignore stderr, but then I'd be blind to actual problems.

How do I tell pg_restore to ignore this error, but still report other errors?

That is not a meaningless error: you used --create to tell pg_restore to create the database, but it is already present. This does not make a lot of sense, since with --dbname=mydb you tell pg_restore to connect to mydb to issue that CREATE DATABASE statement.

So if the database doesn't exist, you get an error because you cannot connect to it, and if it exists, you get an error because you cannot create it.

You should simply omit the --create option, then you are rid of the error.

This is all not new in PostgreSQL v12, so the error would occur with older versions of PostgreSQL as well. Also, the public schema has always been created automatically with CREATE DATABASE.


What is the role of eager writer? What is the role of eager writer? How this process works internally in SQL server?
What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both eager and lazy writer are doing the same job - hardening dirty pages to disk. Is my understanding correct or am I missing any thing over here?
I've read at some blog that, "there is no chance for roll forward operation in bulk logged recovery model as the trasactions are minimally logged". If we take the case where a log backup consists of minimally logged transactions, how could this log restore can become successful without any roll forward operation?

I really confused with above questions. Please help me in learning more about eager writer.

Thank you.

The information about Eager writer process is available in BOL documents

  Eager writing

  The eager write process writes dirty data pages associated with
  minimally logged operations such as bulk insert and select into. This process
  allows creating and writing new pages to take place in parallel. That
  is, the calling operation does not have to wait until the entire
  operation finishes before writing the pages to disk.

Dirty pages: All pages that have changed in memory since they were read from disk or since the last checkpoint are dirty pages. When pages needs to be read or written onto they are brought from disk into memory.

The eager writing process do not wait for the I/O operation to complete. They always use asynchronous (or overlapped) I/O and continue with other work, checking for I/O success later. This allows SQL Server to maximize both CPU and I/O resources for the appropriate tasks.

  My understanding here is, that both eager and lazy writer are doing the same job - hardening dirty pages to disk. Is my understanding correct or am I missing any thing over here?

Yes they both write dirty pages but they are not same. About checkpoint I suggest you read What is checkpoint and what gets logged. Checkpoint writes all dirty pages to disk while eager writer process writes dirty data pages associated with minimally operations such as bulk insert and select into to disk. Checkpoint also periodically scans the buffer pool.

  there is no chance for roll forward operation in bulk logged recovery model as the trasactions are minimally logged.

What you are saying is correct but I would quote, Kalen Delaney who defined minimally logged operations as:

  ones that log only enough information to roll back the transaction, without supporting point-in-time recovery

What happens in BL recovery model is only enough information is logged to rollback the transaction no information is logged to roll forward the transaction this is clearly shown by Gail Shaw in this Article.

  If we take the case where a log backup consists of minimally logged transactions, how could this log restore can become successful without any roll forward operation?

First you must note that in every restore it is not necessary that you need to have a roll forward operation. The roll forward comes into picture when transaction changes page which has been read by backup operation and if that transaction commits while backup is going on then when such backup is restored a roll forward of transaction is required and that is done by reading transaction log.

In case of minimally logged operation the eager writer process guarantees that changes to extent/pages are hardened on disk before the transaction is complete. So the transaction log has information ONLY about the committed changes of the bulk logged operation and so if required it can rollback(it would just go and de-allocate the extents or pages allocated). Since the changes are already written to disk there is no need for roll forward.


PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown? I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines.  I'm also pretty sure that all of you who choose to answer are going to want a lot more in the way of specifics than I can currently deliver.

That said, I'm curious about the following scenario in general:

Say that I have two non-trivial queries.
Query 1 requires 2 minutes to complete on average.
Query 2 requires 5 minutes to complete on average.

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average.  Is this reasonable?

More than that, however, what if I run the two queries concurrently?  Two separate connections at the same time.

Under what conditions would I expect to see a speedup?  (Total time < 7 minutes)
Under what conditions would I expect to see a slowdown?  (Total time > 7 minutes)

Now, if I had 1,000 non-trivial queries running concurrently, I have a hunch that it would result in an overall slowdown.  In that case, where would the bottleneck likely be?  Processor? RAM? Drives?

Again, I know it's probably impossible to answer the question precisely without knowing specifics (which I don't have.)  I'm looking for some general guidelines to think about when asking the following questions:

Under what circumstances do concurrent queries result in an overall speedup?
Under what circumstances do concurrent queries result in an overall slowdown?

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable?

If they use unrelated data sets, then yes.

If they share a data set, and the cache is cold for the first query and the query is mostly I/O bound, then the second one might complete in moments. You need to consider caching effects when dealing with performance analysis and query timing.

  More than that, however, what if I run the two queries concurrently? Two separate connections at the same time.

"It depends".

If they were both using sequential scans of the same table then in PostgreSQL it'd be a huge performance win because of its support for synchronized sequential scans.

If they shared the same indexes then they'd likely benefit from each others' reads in to cache.

If they're independent and touch different data then they might compete for I/O bandwidth, in which case they might take the same amount of time as running sequentially. If the I/O subsystem benefits from concurrency (higher net throughput with more clients) then the total time might be less. If the I/O subsystem handles concurrency poorly then they might take longer than running them sequentially. Or they might not be I/O bound at all, in which case if there's a free CPU for each they could well execute as if the other wasn't running at all.

It depends a great deal on the hardware and system configuration, the data set, and on the queries themselves.

  Now, if I had 1,000 non-trivial queries running concurrently, I have a hunch that it would result in an overall slowdown. In that case, where would the bottleneck likely be? Processor? RAM? Drives?

Yes, that'd very likely slow things down for a number of reasons.

PostgreSQL's own overheads in inter-process coordination, transaction and lock management, buffer management, etc. This can be quite a big cost, and PostgreSQL isn't really designed for high client counts - it works better if you queue work.
Competition for working memory, cache, etc.
OS scheduling overhead as it juggles 1000 competing processes all wanting time slices. Pretty minor these days, modern OSes have fast schedulers.
I/O thrashing. Most I/O systems have a peak performance client count. Sometimes it's 1, i.e. it's best with only one client, but it's often higher. Sometimes performance decreases again above the threshold. Sometimes it just reaches a plateau.


#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' I have a table with several "categories", called collection (e.g. A, B, C, ...).

I would like to create a SQL query to select N records by each collection.

For example:

I would like to select 2 records by each collection.

Table stac_item:

id | collection | name
1  | A          | name_01
2  | A          | name_02
3  | A          | name_03
4  | B          | name_04
5  | B          | name_05
6  | B          | name_06
7  | B          | name_07
8  | C          | name_08
9  | C          | name_09

I expect the following result:

id | collection | name
1  | A          | name_01
2  | A          | name_02
4  | B          | name_04
5  | B          | name_05
8  | C          | name_08
9  | C          | name_09

I have created the SQL query below, but MariaDB gives the following error message:

#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

SQL query:

SELECT *
FROM stac_item a1
WHERE a1.collection IN
(
    SELECT a2.collection
    FROM stac_item a2
    WHERE a1.collection = a2.collection
    LIMIT 2
)

Would anyone know an alternative that I could use? I have searched on the internet, but I was not able to find a solution.

Thank you.

EDIT 1:

I use the following MariaDB Docker image: mariadb:10.5.3

You could use the window function row_number, like so:

select id, collection, name from (
  select id, collection, name,
  row_number() over (partition by collection order by id) rn
  from stac_item
) t
where rn < 3
order by id


How do you setup a new oracle instance from the commandline after having done a database software only install? I'm trying to familiarise myself with the process for setting up new instances/databases from the commandline with the aim of building a tool for deploying databases.

The process we are thinking of using is

(optionally) create a new instance to deploy to
import some data using impdp
run upgrade scripts on the database to get it to the desired version

The bit I'm currently focusing on (+ have just cracked) is the instance creation, although I'd be interested to hear comments on other parts of it + any information on alternative approaches or things that are a good idea!  Im very new to databases aswell as oracle and am having to figure things out mostly from scratch.

The key requirement for all this is that is should be done in a way that can be scripted so we can automate the process.

I'm working on windows with 12c standard edition one and installed just the database software.

Creating a new instance

The following command line call will create a new database + instance

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid -sysPassword pass -systemPassword pass

because I have a dedicated oracle user for my install I also had to use -serviceUserPassword.  With regards to the passwords, despite being in silent mode if you miss these out, then you will be prompted for them rather than the program failing so while technically the minimum is

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid

you wont be able to script that - the program will just hang indefinitely.

this command's usage is slightly mysterious and if you get it wrong it provides the amazingly useful message:

'Not all required options provided for the command "createDatabase".Please refer to the manual for details.'

Thankfully if you trawl through the reams and reams of online documentation eventually you can find what information it needs here along with what all the options are and what they are for.

A further thing I found here is that it can fail but it doesnt tell you from the command prompt - you should check the log files that it makes (it will tell you where these go).  Just because it says 100% complete doesnt mean it has actually worked!  You could just have got 100% of the way to failure!  In particular if it finishes very quickly then this is a warning sign - expect it to take at least a couple of minutes.  I found I needed to run this as administrator the second time around - I suspect this is always the case but on the first run the UAC was disabled.

Starting the listener

This bit is once per-server, so doesnt need scripting for my needs.  I thought it was worth mentioning though as a few more things were required to make my instance usable from other machines.

I found that after doing the above I couldnt connect to the database remotely but could from sqlplus locally.  In someways this local access was a false positive as it turned out there was no listener running on the server (probably because of the software only install).  You can determine if this is the case by looking to see if TNSLSNR.exe is running on the server.

Thankfully starting the listener is easy if it isnt already running.  You use a command line program called lsnrctl.  This is probably the nicest oracle command line program I've used.  It tells you how to use it and just worked without any messing about.  You can just use it interactively and type 'start' when it comes up.

Note you may need to run the above from an administrator shell  If you see something like the following:

LSNRCTL> start
Starting tnslsnr: please wait...

Unable to OpenSCManager: err=5
 TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error

then try doing it as admin.

Automatic registration

After it started I still couldnt connect to the database externally.  You can use lsnrctl status to determine if the listener knows about the database instance.  If you get a message somewhere in the output that says

The listener supports no services

then it isnt working.  I wanted to use automatic registration because it sounded like the way to go and it turned out from a bit of reading, that instances register with listeners when they start.

To restart an instance you do the following (docs here):

Use the sqlplus command line. You will need to login as a user with SYSDBA priveledges (eg system) syntax:

sqlplus name/pass as SYSDBA

then run 'shutdown' and wait for it to finish. then run 'startup' and wait for it to finish.

There is a good post here that also suggests another way of doing it

note if you haven't got a tnsnames.ora file you will need to specify the host in the connection string  - this wont work:

sqlplus name/pass@serviceName

do this instead:

sqlplus name/pass@//localhost/serviceName

Hey presto you should have your instance registered with the listener.  You can verify this by running lsnrctl status again.

You should see something like this:

Service "horse" has 1 instance(s).
 Instance "horse", status READY, has 1 handler(s) for this service...

Opening a firewall port

The final step in making the instances on the server accessible is to open a port on your firewall.   You can determine the port you need to open from the lsnrctl status message - there should be a section that looks like this:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))

1521 is the default port if you don't have a listener.ora file as mentioned here

To open a port on the firewall on our windows 7 server I used the following instructions.

After doing all of the above I could connect to the instance from my local machine.  Sucess!  Hopefully deploying more instances is just going to involve repeating the dbca command.  Since the listener will already be up it should just register with it immediately.  I'll try this out and post any further complications.


Database restore using SSMS leaves source database in restoring state I am using SQL Server 2016 (Developer edition) and wanted to create a copy of a database in my Server instance. I did a simple backup and then a restore by right clicking the database again -> Tasks -> Restore.

By changing the name of the database, the database indeed gets restored in a new created database. So far, so good. However, the original database, that I started with, and where I started the restore from, gets a (Restoring...) state which is impossible to change in a normal way.

Of course I know how to restore a database differently without creating this problem and, I have my original database working again, but my question is this:

Is this normal behavior? I think I stumbled upon a bug.

This is "normal" behaviour in SSMS if you check the box "Take tail-log backup before restore" and the box "Leave source database in the restoring state"

It may not be intuitive and it's probably not the best decision ever to check those boxes by default but you have to un-check the box "leave source database in restoring state" to avoid that behaviour:

So it's not a bug, it's standard SSMS behaviour that changed in a more recent version (I believe those options appeared in 2012 but I'm not sure).

If you want to avoid strange or buggy behaviour in SSMS your best option would be to perform your restores using T-SQL scripts so you have something reproducible even when you change your SSMS version.

See this connect item which clearly states this is by design.


How to LOCK/BLOCK selected row in table to be ReadOnly? Is it possible to LOCK completely or make READ-ONLY where id = value in MariaDB?
or add some trigger locking if someone is trying to update selected row ?

Some people says:
"Trigger is not a good way to prevent update" .... well, just because MySQL/MariaDB allow you to do it and it is the easiest solution I decided to use/create one as follows:

CREATE TRIGGER `my_trigger` BEFORE UPDATE ON `my_table`
    FOR EACH ROW IF NEW.locked THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You are not allowed to change
    this record, please contact your_administrator';
    END IF

As long as it does the job .... i'm happy.
If there is any "against" please let me know.

When would you unlock it?

A typical pattern is:

BEGIN;
SELECT ... FOR UPDATE;
scratch your head, then eventually
UPDATE that row (or not)
COMMIT;

That keeps anyone else from messing with the row, but they can read it.  It's not quite "ReadOnly", and the "lock" is released at the COMMIT.

If this does not suffice, please describe your use case more thoroughly.


(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL Why is x IS NOT NULL not equal to NOT x IS NULL?

This code:

CREATE TABLE bug_test (
    id int,
    name text
);

INSERT INTO bug_test
VALUES (1, NULL);

DO $$
DECLARE
    v_bug_test bug_test;
BEGIN
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);

    SELECT *
    INTO v_bug_test
    FROM bug_test
    WHERE id = 1;

    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);
END
$$;

DROP TABLE bug_test;

gives following output:

(,): t
(,): f
(,): f
(1,): f
(1,): f ???
(1,): t

while I would expect to get this output:

(,): t
(,): f
(,): f
(1,): f
(1,): t <<<
(1,): t

You have to distinguish two situations: you compare one COLUMN against NULL, or you compare the whole ROW (RECORD) against NULL.

Consider the following query:

SELECT
    id,
    txt,
    txt     IS NULL AS txt_is_null,
    NOT txt IS NULL AS not_txt_is_null,
    txt IS NOT NULL AS txt_is_not_null
FROM
    (VALUES
        (1::integer, NULL::text)
    )
    AS x(id, txt) ;

You get this:

+----+-----+-------------+-----------------+-----------------+
| id | txt | txt_is_null | not_txt_is_null | txt_is_not_null |
+----+-----+-------------+-----------------+-----------------+
|  1 |     | t           | f               | f               |
+----+-----+-------------+-----------------+-----------------+

This is, I guess, what you and I would expect. You are checking one COLUMN against NULL, and you get "txt IS NOT NULL" and "NOT txt IS NULL" are equivalent.

However, if you do a different check:

SELECT
    id,
    txt,
    x       IS NULL AS x_is_null,
    NOT x   IS NULL AS not_x_is_null,
    x   IS NOT NULL AS x_is_not_null
FROM
    (VALUES
        (1, NULL)
    )
    AS x(id, txt) ;

Then you get

+----+-----+-----------+---------------+---------------+
| id | txt | x_is_null | not_x_is_null | x_is_not_null |
+----+-----+-----------+---------------+---------------+
|  1 |     | f         | t             | f             |
+----+-----+-----------+---------------+---------------+

This may be surprising. One thing looks reasonable (x IS NULL) and (NOT x IS NULL) are the opposite of one another. The other thing (the fact that neither "x IS NULL" nor "x IS NOT NULL" are true), looks weird.

However, this is what the PostgreSQL documentation says that should happen:

  If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that  contains both null and non-null fields will return false for both tests. In some  cases, it may be preferable to write row IS DISTINCT FROM NULL or row IS NOT  DISTINCT FROM NULL, which will simply check whether the overall row value is null  without any additional tests on the row fields.

I must confess I don't think I've ever used a row-valued comparison against null, but I guess that if the possibility is there, there might be some use-case for it. I don't think is common, anyhow.


How to find usage of each object in other objects from a database? I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all relations inside the code.

So my mission is to identify usages, relationships and make a graphic model, which I think I will use ERwin7 to do it.

I stated today taking every table name and searching it in the source-code and sql metadata. I open all those files in separated instances of Notepad++ and search to see if anyone really uses that table in the source-code or in any stored procedure or function. But there are 1500+ c# files and 857 objects in the database.

Is there a better way to do that?
Is there a tool to tell me where each table is used in the code and in the database without the need of Ctrl+F for each table name or other objects I will also check?
Is ER-win a reasonable choice for doing the re-modeling?

Hopefully, they have named the FK's the same as the PK on the table it is linked to, this could then make it a whole lot easier. Why? What you could do is first return all the table/column names in the database with something like this:

SELECT Table_Name, Column_Name, Ordinal_Position
INTO #TempColumnNames
FROM information_schema.columns
ORDER BY Table_Name, Ordinal_Position

You could then join the table to itself based on the Column_Name, something like:

SELECT t1.Table_Name, t1.Column_Name
FROM #TempColumnNames t1
INNER JOIN #TempColumnNames t2 ON t1.Column_Name = t2.Column_Name
WHERE t1.Table_Name <> t2.Table_Name
AND t1.Ordinal_Position = 1

That should give you a long list of all the tables that have another table with the same column name in it. Remember, this is all dependent on the FK column having the same name as the PK column!

EDIT: I have edited the above to add "Ordinal_Position" which indicates the order the field appears in tables. Almost always, the PK field is listed first, but not always. Check that this is consistent in your DB. This will only link the first field that has the same name as another field and it assumes the table where that field appears first is your PK table.

You can then do something like this:

SELECT t1.Table_Name, t1.Column_Name, t2.Table_Name, t1.Ordinal_Position,
    'ALTER TABLE [' + t2.Table_Name + '] ADD CONSTRAINT FK_' + t2.Table_Name
    + '_' + t1.Table_Name + ' FOREIGN KEY (' + t1.Column_Name + ')'
    + ' REFERENCES [' + t1.Table_Name + '] (' + t1.Column_Name + ')' as FK_TSQL
FROM #TempColumnNames t1
INNER JOIN #TempColumnNames t2 ON t1.Column_Name = t2.Column_Name
WHERE t1.Table_Name <> t2.Table_Name
AND t1.ORDINAL_POSITION = 1

This should give you a whole stack of statements for creating the FK on the FK table and linking it to the PK table. Be careful though, this doesn't do it perfectly. Limitations are:

PK's that are more than 1 field will not work (think of many to many link tables etc).
It does not include schema's though this can be added easily (you may have multiple schemas with the same table names)
You will likely still need to check RBAR (Row By Agonising Row) to ensure these are valid, but essentially you have written out all the code for creating the relationships, you just need to delete those not applicable.


Understanding how relationship sides works So, I have this relations on my tables (see image) and I need to understand some things. I'll enumerate what I understand and what I need from yours is to say if I'm right or not and in case I'm not give me the good answer.

If general_orders_id is FK on natural_person and legal_person tables if I set onUpdate and onDelete to CASCADE what this mean?

1- When I delete a general_orders the cascade will delete all the records on natural_person and legal_person tables?

2- When I delete a natural_person what happen with the general_orders that points to that person?

In order to maintain a good table structure, data consistency and integrity, what are you're suggestions regarding this topic?

0- CASCADE means to 'replicate' the changes that happens on the 'master' table to the 'details' tables. For simplicity, assume the relation (country, city). Each city[detail] belongs to a country[master], or each city is a child of a country. With cascade, if you delete a country, all cities belong to that country will be deleted.

In your case, general_orders is the master table (call it the parent). When you delete an order, the referenced rows from natural_person and legal_person will be deleted.

1- No, not all records will be deleted. Only the referenced one will be.

2- Nothing will happen to general_order table. (If you delete a city, the country will still be there)

Regarding the design: It depends on your model, but I feel that the design is not right. i.e. I think each order should belong to a 'person', and this person's type is either natural or legal. So, I would have:

grnaral_order(id, ..., person_id, ...)
person(id, type[legal, natural])
natural_person(id, name, ...)
legal_person(id, company_name...)

This is one way, but not necessarily the best one, because it totally depends on your analysis


Different plans for the same SQL code I have a slow stored procedure like

CREATE PROCEDURE [dbo].[MyProc]
(
    @Param1 nvarchar(20),
    @Param2 nvarchar(20)
)
AS
BEGIN
    set nocount on

    ;with cte (col1, col2, col3)
    as
    (
        select col1, col2, col3
        from    someTable
    --And the rest of it

I am trying to optimise it because it was taking too long.

When I call it as exec MyProc @Param1=N'xxx', @Param2=N'yyy' it takes 20 seconds or so. To test what I could do I ran it like this:

DECLARE @Param1=N'xxx',
@Param2=N'yyy'
set nocount on

;with cte (col1, col2, col3)
as
(
    select col1, col2, col3
    from    someTable
--And the rest of it

and that takes about a second. Execution plans are completely different. Plan that is used when I call 'exec ...' is different to the plan that is used when I run the code directly. I re-indexed the tables used, I called sp_recompile on the table and the stored procedure itself, I found the plans that are used for the SP and called DBCC FREEPROCCACHE ( on the plan handles, but every time the plan is re-generated, they are different.

Any idea what's going on? Why would the same piece of code use different plans, one that takes 1 sec and the other taking 20 secs?

This is called parameter sniffing, and it's one of the hardest problems to solve in a database.

The basic problem is that data is skewed in a way that makes the cost based optimizer choose different plans for returning your data.

There are a lot of ways people think they "fix" parameter sniffing:

Recompile hints
Optimizer for unknown
Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

Indexes used
Join order
Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

Lookups
Sorts
Join type
Memory grants (and by extension, spills)
Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about.

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting. If you want more help, please share the query plans, along with the code, table and index definitions.

Otherwise, good luck!


trigger or check constraint insert values based upon existence of others For simplicity (and since it captures the essence of my problem), suppose I have a table with data.

CREATE TABLE my_practice_table
(

my_id       NUMBER(12),

field_2     VARCHAR2(20),

field_3     VARCHAR2(20)

);

I want to define a new constraint on the table that has the following logic:

For any new record x inserted into my_practice_table,

if (x.field_2 is null)

   if [there exists a record y in the table such that

   i.) y.my_id == x.my_id

   AND

   ii.) y.field_2 is not null
    ]

       --do not insert x into the table

   else
      --there exists no such y in the table, so we insert x

else (x.field_2 is not null)

 if [there exists a record y in the table such that
    i.) y.my_id == x.my_id
    AND
    ii.) y.field_2 is null
   ]
     --do not insert x into the table
else
     --there exists no such y in the table, so we insert x

Example: Suppose my_practice_table had the following data
    table1: [1    'hi'     'bye' ]
            [2    'hello'   'adios']
            [1    'hi'      'seeya']

The statement:
INSERT INTO table1
(my_id, field_2, field_3)
VALUES
(1, null, 'ciao');

won't work because there is already a record in table1 with my_id equal to 1 and field_2 not equal to null.

Example: Suppose my_practice_table had the following data:
    table2: [1    'hi'     'bye' ]
            [2    null   'adios']
            [1    'hi'      'seeya']

The statement:
INSERT INTO table2
(my_id, field_2, field_3)
VALUES
(2, 'hola', 'this won't work');

won't work because there is already a record in table2 with my_id equal to 2 and field_2 equal to null.

My question is what is the way to go about this in Oracle syntax. The algorithm is simple enough, but I'm just not comfortable enough with Oracle syntax to get the job done.
Do I use a BEFORE INSERT Trigger? How do I scan for values in other fields of a particular row once I found a match on the field my_id? Is there a 'break' statement in Oracle that I can do to speed this up?
Any help or guidance to the right place to look for a possible solution would be appreciated. Thanks

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.


mongostat causing CPU 100% On a 64Bit Debian 6 OS with 4 CPU cores and 8GB RAM, I can reproduce an issue with mongostat.

Whenever I create an index, reIndex or even index in background, according to top, the CPU load is 40-60% and RAM is filling up to 90%.
While index task is running, I try mongostat and watch the CPU load. Immediately, CPU load is nearly 100% and blocking.

  mongostat

  connected to: 127.0.0.1

Nothing after that, only CPU load at the limit until Ctrl + C for stopping mongostat. Few seconds later, CPU load scales down to ~50% and all is fine again.

Trying mongostat again with the same behaviour...

Is mongodb expecting more CPUs or what's wrong?

The root issue here is the index building - when you are building an index, you do so (by default) in the foreground and it blocks all other operations (including the mongostat polling).  In addition, the index build requires external sorting, which is computationally intensive, and potentially significant IO.   In terms of how much CPU and IO hit you should expect, the size of the index, the number of keys and the amount of data will dictate that - generally, the more data you have and the more complex the index, the longer and more expensive this process will be.

To avoid this issue, you can set your index to build in the background, though you should note that until the fix for SERVER-2771 is available in a stable release (checked into 2.5.0 as of writing this answer) the indexes will build in the foreground on secondaries (and hence still be blocking operations).  That is why this guide to building indexes on Replica Sets is still required reading for building large indexes on a set.

Finally, in terms of why mongostat made things worse from a CPU perspective - it is likely unable to poll successfully due to the blocking operation of the index build and this is manifesting as increased CPU until you kill mongostat.  This should be pretty easy to reproduce, and (depending on the level of CPU impact, and the version you are using) would potentially be grounds for filing a bug.  If it still happens with a background index build, as described above, that would be far more worrying.


Postgresql, very long joins I have two tables Places and Redemptions (details in gist)

https://gist.github.com/kunashir/2648e6e4d8a97aa2d03859b8ee08a394

And the query takes a lot of time:

SQL (26910.2ms)  SELECT "redemptions"."id" AS t0_r0,
"redemptions"."uuid" AS t0_r1, "redemptions"."code_value_id" AS t0_r2,
"redemptions"."created_at" AS t0_r3, "redemptions"."updated_at" AS t0_r4,
"redemptions"."place_id" AS t0_r5, "redemptions"."user_id" AS t0_r6,
"places"."id" AS t1_r0, "places"."name" AS t1_r1, "places"."geo_fence" AS t1_r2,
"places"."external_id" AS t1_r3, "places"."city" AS t1_r4,
"places"."country" AS t1_r5, "places"."bounding_box_north" AS t1_r6,
"places"."bounding_box_south" AS t1_r7, "places"."bounding_box_east" AS t1_r8,
"places"."bounding_box_west" AS t1_r9, "places"."image" AS t1_r10,
"places"."created_at" AS t1_r11, "places"."updated_at" AS t1_r12,
"places"."latitude" AS t1_r13, "places"."longitude" AS t1_r14,
"places"."image_uid" AS t1_r15, "places"."image_name" AS t1_r16,
"places"."featured" AS t1_r17, "places"."active_benefits_count" AS t1_r18,
"places"."radius" AS t1_r19, "places"."terminal" AS t1_r20,
"places"."deleted_at" AS t1_r21, "places"."category" AS t1_r22,
"places"."street" AS t1_r23, "places"."zip" AS t1_r24,
"places"."twitter" AS t1_r25, "places"."push_notification_message" AS t1_r26,
"places"."timezone" AS t1_r27, "places"."twitter_boost" AS t1_r28,
"places"."push_notification_message_enabled" AS t1_r29,
"places"."geojson" AS t1_r30, "places"."push_notification_image_uid" AS t1_r31,
"places"."searchable" AS t1_r32, "places"."boost" AS t1_r33,
"places"."geojson_active" AS t1_r34
FROM "redemptions" LEFT OUTER JOIN "places" ON "places"."id" = "redemptions"."place_id" AND "places"."deleted_at" IS NULL

The result of explain: https://explain.depesz.com/s/PYPC

I can't understand when is bottleneck or all is right?

The query itself only takes 82 milliseconds (0.082 seconds).

The reported 26 seconds are either caused by the network or by the application (or SQL client) processing and displaying the result. As far as I can tell, it's highly unlikely that it's the network as the 46375 rows add up to roughly 30KB of transmitted data.


Cannot open SQL Server profiler file Scenario

Debugging a failed transaction, I asked the DBA to run a trace on SQL Server. Eventually he send me a .trc file.

Issue

I use the fn_tracegettable() function to access the trace:

select * from sys.fn_trace_gettable('C:\path\to\myTraceFile.trc', default)

But I get the following error:

  Msg 567, Level 16, State 7, Line 1
  File 'C:\path\to\myTraceFile.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

Facts

The SQL Management Studio I am executing the fn_tracegettable from runs in my local machine.
The .trc file is in my local machine.
Both the server and the local machine run a SQL Server 2008 R2.

I had the same issue. I ran an SQL trace on a remote server and transferred
the trace files to a local directory on my workstation so that I load the
data into a table on my local SQL Server instance for running queries
against.

SELECT * INTO ProfileTracesTable
FROM ::fn_trace_gettable('C:\Users\anthony\Documents\SQL_traces\first.trc', default)

However, each time I tried, I got the following error:

  File 'C:\Users\anthony\Documents\SQL_traces\first.trc' either does not
  exist or is not a recognizable trace file. Or there was an error opening
  the file.

At first I thought the error might be related permission but I ruled this
out since I had no problem loading the .trc files directly into SQL Profiler
or as a file into SSMS.

After trying a few other ideas, I thought about it a bit more and realised
that it was due to permissions after all: the query was being run by the SQL
Server process (sqlsrvr.exe) as the user NT AUTHORITY\NETWORK SERVICE –
not my own Windows account.

The solution was to grant Read and Execute permissions to NETWORK
SERVICE on the directory that the trace files were stored in and the trace
files themselves.

You can do this by right-clicking on the directory, go to the Security
tab, add NETWORK SERVICE as a user and then select Read & Execute for
its Permissions (this should automatically also select Read and
List folder contents). These file permissions (ACLs) should automatically
propagate to the directory contents (if not, open the Properties of the
folder and navigate to the Security → Advanced → Permissions tab and Enable
Inheritance).

If you prefer to use the command line, you can grant the necessary permissions to
the directory – and its contents – by running the following:

icacls C:\Users\anthony\Documents\SQL_traces /t /grant "Network Service:(RX)"


Removing grants for databases that no longer exist We have quite a few MariaDB servers with whom the permissions have gotten a bit messy over the years. I'd like to tidy them up.

I've already written some tools for presenting grants/table information nicely, and this has highlighted that that some users have a LOT of specific grants for databases or tables that no longer exist. On one server there are over 150 databases referenced by users in SHOW GRANTS, but only 12 databases still exist.

How might the users of Stackoverflow deal with housekeeping of this order?

Are there any tools that do basic auditing on Grants, accesses and Databases that will help me sort through this mess?

Ideally, something that spits out a list of mysql REVOKE statements that remove grants for databases that no longer exist, that can then be reviewed by a human before pasting as a query.

I can see a way to anagrammatically sorting through them, but it is likely to take a few hours to write that up. It may still come to that but I'd like to know if this wheel has already been invented.

I would start by verifying that the information in:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

is valid, i.e. is all existing databases, but nothing more. Then you can loop over %PRIVELIGES tables (8 I think it is) and create REVOKE statements from there, example for TABLE_PRIVILEGES:

SELECT 'REVOKE GRANT ON TABLE '
      || RTRIM(TABLE_SCHEMA) || '.' || RTRIM(TABLE_NAME)
      || 'FROM USER ' || GRANTEE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)

In the same matter, go through the remaining _PRIVILEGES tables. All from the top of my head and untested, but it should give you a start.


SQL Server 2008 R2 - Set tempDB size on full disk One of my SQL Server 2008 R2 instances had a job that went mad last night, ended up with the job auto-growing tempDB until it ran out of disk space. To compound the issue I noticed that tempDB is using default values, so if I restart it will go back to 8mb data file growing in 10% chunks.

So at this time there is 8mb free space left on the TempDB drive. Whilst it is in this state, can I set the desired TempDB size of 30GB using ALTER DATABASE commands ready for the next restart, or will the command fail as there is no free space on the drive?

EDIT:-

As per Robert's comments below. This only needs 1 restart as running ALTER DB command on tempdb before restart will cause SQL Server to set tempDB correctly on restart

By setting Tempdb to be 30GB, you are adding the amount of time required for SQL Server to fully restart.  Every time SQL starts, tempdb is recreated.  Allocating that much disk space may take longer than desired.  I would go with 2GB, but let auto-grow by 64MB at a time.

I would also change the MODEL database to grow by 128MB at a time for data, 64MB for the log.  This setting will be applied to all new databases and will prevent the type of internal fragmentation that can happen with too many auto-growths (i.e. the default 10%)

ALTER DATABASE [MODEL] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH=128000KB )
ALTER DATABASE [MODEL] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH=64000KB )


LIKE Query Optimization I have the table shown below:

create table Order_Details
(
 ID int not null primary key
,Order_ID int
,Order_Description nvarchar(40)
)

We have inserted 27,500,000 rows in above table, and when I run below query it will takes 6-7 seconds.

select ID,Order_ID, Order_Description
from Order_Details
where Order_Description  like '%Football Size%'

I have applied a non-clustered index on Order_Description including ID and Order_ID.

Any other ways to optimize query and get result in faster way?

The issue is that double wildcard '%string searches%' are slow in SQL Server because we don't have fancy trigrams searches natively.

Even with a an index leading on the string search column, we have to read every row.

CREATE NONCLUSTERED INDEX ix_displayname ON dbo.Users(DisplayName)

SELECT u.Id, u.Reputation, u.DisplayName
FROM dbo.Users AS u
WHERE u.DisplayName LIKE '%Christopher%'

If you're only ever searching for one string (which is never the case), or you have a limited set of known search strings an indexed computed column (or columns) might work in your favor.

ALTER TABLE dbo.Users ADD SearchString
    AS CONVERT(BIT, CASE WHEN DisplayName LIKE '%Christopher%' THEN 1 ELSE 0 END)

CREATE INDEX ix_yourmom ON dbo.Users (SearchString)

Otherwise, you're gonna have to come up with a better way to locate the data that doesn't involve searching the text, like an OrderLines table which tracks each item in the order that can be searched on an ID.


Transaction with multiple SQLCMD calls in a batch file Is there a good way to do something like the following?

SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION"
FOR %%f in (script1 script2 script3) do (
    SET CURRENT_SCRIPT=%%f
    SQLCMD -S Localhost -E -b -V 1 -i %%f.sql -o %%f.log
    IF !ERRORLEVEL! NEQ 0 GOTO ERROR
    SET CURRENT_SCRIPT=
)
SQLCMD -S %SERVER% -E -Q "COMMIT TRANSACTION"

Because each sqlcmd execution is a separate session, it means you can't have transactions spanning those executions.  You're going to want to combine your scripts into a single script for you to do what you want.

If you can properly order your scripts, the following approach in Powershell will work for you:

$scripts = Get-ChildItem "C:\temp\*.sql" |Sort-Object
$fullbatch = @()
$fullbatch += "BEGIN TRANSACTION;"

foreach($script in $scripts){
    $fullbatch += Get-Content $script
}

$fullbatch += "COMMIT TRANSACTION;"

sqlcmd -S localhost -d test -Q "$fullbatch"

Essentially, I read in each script (assuming they are ordered correctly) into a single batch variable, append a BEGIN TRANSACTION and END TRANSACTION to the batch, then execute that back as a query within my sqlcmd execution.


mysql user host field in slow query log Mysql shows the following value in the slow query log

# User@Host: user[user] @  [127.0.0.1]

I'm assuming one if the "user" entries is a proxied login. An admittedly very quick glance of the code has not come up with how the user_host value is set.

Is this the case, and if so which one?

Here is one way to know

Login to MySQL and run this

SELECT USER(),CURRENT_USER(); SELECT SLEEP(15);

What will this tell you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

Look for the SELECT SLEEP(15); query in the slow log. From there, you should be able to tell whether USER() or CURRENT_USER() was used to set # User@Host:.


MySql Order by isnull() Performance Problem my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.

AddDate and Price has an index.

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50

Explain the sql shown that it is not using the Price index.
So i tried to improve the query and has come out with below sql

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
and Price is not null
order by Price asc limit 50

The new sql works a lot faster and explain shown it uses Price index but the problem is that Price with null values will never selected.

Looking for any comments or suggestions on how to solve this problem. Thanks.

Your original clause:

order by isnull(Price), Price asc limit 50

Uses a function on the Price column. In your case, the function is isnull(); but it does not matter - any function on your column, e.g. COALESCE(Price, ...), Price + 1, etc., would make for the same result.

You see, once there's a function on a column, MySQL is then unable to use that column. Consider: the index uses a BTREE on Price, indexing your millions of rows in ascending order. But your request, to order by isnull(Price) requests an altogether different thing: you just want to provide with NULLs or not-NULLs; well, MySQL will not use the index for that.

I suppose it would be nice of MySQL to recognize a set of "safe" functions, which are monotonic with the index; but MySQL gives up an index the moment it sees a function. It just does not predict how the result of the function looks like.

Other databases support Index functions; the MariaDB fork supports virtual columns and indexes on virtual columns -- both solution can help you out with your query. Plain MySQL just can't eat it.


Multiple triggers vs a single trigger Scenario:

Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed.

Question

Given that the solution is to use database triggers, which option is better?:

1) A single trigger per operation (insert/update/delete):

I_Table - executed on an insert
U_Table - executed on an update
D_Table - executed on a delete

Each trigger would handle multiple unrelated items of business logic.

OR

2) Multiple triggers per operation that were segregated by concern:

I_Table_Logging - for logging on an insert
I_Table_RI - for enforcing referential integrity on an insert
I_Table_BL - for executing business logic on an insert
U_Table_Logging - for logging on an update
U_Table_RI - for enforcing referential integrity on an update
U_Table_BL - for executing business logic on an update
D_Table_Logging - for logging on an delete
D_Table_RI - for enforcing referential integrity on an delete
D_Table_BL - for executing business logic on an delete

I prefer option 2 because a single unit of code has a single concern.  I am not a DBA, and know enough about SQL Server to make me dangerous.

The architecture and schema of the database must not be changed.  There are several places in the schema that should be enforced with explicit relationships, but unfortunately cannot be changed yet.  That is the reason I need to manually enforce referential integrity instead of relying on an explicit relationship.

Are there any compelling reasons to handle all of the concerns in a single trigger?  I'm specifically concerned about performance, order of execution, and rollback.

My personal preference is to create triggers by functionality, not by DML operation. So I have one handling auditing for insert/update/delete, one doing some kind of business rules or referential integrity (again, for insert/update/delete), another doing some ugly hack to work around undesirable behavior in our client software (possibly only for insert or update), etc.

Keeping each "behavior" in one place reduces the chances of forgetting to update logic that's been duplicated across three separate insert, update, and delete triggers.

The biggest pitfall with triggers is that the INSERTED and DELETED tables are not indexed, and you can't index them directly. Joining them against large data sets performs horribly, and things get really out of hand when you've got many triggers on a table, especially when trigger cascading starts coming into play.

Most triggers I write start out by creating table variables @i and @d with the columns from INSERTED and DELETED that will be needed, giving them both a clustered primary key (since that's the only way you can index a table variable), then filling them with the data from INSERTED and DELETED.

I wrote a short (noncommercial) article about triggers, and it also covers a few other points.

http://dave.brittens.org/2012/03/29/writing-well-behaved-triggers/


removal of GUIDS used for all PK's, FKs & Clustered indexes Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of using NEWSEQUENTIALID() either.

Obviously changing some of these sounds like a huge piece of work, but changing the Lookups doesn't sound too painful. I've documented the fragmentation and CL width and NC indexes built from these, optimizer choosing incorrect plans, etc.. but tasked with a workaround i'm coming up a bit short.

My current plan is to investigate how much is possible to change and in the interim add an identity field to the main heavily indexed tables and make that the clustered index and keep the GUIDs as PK's. Is this the best I can hope for? or not even worth it? any other workarounds that i have omitted ?

Thanks

(Moving discussion to answer)

First of all, you have to ask yourself: Why go after GUID first (if at all)? You could end up spending a lot of time rearranging tables and gain no benefit at all.

What do we know:

You state that the queries are generated by NHibernate. This indicates that you are an OLTP system.
Your major waits are CXPACKET and PAGEIOLATCH
Query plans are not doing what you want them to do

None of the above indicate that changing away from GUID is the best thing to do here. Here is why:

First, while moving away from GUID might reduce fragmentation - this has no effect on your query plans (Statistics has). As a matter of fact, it may even make things worse since you will now have to deal with the ascending key problem described here: http://blog.kejser.org/2011/07/01/the-ascending-key-problem-in-fact-tables-part-one-pain/ which will cause your parallel queries to run single threaded.

Second, the presence of CXPACKET as a high wait indicates that you are running a lot of parallel queries. This indicates that you are getting wrong estimates or are lacking indexes - GUID's wont help with that either (and lowering DOP is not the answer here)

Third: Even if you did manage to switch to GUID and using your 20M row example, how much would you actually save? Every row would be 12B bigger (unless you use a heap of course, but that has to be understood well first). There are 20M row them - this is 240MB of RAM (assuming of course, that the pages are full, which they only will be on the keys, not the secondary keys). 10 times that is just over 2GB, which today costs less than one hour of quality DBA consulting. In other words: trying to squeeze more space out of the pages is throwing money and your precious time out the window.

In your situation, it sounds like you need to collect some data from sys.dm_exec_query_stats and try to find out where the poor performance is really coming from. Since this is NHibernate, I would look out for:

Poorly parameterised queries (can be fixed with simple hints or better stats)
Over indexing or lacking good covering indexes
Lacking join conditions
Optimiser chosing hash join when loop would be better (you can use OPTION (LOOP JOIN) temporarily to see if a good loop join plan is even possible)

The best way to remove PAGEIOLATCH is not to squeeze more into memory (unless the database is very small or you can just buy cheap RAM) - it is to avoid doing bad table scans in the first place.


Script to Restore/Create SQL Server 2000 Database from Another Database's Backup I'm trying to create a database, NewClientDB, by restoring another database's, OldClientDB, backup, d:...oldclientdb.bak.  I've always done this via SQL 2000 Enterprise Manager, but the GUI keeps hanging.  I've already created the empty NewClientDB.

Anyone know how to restore the OldClientDB's backup to NewClientDB via script without affecting OldClientDB?

RESTORE DATABASE [NewClientDB]
FROM  DISK = N'D:\OldClientDB.bak'
WITH  FILE = 1,
MOVE N'OldClientDB_Data' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Data.mdf',
MOVE N'OldClientDB_Log' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Log.ldf',
NOUNLOAD,  STATS = 10

Source: BACKUP and RESTORE in SQL Server


Auto running SQL script I am using the below command in my batch file and running it via task scheduler.
osql -S myserver\sqlexpress -E  -i "c:\test.sql" -o "c:\output.txt"

I get the error stating that:

Server is not found or not accessible. Check if instance name is
correct and if SQL Server is configured to allow remote connections.

Could someone advise what exactly should be the myserver and sqlexpress in the above example?
I am referring the link for the above script.
How to automatically execute SQL queries with a batch file
Source: Mitel MiContact Center Knowledge Base

The -S portion of the osql command tells osql where to find the SQL instance that you want to interact with. It typically consists of two parts -- the server name and the instance name.
You'll need to replace "myserver" with the name of the server that your SQL installation is running on. And, if your SQL installation is using a named instance, you'll need to replace "sqlexpress" with the name of that instance. If, on the other hand, you installed SQL as a default instance, you can leave out the part after the backslash.
Examples
I've got SQL running on a machine named DougSQL01. As a default instance. So my osql command would look like this:
osql -S DougSQL01 -E -i "c:\test.sql" -o "c:\output.txt"

I've got another instance of SQL running on another server, DougSQL02. This one is a named instance, with an instance name of "Testing". In this case, my osql command would look like this:
osql -S DougSQL02\Testing -E -i "c:\test.sql" -o "c:\output.txt"

There are some other subtleties about how to specify a server and an instance, like specifying a port instead of an instance name, using an IP address instead of a hostname,  specifying "mssqlserver" for a default instance, and the ability to leave out the name of the instance if a named instance is running on port 1433, but none of them are likely to apply in your case.


Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert Background Information:

I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server.
I have to migrate a lot of Rows (not all), spread over a lot of tables (not all).
The migration will be done table by table.
That means I have written a C# Programm and a lot of Statements to select the needed Records from the Source-DB (proxy Tables) and insert them in the target db/tables.

I have droped all Primary Keys, all foreign keys and all indexes.
This was done to increase performance, to facilitate the table by table migration and to start quicker on developing the Programm.

My Problem:

I need a way to select all Foreign Keys of one Owner, generate their "Create"-Statements and save those to a Table so I can recreate them once the migration is over. (Same Problem for PKs and Indexes)

Currently the fastest way is to use Sybase Central, Click on Indexes, order by Owner/Type, select all foreign Keys, Ctrl-C and Ctrl-V into a Editor. That will give me all "Create"-Statements for the selectet Objects.

I would rather have a script that could generate all "Create"-Statements, but seeing as most information about Foreign Keys is spread over 11 Systemtables/-views I am not able to build a Statement that will give me all Informations that I need.

I also have tried using the Unload-Tool of Sybase Central which will give me all Foreign-Key Statements. But seeing as that is also a long and manual way of doing it, it is not an improvement.

Does anyone have a better way or has anyone maybe written a Script that strips a Database of all foreign Keys to rebuild at a later point in time?

I've tried to do something similar some years ago and the SQL required defeated me as well!

The approach I took was to use gawk to extract all the statements I wanted from the unload file and then run that file. The whole approach could potentially be run in a batch file using the SQLA command line utility dbunload.exe, then gawk, then isql to run the extracted commands. How easy this approach is depends on how happy you are with complex regular expressions etc.

Another approach would be to use the sa_migrate_create_fks() system procedure - it is well documented in the help, but there would be several stages involved.

Alternatively, repost the question on the dedicated SQLA forum http://sqlanywhere-forum.sap.com which is monitored by SQLA developers who may be able to help with a SQL construct.

Good luck


Constellation Schema; Relationships between fact tables What are acceptable types of depedendcies between facts in a multiple-fact schema?

Conceptually talking, As you know a Multi-Dimensional Model may have multiple facts (like in a constellation schema).

Now my question is: if we have for example two facts in the schema and those two facts have a relationship between them.

1- Is having a relationship between the two facts acceptable from a theoretical point of view?

2- If it's acceptable; what is these kinds of the available relationships?

fact1 (1-1) fact2 ;
fact1 (1-n) fact2 ;
fact1 (n-1) fact2 ;
fact1 (n-n) fact2  ;

3- Do the measures have a role in identifying these acceptable relationships. I read that having a (1-n) is acceptable when the measures are in the n side!

Are there any references?

Not really. Fact tables has rows which are a intersection of all the joined dimensions. Joining a fact table to another fact table doesn't really make sense - two facts should be joined through a conformed dimension.
I don't consider it acceptable.
No. Measures quantify the intersection of all the dimensions, they enable you to measure that item not change the granularity or definition of that item. That is what Dimension tables are for.


How to get sum of the count of rows till date from table in MySQL I have a table as below:
TABLE A:
id|  on_date |off_date
1 |2020-09-10|
2 |2020-09-09|
3 |2020-09-08|
4 |2020-09-09|2020-09-10
5 |2020-09-08|

I want to get the count of ids which are in on mode till a particular date (2020-09-11)
Expected output:
count|date
2    |2020-09-08 (because id 3 and 5 has on_date of 2020-09-08)
4    |2020-09-09 (because id 2,3 and 5 has on_date till 09-09-2020)
4    |2020-09-10 (because id 4 not counted because off_date is present and id 1 is added)

I am not really sure how to get this output.

It can be, for example,
SELECT SUM(dates.a_date >= test.on_date AND (dates.a_date < test.off_date OR test.off_date IS NULL)) `count`,
       dates.a_date `date`
FROM ( SELECT on_date a_date FROM test
       UNION
       SELECT off_date FROM test WHERE off_date IS NOT NULL ) dates
CROSS JOIN test
GROUP BY 2
ORDER BY 2;

fiddle
dates subquery generats dates list when the amount of ON ids might have changed.
SUM tests each row against the conditions for particular date and counts the amount of matched rows (testing result is 1 while matching and 0 otherwise).
If some date is absent then look the row with closest lower date.


Resolving sp_Blitz Finding: Many Plans for One Query Why Multiple Plans for One Query Are Bad

So I was running sp_Blitz to get a handle on some systems.  Got some code to get cleaned up as usual.  Some heaps that should have been clustered indexes.  etc.

This particular query was using literals, seemed to be resulting in a lot of plans.  Got the indexing and stuff sorted out on the tables in this query, and even got the query parameterized for the latest code/database to go into production.

But still the query is showing as a parameterization issue.  (DBA helpfully turned the plan cache query into an SSRS report so I can just run through them quickly on the PROD environment from my browser).

Where to go from here?  Ignore it?  (Seems like a lot of plan counts)

Use forced parameterization?  (But the one that is explicitly parameterized is showing up).

Ugh - I see the developer didn't take my advice that the LEFT JOIN was being turned into an INNER JOIN...  I'll have to get that fixed...

Without forced parameterization, will all these literal-style queries never share the same plan?  Is this query too complicated for simple parameterization?

Community wiki answer:

If the query is now parameterized, you should be able to ignore all the ones that still show constant literals - those are in the plan cache now, but they'll age out over time.

In addition to the outer join that's not really an outer join anymore, is SELECT * required, or is it possible there is a skinnier pair of indexes that could cover the actual output requirements (usually some subset of *)? You should also encourage your developers to always use the schema prefix e.g. schema.object.

Simple parameterization only applies to trivial plans.

Also consider optimize for ad hoc workloads. Kimberly Tripp has some good guidance on cleaning the cache. Think of forced parameterization as a last resort, because it will affect all your queries, with a high risk of performance regressions.


MAX of a varchar column I have a table with a column named price typed as varchar. I want the maximum value of this column. I am using this query:

SELECT MAX(price) FROM product_management.`item_list`

but the result is wrong. Please solve this problem.

I am using [SB] SQLyog Ultimate Ver. 9.0.2.0.

I have a table with a column named price typed as varchar.

And there is the problem. You have a column that obviously has numeric values (assuming the name price is relevant) and you have chosen a char type.

  I want the maximum value of this column. I am using this query:

SELECT MAX(price) FROM product_management.`item_list` ;

  but the result is wrong.

The result is 100% correct but not what you expected. The database did exactly what you asked it to - but not what you though you were asking.

You asked for the MAX() of a varchar column and that's what you got. Problem is that for char columns, the ordering is done lexicographically - as in dictionaries - and not with numeric order. Because char type columns have char values (strings) and not numbers.

So, for the database, they are just strings of characters. You could very have a value of peters-sellers in there. The ordering of values of a char column would be - even if all the values are visually "numbers".

1
11
165
17
2
20
200
21
21789
3
35
350
9
99
997

And of course your query would return the lexicographic maximum (997) and not the numeric maximum (21789).

The best solution would be to convert the column to a numeric type - integer, decimal, depending on the values you expect to have - and then MAX() and MIN() and ORDER BY would work as you expect.

There are workarounds, if you can't change the type (by casting the value to a numeric type before applying MAX/MIN functions) but I don't recommend it. This would still allow to store nonsense data in the column and possibly get conversion errors (which number should 'peter-sellers' be converted to?)


Which permissions are required to view Windows Event Logs in SSMS? I'm trying to figure out which permissions are required to be able to view the Windows Event Logs via SSMS.

The SSMS Log File Viewer, where you typically view the SQL Server Errorlog, also has the ability to show the Windows Event Logs via a "Windows NT" checkbox & subtree (pictured below). Being a SQL Server sysadmin and Windows Administrator this shows up just fine for me.

Is there a way to give other users the ability to view the Windows Event Logs within SSMS, without giving them SQL SysAdmin and Windows Admin level permissions?

I've tried the Event Log Readers local security group in Windows, but that doesn't appear to work.

The two groups highlighted in the attached image (Administrators and Event Log Readers) will allow a user, or a member of a group, who is given access to either of these two local groups the ability to view the windows event log. Of course, as long as they are logged on via an AD account.


What is the SSMS option "Execute stored procedure shortcuts without additional execution options"? This is a wordy option; there is no documentation and web searches are no help. When I'm executing queries, I don't notice a difference with the option checked or not checked. What does this setting control?

This is certainly a wordy option and it seems a bit ambiguous to me. You could take "stored procedure shortcuts" a number of ways; initially, I did not reach the conclusion that these were actually referring to the "Query Shortcuts" panel in Tools > Options > Environment > Keyboard, mainly because I was asked a question about them when I wasn't staring at the dialog (the option is, of course, on that very panel).
The option was added in response to the following Connect item (which has been orphaned because Microsoft killed Connect due to GDPR):

Connect #266994 : Allow shortcuts to be configured with specific execution and results options

There, Simon Sabin pointed out that if you execute a stored procedure using a keyboard shortcut (e.g. Alt+F1 for sp_help, or Ctrl+1 for sp_who), that execution would obey any query options you had set for the underlying query window (e.g. SET STATISTICS TIME ON;). This isn't always desirable (the example Simon used was getting a bunch of graphical execution plans when hitting Alt+F1).
So:

If you expect these query shortcuts to execute with all of the same options as the current query window, leave the box unchecked.
If you expect these query shortcuts to ignore any existing query options, check the box (and open a new query window, as it doesn't affect current windows).

Unfortunately there isn't a way to customize this per shortcut, as Simon requested, so you'll have to take it all or nothing.


Query to get dates NOT in table I am building a booking app and part of this involves reserving a courtesy car.

Courtesy cars can be booked out for a period, e.g.

2nd April - 11 April
12 April - 19 April
etc...

These dates are stored in a courtesycarbooking table.

What I want to write is a query which can query the courtesycar and courtesycarbooking tables (they are related) for free dates. Free dates are those which are NOT in the booking table.

The user will specify, in a javascript widget, a start and end date for the courtesy car, which will be sent to the sql server.

How could/should a query like this be written?

To do what you want first step is generate a list of Dates  then join that reservation table to the generate list of dates.

Here is a thread on how to Generate list of Dates in MSSQL

From this result set select where List of dates table failed to joined to the reservation table.  The reservation columns will be filled with NULLs

To write a sample  query for you, have to know the structure the table and unique keys used to identify the records.


How to export specific columns from a SQL table I am cleaning some data bases and was wondering that if there is any proper way to export any single column and all it's entries from a SQL table.

Being specific, I am dealing with SQL tables in phpmyadmin; if i am not wrong, there must be a SQL query for such tasks.

Using phpMyAdmin run a SQL query against the table that you want a column of data from and select only that column (something along the lines of SELECT cas_number FROM analytes).

Scroll to the bottom of the results and click on Export. The only thing that will be exported is the column of data that was selected.


Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command? When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user):

postgres=> create database rich1;
CREATE DATABASE
postgres=> \l+
                                                                            List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |          Access privileges          |   Size    | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-------------------------------------+-----------+------------+--------------------------------------------
 postgres  | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                                     | 7455 kB   | pg_default | default administrative connection database
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin               | No Access | pg_default |
 rich1     | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                                     | 7233 kB   | pg_default |
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                         | 7345 kB   | pg_default | unmodifiable empty database
                                                             : rdsadmin=CTc/rdsadmin
 template1 | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsys                            | 7345 kB   | pg_default | default template for new databases
                                                             : pgsys=CTc/pgsys
(5 rows)

As you can see, the database is put into the pg_default tablespace, but if I specify the default tablespace in the tablespace clause (also still logged in as pgsys) I get a permission error:

postgres=> create database rich2 tablespace pg_default;
ERROR:  permission denied for tablespace pg_default

Here's the permissions for that user:

postgres=> \du pgsys
               List of roles
 Role name | Attributes  |    Member of
-----------+-------------+-----------------
 pgsys     | Create role | {rds_superuser}
           : Create DB

This is PostgreSQL error, but I should mention that this an AWS Aurora instance in case that makes a difference.

Here's the trick, at least with pgAdmin v4:
Leave the tablespace blank. It will default to "pg_default" when creating the database.


Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install? When setting up a server, we installed SQL Server 2008. Now it turns out we don't need it, and we want to use the free Express version instead. Ideally without much interruption to things using the database. Is it possible to install Express side by side with the full version without disrupting things?

The tables are small and the load is relatively light. As in, there are maybe 30 rows in each table, and a few aspx pages that use them to keep track of state. We're not doing anything other than simple queries on a few tables in our current setup. I could get by using SQLite, if I wanted to rewrite some ASP.NET pages to use it.

As Shark already pointed out install or downgrade are distinct actions:

install side by side means to have a new session of Express edition installed WHILE the current running edition is working (btw, what is the version of the current instance?);
DOWNGRADE/UPGRADE means running the installation option over the currently running instance;

Any operation involving the live instance means it will interact and stop with the current instance. Some planning questions are in order now:

What are the dimensions of the database(s) that are in work? (Express edition can handle small dbs only);
What hardware necessities do you have for your current load? (Express edition has some strict limitations in CPU, RAM, hdd space);
What features do you need from the current set? (eg: you won't have SQL Agent jobs with Express Edition);

At first you need to answer all these questions and only then see what your options is.

EDIT: after all your details specified, I'd suggest you to install a new version along with the current working one. Then stop the working instance service, ATTACH the database files to the Express Edition, then move on with the uninstall of the other instance. This will make the database unavailable only for the attach operation.


Cannot log in using SA with SQL Authentication I can not log in to SQL Server 2008 using SQL Server authentication.

I can only log in using Windows Authentication Mode.

I chose only defaults while installing SQL Server 2008 R2.

Did you look anywhere? Right-click the server and choose Properties, then select the Security screen.

Then, log in using your Windows auth admin account, and set the password for the sa SQL auth account.

ALTER LOGIN sa WITH PASSWORD = 'strong password here';


Can't find sun.jdbc.odbc package in Java version 8 I am trying to connect a database query to my class while I compile the package name sun.jdbc.odbc package but it is not found in Java version 8.

Oracle has mentioned related to this in the Tec documents

  JDBC-ODBC Bridge The JDBC-ODBC Bridge allows applications written in
  the Java programming language to use the JDBC API with many existing
  ODBC drivers. The Bridge is itself a driver based on JDBC technology
  ("JDBC driver") that is defined in the class
  sun.jdbc.odbc.JdbcOdbcDriver. The Bridge defines the JDBC sub-protocol
  odbc.

And I think this is the reason why its not found in the Java version 8

  Status of the JDBC-ODBC Bridge The JDBC-ODBC Bridge should be
  considered a transitional solution; it will be removed in JDK 8. In
  addition, Oracle does not support the JDBC-ODBC Bridge. Oracle
  recommends that you use JDBC drivers provided by the vendor of your
  database instead of the JDBC-ODBC Bridge.

Source where the information was found TechNotes,Lance Andersen's Blog Removal of the JDBC-ODBC Bridge from Java SE 8 JDK

I suggest you try the following solutions

Use a third-party database driver
Use a previous version of java


How to send an email with embedded values I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100.

I tried this:

    EXEC msdb.dbo.sp_send_dbmail
        @recipients='MYMAIL',
        @body='100 Daily documents downloaded',
        @subject ='100 Daily documents downloaded from user',
        @query = 'SELECT USERID FROM MYTABLE WHERE MYCOUNTER > ''100'''

I receive this error:

  Messaggio 22050, livello 16, stato 1, riga 0 Error formatting query, probably invalid parameters Messaggio 14661, livello 16, stato 1,
  procedura sp_send_dbmail, riga 504 Esecuzione della query non
  riuscita: Msg 208, Level 16, State 1, Server MYSERVER, Line 1 Invalid
  object name 'MYTABLE'.

You have to use dbname.schema.objectName e.g. dbname.dbo.MYTABLE in your tsql

EXEC msdb.dbo.sp_send_dbmail
        @recipients='MYMAIL',
        @body='100 Daily documents downloaded',
        @subject ='100 Daily documents downloaded from user',
        @query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100'''

EDIT:

You only want to send email when there are 100 Daily documents downloaded from user, so you can add

if exists (
        select 1
        from dbName.schema.MYTABLE
        where MYCOUNTER > '100'
        )
begin
    exec msdb.dbo.sp_send_dbmail @recipients = 'MYMAIL'
        ,@body = '100 Daily documents downloaded'
        ,@subject = '100 Daily documents downloaded from user'
        ,@query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100'''
end
else
    print ' the user has not downloaded 100 docs yet'

Note: Why are you storing integers as chars ? e.g. 100 is stored as '100' ?


Upgrade mongodb from 2.4 to 3.0 I have mongodb 2.4 database with some indexing. Now I want to upgrade that version to 3.0. I searched on google but did not find any firm answer. I tried installing 3.0 but then it gives me error of index.
Does anybody have idea about this?

Answer:

I am able to upgrade the DB. Below are the steps :
1. Install mongodb 2.6
2.  Update the index of admin collection
    db.getSiblingDB("admin").runCommand({authSchemaUpgrade: 1 });
3. Install mongodb 3.0

Upgrade Requirements
To upgrade an existing MongoDB deployment to 3.0, you must be running 2.6. If you’re running a version of MongoDB before 2.6, you must upgrade to 2.6 before upgrading to 3.0. See Upgrade MongoDB to 2.6 for the procedure to upgrade from 2.4 to 2.6. Once upgraded to MongoDB 2.6, you cannot downgrade to any version earlier than MongoDB 2.4.

http://docs.mongodb.org/manual/release-notes/2.6-upgrade/

One of the most important thing to remember after you are on 2.6 if you are trying to use WiredTiger as your storage engine, you do need to take a mongodump by connecting into current mongodb version and then switch to mongodb 3.0 with a different db path,

IMP: You will not be able to use existing DB path as previous storage on mmapv1

Command to use: mongod --storageEngine wiredTiger --dbpath

mongodump and mongorestore can operate against a running mongod process, and used to take a backup and restore the backup respectively.

http://docs.mongodb.org/manual/reference/configuration-options/

storage.engine
Default: mmapv1

New in version 3.0.0.

Specifies the storage engine for the mongod database.
Valid options include mmapv1 and wiredTiger.

If you attempt to start a mongod with a storage.dbPath
that contains data files produced by a storage engine other
than the one specified by storage.engine,
mongod will refuse to start.


Automating MySQL DB Creation with Specific date Here i come again with another challenge . Basically i'm looking forward for the best way to have a windows based script to create a MySQLDB, or multiple MySQLDB, once a week having the following format : YYMMDD_HHMM_DBNAME.

For e.g , every saturday or sunday, the script will be running to create the DB having the previous Thursday date. The goal is then to restore a dump into that new DB.

If anybody have an idea on how this can be done, i will really appreciate.

With thks.

Rgds

This script will dynamically create the database prepended with the date format you want

SET @dbname = 'rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Just set the @dbname in the script.

If you want it as a Stored Procedure, here you go:

DELIMITER $$

DROP PROCEDURE IF EXISTS `MakeRestoreDB` $$
CREATE PROCEDURE `MakeRestoreDB`(GivenDBName VARCHAR(20))
BEGIN

    SET @dbname = GivenDBName ;
    SET @today = DATE(NOW());
    SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
    SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
    SET @ut2 = UNIX_TIMESTAMP(@today);
    SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
    SET @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

END $$

DELIMITER ;

Then, just call MakeRestoreDB('rolando');, and you are good to go.

Give it a Try !!!

Hmmmmm ... some of these statements seem familiar :-)


SQL Server - Timestamp datatype I need some information on SQL Server timestamp.

I found all the required information here.

The article says

  The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Sample syntax is  to create timestamp column is

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

The new syntax is

CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;

So the question is, the word deprecated means Is Microsoft going to completely remove the timestamp datatype or it is removing just the syntax of using it ?

As I am using SQL Server 2014, in Design mode, I am still able to see timestamp datatype.

So can someone please clarify my doubt ?

And also is there any alternative datatype for timestamp, I want to use it for concurrency check ?

Only the timestamp syntax is deprecated, not the whole datatype. According to this, just use the rowversion syntax going forward.

For concurrency checks, rowversion is what you're looking for. The timestamp synonym still works, but is just an alternate name for rowversion. There are no other datatypes that behave similarly.


Why do we need to rebuild and reorganize Indexes in SQL Server After searching the internet i couldn't find the reason for

Why do we need to rebuild and reorganize indexes in SQL Server ?
what does internally happens when we rebuild and reorganize ?

An article on a site says :

  Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

I couldn't understand this, though it says that WHEN to do this, but I would like to know WHY do we need to rebuild and reorganize indexes ?

As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally.

Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index.

External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.

If your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. This will hurt your performance a lot!

When you reorganise an index, then SQL Server uses the existing index pages and just shuffles data around on those ages. This will alleviate internal fragmentation and can also remove a small amount of external fragmentation. It is a lighter weight operation than rebuild and is always online.

When you rebuild an index, SQL Server actually resorts the data of the index and uses a new set of index pages. This will obviously alleviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL Server version and settings.

Please do not expect to have 0 fragmentation after a Rebuild however. Unless you use a MAXDOP query hint, SQL Server will parallelise the rebuild operation and the more processors involved, the more fragmentation there is likely to be, because each processor or core, will rebuild their section or fragment of the index individually, without regard for each other. This is a trade off between best fragmentation levels and time taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB.