Is it possible to allow missing value inserts in mysql instead of requiring "NULL" in the query? I am writing a script to insert values from a file into a mysql database.  The script reads lines from the file and constructs insert queries from them.  Sometimes particular columns are empty,  so an insert query might look like this...

insert into table (col1, col2, col3, col4) values('abc',100,,'def')

where col3 allows nulls, and also has NULL as its default value.

This insert query throws an error,  while if I ran it in MS SQL it would work, and insert null where the insert query has nothing between the commas.

Is it possible to acheive this behaviour in mysql?

I tried doing a replace of ,, with ,NULL, but this fails when there are two empty values in a row - ,,, (I end up with ,NULL,,) and also adds extra processing (and therefore extra time taken) to the code.   If there is a way to allow insert queries like insert into table (col,col,col) values(val,,val) I could avoid having to replace nothing values with 'NULL' when constructing the insert query string.

Edit:

For convenience I include below the code I have written to work around this problem.  My code is in a windows script file (vbscript) (not tagged due to it not being directly relevant to the question being asked, but I'll tag it if it should be)

So here is code to replace the 'nothings' with NULL...

row = split(arfile.readline,"|")

for i = 0 to ubound(row)

    row(i) = rtrim(row(i))
    if row(i) = "" then row(i) = "NULL"

next

Then further down I have a line that combines them all into an insert query (actually it's more complex, it creates 100 insert rows and then executes the command and then creates another 100)

As part of the construction of the query, some values get single quotes added, because they are string or other types that need it.  For that reason, wherever there has been a NULL replacement it becomes 'NULL' so I need to also deal with this...

conn.execute( insertquerystart & replace(insertquery,"'NULL'","NULL") & ";")

No, it's not possible.

It would be interesting to see how your script looks like. Maybe you should consider writing the default value ('null') when writing the script? Or do you receive it as it is?


Updating a column in Table A from a Column in Table B during an after insert Trigger Am using MYSQL Workbench

I have a residence table with a sales tax rate and when a new residence record is inserted I want its sales_tax field updated from a tax rate_table. the 2 tables are connected by zip_code.

I am using WORKBENCH Table Triggers to create the code

I have tried several things but I get errors either on syntax or during a load

If I use "UPDATE" I get an error code 1442 which seems to indicate that "UPDATE" is not allowed.

Things I have tried, code generated by workbench

USE infrastructure$$

CREATE DEFINER = CURRENT_USER TRIGGER `infrastructure`.`Residence_AFTER_INSERT` AFTER
INSERT ON `Residence` FOR EACH ROW
BEGIN

select tr.tax_combined_rate as tax from sales_tax_rate as tr
where residence.res_zip = tr.zip_code;
set residence.res_sales_tax = tax;

END

Result ERROR: Error 1193: Unknown system variable 'res_sales_tax'

I then comment out the line “set residence.res_sales_tax = tax

Result ERROR: Error 1415: Not allowed to return a result set from a trigger

This error suggests that I can not get a value from another table from within a trigger

Can some one give me some guidance
Can this be done?

You need a before insert trigger ("after insert", the bird has flown, it's useless modifying the new vales - if MySQL allows you).  In the trigger body, use the following code:

Declare v_tax int;
select tr.tax_combined_rate into v_tax from sales_tax_rate as tr
  where NEW.res_zip = tr.zip_code;
set NEW.res_sales_tax = v_tax;

See the effect here: https://www.db-fiddle.com/f/eEiK6pKGEQEoYL9BPdfooP/0


How to Disable Windows Authentication in SQL Server I would like to say that as per my Organization requirement they want to Disable Windows Authentication in SQL Server.

My Environment is as follows:-

1) Microsoft Azure
2) Windows Server 2016
3) Microsoft SQL Server 2014 Standard
4) AD is in office 365 and also synchronize with Exchange Server

As I have gone through some SQL Server blog like How to Disable Windows Authentication in SQL Server but might be I have miss somewhere to disable windows authentication in SQL Server.

Any suggestion or help will be appreciated.

To stop Windows users from logging in, don't create logins for them in SQL Server.

By default, just because someone has a Windows login doesn't mean they have permissions in SQL Server. You have to use the CREATE LOGIN command to map Windows users to SQL Server permissions.

If you don't do that, they can't log in.

Your organization may also be asking for a list of people and groups who already have logins, and who need to be removed. In SQL Server Management Studio, go into Security, Logins as described in the documentation, and you can review the list of logins from there, and disable (or drop) logins who shouldn't have access.


How to free used space from disabled index? I've come across a need to free used space from a large number of disabled indexes.

Is it possible to make SQL Server release the used space from these indexes without a drop/create for each index?

And I'm limited to SQL-Server 2014 functionality :/

No. Script out the disabled and unused indexes then drop them.  Save the script of index create statements in your flavor of Source control just in case you need them.


Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity? Do any of the graph-based/graph-aware databases (Neo4j, ArangoDB, OrientDB, or other) have mechanisms for maintaining referential integrity on a par with those offered by relational databases?

I'm exploring various document-based databases to find a suitable engine to use for adding an auxiliary data storage to a certain project.

I discovered graph-based/multimodel databases and they seemed like a good idea, but I was surprised to find that they don't seem to offer the same level of protection of relations/links/edges that modern relational databases have.

In particular, I'm talking about linking deletions of entities/vertices with deletion of links/edges. In a relational database, I can have a foreign key constraint that links records from one table with records in another table, and will either

prevent deletion of record in table A if it's referenced by record in table B ("on delete no action"), or
delete the referencing record(s) in table B if a referenced record in table A is being deleted.

I expected to find a similar mechanics in graph-aware databases. For example, if a "comment" vertex links to a "post" vertex (forming a many-to-1 relation), then there are the following problems/challenges to solve:

Prevent deletion of a post while there are edges from comments to this post. This way, a comment could never have a dangling link/edge to a post. The solution would be: depending on the link/edge properties, either

prevent deletion of a post until all edges from comments to this post are deleted, or
delete all comments linking to this post when the post is being deleted.

Prevent deletion of an edge from a comment to a post without deleting the comment itself, to prevent the comment from not having a link/edge to a post at all.
Only allow creation of a comment if an edge is created to link this comment to a post at the same time.

Are mechanisms like this really lacking in graph-based databases, or was I just unable to find them?

I know that OrientDB has the "link" data type that probably solves the second and the third problem (if a link-typed property is declared mandatory and non-null, then it's impossible to create a record without specifying the link destination, and later it's impossible to break the link by un-setting the property).

However, as far as I remember, it's possible to delete the record which a link-typed property points to, thus producing a dangling link (so the first problem is not solved).

I also know that in certain databases I can use nested documents as an alternative to having multiple linked documents. However, this approach doesn't scale well (for cases when the number of linking records grows can grow indefinitely). Also, it is quite limited (it can't be used as an alternative when several links are needed, say, to a post and to a user; there are other important limitations, too).

Only allow creation of a comment if an edge is created to link this comment to a post at the same time.

This is supported by Dgraph Labs' GraphQL implementation out of the box. In your schema, you would add a exclamation mark ! to denote that an edge is required when added and when retrieved.
type Post {
  id: ID
  comments: [Comment]
}
type Comment {
  id: ID
  text: String!
  onPost: Post! @hasInverse(field: comments)
}

This schema above, would mandate that every Comment have a reference onPost edge and would return errors if you send mutations that did not match this requirement.
This would also error if you were to query a comment and request the onPost edge and it did not exist. How might that happen if the Comments cannot be added without the edge? Because the @hasInverse will do the bookkeeping to keep the Post.comments and Comment.onPost edges consistent. This allows you to delete a post node that could delete Comment.onPost edges.

Prevent deletion of a post while there are edges from comments to this post.
Prevent deletion of an edge from a comment to a post without deleting the comment itself

Right now in Dgraph's GraphQL implementation there is no way to enforce or prevent cascade deletes as there is with SQL-like databases. This would have to be managed from a middleware level. Having that said, Dgraph Labs has implemented a way to currently do this with the @custom directive and are currently developing (promised for 20.11 release) a @lambda directive to enable an almost native javascript middleware functionality. Combining either one of these methods with another currently awaiting release directive @generate. The generate directive will allow you to disable the default generated mutations and then you can create your own custom mutations to handle this business logic as needed with DQL upserts. These custom mutations can then be mapped and served via the GraphQL endpoint.


What datatypes can cause "String or binary data would be truncated"? What kind of datatypes can cause the "String or binary data would be truncated" error? Varchars and other strings are obvious, but what about the "binary data"? Can this include integers or datetimes? Or can this error only be caused by datatypes where you have to specify a length? I could not find any relevant documentation and Google has failed me as well.

I hardly expect Microsoft to reverse-document error messages and try to exhaustively iterate all of the ways in which they could be caused. This is something you will just need to discover through testing.

That said, most types have other validation that occur first, and so would never fail with that specific error message. Most numeric types, for example, have ranges beyond which they overflow, so they return an overflow error instead, and sometimes they're even helpful in identifying the "first" value that caused the problem. Some examples:

DECLARE @x TABLE(a TINYINT);
INSERT @x(a) VALUES(2560); -- 1 char too many

  Msg 220, Level 16, State 2, Line 3
  Arithmetic overflow error for data type tinyint, value = 2560.

DECLARE @x TABLE(a INT);
INSERT @x(a) VALUES(20000000000); -- 1 char too many

  Msg 8115, Level 16, State 2, Line 2
  Arithmetic overflow error converting expression to data type int.

DECLARE @x TABLE(a DECIMAL(5,2));
INSERT @x(a) VALUES(1000.99); -- 1 char too many

  Msg 8115, Level 16, State 8, Line 2
  Arithmetic overflow error converting numeric to data type numeric.

Datetime has similar validation that will fail on the conversion long before it cares about how many characters you actually passed (even if the part that would remain after truncation is valid):

DECLARE @x TABLE(a DATETIME);
INSERT @x(a) VALUES('2014-05-10 12:34:45612347343483434'); -- many chars too many

  Msg 241, Level 16, State 1, Line 2
  Conversion failed when converting date and/or time from character string.

The binary types do return the same unhelpful error message, e.g.:

DECLARE @x TABLE(a VARBINARY(2));
INSERT @x(a) VALUES(0x111122223333); -- 4 chars too many

You have to be careful though, because sometimes silent truncation happens. This is arguably much worse than getting a pesky error message, because you've actually lost data and it is not recoverable.

DECLARE @x VARBINARY(2) = 0x111122223333, @foo VARCHAR = 'bar';
SELECT @x, @foo;

Result:

0x1111    b

Notice that I didn't even declare a length for VARCHAR - in some cases the default is 1, in others it is 30. So even more reason to always match data type definitions exactly and always be explicit.

Did I not cover a type you're curious about? No problem: You can just try it.


MySQL - Alter table to automatically put in a UUID Ok, I know I've done this before.  But I cannot for the life of me figure it out.  I created a table.  One of the columns is labled "LogID", it is also the primary key.

How do I alter it so that this column generates a UUID for each new entry?

Thanks

Just create a trigger to run before insert to generate the UUID for the given column.

CREATE TRIGGER before_insert_mytable
  BEFORE INSERT ON mytable
  FOR EACH ROW
  SET new.LogID = uuid();


Migrating Oracle 9i database to MySql database on a different Windows Server I need to migrate my Oracle 9i database with all its tables from windows server 2003 to MySql on another Windows Server 2012. The oracle database is connected to Visual Studio web project. I have successfully imported the files (.aspx) to Microsoft Visual Studio 2013 Community edition.

(Mind that I need to keep original database intact for few weeks of testing peroid, simply the migration should be , creating a clone copy and not moving the database.)

I am facing problems as the visual studio project has code for connection with old oracle database. I need to recreate the functioning website.

The only thing remaining is migrating that old database to new server.
I tried SQLWays .  But It does that on single machine not from another server.

Please suggest solutions for migration between different servers.

Further, what needs to be changed in my vb code even if I migrated the database successfully to get it working with visual studio 2013 project.
(or may I ask a different question for that?)

As this answer on Stack Overflow indicates there is no built in functionality to do what you want but there are alternatives:

export your tables to csv files and then import into MySql using LOAD DATA INFILE. This solution does not scale well for large databases or schemas with clobs and blobs that are images or documents.  SQL Developer can help you recreate your schema in MySql.
roll your own solution with VB reading the data from Oracle and writing to MySql.  This takes time to code and test and is all throwaway code.
Commercial products:  Golden Gate works great but will cost you far more than you expect

The data access code in your application should, in general, work without change but details are everything. Consult the Oracle documentation for a comparison and you can see many differences and possible issues depending on what your schema has.


SQL Server 2014 Minimum Memory configuration - VMware I am taking over a SQL Server 2014 environment which has been built and configured by a consultant.

They have set a minimum value for memory which I usually set to 0.  According to the consultant and VMware documentation the minimum memory should be set in SQL Server when it is running on a virtual machine.  I always set the Maximum leaving memory available for the OS, but do not set the minimum memory.

Any suggestions/thoughts are greatly appreciated!

There is no clear best practice on this. It all depends on your VMWare configuration and failover requirements.

The idea behind this is the fact that it should be preventing SQL Server from releasing memory when the VMWare balloon driver thinks it needs this.

There are several possible problems with that. One of them being the fact that the balloon driver may be requesting memory because a physical host is failing and the VM needs to be moved elsewhere (or other VM's need to be moved to the host the SQL VM resides on).

In case of disaster, what do you prefer? A slow SQL Server or no SQL Server?

The balloon driver will just request memory from other processes on the server if SQL doesn't release it (SSRS, OS, whatever) and if the OS starts paging SQL will suffer anyway.

If you have this configuration because of you are overcommitting memory it might be a better option to reserve memory for the server in VMWare to make sure SQL actually has available what has been allocated.

This setting could be a good configuration, but setting it as a default seems wrong to me. You need to thoroughly understand what it does and what the effects on VMWare are to decide if it should be configured in your environment. Have a good chat with the datacenter/VMWare folks to understand how they have things set up too.


PostgreSQL: Check the structure of foreign tables How can I check if the structure of a foreign table was changed in the source database in order to know when to re import it. Foreign table was imported by foreign-data wrapper postgres_fdw (PostgreSQL 11.2).

How can I check if the structure of a foreign table was changed in the
  source database in order to know when to re import it. Foreign table
  was imported by foreign-data wrapper postgres_fdw.

As here and here if you’ve imported your schema, then you can query the foreign tables as if they were normal, local tables.

For further your ref postgres_fdw and IMPORT FOREIGN SCHEMA


Managing a SQL Server database with over one terabyte of data Recently we have an audit database that went over one terabyte and, since we have storage problems, management is looking for options.

My proposal is at the end of each year we take a backup and truncate all the tables which will keep the database manageable.

It will not be beneficial to have an archive database as it will again consume the same space.

I would like to have an expert opinion about the options that I can propose to the management that is either allocate more space or truncate the whole database every year.

Get more space.   If you have a business requirement to retain that much data, they have to come up with the money.
Turn on page compression for the biggest tables (and indexes) (test test test!), Log data compresses extremely well, but compressing that amount of data is going to take some time.
See point 1


VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have space to restore a copy on another server connected to the same storage.

I need to be able to run checkcatalog, checkalloc and checktable but even for small tables it doesn't appear to be able to create the internal sparse file dbcc snapshot.

So I was wondering if I can create a SQL database snapshot on another drive and then run those commands against that and then drop the snapshot db once complete.

Thanks!

--Todd

I think Robert Davis answered your question in two posts:

How to manually create a database snapshot and get SQL Server to use it for CHECKDB:

http://www.sqlsoldier.com/wp/sqlserver/day1of31daysofdisasterrecoverydoesdbccautomaticallyuseexistingsnapshot

Performance comparison of CHECKTABLE commands run against database vs manually created snapshot w/ different options:

http://www.sqlsoldier.com/wp/sqlserver/day15of31daysofdisasterrecoveryrunningdbccchecktableinparalleljobs


psql breaks if I specify host localhost I am aware that this or similar has been asked in other places. None of the other answers have worked for me.

Machine is a Macbook Pro 2014 / OS X Yosemite 10.10 with Postgres 9.3.5 installed via Homebrew.

I can only connect to my local postgres instance if I do NOT specify a host. This works:

psql -Umyuser -d mydb
sql (9.4.4)
Type "help" for help.

myuser=#

-h localhost does not:

psql -Umyuser -d mydb -h localhost
psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

It pauses for a long time before I get this message.

I am trying to set up a third party tool that seems to want to specify localhost, so it is not an option just to leave that off.

If I understand what I've read correctly elsewhere, postgres behaves differently if you're using TCP/IP, and specifying -h localhost forces that. Accordingly I have added  the following in pg_hba.conf:

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             localhost               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

(127.0.0.1 AND localhost seems redundant but desperation drives superstition) ... and in postgresql.conf:

listen_addresses = 'localhost'
port = 5432

netstat confirms that Postgres is listening to that port:

☁  ~  netstat -an | grep 5432
tcp4       0      0  127.0.0.1.5432         *.*                    LISTEN
tcp6       0      0  ::1.5432               *.*                    LISTEN
d8d7c0e31d5add1d stream 0 0 d8d7c0e326b18a1d 0 0 0 /tmp/.s.PGSQL.5432

All permissions are set on the socket file:

~ ls -l /tmp/.s.PGSQL.5432
srwxrwxrwx  1 fritz  wheel  0 12 Jul 13:27 /tmp/.s.PGSQL.5432

/etc/hosts has an entry for localhost:

127.0.0.1   localhost
::1         localhost

psql and pg_ctl are pointing to the same installations:

~ which psql
/usr/local/bin/psql
~ ll /usr/local/bin/psql
/usr/local/bin/psql -> ../Cellar/postgresql/9.4.4/bin/psql
~ which pg_ctl
/usr/local/bin/pg_ctl
~  ll /usr/local/bin/pg_ctl
/usr/local/bin/pg_ctl -> ../Cellar/postgresql/9.4.4/bin/pg_ctl

There's nothing named psql elsewhere on my system that seems like it could be hijacking this process:

~  sudo find / -name psql
/Applications/pgAdmin3.app/Contents/SharedSupport/psql
/Users/fritz/devtools/phantomjs/src/qt/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/plugins/sqldrivers/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/sql/drivers/psql
/usr/local/bin/psql
/usr/local/Cellar/postgresql/9.4.4/bin/psql

UPDATE: telnet connects to localhost 5432 for a while and then is disconnected:

~  telnet localhost 5432
Trying ::1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

UPDATE 2: I used wireshark to spy on tcp localhost port 5432. Trouble is, I don't know what I should see and whether/how the results are wrong. Hoping someone out there can make something of this capture file.

Thanks to the suggestion on pgsql-general that I check TCP/IP with netcat I discovered that the first few bytes sent to localhost were being swallowed by a process that was intended to keep an audit trail of Internet access. Uninstalled and everything works as I expect.


Creating trigger from one database table to another database table postgresql I have two database say db1 and db2. And two tables user_auth_table in db1 and user_table in db2. Now I want to trigger a function which sync up few values in both tables for each new row or on each new insertion in user_auth_table.

I have tried to write on my own and creating trigger in db1. But the error says user_table doesn't exist. Which I know why I am getting this error. Any suggestion to target the tables outside the db ?

CREATE TRIGGER sync_user_table AFTER INSERT ON user_auth_table for each row execute procedure syncAndMaintainUsersTable()

create or replace function syncAndMaintainUsersTable()
returns trigger as
$BODY$
begin
if NEW.user_uuid<>OLD.user_uuid then
    insert into user_table values (NEW.user_uuid, CURRENT_TIMESTAMP, NEW.provider);
end if;
return new;
end;
$BODY$
language plpgsql;

Usually the full qualified name of a db table is:
catalog.schema.table

If you want to insert some values into another table you should use this syntax:
insert into catalog.schema.table
values (..., ..., ...)


MySQL best way to define a couple of columns as both null or both not-null On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns are not null. Is there any specific constraint, or could I do it just via BEFORE INSERT / BEFORE UPDATE triggers?
In other words, if a column of a record is null and it is updated setting it with a value, also the other column of that record must be updated with a not-null value. If the column is not null already, I can update it with another value (optionally, the other one too), but if I set it as null, the other column too must be set as null by the update query (no "default" settings).
Thank you.

I should think a table-level CHECK constraint would serve here, something like
CONSTRAINT c1_c2_null_match
  CHECK ((c1 IS NULL AND c2 IS NULL)
     OR  (c1 IS NOT NULL AND c2 IS NOT NULL))

See the MySQL 8.0 documentation.
You could instead use a trigger, but can run into consistency issues that way. Certain bulk-load operations will bypass triggers, meaning that the check is never performed at all and allowing the condition to be violated. A constraint can be temporarily disabled, but the check will be performed when the constraint is re-enabled.
One note: You don't mention which version of MySQL you're using, but prior to 8.0.16 CHECK constraints were ignored, so you would need to use a trigger to do what you want.
To do this with a trigger you need to return an error if the fields don't match, something like this:
DELIMITER //
CREATE TRIGGER c1_c2_null_match BEFORE UPDATE ON tbl
  FOR EACH ROW
  BEGIN
    IF (NEW.c1 IS NULL AND NEW.c2 IS NOT NULL)
    OR (NEW.c1 IS NOT NULL AND NEW.c2 IS NULL)
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Null columns must match';
    END IF;
  END //
DELIMITER;

See this Stack Overflow question.
Hope that helps.


How to find the specific file in a filegroup in which an object resides Today, I encountered below error on one my SQL Server which I manage.

  Could not allocate space for object '%.*ls'%.*ls in database '%.*ls'
  because the '%.*ls' filegroup is full. Create disk space by deleting
  unneeded files, dropping objects in the filegroup, adding additional
  files to the filegroup, or setting autogrowth on for existing files in
  the filegroup.

It occurred during Index Reorg operation. Upon investigation, I found that my DB was split in 3 filegroups and had multiple files in them. All of them had at least one file with auto-growth enabled and disks hosting them had enough free space available. So then why my index maintenance failed? My research led me to this MS article https://msdn.microsoft.com/en-us/library/aa337441.aspx which says:

  When an index is located on several files, ALTER INDEX REORGANIZE can
  return error 1105 when one of the files is full. The reorganization
  process is blocked when the process tries to move rows to the full
  file. To work around this limitation perform an ALTER INDEX REBUILD
  instead of ALTER INDEX REORGANIZE or increase the file growth limit of
  any files that are full.

Ok. So the solution is to rebuild the index so it moves to the new file. But what if I want to continue having my index in same file so that my reorg can continue? Is that possible? Also, is it possible to find out in which file my DB object resides? Most of the blogs have scripts to find objects residing in filegroup but I want to find files in that filegroup so I can manually grow them and see if that fixes it.

All of them had at least one file with auto-growth enabled and disks hosting them had enough free space available. So then why my index maintenance failed?

As it says in the product documentation

Reorganize and Rebuild Indexes: (emphasis added)

The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. So although the filegroup might have free pages available, the user can still encounter error 1105...

But what if I want to continue having my index in same file so that my reorg can continue? Is that possible?

Yes, you just need to ensure that the necessary space exists beforehand, or that the file that needs to grow can do so.

Also, is it possible to find out in which file my DB object resides?

This is not as straightforward as one might expect. To get down to the file level accurately, you need to use the undocumented sys.dm_db_database_page_allocations (SQL Server 2012+) or DBCC IND.
A correct query to do this can be found on this Database Administrators Q & A:
Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?


Aggregating data - Getting data inserted after last time query was run On a SQL Server 2008 R2 DB, I'm running an aggregation query every hour or so, on data that's being constantly inserted into a table, and what I'd like to do is pull all the "new" records that were inserted, since the last time that query ran. An identity column can generally do this, since you can assume it's monotonically increasing, but according to this post (https://stackoverflow.com/questions/2828936/does-sql-server-guarantee-sequential-inserting-of-an-identity-column), you're not necessarily guaranteed that when you do the pull, that you've pulled every record before the record with the greatest id.

Question is, how can I do this? A timestamp with default value seems reasonable, but also seems to fail in my mind for similar reasons. Why? Well if the default value of a time column is [CurrentTime], then could it not happen that with parallel inserts, insert #1 has a greater timestamp than insert #2, if insert #1 is slightly delayed? I guess it really depends on when the time is pulled vs when the row is inserted: something which I have no insight into.

The only solution I can fathom that will avoid this issue is if I pull all records from [LastTimeJobRan] - [SomeConstantOffset] up to [CurrentTime] - [SomeConstantOffset]. In other words, assume I run the job at 1:05, I would then pull all records from 12:00 - 1:00, hoping that 5 minutes would be enough of a window to avoid any "stalls" from inserting (obviously a few seconds would suffice here methinks). From a high level, I'm basically staggering/delaying processing of the latest records that are currently being inserted.

The other solution I'm thinking of involves a combination of storing the time of the last run, and marking records as being "processed". The last time run (offset by a bit as well perhaps) is just an optimization to avoid pulling a lot of old rows that have already been marked.

Am I overthinking this problem? Are there guarantees that the DB makes that obviate this entire line of thought?

I'm sure this is a common enough problem, but I couldn't find much relating to this besides the above SO post. Perhaps I'm using the wrong keywords.

This is a classic senario that we experienced with out datawarehouse. We already had timestamp column, but still to process new records from the last time the query ran was difficult just like you are currently facing.

What we did was - we added 3 columns - LATEST, END_OF_DAY and PROCESSED columns to our main table where the data was loaded for further processing.

LATEST = All the data that gets loaded is defaulted to 1. Meaning that the data is latest.

PROCESSED = Will be set to 1 for the data that is already read and processed by our ETL job. The new data will be having a default of 0.

END_OF_DAY = This is another flag that is useful just in-case if you want to process all the records for the entire day. Just our requirement. Your case might be different.

Note: Above is just a simple example of how a sort of similar issue like you mentioned was addresses. Obviously there were many factors and business requirements that lead us to introduce such extra fields.

Also, you can use Calendar Table and Date/Time Functions to supplement what I mentioned above.

On the top of above, I would suggest to look into Data Loading Performance Guide to get benefit when the data is loaded and the Isolation level (especially RCSI) of the database as if the data is being continuously loaded then there would be blocking issues. Test it, balance it and then implement it !!


Unable to connect to oracle database. Getting ORA12505 error I am newbie to ORCALE

I am trying to connect to oracle server and i am getting this error. This is was working fine before but now i am facing this issue. Need help

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505

this is my listener.ora file

SID_LIST_LISTNITBSCAT =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ITBSCAT)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = ITBSCAT)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

LISTNITBSCAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1522))
  )

ADR_BASE_LISTNITBSCAT = /u01/app/oracle

This is my tnsnames.ora file which is my env

(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ITBSCAT)
    )
  )

DMSORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.2.89)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ARCHDBGG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.5.16)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = archdb)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-ORACLE-11G-RHEL6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Listener status:

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-OCT-2018 18:26:36
Uptime                    0 days 2 hr. 8 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DB-ORACLE-11G-RHEL6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB-ORACLE-11G-RHEL6)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@DB-ORACLE-11G-RHEL6 admin]$

TNSPING ORCL

[oracle@DB-ORACLE-11G-RHEL6 admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on
11-OCT-2018 21:32:30

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
DB-ORACLE-11G-RHEL6)(PORT = 1521))) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec)
[oracle@DB-ORACLE-11G-RHEL6 admin]$

This: The listener supports no services.

Your database is not started, or it is registered into a different listener.

Assuming you have the simplest default configuration, just start your database with oracle user:

$ . oraenv
ORACLE_SID = [oracle] ? orcl
...
$ sqlplus / as sysdba
SQL> startup

Do not forget case sensitivity when providing the value for ORACLE_SID.


SQL Server Difference between CPU and CPU_TIME In MS SQL Server 2014, what is the difference between sys.dm_exec_requests.cpu_time and master.dbo.sysprocesses.cpu? I see that sometimes the two values are the same and other times the value are different. When should the values be different?

The following query retrieves both values.

SELECT
      req.cpu_time
    , sp.cpu
FROM sys.dm_exec_requests req
JOIN master.dbo.sysprocesses sp ON req.session_id = sp.spid

Here are examples from my server.

Here's what I can tell you.

Internally, they use two different ways obtaining the data; sysprocesses uses the "legacy" method and sys.dm_exec_requests uses a newer method. Unless you have a very pedestrian workload these will generally not be the same.

Overall, I must agree with Aaron and not use sysprocesses as it is deprecated. Having said that, either method will get you "close enough" to troubleshoot issues and there shouldn't be such a difference that it hides or masks an issue from your troubleshooting efforts.

Note, this was tagged as SQL2014 and thus this answer should be considered relevant only for SQL Server 2014 specifically build 12.00.5538 (SP2 CU3).


SSDT Publish failing with an error without even running any statements My publish attempts are failing with this error

  Msg 205, Level 16, State 1, Line 7572 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Taking the old-school advice to print status messages between deployment script execution, I have narrowed it down to this:

The line number mismatches because of SQL Server quirks, which is why you have to PRINT stuff like a heathen (see the link above)

Note how there were literally no statements executed. I also had a verbose trace running which confirms that nothing was executed:

How could the control flow statements between two PRINT statements possibly cause an error?!

Turns out the GO statement segregated the successful code from the remainder, which indeed contains an erroneous statement (as per the error message), and so the error was thrown during syntax validation.

By taking the line number of the GO statement and adding the line number shown in the error message (which is really the offset in the batch), I found a bad MERGE statement, since double-clicking the error message in the SSDT publish results takes me to a random spot in the publish script.

So it was caused by statement batching, with syntax validation finally running on a deployment script (that is excluded from building with the SQL project).


How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute? I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS.

If i have installed fresh database and I'm doing first entry in it using CMS: I would like to know which tables got updated/appended in last 1 min in that DB.

It is somewhat similar to this question https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated

But in my case I dont know which tables to check. I can check each and every table in the db using the solution posted in the question but I have a gut feeling that there is a better solution for this.

There are two distinct answers I have to give by Storage Engine

MyISAM

For all MyISAM tables, checking the last time a table was updated is quick and dirty

SET @IntervalMinutes = 2;
SET @IntervalSeconds = @IntervalMinutes * 60;
SET @DBtoCheck = DATABASE();
SELECT table_schema,table_name,update_time
FROM information_schema.tables WHERE engine='MyISAM'
AND table_schema = @DBtoCheck
AND update_date > (NOW() - INTERVAL @IntervalSeconds SECOND)
ORDER BY update_time DESC A;

InnoDB

I have some bad news: you cannot trust the INFORMATION_SCHEMA database because

Transaction Behavior of InnoDB
Location of the Data and Index Pages
Heavy duty locking of the INFORMATION_SCHEMA when reading about an InnoDB table

In order to know the last update by some timestamp, you must have innodb_file_per_table enabled. If not, all the data and index pages will sit inside ibdata1 (system tablespace). It would be anybody's guess when a table experienced an update.

You should first cleanup the InnoDB Infrastructure.

Then, the easiest way to get the timestamp is to ask the OS for it

EXAMPLE

Given this scenario

datadir is /var/lib/mysql
you want timetamp of mydb.mytable

Simply run this in the OS

TS_TAB=`ls -l --time-style="+%s" /var/lib/mysql/mydb/mytable.ibd | awk '{print $6}'`
TS_NOW=`date +%s`
(( TS_DIFF = TS_NOW - TS_TAB ))

TS_DIFF will be the number of seconds elapsed since the last time a write was posted to that table. That write can be anything (INSERT, UPDATE, DELETE, DDL).

That's as fast as I can get it for InnoDB

Give it a Try !!!


How to get a surrogate key value in order to put a foreign key in and create relationship? I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the foreign keys, since it has no meaning, being a surrogate key.

Here are my tables :

  Quote (quoteID PRIMARY KEY, quoteValue, quoteDate, currency)

  TermStrucureComponent (TSComponentID PRIMARY KEY, type)

  InterestRate (rateID PRIMARY KEY, quote FOREIGN KEY to quoteID, TermStructureComponent FOREIGN KEY to TSComponentID, timeToMaturity)

So I have my InterestRate table that has two relationships: one with Quote, and one with TermStructureComponent. After inserting rows in the two first tables (without any foreign key), I need to insert it in InterestRate. But I don't know how to fill in the quote and TermStructureComponent fields, because I don't know their value in the other tables, since they are surrogate keys.

How should I do that? Thank you for your help

Assuming your surrogate keys are auto-incrementing integers marked as primary keys they will be the "rowid" for that table so you can read the last value added for a single insert using SELECT last_insert_rowid(). This operates like scope_identity in other databases. You would need to read each one after each row insert as each will replace record of the previous one.

sqlite doesn't seem to have an equivalent to the OUTPUT clause found in SQL Server and the RETURNING clause in Postgres & Oracle, so I don't know of a way to read multiple generated ID meaning that you'll have to do this one row at a time.


Which DBMS is good for super-fast reads and a simple data structure? I am developing a product that, as a part of it's operation, must track a large number of files/directories. The idea is to store stat information in a database then, on boot, create watches for each file. Files that change will be queued (in the database) for a group sync to a remote database. They will be synced in order of priority, a number between 1-10.

Information about database:

< 100,000 entries of stat info
Entire database read at boot, only the file path is necessary
Queued files will have a priority field (nothing else needs be searched by)
Insertions can be slow

I've found a couple databases that I think will work, but I'm not sure which would be the best:

Redis- store file-path as key, stat data as value; queue would be a list
MongoDB- more query options than Redis, but still fast

I'm thinking a NoSQL database would be the best solution here, as there isn't too much relational logic going on, and the total data size isn't too large (something like < 100 mb, closer to < 30 mb).  I did look at SQLite because it seems to be simple enough to embed in an installable application.

Since this is a distributed application for end-users and not a high-load server, the database doesn't have to support many simultaneous users. The main priority here is to find a database whose model makes the most sense.

So the question, which database would be most applicable for this situation?

Also, are there any other databases that would make more sense for an application like this?

If you are not that concerned with relational logic, want really fast read speed, and you are willing to work with an RDBMS, I would prejudicially venture to say MySQL. Why ???

The MyISAM storage engine has an option that can allow the physical structure of the table to be augmented for better performance. What is that option ? The ALTER TABLE option ROW_FORMAT.

For example, the book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.

What if you already have a MyISAM table that is populated with data ? You could get metrics for recommended column definitions based on the data present in the MyISAM table. What query presents those metrics ?

SELECT * FROM tblname PROCEDURE ANALYSE();

PROCEDURE ANALYSE() This will not display data. It will read the value of every column and recommend column definitions. Example, if you have a type column whose values are 1-4, it would sugggest using an ENUM of those 4 values. You could then choose to use TINYINT or CHAR(1) since they take the same amount of space (1 byte).

Here is something else to consider: Since you were thinking about using a NoSQL DB, have you ever thought of using MyISAM in a NoSQL manner ? This is quite possible. Page 175 of the same book I mentioned suggests using HANDLER structures to read a table without the relational baggage. In fact, page 175 gives this example:

CREATE TABLE customer_mileage_details
(
    customer_id INT NOT NULL,
    ff_number CHAR(10) NOT NULL,
    transaction_date DATE NOT NULL,
    mileage SMALLINT NOT NULL,
    INSERT(customer_id),
    INSERT (ff_number,transaction_date)
) ENGINE = MYISAM;

This table contains millions of rows. Suppose that you need to create a data analysis apllication that has the following requirements:

It needs to retrieve blocks of information as quickly as possible.
Based on user input or other factors, it will likely "jump around" in the table.
It is not concerned with concurrency or other data integrity issues.
Cross-application table locking is not required.

These commands allow quick-and-dirty reads from the table:

HANDLER customer_mileage_details OPEN;
HANDLER customer_mileage_details READ ff_number FIRST WHERE ff_number=('aaetm-4441');
HANDLER customer_mileage_details READ NEXT LIMT 10;
HANDLER customer_mileage_details CLOSE;

I hope this give food for thought. Please look into it.

CAVEAT

What is very ironic about about me writing this particular post is that I wrote an earlier post about HANDLER being used in Percona Server binaries and thinking that using it was out-of-date. Since that older post, I have never thought I would ever write something in support of HANDLER structures. I now stand corrected.


FBI on function with concatenated string I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")

When trying to search on this index, I wind up with a full table scan. Any idea why? And if this is just completely broken like I think it is, would you suggest a column index on these four columns?

EDIT

Sorry about not providing the query. Yes, the query is a like, and the columns are all nullable. So I have

select *
  from person p
  where UPPER("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")
    like replace(upper('<search string here>'), '*', '%') || '%'

Any thoughts? Thanks for the quick answer.

Are the columns nullable ? is the query a LIKE ? Is there an NLS issue ?

I'd expect a

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") = :bind

to use a index range scan

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") LIKE :bind

may use a index fast full scan or a table scan depending on whether columns from the table were likely to be required. If it thinks 1 in 5 rows will match and that for each of those it needs a column not in the index, then it would be slower to use the index+table lookup than a straight table scan.

It could be the table is very small and it isn't worth using the index.


Show application name instead of process in warning (PostgreSQL) Sometimes we get a warning like this:

2019-... [15138] [Xc0BxiGn2g7j] LOG:  process 15138 still waiting for ShareLock on transaction 274489730 after 1000.107 ms
2019-... [15138] [Xc0BxiGn2g7j] DETAIL:  Process holding the lock: 14625. Wait queue: 15138.
2019-... [15138] [Xc0BxiGn2g7j] CONTEXT:  while deleting tuple (1,32) in relation "foobar"
2019-... [15138] [Xc0BxiGn2g7j] STATEMENT:  DELETE FROM "foobar" WHERE "foobar"."id" IN (2216750)

Unfortuantely the process id (in this case 14625) does not help much.

We set the application_name for all connections to PostgreSQL to a unique request-id.

Is there a way to let the above message print out the application name of the process which holds the connection?

Then it would make it much easier to investigate the root cause.

I updated the question, to show that the request-id of the blocked process is already visible to me. I am searching the request-id of process 14625.

Just include %p and %a in your log_line_prefix and set log_disconnections = on.

Then you get the application name and process ID logged whenever a session disconnects. That will enable you to find in the log which application name belongs to which session. This will work unless sessions are in the habit of changing their application_name.

For active connections, just examine pg_stat_activity.


Item not automatically selected in intellisense dropdown I have intellisense enabled in SSMS. When i start typing, I do see the intellisense dropdown with list of options but the option that matches what i am typing is not automatically selected. I have to use down arrowkey to select the item and then hit enter to auotcomplete. Is there any setting in SSMS to make intellisense behave the way we have it in Visual Studio?

Thanks

Do you mean "Toggle Completion Mode", on the Edit->Intellisense menu?


Create login with SID as string I'm trying to create a login as follows:

CREATE LOGIN [Test] WITH PASSWORD='Test123', SID=SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513')

But no matter what it looks like SID only accepts a binary input, no subqueries or anything else really.

Incorrect syntax near 'SID'.

Is there a way to create a login with SID with a string?

A funny thing about SID_BINARY() - it outputs binary(28) in this case, whereas CREATE LOGIN expects binary(16). As Solomon points out, and adding here so there is no ambiguity, this means there is no way to create a SQL Server SID based on - or that can map to - the Windows identifier you posted in your question. You want CREATE LOGIN ... FROM Windows for that.

Your first problem, though, is the syntax error. The reason you're getting that is because you can't use expressions in a CREATE LOGIN statement, and it doesn't take variables either, so you need to construct dynamic SQL.

I tried to use all 28 bytes as follows:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [Test] WITH PASSWORD = N''foo'',
  SID = ' + CONVERT(varchar(64), SID_BINARY(@sid_in), 1) + N';';

PRINT @sql;

Yielded:

CREATE LOGIN [Test] WITH PASSWORD = N'foo',
  SID = 0x01050000000000051500000027035A185996571BAD3724B801020000;

Of course, executing that SQL yielded an error, as I expected:

  Msg 15419, Level 16, State 1
  Supplied parameter sid should be binary(16).

So then I wondered if truncating to 16 bytes would work. I thought this was incompatible because losing 12 bytes should be a problem:

DECLARE @sid_in varchar(100), @sid_out varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

SELECT original = SID_BINARY(@sid_in);
SELECT trimmed  = CONVERT(binary(16), SID_BINARY(@sid_in));

Results:

original
----------------------------------------------------------
0x01050000000000051500000027035A185996571BAD3724B801020000

trimmed
0x01050000000000051500000027035A18

But then I tried it, and it (kind of) worked. Here is an example:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [Test] WITH PASSWORD = N''foo'',
  SID = ' + CONVERT(varchar(64), CONVERT(binary(16),SID_BINARY(@sid_in)), 1) + N';';

EXEC sys.sp_executesql @sql;

And to prove that trimming those 12 bytes off has little effect:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

SELECT SUSER_SNAME(CONVERT(binary(16),SID_BINARY(@sid_in)));

Results:

----
Test

This just means that you can write your code in such a way that you can still get to the right login given the Windows S-1... identifier. But there is a side effect of this trimming that may or may not be important to you. It is impossible to get back to the original SID you passed in because some of that data is gone. So if you create the functions @eckes points out above, you will find slightly different output than you would expect:

SELECT name, sid, ws1 = dbo.fn_SIDToString(sid)
  FROM sys.server_principals
  WHERE name = N'Test';

Results:

name    sid                                   ws1
----    ----------------------------------    ------------------
Test    0x01050000000000051500000027035A18    S-1-5-21-408552231

That may or may not be acceptable. I do not know enough about Windows identifiers or about exactly why you want to create a SQL auth user that is appears on first glance to be mapped to one to know if this will meet your needs. But it gets past both the syntax error and the 28/16 issue.

Function I used, included here in case the link dies:

CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END


SSMS login by external developers This is my first question so please bear with me !

We have an external company whose developers need to login into an instance on our on-premise SQL Server 2016.

They have AD logins, and these are in a distribution group that has access to the relevant instance.

I've created a Self Certificate, installed it to SQL server and encrypted the connection using it, and even installed it on my laptop (which is off our Domain).

To test the situation I use a local laptop login, VPN to our network, and run SSMS using 'runas /netonly /user.....' with the user being a domain user having the same rights as the remote developers.

When I try to connect I get

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

even though the certificate is trusted.

If I tick Trust Server Certificate in the SSMS connection options I get "Login failed for user ''"

I'm thinking we need to buy a Certificate - but if anyone has come across a similar situation with a different resolution please let me know !

Thanks.

I've created a Self Certificate, installed it to SQL server and
  encrypted the connection using it, and even installed it on my laptop
  (which is off our Domain).

By "Self Certificate" do you mean a self-signed certificate created on the machine or a certificate created by your private Certificate Authority on your domain (probably domain controller)?

I'm assuming you mean a certificate issued by your internal CA, so you need to make sure you install the public key for your CA into the Trusted Root Certification Authorities store on your laptop so that the certificate identity can be validated by the third party (your internal CA). In a domain-joined machine, this is done automatically, but as your laptop is not domain-joined it may not have the internal CA as a trusted CA.

Also, since you're running SSMS as a domain user, ensure the SQL Server certificate is installed in the local computer certificate store and not the current user store, otherwise the runas user won't see the SQL Server certificate.

Lastly, validate the certificate meets the requirements defined by Microsoft here, and make sure that you've configured SQL Server to force encryption in SQL Server Configuration Manager.

Once you've done this, connect to SQL from either the machine or a workstation that is domain-joined and validate that your connection is encrypted:

SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

This should return true if encrypted or false if unencrypted. Once you've confirmed encryption works from domain-joined machines, test from your non-domain laptop.


What does "volume" mean in MAX_IOPS_PER_VOLUME? When setting MAX_IOPS_PER_VOLUME in a Resource Pool, what exactly does "volume" mean?  Specifically, how many "volumes" would be the following cases:

Locally attached disk that's split into two partitions E: and F:
Software RAID 1 set E: composed of 2 locally attached disks (yes I know software RAID is bad-- adding this case to help me understand SQL Server's definition of "volume", not to design a production setup!)
Hardware RAID 1 set E: composed of 2 locally-attached disks
SAN disk E: on who knows/who cares how many disks.
1 SQL Server filegroup spread across two locally attached disks E: and F:

I assume the answers to #3 and #4 are "1 volume" and #5 is "2 volumes" but it's #1 and #2 that I'm most curious about.

The specific reason I'm asking is wondering if it's possible to increase the Resource Governor's IOPS limit for locally-attached SSD tempdb while having a lower limit for our SAN data storage.

So I'm wondering if splitting a single physical disk into multiple partitions might be a way to do this, by putting separate tempdb files on each partition so the total tempdb If #1 above makes SQL Server treat one physical disks as multiple volumes for throttling purposes, this may be an option. I'm assuming that this won't work-- that SQL Server is smart enough to know that 2 partitions is one "volume". But was worth asking.

A "volume" is either a drive letter, or a raw partition in SQL Server.

For a resource pool configured for X maximum IOps per volume where a query uses tempdb, splitting that tempdb across multiple volumes on a single physical disk, SSD or not, with the data on a single volume may well give you the granularity you desire.  As always, I'd test it.


pg_dump and ERROR: missing chunk number 0 for toast value I'm using PostgreSQL 8.4.15. While running pg_dump to backup a database, I got the following error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 123456789 in pg_toast_987654321
pg_dump: The command was: COPY public.my_table (id, .... all the columns ...)

When searching for this error message, I found a couple of references (here and here) that suggested to reindex the table. (In these discussions, there was a reference to querying the pg_class table to find the right pg_toast_XXXXXX value, but it seemed that it was because it wasn't displayed in their error messages. I skipped this part because I had a value displayed in the error message. I guess it might be a convenience due to a later version of PostgreSQL.)

I ran the following:

REINDEX table pg_toast.pg_toast_987654321;
VACUUM ANALYZE my_table;

I'm now able to use pg_dump without errors.

What's pg_toast and what did these commands actually do? Are these merely about simple cleanup or could they have got rid of some rows in that table? What could have caused the problem in the first place?

There are about 300000 rows in this table, but I would expect there to be only about 250 new rows since the previous successful backup (this table is only used for INSERT/SELECT, no UPDATEs).

Given that what you did was a reindex, what probably happened was it used an index scan to try to locate the toasted values in the table and couldnt find one.  This sounds like a corrupted index.  Vacuum analyse does alter the table but reindex does not and the changes are very minor.

The way to think about this is that TOASTed attributes are actually broken into chunks of about 4k in size and these are stored in rows.    They are looked up and sorted/reconnected with the main row at query time.  It sounds like an index used here was corrupted and so the reindex solved the problem.

I have found corrupted indexes are usually a sign that something is not well with the server.  It is good to check and make sure memory, CPU's and hard drives are all happy and not reporting problems.  I have found overheating servers to be particularly prone to index corruption and if indexes can get corrupt one has to worry about data becoming corrupt too.


Backup data base in oracle 12C We have 12c database. we had create a pdb. Now i want to take a backup through
RMAN. Could you please share the steps to configure this backup.

I know to configure backup in 11g..For 12c Please share the steps.

thank you,

Backing Up PDBs with RMAN

  To back up one or more PDBs while connected to the root:

  Start RMAN and connect to the root as a common user with the SYSBACKUP
  or SYSDBA privilege as described in Connecting as Target to the Root.
  Issue a BACKUP PLUGGABLE DATABASE command at the RMAN prompt.

  The following example backs up the PDBs sales and hr:

  BACKUP PLUGGABLE DATABASE sales, hr;

  To back up one PDB while connected to the PDB:

  Start RMAN and connect to the PDB as a local user with the SYSBACKUP
  or SYSDBA privilege as described in Connecting as Target to a PDB.
  Issue a BACKUP DATABASE command at the RMAN prompt.

  BACKUP DATABASE;


what is the best practice for tempdb autogrowth? I have a client's server with about 15 databases on this instance with a data total of about 100 GB. One of the databases is for a sysaid app.
his temp db files are one mdf one ndf and of course an ldf.
the mdf is on the same drive as the other mdfs and so if the ldf file.

When I set the autogrowth properties I know I should keep it as unrestricted growth but when I need to choose between autogrowth by % or MB what to choose? and how do I know what number to configure?

Thanks in advance!

For me, the best practice is to just size the files large enough, at the outset, to accommodate the growths you expect.

Let's use some simple numbers. Say you have a 100 GB drive that you've allocated to tempdb (or 100 GB of some shared drive). Your instance has 4 tempdb files, and you've decided to set them initially at 100 MB and to grow 10 MB at a time. Initially, that 100 GB drive or slice looks like this (not to scale, obviously):

As your system uses tempdb resources, each tempdb file will grow, 10 MB by agonizing 10 MB. After a few growths:

Note that each growth event, while likely fast-ish, forces all connections to wait. This is more pronounced when Instant File Initialization is not enabled, and if the files are configured to grow together (which requires a trace flag in some versions, but this is the right thing to do, because you don't want any single file to be bigger than the others).

When you get to the end of the drive, all 4 files will fail to grow at the same time. And this is a business continuity event. When tempdb can't grow here, activity halts:

Now, if you're getting to this point, ever, you need to acknowledge that either you need to change the way you use tempdb, or 100 GB isn't enough and you need to allocate more space. But I would argue that it's better to simply start here and configure this size for your tempdb initial data file sizes:

Well, technically, I would size them at, say, 20 GB each, with a 500 MB autogrowth. This allows you to still be warned well in advance of filling up the 100GB slice, but without paying for all of those incremental file growths in the meantime.

After all, if tempdb is going to grow to fill that space, it's not like you can use it for anything else.

  which size should i give the autogrowth

There is no magic number (the defaults have been traditionally way too conservative for practical use, but this is getting better). Your autogrowth setting will be based on the performance characteristics of your disk, and how often you expect to grow. If you have SSD or similar, and have IFI enabled, size is probably much more tied to how much warning you want to have before hitting the end of the slice. If you have spinny, mechanical drives, and/or don't have IFI enabled, you will probably want to test various settings and see where the time it takes to grow the file by different amounts meets your tolerance for all transactions on the system to wait.


XML load fails on postgresql 9.1, but not in 9.3, any workaround? I have a problem loading an xml on an xml field of a table.
The load is done by ruby code, but this is not the point.
Everything worked well for years, but xml is increasing size....

So one day, today, xml is near 12 megabytes. Sysadmin called dba (me): the service is not working!

I did some test. Result is: when I test this query:

SELECT * FROM XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('xmlstuff.xml'), 'UTF8'))

It works fine on Postgresql 9.3
It gives me this in 9.1 in production or test:

ERROR:  invalid XML document
DETAIL:  Entity: line 1: parser error : internal error

Entity: line 1: parser error : Extra content at the end of the document

Any workaround for this? I'm planning an upgrade in the next months, but I have the problem now :-(

You hint us about the XML size but it may well not have anything to do with your problem.

If it worked fine for years, I would suspect something has changed in the XML format.

If the XML file appears to be valid (since it works in 9.3), you may be hitting a bug with previous versions of POSTGRES.

Try comparing 2 XMLs, before and after it started failing.

Apparently POSTGRES wasn't working well with XML files having DOCTYPE elements in it. This is only an example (although that could be it)

So while the XML file may be technically good, a change in its format may have place you in the way of a bug in versions prior to 9.3.

For your test, try an older XML file and a new one on the 9.1 version. If one works, you know it's a problem in the file format. If not, you at least know it's not the XML format.

You may also want to verify if there was any patching of the OS done. Postgres uses libxml2 to parse XMLs. if that has changed, this could be it and a downgrade could solve your problem.


Newdatabase option is not available in sql server management studio 2014 I connect to analysis server using windows authentication. But I can't create the new database.

For some reason, SSMS doesn't provide this New Database menu option for SSAS Tabular. But you can easily create a new empty or shell database with the following XMLA script. Just click the XMLA button in the toolbar and then paste in the following. Edit the ID and Name property to the desired database name. And edit the DOMAIN\GroupName to set the group that has admin permission. This will allow members of that group to deploy over this database without being SSAS admins for the whole instance.

The following script is for SSAS 2012 SP1 or 2014 Tabular models:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
        <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
            <ID>ShellDatabase</ID>
            <Name>ShellDatabase</Name>
            <ddl200:CompatibilityLevel>1103</ddl200:CompatibilityLevel>
            <ddl200_200:StorageEngineUsed>InMemory</ddl200_200:StorageEngineUsed>
            <Language>1033</Language>
            <Collation>Latin1_General_CI_AS</Collation>
            <DataSourceImpersonationInfo>
                <ImpersonationMode>Default</ImpersonationMode>
            </DataSourceImpersonationInfo>
            <Roles>
                <Role>
                    <ID>Role</ID>
                    <Name>Administrators</Name>
                    <Members>
                        <Member>
                            <Name>DOMAIN\GroupName</Name>
                        </Member>
                    </Members>
                </Role>
            </Roles>
            <DatabasePermissions>
                <DatabasePermission>
                    <ID>DatabasePermission</ID>
                    <Name>DatabasePermission</Name>
                    <RoleID>Role</RoleID>
                    <Process>true</Process>
                    <Read>Allowed</Read>
                    <Administer>true</Administer>
                </DatabasePermission>
            </DatabasePermissions>
        </Database>
    </ObjectDefinition>
</Create>

And for SQL 2016 Tabular models using the latest 1200 compatibility level, use the following script. Just change the name to the desired database name and the DOMAIN\GroupName to your administrators group.

{
  "create": {
    "database": {
      "name": "ShellDatabase",
      "compatibilityLevel": 1200,
      "model": {
        "culture": "en-US",
        "roles": [
          {
            "name": "Admins",
            "modelPermission": "administrator",
            "members": [
              {
                "memberName": "DOMAIN\\GroupName"
              }
            ]
          }
        ]
      }
    }
  }
}


SQL Server Alias won't work for my database instance I have setup a SQL Server 2019 instance on my machine which has a computer name in a format like this:

AB-ABC1234ABAB

My SQL instance is named 'SQL2019', therefore I connect to my local SQL instance by supplying the following name:

AB-ABC1234ABAB\SQL2019

I have scripts which rely on server aliases, e.g.

Alias1
Alias2

To set these up, I navigated to the 'Sql Server Configuration Manager' and performed the following steps:

Navigate to 'Protocols for SQL2019' and enabled 'TCP/IP'
Navigated to the 'Aliases' for 'SQL Native Client 11.0 Configuration (32bit)'
Created the alias 'Alias1' pointing to my server name: AB-ABC1234ABAB\SQL2019 and protocol 'tcp'
Repeated above step for alias 2
Repeated above two steps for the 'SQL Native Client 11.0 Configuration' also.
Opened Windows Services and restarted the 'SQL Server (SQL2019)' service.

In SQL Server Management Studio, I have connected to my instance using Windows authentication, then I have created a new login and selected 'SQL Server authentication'. I have then been tested that login by logging in by specifying the computer name as the server (AB-ABC1234ABAB\SQL2019). Then I have disconnected and typed the alias name (e.g. Alias1) in the login box, selected SQL Server authentication and supplied the same login I used earlier.
When I do this, the connection attempt times out. It appears neither alias works for me.
Error I get is the standard one, e.g. "provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)".
What steps can I take to troubleshoot this issue? Could the hyphen in the server name be cause for concern?
I noticed that if I delete the SQL alias I get a slightly different error: "error: 40 - Could not open a connection to SQL Server - the network path was not found."

The problem was that the 'SQL Server Browser' service was not running.

I right-clicked this, clicked 'Properties', set the start mode to 'Automatic' then manually selected to start the service.


What will be the best suited datatype to store a text file in database I want to store a text file containing logs in a database(SQL Server). That file size can vary from 1 MB to 10 MB. what will best suited data type to store it in to the database. Will it be BLOB or FILESTREAM or any other type?

If your files are going to be >1MB, then FILESTREAM is your best option. You can look at BOL documentation here, which clearly describes and advises you when is the best situation to use it.


Change SQL Server backup destination while retaining server's knowledge of location I currently have a number of SQL Server (2008 R2) databases that are receiving tlog and differential backups.

The backups are currently being stored on a NAS that is going to be repurposed. I need to start sending the backups to a new NAS that has a different path from the current NAS.

What I would like to do is forklift all of the current files from the current NAS to the new NAS, and reconfigure the current backup jobs to begin saving to the new NAS.

I know this won't break the restore chain, but I'm concerned this move will break SQL Server's awareness of where to find backup files in the backup chain. (It will try to find old backup files in in the old NAS location, not the new NAS location.)

Can this be accomplished by updating the records in the backupmediafamily table alone, or is there more to it than this?

If your retention period for backups is 1 month or something relatively limited, I would suggest the following approach.

Retain the OLD NAS until the expiry of your retention period for those backups.
Create your directory structure on the NEW NAS and redirect your backups there.
Once your retention period on the OLD NAS expires, then drop use of the OLD NAS.

As you delete old backups the SQL Server will still have the OLD NAS path on the backups that went there.  So it will progressively delete those files without any further action from you.

I generally recommend against updating system tables.

When we have to move files, we just move them and cope with an extra script to handle the moved files until they are purged.  If you have a week or two on NEW NAS the likelihood of needing the older backups is small.


Index Defrags during a Full Database Backup What are the performance drawbacks when running an Index Defrag during a Full Database Backup? Is there a way to pause/stop an Index Defrag when a backup starts?

I utilize Reorganize when frag is between 10 and 30%, I utilize Rebuild when frag is greater than 30%.

What are the performance drawbacks when running an Index Defrag during a Full Database Backup?

Any database operation will generate extra log that the full backup needs to capture (the Full backup contains, at least, all log denerated during the backup). However, defrag is fairly small log overhead. I would not be worried.

  Is there a way to pause/stop an Index Defrag when a backup starts?

No, but Defrag is perfectly OK to stop and resume later. If you interrupt defrag  you won't loose the work so far. Restarting a defrag later will continue from where it was interrupted.

  I utilize Reorganize when frag is between 10 and 30%,

That is, most likely, useless. Defrag is largely a myth. Stop Worrying About SQL Server Fragmentation


Can I get a tree-structure from a self-referenced (hierarchical) table? Given a hierarchical table like this:

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);

I would like to obtain the whole tree structure.

For instance, using this data:

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

I would like to obtain:

+----+-----------+---------------------+
| id | parent_id | description         |
+----+-----------+---------------------+
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  4 |     2     |     1.1.1 Group     |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.2.2 Items |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
+----+-----------+---------------------+

I'm fetching records using a recursive query like this:

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
OPTION (MAXRECURSION 0)
;

And this is the current result:

+----+-----------+---------------------+
| id | parent_id | description         |
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  4 |     2     |     1.1.1 Group     |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.1.2 Items |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
+----+-----------+---------------------+

I can't figure out how to order it by levels.

Is there a way to set a rank for each sub-level?

I've set up a Rextester

Add a "path" field and sort by that similar to a file path. As ypercube mentioned, the sorting is overly simplistic in this example and just happens to work but for simplicity's sake I'll leave as is. Most of the time when I use this pattern I sort by name rather than ID anyway.

IF OBJECT_ID('[dbo].[btree]', 'U') IS NOT NULL
    DROP TABLE [dbo].[btree];
GO

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);
GO

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.2.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast('root' as varchar(100))
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1,
           Path = Cast(tree.path+'/'+right('000000000' + cast(c2.id as varchar(10)),10) as varchar(100))
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
Order by path
OPTION (MAXRECURSION 0)
;

Here a rextester


How can I make sure the SQL Server query optimizer uses the exact tables in the query I have a query running in SQL Server 2008:

select count(*)
from table1 join
table2 on table1.col1 = table2.col2

In the data warehouse where this script runs, a table called thin_table1 is exclusively populated by table1 (we use this thin table for indexed view creation. Read this answer for a little more detail).

The problem is that the optimizer is choosing to use thin_table1 rather than table1 during execution. This does not happen in SQL Server 2005. This new execution plan won't work for our current operation.

How can I turn off this "pass-through" feature, either at the database or session-level (or in SSIS)? I have many SSIS packages and stored procedures running during the data load, so I don't want to touch all the objects individually.

At this point, even knowing what that feature is called would be helpful in searching for the answer.

EDIT:
Went back to look at the same plan in 2005. Looks like it did happen there, but it to much less dramatic effect. I thought it was a problem just in 2008 but the same functionality appeared in 2005.

EDIT2:
A DBA here noticed that the plan is referencing an indexed view. We normally drop our indexed views at runtime, but in this test scenario, they were still built. Looks like when the indexed view is active, it will use that and any tables associated with it when the query executes.

Is there a way to bypass this automatic reference to indexed views?

There are only two possible ways that the execution plan for your query might not read from table1 directly. I will use the following indexed view created in the AdventureWorks sample database to illustrate:

CREATE VIEW dbo.IV
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IV (ProductID);

1. Table1 is an indexed view

In this case, the optimizer will make an estimated-cost-based decision whether to read from the indexed view or to expand the view and read from the base tables it references. Example:

SELECT
    ProductID,
    cnt
FROM dbo.IV;

The optimizer chooses to read from the indexed view directly:

If the optimizer chose instead to expand the view, we could use the NOEXPAND table hint to prevent that. This hint is required in non-Enterprise SKUs to access indexed views directly.

The EXPAND VIEWS query hint forces expansion of the view, leading to a plan that reads from the base tables:

SELECT
    ProductID,
    cnt
FROM dbo.IV
OPTION (EXPAND VIEWS);

2. An indexed view exists which matches the query

SQL Server Enterprise Edition contains a feature which can match queries to indexed views, where the indexed view is not referenced in the query:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

Despite not mentioning our indexed view, the execution plan does:

About indexed view matching

This matching is only possible where the query processor has guarantees that the rewrite will always produce correct results. These guarantees include the fact that any changes to the tables referenced by the indexed view will also be reflected in the indexed view.

Any INSERT, UPDATE, DELETE or MERGE query that affects the Product or TransactionHistory tables will have extra operations added to the execution plan to make the appropriate changes to our indexed view.

The query optimizer does not keep track of any tables that might be created by the user from the base tables - they will not be maintained to reflect base table changes, and any indexed views created on these new tables will reflect only changes to those tables, not the originals. There is no magic here - the relationship between an indexed view and its base tables is very explicit.

The thin table example

To take an example that appears to match your question, say we create a 'thin' extract from the Product base table, containing only the ProductID column:

CREATE TABLE dbo.ThinProduct
(
    ProductID integer NOT NULL

    CONSTRAINT PK_ThinProduct
    PRIMARY KEY (ProductID)
);

INSERT dbo.ThinProduct
    (ProductID)
SELECT
    p.ProductID
FROM Production.Product AS p;

Now we drop the original indexed view and create a new one that references the thin table instead:

DROP VIEW dbo.IV;
GO
CREATE VIEW dbo.IVthin
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IVthin (ProductID);

A new query that references the thin Product table directly, can use the new indexed view:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

A query that references the original Product table cannot use this new indexed view, because there are no guarantees that IVthin will stay in step with any changes to the Product table (it will reflect changes to the ThinProduct table):

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

The execution plan shows base table access - it cannot use the indexed view:

Summary

Indexed view matching can only be performed where the appropriate guarantees are enforced by the engine. The scenario outlined in the question cannot occur as outlined there. The SSIS package must be referencing indexed views that can be expanded or issuing queries that can be matched to an indexed view for a query reference to table1 to resolve to anything other than that named object.

I appreciate this answer may not help you except in a general sense, but the discussion around the question has gone on for quite some time without clear specifics emerging. The question could be made easier for answerers to address specifically by including the actual SSIS query, table and indexed view definitions, and actual execution plans.


linking databases for reporting I am attempting to design a simple reporting feature for some financial market data.

I have a few sets of data, and I am trying to figure out what will be the best way to organize the tables, in particular for reporting.

I am using Microsoft SQL Server Express, which I am comfortable with for simple tasks and I've designed a few reports.

I have one table with Trade data, which is basically a log, with Trade #, Entry Date, Entry Price, Exit Date, Profit and some other information. Simple and easy.

I want to link each trade entry with some data specific to that entry. For example, in my Trade Table, I might have 200 entries. Each entry, however has pricing data from before and after the trade date that I would like to get.

I want to produce a report that lists the trade data, adjusted for parameters which I can do.  The report could display a graph to show trade specific data that is more detailed than can just be fit in columns from the current record.

Any ideas or references to check out would be greatly appreciated.

If you have the option of moving the data into another database I would begin looking at using a star schema.  Here is a website that might help you begin research

http://www.ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx

Star schemas are built specificly for the fast retrieval of large amounts of data and are very good at aggregating that data over time.  Plus having the data seperated from your OLTP database means that there are not as many draw backs to adding indexes.  What's more having your data layed out in a Star Schema sets you up to be for creating data cubes with SSAS in the future; although, you do not need a cube to interface with the data.


When exactly are multiple users unable to simultaneously run a stored procedure with a temp table? I have a question regarding a piece of documentation on Temp Tables that I recently read on TechNet.  The fourth paragraph of the Temporary Tables section on that page reads as follows:

  If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.

I work in an environment where we make significant use of a handful of stored procedures that use indexed temp tables, and we've never encountered an issue where users are having to wait for one execution to complete before the next begins.  I hope that will continue to be the case, but I'm concerned that it could become an issue if this caveat is not properly understood.

Specifically, I am unclear on the following points:

Does this apply only to global temp tables, or to local ones as
well?  It seems strange that a table that isn't visible outside of
the session (as in the latter case) would prevent another session
from executing simultaneously.
What qualifies as a "named constraint"? Don't all constraints have names (even if they are system-generated)?  Is this referring to constraints with a user-defined alias?  This seems like poor phrasing to me.
Does "multiple users" actually mean multiple sessions?  These procedures are called through our application using a single service account, so 99.9% of calls to our scripts are made to the DB by that single account (and I'm unconcerned about the occasional call an admin may make on the backend).  If the service account can run the sproc in multiple sessions simultaneously, then this issue is moot for my purposes.

This applies to local temp tables.

The difference between named and unnamed constraints is this:

CREATE TABLE #t1 (c1 INT PRIMARY KEY CLUSTERED)

CREATE TABLE #t2 (c1 INT,
                     CONSTRAINT pk_c1 PRIMARY KEY  CLUSTERED(c1) )

Letting the system name constraints makes it extremely unlikely that there will be a collision. In this example, if you open two windows in SSMS, you'll be able
to create #t1 in both, but not #t2.

Global temporary tables are shared by all users, so you have to handle things differently. They don't get 'destroyed' until the last session is done using them, so you need to make sure that when users access them, they can only access their data. This is sometimes done by SPID, other times by a hash value. It depends on how the global temp table is used.

Typically for global temp tables, stored procedures will check to see if they exist, and then only create them if the OBJECT_ID() is NULL.

Multiple users means multiple sessions. The login name has nothing to do with it. If George runs sp_something @i = 1 and Gina runs sp_something @i = 2, it doesn't matter if they're both logged in as User1, they'll have different SPIDs.


Copy MySQL User but change HostName I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second entry utilizing '%' as the hostname. Any suggestions?

You have to call SHOW CREATE USER 'username'@'localhost' and SHOW GRANTS FOR 'username'@'localhost' for all the users fetched by

SELECT user, host
  FROM mysql.user
 WHERE host = 'localhost'

and to replace all 'localhost' entries by '%' in the returned statements.


Adding a nullable column concurrently with an update or insert In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update?

For example:

ALTER TABLE MyTable ADD MyColumn varchar(255) NULL;

Referencing the documentation for ALTER TABLE (highlighting mine),

  The changes specified in ALTER TABLE are implemented immediately. If
  the changes require modifications of the rows in the table, ALTER
  TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M)
  lock on the table to make sure that no other connections reference
  even the metadata for the table during the change, except online index
  operations that require a very short SCH-M lock at the end. In an
  ALTER TABLE…SWITCH operation, the lock is acquired on both the source
  and target tables. The modifications made to the table are logged and
  fully recoverable. Changes that affect all the rows in very large
  tables, such as dropping a column or, on some editions of SQL Server,
  adding a NOT NULL column with a default value, can take a long time to
  complete and generate many log records. These ALTER TABLE statements
  should be executed with the same care as any INSERT, UPDATE, or DELETE
  statement that affects many rows.

Also, Paul Randal has some good information about what happens when you add columns (and how long the operation might take) in Misconceptions around adding columns to a table.  To summarize some main points:

New column is nullable, with a NULL default. The table’s metadata
records the fact that the new column exists but may not be in the
record. This is why the null bitmap also has a count of the number of
columns in that particular record. SQL Server can work out whether a
column is present in the record or not. So – this is NOT a
size-of-data operation – the existing table records are not updated
when the new column is added. The records will be updated only when
they are updated for some other operation.
New column is nullable, with a non-NULL default. It depends which
version of SQL Server you’re using:

Before SQL Server 2012: This IS a size-of-data operation. The
non-NULL default forces all existing records to be updated when the
column is added, and so the null bitmap will be updated too.
SQL Server 2012 onward: same behavior as for a NULL default nullable
column (i.e. metadata only operation)

New column is not-nullable (obviously with a non-NULL default). This
IS a size-of-data operation.