how to insert multiple columns(around 40) into my sql database using java i have a program to read the content of an excel file to my-sql db. i have 40 columns in excel and i created a table of 40 fields. and i want to import the data from excel into corresponding table filed.

My doubt is, is there any better way to write preparedStatement than the following;

con.prepareStatement("insert into insurance.bank_stmnt_ds values(?,?,?,...,?(40 times))");

I am not sure whether you need compulsory callable statement or not.

If you are ready to use other ways, best way is convert your Excel sheet into CSV file and you can directly load data from CSV file into database by using following syntax:

"Load data infile "c:/filename.csv" into table tablename Fields terminated by ',' Lines terminated by '\r\n'"

This is sample syntax, and you can find the full syntax in net based on requirement.


how to write this query accordingly i need this query to run against 20 instances from main server that have linked server to those 20 instances.i want to change the [@server]  to the right instance via cursor and make the SELECT statement to work.

how can i write the query accordingly?

THX

declare @server varchar(50)

set @server = ‘servername\instnacename’

SELECT N'’ + ISNULL( + ‘
’ + (SELECT CONVERT(NVARCHAR(MAX),column_name) from [@server].db_name.dbo.table_name),'
Not Avail’) + N'
	’

If you only need the SELECT statements for each server

SELECT 'SELECT CONVERT(NVARCHAR(MAX),column_name) from ' + QUOTENAME(name) '.db_name.dbo.table_name;' FROM sys.servers where server_id != 0

This gives me the statements for my two linked servers

SELECT CONVERT(NVARCHAR(MAX),column_name) from [Server1].db_name.dbo.table_name;

SELECT CONVERT(NVARCHAR(MAX),column_name) from [Server2].db_name.dbo.table_name;

If you need to execute the statements at once, you could use a cursor and dynamic SQL.

DECLARE @server varchar(50)
DECLARE @SQL NVARCHAR(MAX)
DECLARE C CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.servers where server_id != 0
OPEN C
FETCH NEXT FROM C INTO @server

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
'
SELECT CONVERT(NVARCHAR(MAX),column_name) from '+QUOTENAME(@server)+'.db_name.dbo.table_name;'
--PRINT(@SQL)
EXEC(@SQL)
FETCH NEXT FROM C INTO @server
END
CLOSE C
DEALLOCATE C


Rebuild large Oracle 10g index with insufficient TEMP space? One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of COMPRESS, for example) but it fails with errors relating to TEMP getting full - it's 4GB at present.

Is there a way to build an index that will be larger than TEMP? I realise I could export the table, truncate it, create new and interesting indices and then reload, but that goes way beyond my capacity for pain...

What is TEMP used for in Oracle? As a scratch area for doing sorts that won't fit into main memory. So the issue is that you need to do a big sort in order to create your index, and you don't have a large enough PGA for it. So your options are: a larger PGA or a "temporary temporary" tablespace; simply create one big enough, make it your user's temporary tablespace (ALTER USER xxx TEMPORARY TABLESPACE temptemp;) build the index, then set your user back and drop it.


Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible We have a legacy application that is only used for a read only reference.

It is using SQL Server 2000.
We want to migrate its database to SQL version 2012, however we can't upgrade the software and the vendor doesn't exist anymore.

The problem is that the views/stored procedures on the database are using old NON-ANSI SQL code that isn't supported anymore.

When we tried to simply restore it on SQL 2008R2 (as there isn't a direct restore from 2000 to 2012), and increase the compatibility level to 100, we got validation errors on the views/stored procedures, as they're using old join syntax.

  The query uses non-ANSI outer join operators ("=" or "="). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

As there are ~200 objects that need to be updated with a newer and proper join syntax code, I was wondering whether a tool/script/code-generator exists somewhere that will help me convert the code.

I did notice that if I right click the view object and select Design, the query of the view IS converted to the proper syntax.
But that isn't automated enough for me.

Any thoughts?

The best match to your requirement that I can think of is the Query Designer in SSMS.

You can open the designer by right-clicking any table in your database and selecting "edit top 200 rows".

Then, open the SQL pane by clicking the button in the toolbar:

Now you can paste your statement in the SQL pane and use the syntax checker to convert the code:

The converted code goes like this:

As you may have noticed, many things are wrong here:

all formatting is gone
wildcards are expanded. This may actually be a good thing.
Unnamed expressions are named with wonderful random names such as "Expr1".
The syntax checker may rewrite your query in ways that don't match the initial code (see this connect item, for instance)

This method (and any other automated method) is not 100% bulletproof and can be used as a starting point to help you in the process, but it definitely needs manual intervention and extensive tests on the converted code.


Suppress specific SQL Server log warnings and messages I want to minimize the output of certain warnings and messages in the SQL Server 2012 logs, such as when an spid is killed. I am unable to find information on suppressing/expanding which messages are actually sent to the error logs. Is this done through trace flags?

I am trying to reduce the number of warnings on our development database server and restrict it to legitimate errors. We have devs killing spids or flushing the query cache for performance tuning (playing with index tuning, forcing intentional table contention, creating deadlocks, etc), across multiple databases on the same server. Having a log file filled with these warnings is just an annoyance that I wanted to see if it were possible to suppress.

Overall, suppression or inclusion of logging is generally accomplished via trace flags - you are correct.

This, however, does not mean that:

There is a trace flag for each log item
The trace flags are publically documented
The trace flags don't differ through versions

  such as when a spid is killed

There isn't a trace flag to turn this off, and I highly doubt that if there were one it would ever be documented or given out.

If the killed session messages annoy you, filter them out as Aaron has stated. You can create your own noise words list or known message list that you don't care about specifically for your dev environments.

Additionally, I'd look into giving them some SQL Azure databases to let them do development so that I didn't have to worry about this kind of stuff and could focus my time on actual production issues or new projects.

Disclaimer: I work for Microsoft.


Oracle table is occupying too much space in disk I have a simple table in Oracle. The table has 1 million records, with an average record size of 200 bytes. Adding column definition sizes, a record shall not have over 750 bytes.

According to a simple calculation the data in the table is around 200 MB, and a reserved space of 750 MB. However the table is using 102 segments of 100 MB each, for a total size of near 10 GB, according to the following query:

select segment_name, segment_type, tablespace_name, bytes/1024/1024 as size_in_mb, bytes, extents
  from user_segments
 where segment_name in (select table_name from all_tables where owner=user);

So, I have two questions:

How can I explain the oversize. The table has no indexes, or anything that should add content to the table. The table was filled from an Informatica ETL process using bulk mode.
How can I get rid of oversize.

I do not have DBM privileges on the database.

The culprit was Informatica bulk mode insertion and the segment configuration in Oracle.

This is what I found out, by analyzing the problem with dbms_rowid:

Informatica bulk mode insert data in blocks of 10,000 rows. My over 1 million records required 101 such blocks.
Informatica writes a block of rows in one segment and the next block in the next segment.
Segments were configured to 100 MB. 101 segments were 10 GB.
Each 100 MB segment consist of 12,800 blocks of 8 KB. Each block could accommodate between 29 and 33 records. 10,000 records needed nearly 323 blocks, give or take. So there was 12,477 unused blocks in each segment.

So, the possible solutions are:

Configure Informatica for normal mode insertion. It inserts a record letting Oracle handling the insertion. There is supposed to be slower but it is not significantly slower, even for over 1 million records.
Configure Informatica to insert larger blocks. I haven't try it and I am not sure it is possible.
Configure a smaller segment size in Oracle. For this particular table, a segment size of 3 MB or 4 MB might have been enough.


How to select the members who has the age more than 18 (from current date) by SQL in teradata? If we are having a table named 'MEMBERS' with below details,
then how to fetch the data from the table with the age 18 or more?
S.no  Name     DOB        City
1     sam     19890203   USA
2     andrew  20020304   UK
3     karun   20050827   sydney
4     virat   19870202   India
5     mrunal  19920802   India

Could anyone please help me on this?

Simple logic is based on comparing DOB with the date 18 years before today.
If the datatype of DOB is DATE:
where DOB < add_months(current_date, -18*12)

If it's an INT:
where DOB < cast(add_months(current_date, -18*12) as int) + 19000000

If you want to show the age
trunc(months_between(current_date, DOB)/12)

or for INT
trunc(months_between(current_date, cast(DOB - 19000000 as date))/12)

+/- 19000000 is based on the internal format of a Teradata date:
(year - 1900) * 10000
+ month * 100
+ day


Is using association tables for One-to-Many relationships common practice? I'm currently running into a conceptual disagreement with some database architects at work.  My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find supporting evidence online for their side.

The part of the database that we're discussing has to do with pieces of manufacturing equipment within different areas.
There is a table containing all unique AREA_CD's.  Each area can contain multiple pieces of equipment (EQUIP_CD), but a single piece of equipment cannot be in multiple areas.  I.e. this is a One(AREA_CD) to many(EQUIP_CD) relationship.

My understanding of db design is that we should have a table for the equipment directly related to the area table where:
AREA_CD= PK & FK to area table
EQUIP_CD= PK
(The combined PK is because there are some pieces of equipment that share the same code name, but are not the same. This is an unfortunate reality from the legacy system we are trying to upgrade).

The architects are saying that it is best practice to have the area table and equipment table joined via an association table, which I thought was specifically for many-to-many relationships.

This would make sense to me if an EQUIP_CD referred to a specific type of equipment (with attributes we're interested in storing) of which multiple could exist in different areas.  However that would be a many-to-many relationship which isn't the case here.  We have some shared code names that refer to different types of equipment, and the attributes depend on which area they are in.

Their justifications for the use of an association table are the following:

Data protection.  The association table is protected by the code tables.. but I don't think this adds any value in this case.  There are other tables which are connected to the EQUIP_CD which will protect it.
Future flexibility.  This is understandable to me.
Best practice. Is it though?

So my question.. Is the use of an association table for a one-to-many relationship best practice?  Are there any other justifications for this design beyond the three above?

I'm not looking to challenge my colleagues, just further my learning.  Thank you in advance!

Update:  The data model is being fixed, we're getting rid of those association tables.

For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.

Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:

If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.
If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)


current_timestamp default column value working in Windows but not in Linux This MySql query runs fine in Windows:

CREATE TABLE `posts`.`table` (
  `idTable` INT NOT NULL AUTO_INCREMENT,
  `Column1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`idTable`));

In Ubuntu, it is giving the following error:

  ERROR 1067: Invalid default value for 'Column1'

How do I give CURRENT_TIMESTAMP as the default value for 'Column1' in Ubuntu?

The version of MySql is this:

mysql  Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3

The reason is the different versions installed in the two environments.

The option for DEFAULT CURRENT_TIMESTAMP for datetime columns was added in 5.6 version (specifically in 5.6.5). See the doumentation about the DATE, DATETIME, and TIMESTAMP Types:

  The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

Most probably your Linux installation has an older version while the Windows has a newer. The solution would be to upgrade to the newer version (or have it installed besides the old).

General info about upgrading from 5.5 to 5.6 here: Upgrading from MySQL 5.5 to 5.6 and specific about upgrading in Ubuntu 14.04 LTS.

Don't forget to backup your data before you upgrade and run mysql_upgrade immediately after.


MySQL server migration to new hardware I have to migrate working DB from old server (~50 Gb, mysql-5.5.43, openvz, all data in InnoDB) to a new hardware and new mysql version (mysql-5.6.28, debian). DB is "alone" - no replica servers. Dumps regulary made by mysqldump. Also software on PHP is running on server

Simple move using mysqldump isn't ok, 'cause restoring base is longs about 8 hours. I have no window of time for this...

So, there's a thought: make new server as a slave to existing server, wait till mysql replication sync is done,  turn off old server, make slave become master and continue working. Is this possible? Will be there any software-kind problems?

Or, this thought is wrong. If so, how, then, I have to migrate my DB to new hardware and software with minimal downtime?

In your case restoring database is taking more time, so I suggest to use AWS database migration service.

It's very simple,less human work and fastest method. And also your real time data will sync.
Instead of replication use this.


Error connecting to MSSQL server from Linux A colleague is trying to connect to the MS SQL Server Instance from linux using ODBC driver 17 and get's the below error.

  ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

The Connection string appears as below:

SQL_SERVER = 'Myserver'
SQL_DB = 'Mydatabase'

'DRIVER={ODBC Driver 17 for SQL Server};SERVER=MYServer;DATABASE=MyDatabase;Trusted_Connection=yes'

The same issue was reported here. Apparently a change to the settings and the ODBC fixed the issue, you may want to give a try.
Also I'm seeing Trusted_Connection=yes is the authentication possible between the linux and the SQL Server? If not maybe you should go with SQL Authentication.


composite index vs normal index I need to index some  tables and for that i found that there are different kinds of indexing.  I am kind of clueless on their proper place of usage.
Should i create index on all the fields that want to retrieve quickly or Should i create composite index on couple of fields?
Is it OK to create composite indexes along with the normal indexes? suppose i have 3 fields :

FirstName
LastName
State

Can i individually index each field and then have different composit indexes on these fields as well?
For example by writing :

Create index indx_name on tblTest(FirstName);
Create index indx_lastname on tblTest(Last name);
Create index indx_state on tblTest(state);
create index indx_cmp_fls on tblTest(FirstName,LastName,state);
create index indx_cmp_lfs on tblTest(LastName,FirstName,state);
create index indx_cmp_sfl on tblTest(state,FirstName,LastName);
create index indx_cmp_slf on tblTest(state,LastName,FirstName);

Is it OK?
Is it suggested? if not what is the best way ?
Is it also OK to have index on all fields ?

Your question amounts to, "How do I use indexes?" You should read some Oracle documentation to get up to speed on how to use indexes and how to determine what columns to index. Oracle has an entire library of free online books and documentation for their products. A few good links to start:

The chapter on Indexes and Index-organized Tables from Oracle Database Concepts (the entire book is definitely worth reading)
The Oracle Database Performance Tuning Guide on Choosing Columns and Expressions to Index
Using Indexes in Database Applications from the
Oracle Database Advanced Application Developer's Guide


Why does implicit conversion from sql_variant (basetype decimal) not work well with float Why is ResultFloat = 0 in the query below?

Am I missing a point within the cast/convert, sql_variant docs?

declare
    @v sql_variant = convert(decimal(28,8), 20.0);

select sql_variant_property(@v, 'BaseType') as BaseType,         -- 'decimal',
       iif(convert(int, 10.0)     < @v, 1, 0) as ResultInt,      -- 1
       iif(convert(decimal, 10.0) < @v, 1, 0) as  ResultDecimal, -- 1
       iif(convert(float, 10.0)   < @v, 1, 0) as  ResultFloat,   -- 0 !
       iif(convert(float, 10.0)   < convert(float, @v), 1, 0) as  ResultFloatFloat,  -- 1
       iif(convert(float, 10.0)   < convert(decimal(28,8), @v), 1, 0) as  ResultFloatDecimal;   -- 1

Sql Server 2012

From sql_variant (Transact-SQL)

  When sql_variant values of different base data types are compared and
  the base data types are in different data type families, the value
  whose data type family is higher in the hierarchy chart is considered
  the greater of the two values.

The base data type family for @v is Exact numeric and the base data type family for convert(float, 10.0) is Approximate numeric.

Approximate numeric is higher up in the hierarchy chart than Exact numeric so when you compare two sql_variant values where one is Approximate numeric and the other is Exact numeric the Approximate numeric value will always be considered greater.

The full hierarchy order is

+---------------------+------------------+
|  Data type family   |     Members      |
+---------------------+------------------+
| sql_variant         | sql_variant      |
+---------------------+------------------+
| Date and time       | datetime2        |
|                     | datetimeoffset   |
|                     | datetime         |
|                     | smalldatetime    |
|                     | date             |
|                     | time             |
+---------------------+------------------+
| Approximate numeric | float            |
|                     | real             |
+---------------------+------------------+
| Exact numeric       | decimal          |
|                     | money            |
|                     | smallmoney       |
|                     | bigint           |
|                     | int              |
|                     | smallint         |
|                     | tinyint          |
|                     | bit              |
+---------------------+------------------+
| Unicode             | nvarchar         |
|                     | nchar            |
|                     | varchar          |
|                     | char             |
+---------------------+------------------+
| Binary              | varbinary        |
|                     | binary           |
+---------------------+------------------+
| Uniqueidentifier    | uniqueidentifier |
+---------------------+------------------+


Create a view containing multiple rows per customer I'm creating a TASKS view that should list up to 10 different "todo" tasks per customer in my system.

Example tasks could be "check customer order", "send customer order", "enter invoice in external system"... At a given time, there can be multiple active tasks for a customer.

All these tasks are performed manually and I want to consolidate them all in a view so that I can display them in a single screen, send email notifications when they are due, etc...

My current query looks like this :

SELECT 1 as task_id, customer_id, created as since, confirmed as done
FROM CUSTOMERS
WHERE cancelled IS NULL
...
UNION ALL
SELECT 12 as task_id, customer_id, invoice_paid as since, invoice_registered as done
FROM CUSTOMERS
WHERE cancelled IS NULL AND has_invoice

The query is working fine but the performance is not so great.

Even queries like SELECT * FROM TASKS where customer_id = 12 take a few seconds.

Reading the CUSTOMERS table 12 times to generate only 0 or 1 row per task_id seems wasteful.

Is there any way to improve it ? I had thought of something along these lines (but it is not valid SQL) :

SELECT * FROM (
    SELECT VALUES (1 as task_id, customer_id, created as since, confirmed as done, cancelled_date is not null as ignore)
    ,...
    , (12 as task_id, customer_id, invoice_paid as since, invoice_registered as done, cancelled_date is not null or not has_invoice as ignore)
    FROM CUSTOMERS
) a
WHERE not a.ignore;

You can use a LATERAL join to rewrite the view:

CREATE VIEW tasks AS
SELECT c.customer_id, t.*
FROM customers AS c,
     LATERAL
     (   SELECT  1::int, c.created, c.confirmed
       UNION ALL
         SELECT  2, ---
         WHERE ---
       ...
       UNION ALL
         SELECT 12, c.invoice_paid, c.invoice_registered
         WHERE c.has_invoice
     ) AS t (task_id, since, done)
WHERE c.cancelled IS NULL ;

Adding a filtered index on (customer_id) WHERE (cancelled IS NULL) should  improve efficiency (perhaps of the existing view as well, although I'm not sure if the condition will be pushed down through the UNION) for the query you use:

SELECT * FROM tasks WHERE customer_id = 12 ;


Inserting records into Mongodb using Pymongo is using up my RAM incrementally and is eventually exhausting all memory I am writing Python code that parses through a huge XML document of size 17GB(Wikipedia collection) and inserts each page title along with the links in that page (storing as a list) present on the page in a MongoDB collection. I am inserting using the command links.insert_one(data) wheredata is a dictionary consisting of the following

data = {
          'document': self.title, # a string (basically title of the Wikipedia page)
          'links': self.links # list of strings (all hyperlinked words in a page)
       }

<db_name>.insert_one(data)

I am repeating this process for every page (around 19 million pages)

When I monitor my RAM usage(using htop in Ubuntu), I see that the amount of RAM used by MongoDB keeps increasing and continues till all my memory is used eventually crashing the system.

I have also made the following observation when I comment out the insertion statement and execute the code, it works fine. So, I presume that the problem is with my MongoDB insertion process and not with the actual python logic.

Any help would be really appreciated.

You can set the WiredTiger cache size, see hardware considerations.

Other than this, run MongoDB on containers (Kubernetes, Docker, rkt), where you can easily control resource usage. If you are using systemd for running MongoDB, you can also set resource usage.


Restore DB Backup with limited amount of disk space I want to restore a SQL Server database using a backup (.bak) of about 60GB.

Is it possible to do this on a machine with only 100GB of free disk space?

Or do I need at least the 60GB for the .bak file and the 60GB for the final database on SQL Server?

Are there any ways of just copying the .bak file into the server?

Network shares are not visible in the SSMS - only local drives like USB devices.

SQL Server need to create the database with the size it had when the backup was produced. No way around that.

You can possibly have the backup file on a different machine and restore using an UNC path to that machine. Something like

RESTORE DATABASE myDB FROM DISK = '\\server\share\db.bak'

Above assumes that the service account for the SQL Server service has permissions on the backup share/file.

But, again, investigate what size each database file had when the backup was produced using RESTORE FILELISTONLY - that is the size the files will be created with.


rebuild on clustered index, why does datasize shrink? When we did a rebuild on a clustered index on a table that have about 15gb data in it and  the datasize shrunk to 5gb, how can this be? What kind of "data" is removed?

Data size i mean the "data" column of DBCC sp_spaceused

  Before Rebuild on clustered index:

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    39169656 KB 15857960 KB 22916496 KB 395200 KB

  After Rebuild on clustered index:

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    29076736 KB 5867048 KB  22880144 KB 329544 KB

TSQL for rebuild:

USE [DAX5TEST]
GO
ALTER INDEX [I_212RECID] ON [dbo].[LEDGERJOURNALTRANS] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE, FILLFACTOR = 85 )
GO

When a table has a clustered index, the index is the table data (otherwise you have a heap type table). A rebuild of the clustered index (any index in fact, but the space wouldn't be counted as "data" for a non-clustered index) will result in partially used pages being merged into a more full form.

As you insert data into an index (clustered or otherwise) in index order leaf pages are created as needed and you will only ever have one partial page: the one at the end. As you enter data out of index order a page needs to be split for the data to fit in the right place: you end up with two pages which are approximately half full and the new row goes into one of them. Over time this can happen a lot, consuming a fair amount of extra space, though to an extent future inserts will fill some of the gaps. Non-leaf pages will see a similar effect too, but the actual data pages are far more significant in size then they are.

Also deletes may result in partial pages. If you remove all the rows in a page it gets counted as "unused" but if it has one or more row of data left it is still counted as in use. Even if there is only one row using 10 bytes in a page, that page counts as 8192 bytes in the count of space used. Again future inserts might fill some of the gap.

For variable length rows, updates can also have the same effect: as a row gets smaller it may leave space in its page that is not later easy to reuse, and if a row in a nearly full page grows longer it could force a page split.

SQL Server does not spend time trying to normalise the data by rearranging how pages are used, until explicitly told to such as your index rebuild order, as such garbage collection exercises could be a performance nightmare.

I suspect this is what you are seeing, though I'd say that having enough space allocated for ~2.7 times the amount the data absolutely needs to is a particularly bad case. It might imply that you have something random as one of the significant keys in the index (a UUID column perhaps) meaning that new rows are unlikely to ever be added in index order, and/or that a significant number of deletes have happened recently.

Page Split Example

Inserting in index order with fixed length rows of which four fit into a page:

Start with one empty page:
        [__|__|__|__]
Add the first item in index order:
        [00|__|__|__]
Add the next three
        [00|02|04|06]
Adding the next will result in a new page:
        [00|02|04|06] [08|__|__|__]
And so on...
        [00|02|04|06] [08|10|12|14] [16|18|__|__]

Now for adding rows out of index order (this is why I used even numbers only above): Adding 11 would mean either extending that second page (not possible as they are of fixed size), moving everything above 11 up one (far too expensive on a large index) or splitting the page like so:

[00|02|04|06] [08|10|11|__] [12|14|__|__] [16|18|__|__]

From here, adding 13 and 17 won't result in a split as there currently room in the relevant pages:

[00|02|04|06] [08|10|11|__] [12|13|14|__] [16|17|18|__]

but adding 03 will:

[00|02|03|__] [04|06|__|__] [08|10|11|__] [12|13|14|__] [16|17|18|__]

As you can see, after those insert operations we currently have 5 data pages allocated which could fit a total of 20 rows, but we only have 14 rows there ("wasting" 30% of the space).

A rebuild with default options (see below about "fill factor") would result in:

[00|02|03|04] [06|08|10|11] [12|13|14|16] [17|18|__|__]

saving one page in this simple example. It is easy to see how deletes can have a similar effect as out-of-index-order inserts.

Mitigation

If you are expecting data to come in a fairly random order with respect to index order, you can use the FILLFACTOR option when creating or rebuilding an index to tell SQL Server to artificially leave gaps to later fill in - reducing page splits in the long run but taking more space initially. Of course getting this value wrong can make things far worse rather than making the situation better, so handle with care.

Page splitting, particularly on the clustered index, can have a performance implication for inserts/updates so FILLFACTOR is sometimes tweaked for that reason instead of the space use issue in databases that see a lot of write activity (but for most apps, where reads outweigh writes by several orders of magnitude, you are generally better off leaving fill-factor at 100% except for specific cases like where you have indexes over columns with effectively random content).

I assume other big name DBs have a similar option, if you need this level of control in them too.

Update

Regarding the ALTER INDEX statement added to the question after I started typing the above: I assume that the options are the same as when the index was first built (or last rebuilt) but if not then the compression option could be very significant if it was added this time around. Also in that statement the fillfactor is set to 85% not 100% so each leaf page will be ~15% empty immediately after the rebuild.


storing file paths in table I'm working on a project which requires millions of images to be stored for their relative rows.

I have decided to store the images on the file system and store the image's path in a separate table which has a foreign key to the row which the images belong to.

Since I will be storing millions of images, I'm a bit concerned about keeping track of these images. I will have to add a few computers/hdds to store the images. From what I have read I should be using a distributed file system. Am I correct?

Are there any best conventions I should follow before I start implementing this? I'm thinking about storing the images and giving them a UUID name, and to store the UUID as the path of the image. Then my application ill contact the rest of the path.

I have decided to store the images on the file system and store the image's path in a separate table which has a foreign key to the row which the images belong to.

Why not just store the paths on the images table? Why have a 1:1 relationship?

  Since I will be storing millions of images, I'm a bit concerned about keeping track of these images. I will have to add a few computers/hdds to store the images. From what I have read I should be using a distributed file system. Am I correct?

You likely do not need a distributed file system. I wouldn't bother with that. Just start an ZFS array up.

  Are there any best conventions I should follow before I start implementing this? I'm thinking about storing the images and giving them a UUID name, and to store the UUID as the path of the image. Then my application ill contact the rest of the path.

I wouldn't do that. You're only talking millions. Just use an int. If you want to store something useful on the row, I would store the sha256 of the image.


VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed? For a log table, I have a custom message column.

It will never be updated.
It will be searched with LIKE conditions.
It will be capped at 1KB.

TEXT fields are slower than VARCHAR fields for lookups because they stored separately, while VARCHAR fields are stored inline.

What I don't know and couldn't find here on SO (there already are hundreds of VARCHAR vs TEXT questions) or on the MySQL docs: when inlined, do VARCHAR fields take at least the number of bytes specified, or do they vary?.

One of the goals is keeping the size on disk as small as possible. If VARCHAR does not vary, then TEXT has a significant advantage. Otherwise, VARCHAR will provide both lookup speed and small disk footprint.

EDIT: For clarification, if the field is created with ASCII collationwith and a size of 100, a string of length 10 will take 10 bytes or 100 bytes on disk (for inline VARCHAR)?

As the docs say the min size occupied by a varchar is 1B and the max is 256B (before 5.0.3) and 65,536B (after 5.0.3)

But when it comes to look-up performance always searching on a char will be faster then a varchar (it makes sense since you will have fixed size rows then the look-up process will be much faster because it can calculate exactly where in the data the desired value is )

EDIT

If you have varchar(100) and inserted a string of 10 chars then the space occupied is 11B (varchar uses 1B to store the length of the string max. 255 for ver <= 5.0.3 and 2B for ver > 5.0.3 since max. 65,535).
If you have char(100) and inserted a string of 10 chars then the space occupied is 100B.


Why does this query take so long to execute? I'm currently using Microsoft SQL Azure (RTM) - 12.0.2000.8 as the database. The database currently has 10 DTUs.

The idea here is that I want to make the query based on the view. This view contains of simple SELECT() syntax with 5 table joins. This view give output roughly about 253K rows in about 6 seconds.

CREATE VIEW [dbo].[TopAdsDisplaySumaryView]
AS
SELECT
        client.Id AS ClientId,      -- (PK, int, not null)
        client.PartnerId,           -- (FK, int, not null)
        adsPict.Id AS AdsPictureId, -- (PK, int, not null)
        adsPict.ImageName,          -- (nvarchar(max), null)
        displayAds.DisplayTo,       -- (datetime, not null)
        displayAds.DisplayFrom      -- (datetime, not null)
FROM
        dbo.Machines AS machine
        INNER JOIN dbo.MachineGroups AS machineGroups ON machineGroups.Id = machine.MachineGroupId
        INNER JOIN dbo.Clients AS client ON client.Id = machineGroups.ClientId
        INNER JOIN dbo.DisplyadAds AS displayAds ON displayAds.MachineId = machine.Id
        INNER JOIN dbo.AdsPictures AS adsPict ON adsPict.Id = displayAds.AdsPictureId

Notice that I'm only taking the column from tables clients, displayAds, and adsPictures, but I have to join tables machines and machineGroups in order to get data from clients. displayAds table has the most data.

From here I want to generate the top 5 data with the most data count. This is the query:

SELECT TOP 5
    adsSum.PartnerId as PartnerId,
    adsSum.ClientId as ClientId,
    adsSum.AdsPictureId as AdsPictureId,
    adsSum.ImageName as ImageName,
    count(*) as TotalDisplay
FROM [dbo].[TopAdsDisplaySumaryView] adsSum
GROUP BY adsSum.PartnerId, adsSum.ClientId, adsSum.AdsPictureId, adsSum.ImageName
ORDER BY TotalDisplay DESC

Here's the actual execution plan for this query: https://pastebin.com/CcR9cC5e

When this query is executed, it takes about 1 minute 15 seconds. I'm trying to find out why this query takes a lot of time to execute, while executing the view only takes 6 seconds.

Here's my attempts so far:

Created an index for displayAds table. This gives me ~1 second improvement, no big change.

CREATE NONCLUSTERED INDEX
[MachineIdIndexIncludeAdsPictureIdDisplayFromDisplayTo] ON [dbo].[DisplyadAds]
(
    [MachineId] ASC
)
INCLUDE
(
    [AdsPictureId],
    [DisplayFrom],
    [DisplayTo]
)
GO

Created an index for adsPictures table. Nothing changed.

CREATE NONCLUSTERED INDEX [IX_AdsPictures_IncludeImageName] ON [dbo].[AdsPictures]
(
   [Id] ASC
)
INCLUDE ( [ImageName])
GO

Tried to remove column adsPict.ImageName in the query, the execution time was greatly improved to only 3 seconds (~72 sec. improvement!). I suspect that column is the problem, but I need it to show.

The maximum record length of ImageName is 36. Mostly around 11~20. There are 55 rows in AdsPictures table.

Any idea how to resolve this problem? Any help would be greatly appreciated.

Here's what I see in the XML for your actual plan:

<WaitStats>
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="66883" WaitCount="1649" />
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="56086" WaitCount="3980" />
</WaitStats>
<QueryTimeStats CpuTime="6665" ElapsedTime="73522" />

You can also see this information in the root node of the plan in SSMS if you don't like looking at XML:

You can read about the SOS_SCHEDULER_YIELD wait here. A good layman's explanation is that your query was only able to execute on a CPU for 6639 ms. It spent 66883 ms on a queue waiting to get on a CPU. Based on your description of the problem ("executing the view only takes 6 seconds"), you have a server performance issue instead of a query performance issue. Of course, there may be ways to make the query use even less CPU, but I would approach this problem by verifying that the server has the correct number of CPU cores/DTUs/whatever and by looking at the queries that use the most CPU on the server. Your query could run almost 12X faster if there was less load on the server.


SSIS package hits "login failed for user" when simultaneous operations occur against the same target database Quick summary

We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012.  The new packages we deploy using project deployment to an Integration Services Catalog.  The packages import data into another SQL Server 2012 database on a separate server/instance.

It seems we get a "Login failed" error when the package is attempting to execute multiple simultaneous SQL operations against the same SQL Server 2012 target database.

Quick comparison of environments

OLD (where it works)

Target database is SQL Server 2008, on physical Windows Server 2003.
SSIS 2008 packages executed from file system on Windows Server 2003; receive parameters from machine environment variables.

NEW (where we sometimes get the error)

Target database is SQL Server 2012, on virtual Windows Server 2012.
SSIS 2012 packages executed from SQL 2012 Integration Services Catalog on separate server: also virtual Windows Server 2012.

The error

An unusual error is occurring for some packages in the new environment:

DTS_E_OLEDBERROR
An OLEDB error has occurred
Error code: 0x80040E4D
An OLEDB record is available.
Source: "Microsoft SQL Server Native Client 11.0"
HResult: 0x80040E4D
Description: "Login failed for user 'MyUser'"

In the target database SQL log, we have this entry:

Source: Logon, Message: Login failed for user 'MyUser'. Reason: Password did not match that for the login provided. [CLIENT: {IP addr. for SSIS DB server}]

While this appears cut and dried, it does not tell the whole story.

What we know

First of all, we double-checked the database connection strings over in the SSIS package. The packages have database connection managers whose connection properties are bound to project parameters, which in turn are bound to environment variables in the Integration Services Catalogs.

We verified that the packages are executed using the correct environment.

Furthermore, SQL operations using the same connection manager work fine leading up to the error!

The common factor in all the errors seems to be that when multiple simultaneous SQL operations occur using the same database connection manager, we get this "login failed" error.

When we refactor a failing portion of a package to execute database operations one at a time instead of simultaneously, the error goes away.

But it worked in our old environment...

These packages worked fine in SSIS 2008 targeting a SQL Server 2008 database, running on physical Windows Server 2003 servers.

...And it still works when we run the packages from Visual Studio 2012

When we run the new SSIS 2012 packages from Visual Studio against the same target database (SQL 2012 on virtual Windows Server 2012), we do not encounter the error.

This leads us to believe it has something to do with how they are deployed and run in the Integration Services Catalog, but we cannot figure out what is wrong.  Here is where we could use some help.

Here it is:  the failing login in question was a SQL Server login. Here is how we configured the SQL connection manager properties for old, new, and developer environments:

ServerName - MyServer
InitialCatalog - MyDB
UserName - MyUser
Password - ***********
ConnectionString - Data Source=MyServer;User Id=MyUser;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

While this worked perfectly on the old SQL 2008 environment, and it was no problem from Visual Studio, it failed during simultaneous database operations from the SQL 2012 Integration Services Catalog, but started working when we added Password=****** to the connection string.

ConnectionString - Data Source=MyServer;User Id=MyUser;Password=********;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

It would seem that if a second simultaneous connection is being opened while in the SQL 2012 Integration Services Catalog execution environment, only the connection string itself (of the initial connection?) is consulted, not Password or other constituent properties of the connection manager.  Therefore, Password has to be in the connection string.


Single-entry accounting schema I've already built a double-entry accounting schema on a previous app, and it was very complicated. For a new app I want the simplest and most elegant accounting schema possible (transaction volume is low, and uncomplicated).

People can earn money via ad on their website (like google adsense)
We issue them payments for their earnings at a varying interval (weekly, monthly, etc)
Their account can be debited (eg. their website had fraud robot visits), or it could be credited (signup bonus, or a technical error where we didnt pay them enough)

In its simplest form, I have a transactions table with these core columns (ignoring other fluff columns):

id (string)
type (enum: DEBIT/CREDIT)
description (string)
amount (integer)

All of this is well and good, there is only 2 confusing parts that I would like someone to tell me if my solution is sane or not.

First is the fact that earnings need to be "settled" in some way. Fraud and other hiccups are common, so "live" earnings are constantly adjusted. Without an intermediate settlement step, these price variations disappear into the ether.

So my first idea as to create a transactions record every single day for the previous days "settled" earnings. It might look like this, including one demo "fraud" example:
"CREDIT", "User Earnings - Jan 3, 2021", 12021
"CREDIT", "User Earnings - Jan 4, 2021", 8319
"CREDIT", "User Earnings - Jan 5, 2021", 9130
"DEBIT", "Fraud Adjustment", 771

Question 1 is, "does this seem sane so far?"

The next confusion deals with how to cleanly link a payment (transaction) with other transactions (the 4 rows above).

If I wanted to pay the above 3 earnings rows + 1 adjustment row, it would look like this:
"DEBIT", "User Payment", 28699

This is fine, except that it's not in the database which transactions this transaction is associated with. One solution might be to make a many-to-one relation on transaction to itself, but a transaction being associated with other transactions feels "wrong".
I envisioned when an admin issued a payment, it would show a list of all un-associated transactions with checkboxes next to them you could choose from, which would make those transactions tied to the payment somehow, and then they wouldn't accidentally be paid for again, as they would disappear from the list.
My biggest fear is re-inventing the wheel. I'm OK with doing a simpler solution if its "normal" in the real world. I don't want to be a single developer trying to "invent" a solution that's actually really stupid and is easily solved in a different way.
Thank you!

For your first question, that's an appropriate approach to balancing the Transactions. Every monetary change should be recorded as a Transaction. So at whatever rate you consolidate things like fraud, e.g. once a day (or as frequent as every minute, doesn't really matter as long as you record the Transaction for the consolidated amount every time) will result in how many of those kinds of Transactions to be generated.
To your second question, there's nothing inherently wrong with it being a one-to-many relationship, and business context aside, that's how I'd first imagine it. Bringing business context back in, normally a financial system in this space would handle these things through journaling. The concept of a journal is usually represented by two tables, JournalHeader and JournalLines. The JournalHeader represents generic information such as who created it and when it was created, whereas the JournalLines represent the individual Transactions consolidated via settlement. Usually there's a field on the JournalLines table that ties back to the original source of the transactions that were consolidated (generally another table, e.g. if there were SalesOrders that generated Transactions, the JournalLines would tie back to the original SalesOrder they consolidated). This relationship is one-to-many in nature, and standard.
I'm a little fuzzy on the exact implementation of such (as I haven't worked with that kind of finance in a while), but hopefully that helps paint the picture. Long story short, it is ok and normal practice to have a one-on-many relationship between the settling Transaction to its settled Transactions.


MySQL Workbench - store a query to apply to various schemas We have multiple identical schemas. How can I store a query and easily apply it to any of multiple schemas? Bonus points for a drag & drop or point & click method of executing the query.

My first attempt was to set a variable for the schema, like this

SET @db = MySchema;

SELECT * from @db.mytable;

but I get Error Code: 1146. Table 'db.tagdescriptions' doesn't exist.

I'm using MySQL Workbench 6.3, connecting to MySQL Community Server (GPL) 5.6.19-log on Win64.

In addition to what Rick wrote:

If you just wanna have an ad hoc solution (not something you frequently use) you could also just have your query in the editor without a schema and sequentially make each of the schemas you want to process the default schema by double clicking on it's name in the schema tree. I.e. make first one default, run the query. Make the next one default and run the query, and so on.


Why does my SELECT DISTINCT TOP N query scan the entire table? I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two alternating values. I'll use the GetNums function to generate the data:

DROP TABLE IF EXISTS X_2_DISTINCT_VALUES;

CREATE TABLE X_2_DISTINCT_VALUES (PK INT IDENTITY (1, 1), VAL INT NOT NULL);

INSERT INTO X_2_DISTINCT_VALUES WITH (TABLOCK) (VAL)
SELECT N % 2
FROM dbo.GetNums(1000000);

UPDATE STATISTICS X_2_DISTINCT_VALUES WITH FULLSCAN;

For the following query:

SELECT DISTINCT TOP 2 VAL
FROM X_2_DISTINCT_VALUES
OPTION (MAXDOP 1);

SQL Server can find two distinct values just by scanning the first data page of the table but it scans all of the data instead. Why doesn't SQL Server just scan until it finds the requested number of distinct values?

For this question please use the following test data that contains 10 million rows with 10 distinct values generated in blocks:

DROP TABLE IF EXISTS X_10_DISTINCT_HEAP;

CREATE TABLE X_10_DISTINCT_HEAP (VAL VARCHAR(10) NOT NULL);

INSERT INTO X_10_DISTINCT_HEAP WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_10_DISTINCT_HEAP WITH FULLSCAN;

Answers for a table with a clustered index are also acceptable:

DROP TABLE IF EXISTS X_10_DISTINCT_CI;

CREATE TABLE X_10_DISTINCT_CI (PK INT IDENTITY (1, 1), VAL VARCHAR(10) NOT NULL, PRIMARY KEY (PK));

INSERT INTO X_10_DISTINCT_CI WITH (TABLOCK) (VAL)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_10_DISTINCT_CI WITH FULLSCAN;

The following query scans all 10 million rows from the table. How can I get something that doesn't scan the entire table? I'm using SQL Server 2016 SP1.

SELECT DISTINCT TOP 10 VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);

There look to be three different optimizer rules that can perform the DISTINCT operation in the above query. The following query throws an error which suggests that the list is exhaustive:

SELECT DISTINCT TOP 10 ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, QUERYRULEOFF GbAggToSort, QUERYRULEOFF GbAggToHS, QUERYRULEOFF GbAggToStrm);

  Msg 8622, Level 16, State 1, Line 1

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

GbAggToSort implements the group-by aggregate (distinct) as a distinct sort. This is a blocking operator that will read all of the data from the input before producing any rows. GbAggToStrm implements the group-by aggregate as a stream aggregate (which also requires an input sort in this instance). This is also a blocking operator. GbAggToHS implements as a hash match, which is what we saw in the bad plan from the question, but it can be implemented as hash match (aggregate) or hash match (flow distinct).

The hash match (flow distinct) operator is one way to solve this problem because it is not blocking. SQL Server should be able to stop the scan once it finds enough distinct values.

  The Flow Distinct logical operator scans the input, removing duplicates. Whereas the Distinct operator consumes all input before producing any output, the Flow Distinct operator returns each row as it is obtained from the input (unless that row is a duplicate, in which case it is discarded).

Why does the query in the question use hash match (aggregate) instead of hash match (flow distinct)? As the number of distinct values changes in the table I would expect the cost of the hash match (flow distinct) query to decrease because the estimate of the number of rows it needs to scan to the table should decrease. I would expect the cost of the hash match (aggregate) plan to increase because the hash table that it needs to build will get bigger. One way to investigate this is by creating a plan guide. If I create two copies of the data but apply a plan guide to one of them I should be able to compare hash match (aggregate) to hash match (distinct) side by side against the same data. Note that I can't do this by disabling query optimizer rules because the same rule applies to both plans (GbAggToHS).

Here's one way to get the plan guide that I'm after:

DROP TABLE IF EXISTS X_PLAN_GUIDE_TARGET;

CREATE TABLE X_PLAN_GUIDE_TARGET (VAL VARCHAR(10) NOT NULL);

INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT CAST(N % 10000 AS VARCHAR(10))
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_PLAN_GUIDE_TARGET WITH FULLSCAN;

-- run this query
SELECT DISTINCT TOP 10 VAL  FROM X_PLAN_GUIDE_TARGET  OPTION (MAXDOP 1)

Get the plan handle and use it to create a plan guide:

-- plan handle is 0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000
SELECT qs.plan_handle, st.text FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%X[_]PLAN[_]GUIDE[_]TARGET%'
ORDER BY last_execution_time DESC;

EXEC sp_create_plan_guide_from_handle
'EVIL_PLAN_GUIDE',
0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000;

Plan guides only work on the exact query text, so let's copy it back from the plan guide:

SELECT query_text
FROM sys.plan_guides
WHERE name = 'EVIL_PLAN_GUIDE';

Reset the data:

TRUNCATE TABLE X_PLAN_GUIDE_TARGET;

INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

Get a query plan for the query with the plan guide applied:

SELECT DISTINCT TOP 10 VAL  FROM X_PLAN_GUIDE_TARGET  OPTION (MAXDOP 1)

This has the hash match (flow distinct) operator that we wanted with our test data. Note that SQL Server expects to read all of the rows from the table and that the estimated cost is the exact same as for the plan with the hash match (aggregate). The testing that I did suggested that the costs for the two plans are identical when the row goal for the plan is greater than or equal to the number of distinct values SQL Server expects from the table, which in this case can be simply derived from the statistics. Unfortunately (for our query) the optimizer picks the hash match (aggregate) over hash match (flow distinct) when the costs are the same. So we're 0.0000001 magic optimizer units away from the plan that we want.

One way to attack this problem is by decreasing the row goal. If the row goal from the optimizer's point of view is less than the distinct count of rows we'll probably get hash match (flow distinct). This can be accomplished with the OPTIMIZE FOR query hint:

DECLARE @j INT = 10;

SELECT DISTINCT TOP (@j) VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, OPTIMIZE FOR (@j = 1));

For this query the optimizer creates a plan as if the query just needs the first row but when the query is executed it gets back the first 10 rows. On my machine this query scans 892800 rows from X_10_DISTINCT_HEAP and completes in 299 ms with 250 ms of CPU time and 2537 logical reads.

Note that this technique will not work if the statistics report just one distinct value, which could happen for sampled statistics against skewed data. However, in that case it's unlikely that your data is packed densely enough to justify using techniques like this. You may not lose much by scanning all of the data in the table, especially if that can be done in parallel.

Another way to attack this problem is by inflating the number of estimated distinct values SQL Server expects to get from the base table. This was harder than expected. Applying a deterministic function cannot possibly increase the distinct count of results. If the query optimizer is aware of that mathematical fact (some testing suggests it is at least for our purposes) then applying deterministic functions (which includes all string functions) will not increase the estimated number of distinct rows.

Many of the nondeterministic functions did not work either, including the obvious choices of NEWID() and RAND(). However, LAG() does the trick for this query. The query optimizer expects 10 million distinct values against the LAG expression which will encourage a hash match (flow distinct) plan:

SELECT DISTINCT TOP 10 LAG(VAL, 0) OVER (ORDER BY (SELECT NULL)) AS ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);

On my machine this query scans 892800 rows from X_10_DISTINCT_HEAP and completes in 1165 ms with 1109 ms of CPU time and 2537 logical reads, so the LAG() adds quite a bit of relative overhead. @Paul White suggested to try batch mode processing for this query. On SQL Server 2016 we can get batch mode processing even with MAXDOP 1. One way to get batch mode processing for a rowstore table is to join to an empty CCI as follows:

CREATE TABLE #X_DUMMY_CCI (ID INT NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX X_DUMMY_CCI ON #X_DUMMY_CCI;

SELECT DISTINCT TOP 10 VAL
FROM
(
    SELECT LAG(VAL, 1) OVER (ORDER BY (SELECT NULL)) AS VAL
    FROM X_10_DISTINCT_HEAP
    LEFT OUTER JOIN #X_DUMMY_CCI ON 1 = 0
) t
WHERE t.VAL IS NOT NULL
OPTION (MAXDOP 1);

That code results in this query plan.

Paul pointed out that I had to change the query to use LAG(..., 1) because LAG(..., 0) doesn't appear to be eligible for the Window Aggregate optimization. This change reduced the elapsed time to 520 ms and the CPU time to 454 ms.

Note that the LAG() approach isn't the most stable one. If Microsoft changes the uniqueness assumption against the function then it may no longer work. It has a different estimate with the legacy CE. Also this type of optimization against a heap isn't necessary a good idea. If the table is rebuilt it's possible to end up in a worst case scenario in which almost all of the rows need to be read from the table.

Against a table with a unique column (such as the clustered index example in the question) we have better options. For example we can trick the optimizer by using a SUBSTRING expression that always returns an empty string. SQL Server does not think that the SUBSTRING will change the number of distinct values so if we apply it to a unique column, such as PK, then the estimated number of distinct rows is 10 million. This following query gets the hash match (flow distinct) operator:

SELECT DISTINCT TOP 10 VAL + SUBSTRING(CAST(PK AS VARCHAR(10)), 11, 1)
FROM X_10_DISTINCT_CI
OPTION (MAXDOP 1);

On my machine this query scans 900000 rows from X_10_DISTINCT_CI and completes in 333 ms with 297 ms of CPU time and 3011 logical reads.

In summary, the query optimizer appears to assume that all rows will be read from the table for SELECT DISTINCT TOP N queries when N >= the number of estimated distinct rows from the table. The hash match (aggregate) operator may have the same cost as the hash match (flow distinct) operator but the optimizer always picks the aggregate operator. This can lead to unnecessary logical reads when enough distinct values are located near the start of the table scan. Two ways to trick the optimizer into using the hash match (flow distinct) operator are to lower the row goal using the OPTIMIZE FOR hint or to increase the estimated number of distinct rows using LAG() or SUBSTRING on a unique column.


Sql Server rollback to some point in time This seems like a fairly common scenario, yet I'm not sure how to do this.

I have a very large database (think a lot of terabytes), which makes it impossible to create a full backup. At some point, I want to run an update script on the database that can do some schema modifications, alter some data, etc.

To be on the safe site, I would like to be able to undo these operations if something goes wrong. It's okay if no-one can access the database during the update process.

To make it a bit more complex, I don't want to ruin the daily maintenance / backup process.

I've been thinking about putting the database in single server mode and then rolling back the transaction log for a specified period of time, but I'm not really sure how to do this (and time doesn't feel really reliable). I've looked at the 'checkpoint' function, but am unsure how to use this. Anyone?

Use a database snapshot. Create a snapshot, do your worse. If things go ugly, revert to the snapshot, see Revert a Database to a Database Snapshot. Database snapshots are copy-on-write so they take almost no space on disk (the space taken will grow as you update the original db and the copy-on-write must save the previous data). They're fast to create and drop.

If you have to revert from a snapshot it will break the backup log chain:

  Reverting breaks the log backup chain. Therefore, before you can take log backups of the reverted database, you must first take a full database backup or file backup. We recommend a full database backup.

This will impact your maintenance and you must plan accordingly, eg. be prepared to re-seed the log chain with a new full backup. Unfortunately, there is no realistic alternative. Of course, you should test your migration scripts before attempting them on production. You are using scripts, aren't you?


Auto-increment key and Foreign Key as composite for relational database I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the internet and I have become increasingly confused as to how I will do what I want to do. Below is what I am trying to accomplish.

I have a need for a MySQL database that will hold patient information. Patient information will be name, id (unique), and so on as well as all of the tests and samples for each test.

Each patient can have multiple tests
Each test can have multiple samples
If I delete a patient then all test and samples are deleted
If I delete a test then all samples will be deleted
If I delete all samples of one test OR all tests of one patient then the test OR patient should not be deleted.

So far I understand a relational database is what I need. I also understand that I can use foreign keys to link the patient id's for each table as well as the test id for each table. I have also come to the conclusion that I would like data integrity so that I don't have to manually manage deleting and updating the rows. This would also not allow for orphaned rows. I believe this can be done with a composite key but this is where I start getting confused on how to set it up.

I would expect it to work like this (with each line being an insert statement):

Patient Table:

|-------------------|
|Patient ID  |Name  |
|-------------------|
|12345       |ANG   |
|54321       |JUE   |
|-------------------|

Tests Table:

|----------------------|
|Test ID  |Patient ID  |
|----------------------|
|1        |12345       |
|2        |12345       |
|3        |12345       |
|1        |54321       |
|2        |54321       |
|4        |12345       |
|----------------------|

Sample Table:

|----------------------------------|
|Sample ID  |Test ID  |Patient ID  |
|----------------------------------|
|1          |1        |12345       |
|2          |1        |12345       |
|3          |1        |12345       |
|1          |2        |12345       |
|1          |1        |54321       |
|1          |2        |54321       |
|2          |2        |54321       |
|2          |2        |12345       |
|----------------------------------|

Can this be done easily? I also know that this can be accomplished with one or two triggers but I understood that to not handle deletions and the like. Is there another way to do this that is easier to implement? Also, data being written and read from this database is handled by my LabVIEW program.

You don't need composite keys to enforce your referential integrity in your case.  The reason is that you have a pretty straight-forward three tier hierarchy:

PATIENT
   +
   |
   ^
 TEST
   +
   |
   ^
SAMPLE

Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.

This works because each sample record needs a test record and each test needs a patient.  If you delete a patient, their tests must be cascade deleted.  If you delete a test its samples must be cascade deleted.  Therefore you don't need patient_id in the TEST table.  All you need is to declare cascade deletion on your foreign keys, like so...

...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
      REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
      REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...


Create second instance of mysqld on windows xp I am trying to install the new version of the mysql on the windows7 and windows xp . In few of the computers already a new version of mysql are installed. Here I just want to create a second instance of the service of mysqld with my my_XX.ini file.

I have successfully configured the instance on the windows 7 pc . But can not create instance on the windows xp for the same .

I am trying this code on the command prompt which run as administrator. but it does not start the service

sc create mysqld2 binPath= c:\mysql\bin\mysqld.exe --defaults-file=c:\mysql\my_XX.ini

Even I have tried with the mysqld command to start the mysql as per other my_xx.ini with

mysqld --defaults-file=c:\mysql\my_XX.ini

I am not able to create a second instance on the windows xp . Please help me to start the second instance of mysql on windows xp with windows service

=> only set my_ibt.ini  in the pateh C:\RailsInstaller\
=> my_xxx.ini

[mysqld_xxx]
#if already installed then use that basedir
basedir = "C:\RailsInstaller\MYSQL"

#where you want to set your database
datadir="C:\RailsInstaller\MYSQL\data"
port = 43307
enable-named-pipe
socket = mypipe1

In the command promt which runs as administrator go to
\MYSQL\bin>
And run Command :

mysqld --install mysqld_xxx --defaults-file=C:\RailsInstaller\my_xxx.ini

Service successfully installed.

Then :

net start mysqld_xxx

The mysqld_ibt service is starting.
The mysqld_ibt service was started successfully.


Joining two tables in one has a subquery which is returning id count present in one table which is not presented on another field of a particular user I have a two tables

My first table goes in at this way

CREATE TABLE `inventory` (
  `id` int(64) NOT NULL,
  `user_id` int(50) NOT NULL,
  `placed_date` date NOT NULL,
  `name_of_campaign` varchar(250) NOT NULL,
  `budget` int(64) NOT NULL,
  `start_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `platformType` int(64) NOT NULL,
  `titleOfTheVideo` varchar(250) NOT NULL,
  `videoUrl` varchar(250) NOT NULL,
  `redirection_url` varchar(250) NOT NULL,
  `photo_url` varchar(250) NOT NULL,
  `tags` varchar(250) NOT NULL,
  `language` int(64) NOT NULL,
  `country` int(11) DEFAULT NULL,
  `state` int(11) NOT NULL,
  `city` int(11) NOT NULL,
  `age` int(64) NOT NULL,
  `max_age` int(11) NOT NULL,
  `gender` int(64) NOT NULL,
  `publisher_catagory` int(64) NOT NULL,
  `publisher_name` int(64) NOT NULL,
  `status` int(11) NOT NULL,
  `paid` int(60) NOT NULL,
  `min_cpc` int(60) NOT NULL,
  `max_cpc` int(60) NOT NULL,
  `min_cpv` int(60) NOT NULL,
  `max_cpv` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

For this data

INSERT INTO `inventory` (`id`, `user_id`, `placed_date`, `name_of_campaign`, `budget`, `start_date`, `ending_date`, `platformType`, `titleOfTheVideo`, `videoUrl`, `redirection_url`, `photo_url`, `tags`, `language`, `country`, `state`, `city`, `age`, `max_age`, `gender`, `publisher_catagory`, `publisher_name`, `status`, `paid`, `min_cpc`, `max_cpc`, `min_cpv`, `max_cpv`) VALUES
(49, 113, '2018-05-02', 'Ads', 50000, '2018-05-25', '2018-05-28', 1, 'Some title', 'uploadvideos/arun vm.webm', 'http', 'screenimages/arun vm.png', 'videoads,videos,ads', 2, 96, 1639, 1103581, 25, 30, 2, 1, 109, 1, 1, 25, 30, 5, 10);

And my second table.

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data for the same.

INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`, `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`, `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`) VALUES
    (1, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (2, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (3, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (4, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (5, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (6, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
(7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0);

This query

select id,
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
  publisher_id
from
  (select id,
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  group by ad_id, visitor_ip) t group by t.ad_id;

In at the second table gives me this answer.

Now i want to fetch this answer along with the ad name and budget from my first table i am not getting a proper way to join the tables how can i do the same?

As far as I understand, a simple inner join on the foreign key/primary key pair should suffice?

Assuming that ad_analytics.ad_id refers to inventory.id (consider using foreign key constraints!) and by "name" you mean name_of_campaign I have something like

select t.id,
  t.ad_id,
  sum(t.impression) total_impression,
  sum(t.view) total_views,
  sum(t.clicks) total_clicks,
  t.publisher_id,
  i.budget,
  i.name_of_campaign
from
  (select id,
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  group by ad_id, visitor_ip) t
  inner join inventory i
             on i.id = t.ad_id
group by t.ad_id;

in mind.

Note, that I had to introduce using the aliases as a prefix in the list of selected columns, as id would have been ambiguous otherwise (it's in both tables).


MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file.

This never seemed to happen before.

I understand that SLEEP_BPOOL_FLUSH is a normal wait for the CHECKPOINT process when the disk subsystem is busy. Actually, we've always had a very busy disk subsystem so I don't understand why this has become an issue.

(If you manually run a checkpoint command, same results...your user process waits in SLEEP_BPOOL_FLUSH).

Is there ANYTHING we can do to push the checkpoint to occur? Can the parameters for the checkpoint be tweaked so this does not occur?

(SQL Server 2005, RAID subsystem, Simple Recovery Model)

If Resource Monitor says that SQL Server is actually performing the checkpoint (i.e., it's working okay, just that it's slow), all you can and should do is wait.

If the disk subsystem is busy anyway (shared storage?), is it possible the other load has increased and now the I/O subsystem is near or at capacity? If you aren't measuring (recording) metrics that would tell you so, I would highly recommend this, using either PerfMon directly or some other 3rd-party tool. (It also depends on the storage architecture. The main point is to be able to track the I/O load over time.) If you're on a SAN, was there a change in the I/O subsystem itself that would be transparent to your SQL box?

One thing you can consider is changing the Recovery Interval setting, which lets you manually control how frequently checkpoints occur. Note that this is an instance setting. I strongly recommend reading the documentation thoroughly before changing it, because it's possible you could make things worse by moving away from the default (depending on your workload).


Why does a rollback of a CREATE INDEX statement take longer than expected? Recently, I needed to apply a clustered index to a table. I first performed the operation in a Staging environment. The operation completed in about 20 minutes, so we planned for PROD to take approximately the same time. After I executed the statement, we let it run for about 4 minutes before we noticed some unanticipated blocking that was causing application queries to time out. Due to the timeouts, we decided to stop the execution, so I cancelled it.

Now, I assumed that either one of two things would happen when I pressed the cancel button:

1) SQL Server would stop organizing the data on disk and leave anything it had already moved where it was, thereby causing the rollback to be almost instant.

OR

2) SQL Server would take the data it had already organized on disk and move it back to where it was before, thereby causing the rollback to take approximately as long as the statement had been executing. In this case, the statement had been running for 4 minutes, so the rollback should make the total time 8 minutes.

Well, neither one of those things happened. The rollback actually took 20 minutes by itself, which was the amount of total time the index took to apply in Staging. This made the total time 24 minutes (execution for 4, rollback for 20).

Now, I am no internals expert, but what I infer from this is that after I clicked the cancel button, SQL Server had to scan the entire index on disk to find what data it had moved, and then move it back.

Does anyone have any more insight on this? Am I even in the ballpark here?

FYI, the only thing different between the Staging and Prod environments is that Staging has about 1/6 of the RAM. Also, the table was around 28 million rows and was a heap.

Thanks all!

I believe what you are looking for is in the warning section of this MSDN reference under the Managing Heaps section.

  Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

Your tempdb is going to play a critical role here and if you already have contention there with normal production requests maintenance tasks like indexing or de-indexing could take significantly more time to complete.  And as Kin stated your rollback is probably single threaded vs multi-threaded with the create and alter statements.


What is the strategy of query evaluation in DBMS when Limit is used in query? I have following tables in database

post(post_id , user_id , post_text , parsed_text) ;
Comment(comment_id , post_id , user_id , comment_text) ;
friend(id , user_id , friend_id ) ;
group (id , user_id ,group_id ) ;
like  (id, user_id , page_id ) ;

SQL to retrieve data from posts

 $posts =  mysql_query("select * from post where user_id in
(select f_id  from ((select friend_id as f_id from friend  where user_id = 'a')
union (select group_id as f_id  from group  where user_id = 'a')
union (select page_id as f_id   where user_id = 'a'  ))) order by post_id limit 0 , 30");

In these table there are BTREE index on post_id in post table , comment_id in comment table and  id in like , friend , share table ?

How DBMS evaluates this query to get 30 rows .

Is there any way to retrieve data from database from bottom  of the table ? Bottom in the sense id is auto increment so i need to retrieve data from database as

If table has id 'N' then it should start retrieving data as
first 'n-1' th'tupple ,second  'n-2' th tupple ,third 'n-3' th tupple and so on
without using 'ORDER BY' .

This can reduce time complexity for above query .

Simplifying down to the post table, for discussion.

Create a unique index on the post_id.

CREATE UNIQUE INDEX recent_post_id ON post(post_id)

Then query the data as such:

SELECT *
FROM post
WHERE user_id IN (your criteria)
ORDER BY post_id DESC
LIMIT 30

Of course, the most overhead may well be your nested collection of rows that you UNION together to get the criteria for recent posts.


Ramifications of Full SYSAUX - Oracle What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?
There are plenty of sites discussing how to view the SYSAUX occupants, to reduce the size of SYSAUX via dbms_stat.purge_stats(), to add datafiles to SYSAUX tablespace, and to alter_stats_history_retention to avoid the problem in the future.  This is not what is being asked here.
What are the consequences of not doing any of the previously mentioned tasks, but still utilizing the database?  I've read that SYSAUX is important, but not critical (critical stuff is in SYSTEM).  However, I have not seen any further discussion about what are the consequences of continuing to operate with a full SYSAUX.
For example, will performance be an issue?  If so, in what ways?
Does Oracle stop writing to SYSAUX or does Oracle try to delete just enough "old" items to make room for "new" items? (most likely it depends on what it is, but don't see any documentation or article that describes the resulting behavior of a full SYSAUX tablespace and what the resulting consequences may be and what to watch out for).
If one gathers stats will Oracle purge the oldest historical stats to make room for the new ones?  If so, is it true MMON gets called to do this "on-the-fly" rather than during the nightly process to maintain the retention setting.  If so, is this costly if an application is doing a lot of stat gathering and to what extent is it costly?
Thanks in advance.
EDIT: Please assume that AUDSYS schema is not in SYSAUX tablespace.

What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?

Nearly full?  None at all.
Completely full?  Probably a dead database!
By far the biggest consumer of SYSAUX in 19c is the Unified Audit Trail.
Where we used to dump gigabytes of Audit files into a file system somewhere and delete them occasionally, 19c now insists that we dump the same gigabytes of Audit data into tables held in SYSAUX, by default.
It has always been the case that if the Audit system cannot record what it wants to then it throws an almighty strop and crashes the entire database.
Nothing like subtlety.
So, if your Unified Audit Trail is writing to SYSAUX and SYSAUX fills up completely?
Bye bye, Database.
Oracle recommend changing the tablespace used by the Unified Audit Trail.
Kind of makes you wonder why they don't ship Oracle Database with a separate tablespace for this in the first place.


SQL server Database statistics degrading after execution I have been experiencing timeouts while querying my SQL Server DB in particular one table. I tried updating the statistics and it fixed the performance issues for a couple of days.

I started digging deeper and found out that there is a scheduled synchronization task that retrieves data from a web-service and updates the database. This task uses Entity framework for this purpose. Several colleagues tried to fix this but so far no luck.
I wanted to take a step back.

Throw EF out of the picture
this should not be a piece of C#/EF code to do this but is a perfect job for an ETL tool. A delta update should (according to my understanding of DB statistics do the job). So far, every time the scheduled task runs, everything is being updated.

Can someone provide me with more information or insights in this situation for clues so that I can try to fix the code before bringing in new tools and solutions.

Starting at the beginning:

SQL Server uses statistics objects to generate cardinality estimates for the result set steps during the optimization phase. Cardinality estimates are used to generate a query plan, which is the logical plan by which SQL Server will physically access and retrieve the data in order to satisfy the request. Generally speaking, more accurate statistics lead to a better plan because SQL Server chooses the logical and physical operators to use based on these cardinality estimates. Good estimates give SQL Server a better chance at choosing the best operation for the amount of data it expects to process.

A SQL Server Statistics object consists of a header, a density vector, and a histogram. The density vector and the histogram are used under different circumstances when estimating cardinality.

Modifications (via INSERT, UPDATE, and DELETE) necessarily change the data profile (INSERT and DELETE by modifying the number of rows in the table itself, and UPDATE by modifying the number of rows that satisfy a condition) so any of these "writes" can ultimately affect the optimization and planning phases of query execution. For a minor number of writes, this generally isn't an issue as a few rows here-and-there won't necessarily change the operators that SQL Server uses. A notable exception is if the written data are inserts that "add on" to the existing data set (e.g. an IDENTITY column, or inserting the next day's data). This is called an ascending key and can be problematic because the values exist outside of the boundaries of the histogram and (up until SQL Server 2014) SQL Server has no knowledge of the number of rows that would satisfy a request with the new key as a filter value. Sometimes you'll hear about statistics that have taken many modifications as "stale."

Because you've not shared the nature of your scheduled synchronization task, I can't tell you why updating statistics helps, in your case. It could be ascending key, in which case the update will include the new key values in the histogram. Or it could be that you've squeezed in enough data to affect the cardinality of the result set of an extant value, but your cached plan is not fit for this new amount of data. It also depends on the queries with which you're experiencing this performance degradation. Are they queries on the new data, old data, or all data? Additionally, I've seen some poor SQL generated by Entity Framework, which adds layers of complexity to the optimization and planning phase so that when the poor SQL is combined with "stale" statistics, you get a bad plan that degrades over time (if you're lucky). So, calls to scrap EF are not bad advice, but let's stay focused on the statistics portion.

Depending on your version of SQL Server, updating statistics might cause plan cache invalidation. If you're running 2005->2008R2, invalidation will only occur for relevant plans if the AUTO_UPDATE_STATISTICS database option is enabled on the database. Additionally, for versions >= 2012, you'll get an invalidated plan after a stats update if there were any modifications to the stats objects. What this means is that blindly running a stats update (especially in 2012 and newer) won't give you a better plan if you've not modified any data.

So how can you check to see if you've modified any data? Here's a query that I use (of particular interest to you would be the ModificationCounter column):

select
    SchemaName = sch.name,
    TableName = t.name,
    StatName = s.name,
    IndexName = i.name,
    StatLeadingColumn = index_col(quotename(sch.name)+'.'+quotename(t.name),s.stats_id,1),
    StatColumns = sc.StatColumns,
    IsFiltered = s.has_filter,
    StatFilter = s.filter_definition,
    IsAutoCreated = s.auto_created,

    StatLastUpdated = sp.last_updated,
    ModificationCounter = sp.modification_counter,
    StatRows = sp.rows,
    StatRowsSampled = sp.rows_sampled,
    StatUnfilteredRows = sp.unfiltered_rows,
    StatSteps = sp.steps
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sch
    on t.schema_id = sch.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
cross apply (
                select
                stuff(
                        (
                            select ', '+c.name
                            from sys.stats_columns scol
                            join sys.columns c
                                on scol.object_id = c.object_id
                                and scol.column_id = c.column_id
                            where scol.object_id = s.object_id
                                and scol.stats_id = s.stats_id
                            for xml path(''), type
                        ).value('.','nvarchar(max)')
                        ,1,2,''
                    ) StatColumns
            ) sc
left join sys.indexes i
    on s.object_id = i.object_id
    and s.name = i.name

In order to run the above, you're going to need to be a member of the db_owner or db_ddladmin fixed database role. The sysadmin server role can obviously run this as well, but I don't generally advocate for randomly adding folks to that server role.

To check to see if a filter value is in the histogram, you can run

DBCC SHOW_STATISTICS('dbo.TableName','StatisticsName') WITH HISTOGRAM;

That will output the histogram step boundary values (RANGE_HI_KEY). If the filter is greater than the last value for this column, then it's outside of the boundaries of the table and you are at risk for cardinality estimates that are quite incorrect.

There's also a caveat to the AUTO_UPDATE_STATISTICS behavior: it updates based on the number of rows in the table.

If a table has 0 rows, the statistics will be "stale" (invalidated) when any rows are added.
If the table has <= 500 rows (when statistics were last collected), the statistics will be invalidated when data has changed on the leading column of the statistic by more than 500.
If the table has > 500 rows (when statistics were last collected), statistics will be invalidated when there are modifications to the leading column of (500 + 20% of table cardinality).

When these thresholds are met, the statistic is invalidated and the relevant query plan(s) are invalidated. The next time a query is executed, SQL Server will see the invalidation and attempt to load statistics to come up with a new plan. If the statistics are also invalidated, SQL Server will sample the data at the default rate before proceeding to the next step in optimization and planning. You can work around this by enabling the AUTO_UPDATE_STATISTICS_ASYNC database option, but then you're stuck with the invalid query plan for another execution.

Trace Flag 2371 has also been suggested as a potential fix. This changes the threshold above from a stepped linear threshold to a logarithmic threshold, triggering an update when the modification counter hits SQRT(1000*Table Cardinality) (buried in this whitepaper).


GRANT ROLE TO USER in MySQL I created one role QI_RO, granted all privileges on all schemas on the server and I was trying to grant role privilege to another user but throwing error.
GRANT QI_RO to test_user@'%';

Error Message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QI_RO to test_user@'%'' at line 1

DB version:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

How to grant role privileges to users in Mysql like other databases, Postgres and oracle?
Thanks

It is only supported in  mysql 8 https://dev.mysql.com/doc/refman/8.0/en/roles.html


Database diagram for linked server tables I run Microsoft SQL Server Express 64-bit version 14.0.2027.2 (X64) on Windows 10 Pro 10.

I created a linked server connecting tables from a Microsoft Access (mdb) database to Server Objects/Linked Servers on my SQL Server Express instance. All the linked tables seem to be working well.

I would like to create a Database Diagram for the linked tables. Since I am new to SQL Server, as far as I know ER diagram is possible to make only for the local database.

Is there any option how to make an ER diagram for linked server tables?

If not (when diagram is allowed only for native database/tables), is there any possibility to link tables directly into a native SQL Server database and then create diagram for tables?

There's no way to do this with the native tools within SQL Server Management Studio. You could get a third party tool like ER/Studio, but even that won't automatically model the linked tables. You'll have to enter them manually. External tables just aren't considered to be a part of the fundamental model of a given database. They're external, additional, other.


how to restore database from other server - sql I have backup database from server A. Then I want to restore it in server B.
I have manage to restore it, but I cannot connect to it when running my VB program. I already change the configuration file, but still doesn't work.

I guess that I need to change something related with the permission, but I dont know what should I change. Anyone can help me?

In the absence of more information, I'll guess SQL Server.

SQL Server uses logins to connect at the Instance (server) level
A login maps to a user in a databases

You can see this in SSMS in the Security nodes on Object Explorer.

I suspect you have "orphaned" users. That is, the users in your restored database no longer have an associated login to map to. This need recreated (Windows logins) or remapped (SQL logins)

The MSDN article Troubleshooting Orphaned Users has checks, fixes and more details. I'd simply copy/paste or re-hash this so I recommend you look at this.


Meaning of the is unique clause here Suppose I have two relations R and S and I execute the following query:

Select R.* from R,S where R.a=S.a and is unique R

What is the effect of the is unique clause here ?

I would say "is unique" is an operator that returns true if, and only if. the tested SQL relation does not contain any duplicate tuples.

For context, this is question 11 in this quiz, and Q54 in GATE 2014 Paper 2 (pdf).

Answer D is therefore incorrect, because it returns an empty (null) result if R contains any duplicate tuples.


Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens? Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in this case? Would the two triggers keep inserting records into the tables over and over until the database filled up? Is there some limit to how many times this will happen?

Aside from the obvious "uh, don't do that"... this took about two minutes to test.

USE tempdb;
GO

CREATE TABLE dbo.TableA(ID INT);

CREATE TABLE dbo.TableB(ID INT);

GO
CREATE TRIGGER dbo.TableAToB
ON dbo.TableA
FOR INSERT
AS
  INSERT dbo.TableB SELECT ID FROM inserted;
GO
CREATE TRIGGER dbo.TableBToA
ON dbo.TableB
FOR INSERT
AS
  INSERT dbo.TableA SELECT ID FROM inserted;
GO

INSERT dbo.TableA(ID) VALUES(1);

Error:

  Msg 217, Level 16, State 1, Procedure TableBToA
  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Also, no row is ever inserted into either table.


Full text search in XML documents I would like to do full-text search in XML documents.

However, I can't get matches for attribute values (I get a syntax error if I provide the full attribute value including the : and / characters in the example below). Also, I don't know how to specify my own delimiters for the parser.

Below is SSCCE:

I would like to be able to select for the last component of the attribute value: attributevalue and for the parser to recognize : and / as delimiters and so yield boo1, boo2, some and attributevalue as lexemes.

DROP TABLE IF EXISTS xmldocument;
CREATE TABLE IF NOT EXISTS xmldocument (
i SERIAL NOT NULL,
content XML NOT NULL
);
ALTER TABLE xmldocument ADD PRIMARY KEY (i);

INSERT INTO xmldocument (content) VALUES
('foo')
,('foo')
;

-- matches both records as expected
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'foo';

-- no match
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'attributevalue';

-- no match
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'boo2';

 -- no match
 SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'boo4';

As to why I don't just use ILIKE % etc. The reason is that I need to optimize by using a GIN index and I don't think it is possible or meaningful to build an index on simple VARCHAR values.

* update after accepting the answer*

Based on the accepted answer, the following queries all work as expected:

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('foo');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('attributevalue');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('boo2');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('boo4');

If you don't want to write your own parser, the quick and dirty solution would be to replace < and > with some other punctuation, so that the existing parsers don't decide to discard them as html tags.

SELECT *
FROM xmldocument
WHERE to_tsvector(regexp_replace(content::text,'[<>]',' ','g')) @@ 'boo2';

  As to why I don't just use ILIKE % etc. the reason is that I need to optimize by using a GIN index and I don't think it is possible or meaningful to build an index on simple VARCHAR values.

If you use the pg_trgm extension, you can build a gin index on VARCHAR values which will optimize ILIKE queries.  How effective it is depends on the size of your indexed documents, and the size of your query.  I'd recommend trying it and seeing how it does for you.

Version 1.2 of pg_trgm (to be included in PostgreSQL 9.6, but it is fairly easy to back-port into 9.4 and 9.5 if you are willing to compile some code) will be much more effective with large queries.


No "Using locked pages for buffer pool" in SQL Server log As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to show up in the SQL Server log. I want to make sure I can do this correctly before I do it for a client.

Our development server is a Windows 2003 with 4 GB of RAM, SQL Server 2008 R2 Standard Edition 32Bit.

Below is what I have attempted:

Checked "Use AWE to allocate memory".
Set the "Maximum server memory"
setting.
Added startup parameter -T845 in the
SQL Server service configuration.
The SQL Server service is running on
the LocalSystem Account so from what
I have read I do not need to enter an
account in the "Lock Pages in Memory"
priveledge. However, I did try
changing the service to operate under
the "Administrator" login, and added
that login to the "Lock Pages in
Memory" privelege.
Restarted SQL Server Service.

Nothing I have done will put

  Using locked pages for buffer pool

into my SQL Server log, but the 845 trace appears to be turned on when I run DBCC TRACESTATUS.

Anyone have any ideas about why nothing I have done appears to turn this on?

As I see, you're using a 32 bit system, so please follow the information from this MSDN article: Do I have to assign the Lock Pages in Memory privilege for Local System?. It's from MS's own CSS team.

Quote from it:
"If you are using 32bit systems, the messages and algorithm are slightly different. The message you should look for in the ERRORLOG is the following:

Address Windowing Extensions is enabled

If the privilege was not set correctly, you will see this message:

Could not use Address Windowing Extensions because the 'lock pages in memory' privilege was not granted.

These messages will NOT show up for 32bit systems unless the configuration value 'awe enabled' is set to 1. The second message can show up if the "Lock Pages in Memory" privilge was changed after successfully configuring AWE and then the privilige was cleared OR you used RECONFIGURE WITH OVERRIDE and the privilige was not set. "

Another detailed article regarding "Lock pages in memory" is from well known sql writer Glenn Berry: SQL Server and the “Lock pages in memory” Right in Windows Server.