Why can't I shrink my logfile below to the requested value?

According to this documentation (Section:How Does Shrinking the Log File Work?):

  When any file is shrunk, the space freed must come from the end of the
  file. When a transaction log file is shrunk, enough virtual log files
  from the end of the log file are freed to reduce the log to the size
  requested by the user. The target_size specified by the user is
  rounded to the next highest virtual log file boundary.

You have 1GB and 2GB log files which equate to 8 and 16 vlf's of 131,072.  You said shrink to 512MB (524,288kb).  Since that is on a VLF boundary, it gets rounded up to the next highest VLF boundary (add 131,072) which would be (655,360kb) which is fairly close to the disk space allocation you're seeing.


How to control number of columns after pivoting date as column

change the inner query to following using TOP () GROUP BY and ORDER BY to limit to 8 distinct date

SELECT  TOP (8) CONVERT(VARCHAR(10), ReportedDate, 120)  AS ReportedDate
FROM    Activity
GROUP BY CONVERT(VARCHAR(10), ReportedDate, 120)
ORDER BY ReportedDate


Has anyone ran into this replication breaking bug before?

That bug is referenced in the MySQL Documentation for 5.5 and 5.1.40+ as that condition being unsafe.

Note the bottom of the bug report:

  [10 Dec 2010 3:34] Daogang Qu  Any statement that causes a trigger or
  function to update an AUTO_INCREMENT column is considered unsafe for
  statement-based replication after a patch of
  Bug#45827(http://lists.mysql.com/commits/9031). I had backported the
  patch to mysql-5.1-rep2 long ago. But I didn't see it in
  mysql-5.1-bugteam. The patch must be missed to merge to
  mysql-5.1-bugteam. I'm going to commit a patch to fix it in bug#45827.
  Thanks for the check.

  [10 Dec 2010 4:12] Daogang Qu  Sorry. The patch of bug#45827
  backported into mysql-5.1-rep2 should be merged into 5.5 instead of
  5.1. It's merged into 5.5 successfully.

  For 5.1, We mark all the statements that invoke a trigger or call a
  function that updated autoinc fields as unsafe and will switch to
  row-format in mixed mode. But in statement mode, we didn't emitted the
  warning.

  For 5.5, we emitted the warning in statement mode, because  a suppress
  warning feature is introduced.

Could a patch have been overlooked between minor releases of 5.1? That is the only probable cause. Now that Oracle is the grandfather of MySQL (via Sun), don't hold your breath for any fixes.

MySQL 5.1.59 was released Sept 16 2011 and this is still around?

It must have been an overlooked/forgotten patch.


tsql to find users and database level permissions mapped to a certificate

You can use this table to find users mapped to certificate

select * from sys.database_principals where type = 'C'

If the principals are SQL users, maybe you will have to remap users because when you migrate users and logins from one sql server to an other, SID aren't regenerated and may be different on the new server. So all the link on the users aren't valid.

Hope this help you.


Create view shows single column values as columns aliases (names) in view

Get rid of id in answers.  Instead, have PRIMARY KEY(student_id, question_id).

While you are at it, change the 255 down to something realistic.

The sluggishness is due to having to scan the entire answers table for each JOIN.

And I suspect this was just plain wrong:

LEFT JOIN addresses ad ON ad.id = s.id


Actual Number of rows is too high even with new tables

The number of records in TransmittedManifests table is much less than the number of records in LWTest. In such scenario, good solution is to use NOT EXISTS  approach (as in Query 2) to reduce the number of actual rows. Refer Joins without JOIN - Rob Farley

Now, Martin Smith’s comment helped me demystifying the count per execution (estimate) and total count (actual). For Query 1, the ActualExecutions="904" for LWTest table. So 128385/904 = 142 is the actual rows per execution which is somewhat close to the estimate 104.

In SQL Server 2005, the ActualExecutions can be seen by making a profiler trace adding performance events as shown in the diagram below. [In SQL Server 2012, I could see this information captured in execution plan diagram itself].

XML

SQL Server 2005 Trace Settings


Example where schema are better than multiple databases

Postgres documentation indeed provides the answer, although it is spread across its many sections. It begins with this general statement:

  It is not possible to access more than one database per connection. [...] Databases are physically separated and access control is managed at the connection level. If one PostgreSQL server instance is to house projects or users that should be separate and for the most part unaware of each other, it is therefore recommended to put them into separate databases. If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.

There are some things that you can control only at the database level, but not at the schema level. Among them:

Object privileges. The database owner role privileges override anything granted at the object (schema, table, etc.) level, unless explicitly revoked.
Database connection privileges. You can control at the database level what users can connect to it from what IP addresses.
Configuration parameters. This allows you to tune each database for its specific workload.
Maintenance. While you can backup and restore individual schemas using pg_dump and pg_restore, operations like point-in-time recovery, log shipping, and streaming replication work at the database (actually, cluster) level.

The minor downside of having multiple databases on the same server is the storage and memory overhead of maintaining and caching system catalogs for each database.

If you often query tables in different namespaces you might also prefer multiple schemas over databases. It is possible to query tables across different databases, but this requires setting up foreign data wrappers, which brings about additional maintenance and possible performance implications.

In short, if your applications require strict security isolation (e.g. the administrator in one namespace must not have access to other namespaces), or they have very different workload patterns, and you don't need to query tables in different databases, you should choose to implement multiple databases.

If, on the other hand, the workload pattern is the same for all namespaces, and you don't require strict separation of duties, and you often query tables across namespaces, you should probably use schemas in a single database for logical isolation of data. This is often the preferred scenario for multi-tenant applications.


What is the word that means a query predicate can use an index

The word you're looking for is "SARGable". From Wikipedia:

In relational databases, a condition (or predicate) in a query is said
to be sargable if the DBMS engine can take advantage of an index to
speed up the execution of the query. The term is derived from a
contraction of Search ARGument ABLE.


Why could query performance improve enormous after db restore on new server with worse hardware?

The amount of memory available to the SQL Server is an important input to the choice of execution plan made by the query optimizer.
It is certainly possible that the new SQL Server, with its reduced memory, is encouraging plans without memory-consuming operations (e.g. sorts and hashes) that happen to be 'better' for the type of queries you are executing.
If this is indeed a primary factor, reducing the amount of memory available on the original server would also likely increase its performance. Typically, this would be unusual, assuming statistics are good and the queries usually get reasonable cardinality estimates. Nevertheless, it might explain what you are seeing; a detailed analysis would be required to be sure.
To answer your final question: yes statistics are a persistent part of the database and are included in a backup/restore.
Update
Based on the new information in the question concerning the vastly improved I/O performance of the second system, yes, that is most likely the reason.


Select rows changed in current transaction

You could return the rows from the proc via the OUTPUT clause.

insert into YourTable(Column1, Column2, Column3)
output inserted.Column1, inserted.Column2, inserted.Column3
--select... or values()... or what ever insert method

You would use the same methodology for DELETE and UPDATE. You can also store the OUTPUT into a table for further processing if need be.

Here is a DB Fiddle showing the below code

create table myTable (i int)
go

create proc myInsert
as
begin
   insert into myTable (i)
   output inserted.i
   values
   (1),(2),(3)
end
go

exec myInsert
go

create proc myDelete
as
begin

   create table #myTemp (i int)

   delete from myTable
   output deleted.* into #myTemp
   where i >= 2

   select * from #myTemp
end
go

exec myDelete
go


You have to take in count that a 3rd box with SSRS counts more license.
In that case you have to place the SSRS databases on the alwayson cluster and the SSRS website on the 3rd box. You will not have HA solution for it and you will suffer downtime for server maintenance.
Otherways, if you choose the scale out deployment on the alwayson cluster with a net balancer in front of it you will have redundancy and no increase in license costs.
But you need a new component in the architecture, a network balancer. You can't enable microsoft NLB on the clusters' nodes because it is not compatible with the cluster itself.


How to extract the structure out of DB2 (Back up Structure)?

Since YperSillyCube(TM) insists, I will add his answer. db2look is the tool that comes with DB2 to extract DDL from the database. You can extract things like tables, constraints, indexes, triggers, stored procedures, etc. As well as security statements, federation settings, buffer pool and tablespaces, and some database settings.

I highly recommend using it. I used to use it all the time when I worked with DB2. Very handy. Especially when combined with tools like Beyond Compare to compare DDLs between environments, etc.

The IBM Knowledge Center link is good documentation to read up on. Ember Crooks has posted two great articles on db2look here and here.

Personally, I tended to use the following options the most with the command.

db2look -d <databasename> -a -l -e -x -o <file to extract to> -td"@"

This allows me to grab several objects besides just the default, the tablespaces and bufferpools, some security settings and then make sure I set the @ sign as the statement terminator in case I have triggers, functions, or stored procedures.

You can then import those into a new database using the following db2 -td@ -vf <filename> and then either redirect the results out or pipe to tee as I like to do on Unix. Do note that the character you use as the statement terminator in the db2look command needs to match the statement terminator supplied when importing the file.


How can I prove that the following T-SQL is bad for performance?

There is dynamic SQL, so no cache plans, meaning plans generated every time

Not necessarily true. Dynamic SQL can (and does) use cached plans just as well as static SQL. For dynamic search conditions resolving to dynamic SQL is oft the right answer. See Dynamic Search Conditions in T-SQL for more details.

  There is an INSERT SELECT pattern, so table locking is more likely to be an issue.

Not necessarily true, specially with a @tempTable

  have re-written some of the Stored Procedures in this way instead

Using multiple OR conditions like that is an anti-pattern. You are forcing the query optimizer to come up with a plan that works for any value of all those parameters. Usually the only solution is a scan, ignoring any index. The original code was better.

  Can anyone offer ... any better ways of proving my suspicions?

Yes. Measure. Use a methodology like Waits and Queues. Don't relly on your intuition. Find the bottlenecks and address them accordingly.


MYSQL Subquery in columns of select statement

in ideal situation, when Names.id = Users.id return only 1 record, both queries the same.

The difference when it not true.

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

will stop work and return error, so You will need add LIMIT class

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY something LIMIT 1) as name
 FROM Users

at the same time query

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

continue work without errors, this query return all rows from Names related to Users

In some other cases when You expect only 1 Name, You will need add GROUP BY conditions

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id GROUP BY Users.id

But this condition could return unpredicted name from Names (and it not 100% legal construction for SQL), and You again would need add 1 more level of JOIN with derived tables, and some time it could be ugly construction,

so, You always can compare what form of query more correct for selected case, simple example:

SELECT t1.id,t1.email,t2.name FROM Users t1 JOIN
(SELECT id, name FROM Names n1 INNER JOIN
(SELECT MAX(dateregistered) as dateregistered, id FROM Names GROUP BY id) n2 ON n1.id=n2.id AND n1.dateregistered=n2.dateregistered) t2
ON t1.id = t2.id

will return same result as:

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY dateregistered DESC LIMIT 1) as name
FROM Users

Add: Example with Names look like not realistic, but real situation - when You need request not name which is really 1 per person, but actual postal address for client with 10 years history. He can have 20 addresses, and You need return most resent


Log Shipping vs Data Replication in SQL Server 2012

If you are using SQL Server 2012 as reporting, then you can use logshipping and configure your secondary as standby - that will allow users to read data from secondary when logs are not being restored. You can delay the restore of log files on secondary as per your needs.

Note that from lower to higher version, you are doing a one way. You wont be able to restore the sql server 2012 databases to sql server 2005 or 2008R2 easily (if you failed over).

Database transactional replication wont be supported as your subscriber (reporting server) would be on a higher version than publisher (sql server 2005 and 2008R2). See my answer here.

Also refer to SQL Server Many-to-One replication


We are using a merge replication topology between our servers where:

Initial snapshot is made available to subscribers through ftp
further data exchange (in our case, merge sync between servers) is done through web replication

Here is a script I once proposed to launch such a replication. You will notice that one of the sp_addmergepullsubscription_agent parameters, @use_ftp, is set to True. The @internet_url parameter sets the https adress used for replication. There are other options allowing the use of an alternate snapshot location and/or its compaction (which could make sense when you are talking about a 1To database ...)

Hope it helps


This works fine:

select regexp_substr(':123:456:', '(\d+):', 1, 2, 'i', 1) from dual;

I think yours fails because the opening and closing colons won't get matched by both occurrences (because the first match is greedy).


Is there any unique identifier for PostgreSQL? How can the master identify its slave unqiuely?

The master does not "find" the replicas.  The replicas find the master.

The master identifies the replicas by either their slot_name, or their application_name, or not at all, depending on the context.

What are you trying to do?


How do I prevent updates on an entire table (but allow inserts) in Mysql 5.5?

You can revoke 'UPDATE' privilege for a particular table. Or you can write BEFORE UPDATE trigger that raises an error (using signals or issuing a statement that always fails).


You're right about each of your options.  Log Shipping is perhaps the easiest way to almost accomplish what you want, but there is the drawback that you won't be able to read from your secondaries while the logs are being restored.  If your goal is simply to become more highly available in case something bad happens, I advise you to at least get your backup strategy up to speed ASAP.  That means putting your databases in FULL recovery mode and performing transaction log backups at a high frequency, and putting those backups on a different disk than your database and transaction log.

One thing you didn't mention was creating a simple shared-disk cluster.  Again, it depends on what type of redundancy you want.  If you're just trying to ensure against a server hardware failure, this might be the way to go.

The technical solution to your problem that gives you the highest availability and flexibility is to upgrade to SQL Server 2014 Enterprise Edition, which has support for AlwaysOn Availability Groups.  AlwaysOn is a technology almost identical to mirroring, but allows for a readable secondary (to be sure, there are other benefits).

That upgrade can be costly, however, since it requires Enterprise Edition.  It's also pretty tricky to configure the first time, and you'll need to make sure you have a good relationship between your DBA-ish people and network administration team to pull it off.  It may even be worth hiring a consultant for this setup alone if you really don't have the DBA resources.  Just make sure the consultant has experience setting up this type of cluster.  It IS something you can pull of yourself if you know enough about networking, security, clustering, and SQL Server, though.  All the resources are out there, you'll just have to do some digging.  Brent Ozar has some good resources you can check out, but you'll probably want to look into a book purchase.

Another nice thing about AlwaysOn AG's is that you can set up an Azure instance as your secondary without making another hardware purchase.  This can be a really smart for DR scenarios where your main data center goes out -- you'll have Azure's completely separate infrastructure to fall back on.

Sorry, I think I didn't answer your question too well, I think I just gave you a lot more to think about.  That's how this goes, I guess...


First, if you haven't done the first two that vyegorov suggests do it now.  If you have already vacuumed, it is not clear what sort of damage you may have already done in this case.  This error should not be possible on new versions of PostgreSQL due to a number of checks, and had you upgraded some time ago, this wouldn't have happened.  I highly recommend trying to stay on supported branches in the future.

I want to take a moment and describe what likely caused the problem and what it means.  The prognosis IMO is not good and recovering the data, if it is even possible, is likely to be expensive and time consuming.  I sincerely hope you have a good backup from before the transaction wraparound.  If not, ouch....

What Went Wrong

PostgreSQL uses something called MVCC, which means that old versions of rows are kept around until they are clearly no longer used.  MVCC as practiced by PostgreSQL stamps each row with a minimum transaction and a maximum transaction, and uses these for visibility management.  When a transaction rolls back those rows entered are no longer visible and those rows deleted remain visible.  Transaction ID's are 32-bit integers.

Periodically you are supposed to vacuum PostgreSQL instances.  This, among other things, manages MVCC so that fewer transaction id's need to be checked, manages free space in tables, and can reset the transaction id sequence.

When the transaction ID wraps around, bad things happen but they boil down to the fact that PostgreSQL can no longer be sure what rows are visible and what rows are not.  Note thais applies not only to rows in your own tables but in the system catalogs as well.  This is a very, very bad thing.  The best approach, if you can do it, is to restore from a backup before the wraparound occurred.  This is why databases and tables are not showing up and why vacuuming full duplicates them.

Why Upgrading Would have Fixed This

Modern versions of PostgreSQL come with autovacuum automatically enabled which runs vacuum processes in the background to help prevent this sort of problem.  More recent versions also will refuse to start new transactions for non-superusers once wraparound is approaching.  This gives you a chance to detect and correct the problem before you suffer possibly catastrophic data loss.

PostgreSQL 7.4 hasn't been supported in nearly three years.  I don't know when the last time anything was vacuumed but it must have been billions of transactions ago.  This is not good.


How to use "BACKUP DATABASE", but the database is in a different server.?

EXEC SQL2.master.sys.sp_executesql N'BACKUP DATABASE dbname
  TO DISK = N''D:\path_on_remote_server\...'';';


Setting maxdop 1 vs setting the threshold for parallelism high

There is an easy solution if you have Enterprise (expensive) Edition in use:
Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


How can i enforce data integrity in this setup?

Have two columns in tblSchoolTerm one called TermSchoolID and the other YearSchoolID. Add a check constraint that enforces they are equal.

From the information given, however, it would seem to me that your model is incorrect. I would have thought a Term would relate to a Year, then School would have many-to-many with Term. You know your business rules best, though.


Join tables with same keys, second table has multiple values for key

You have two possibilities:

Two joins. Probably a good alternative if you do a limited search, and don't want all available attributes (i.e.: you ignore capital)

SELECT
    hostname,
    type_var.varvalue AS `type`,
    location_var.varvalue AS location
FROM
    hosts
    JOIN vars AS type_var
        ON (type_var.object_id = hosts.host_object_id) AND  type_var.varname = 'type'
    JOIN vars AS location_var
        ON (location_var.object_id = hosts.host_object_id) AND location_var.varname = 'location' ;

max(case) and GROUP BY. This is a typical approach to unpivot in standard SQL. This is normally faster went you either want all attributes (type, location, capital), or you don't limit your queries with a WHERE that restricts the result significantly. (In your case, without WHERE, I'd choose this approach:

SELECT
    hostname,
    max(case when varname = 'type'     then varvalue end) AS `type`,
    max(case when varname = 'location' then varvalue end) AS location
FROM
    hosts
    JOIN vars
        ON (vars.object_id = hosts.host_object_id)
GROUP BY
    object_id ;

In both cases, you'll get:

hostname | type    | location
:------- | :------ | :-------
America1 | Country | N.A.
Another1 | Another | Location

NOTE 1: This assumes that vars has PRIMARY KEY (object_id, varname). Otherwise, you could have more than one type or location per object_id, and that would give you more than one row per object_id in the first case, and just one in the second, choosing the max among them.

NOTE 2: These queries are standard SQL and should work not only on MySQL, but on most SQL RDBMS.

You can check everything at dbfiddle here


Using a Calculated Field in a Separate Calculated Field

If you just need to hack this in, you can embed the first expression in the second:

,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
        ELSE 0
         END AS RateReset

,CASE WHEN ln.TIME_RATE = 1
     AND (CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
     ELSE 0 END) = 3 THEN 'Pass'
      ELSE 'FAIL'
        END AS OGIntRate

Although based on this very limited excerpt of the query, you should just be able to check for INT_RATE_CODE = 'C':

,CASE WHEN ln.TIME_RATE = 1 AND ln.INT_RATE_CODE = 'C' THEN 'Pass'
      ELSE 'FAIL'
        END AS OGIntRate

If you're able to change the table definition, you could add a computed column for the first CASE expression, and then reference it directly in the other CASE expression.  Like this:

ALTER TABLE YourTableName
ADD COLUMN RateReset AS CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1 WHEN ln.INT_RATE_CODE = 'B' THEN 2 WHEN ln.INT_RATE_CODE = 'C' THEN 3 ELSE 0 END;

Then OGIntRate could be based off of RateReset directly like you have in your original code.


I'd create a seperate index on SKU and ModelNumber. I don't think an index on RetailerID will do much good as there won't be many different retailers .

The best approach to analyze this is by comparing the execution plans & times of the different sets of indexes


InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

You can and will be better served by removing created_by from your primary key and creating a separate index for it.

This will allow those queries which use created_by in the where clause (e.g. where created_by = ?), to be able to use that index to service the query.

There may be a slight, and probably not noticeable, performance hit from managing the primary key and the index on an insert. However this price is nothing to pay for not doing full table scans when executing such queries needing it to be be the primary search in the where clause.


Ops Manager Install in Windows and connect to mongod instance started in linux

Can we install ops manager in windows and connect to mongodb instance
  started in Linux operating system?

No, I have not done as practically , as per my knowledge due to platform & their connection string used to access the Ops Manager Application Database.

  Does ops manager installation on windows require separate installation
  of mongoDB instance on windows also?

No, Because, the databases require dedicated MongoDB instances. Do not use MongoDB installations that store other data. The Backup Database is required only if you will use the Backup feature.

  Can we install ops manager on linux and access it from windows using
  URL?

Yes,For example you are going to access ops manager through the following URL

http://<OpsManagerHost>:8080

You have to make sure that the host is the fully qualified domain name of the server.

For further your ref here , here and here


How to show multiple names for one record but not duplicate the record?

you structure looks good . except that you could merge the amount to the part table . as every part is strictly associates with price.

also, only reason you will have duplicate rows is if any of your dimensions tables do not follow strict primary constraints.

Car
 - id (pk)
 - carname
 - image
 - category
 - status

Parts
 - partid (pk)
 - partname
 - amountValue

CarParts
 - carpartid (pk)
 - carid(fk)
 - partid(fk)
 - amountid(fk)

Category
 - id (pk )
 - categoryname


Mysql: Unable to drop a columns NULL default

I suspect your integrator was asking to remove the NULL from the column.

First check there are no NULL columns:

SELECT * FROM TableName WHERE Prenom IS NULL

Or to set to empty string:

UPDATE TableName SET Prenom = "" WHERE Prenom IS NULL

Then NULL for the column can removed with:

select version();

| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |

create table TableName ( Prenom  varchar(50) )

✓

show create table TableName

Table     | Create Table
:-------- | :----------------------------------------------------------------------------------------------------------
TableName | CREATE TABLE `TableName` (`Prenom` varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

ALTER TABLE TableName MODIFY COLUMN Prenom varchar(50) NOT NULL

✓

show create table TableName

Table     | Create Table
:-------- | :------------------------------------------------------------------------------------------------------
TableName | CREATE TABLE `TableName` (`Prenom` varchar(50) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

db<>fiddle here


I don't see any issues with your query, regarding correctness of the update.

Another way to get the same results, using window functions.

Regarding efficiency, you'll need to check execution plans and test both methods.

UPDATE a
SET a.best_seller = b.book_id
FROM authors AS a
  JOIN
  ( SELECT author_id, book_id,
           rn = ROW_NUMBER() OVER (PARTITION BY author_id
                                   ORDER BY copies_sold DESC)
    FROM books
  ) AS b
  ON  b.author_id = a.author_id
  AND b.rn = 1 ;


mysqld constant CPU usage when absolutly nothing is connected and no queries

Yes, your kill statement is the likely cause.

The command SHOW PROCESSLIST will only show you what is happening in foreground threads.  InnoDB is likely rolling back the transaction in the background.  Some diagnostics you might find useful:

SHOW ENGINE INNODB STATUS - see what is happening inside of InnoDB.  Under 'TRANSACTIONS' you should see your killed staetment being rolled back.
SELECT * FROM performance_schema.threads - show all threads in MySQL.
MySQL Workbench 6.1 includes Performance Schema Reports, exposing some of this data in a visual form.


how to search for a table across catalogs of informix database?

The chances are that you can find what you want by running a query against sysmaster:'informix'.systabnames  – which is a table in the sysmaster database.

SELECT * FROM sysmaster:'informix'.systabnames

Sample output (rather carefully selected rows):

partnum  dbsname    owner     tabname                       collate      dbsnum
INTEGER  CHAR(128)  CHAR(32)  CHAR(128)                     CHAR(32)     INTEGER
1048586  sysmaster  informix  sysusers                      en_US.819    1
1048641  sysmaster  informix  sysextdfiles                  en_US.819    1
1048648  sysuser    informix  sysattrtypes                  en_US.819    1
1048673  sysuser    informix  syssecpolicycomponents        en_US.819    1
1048704  sysadmin   informix  sysconstraints                en_US.819    1
1048723  sysadmin   informix  sysinherits                   en_US.819    1
1048731  sysadmin   informix  syserrors                     en_US.819    1
1048742  sysadmin   informix  sysxasourcetypes              en_US.819    1
1048750  sysadmin   informix  sysseclabelnames              en_US.819    1
1048794  sysadmin   informix  storagepool                   en_US.819    1
1048825  sysadmin   informix   124_51                       en_US.819    1
1048843  sysadmin   informix  mon_users                     en_US.819    1
1048856  sysadmin   informix  mon_prof_idx2                 en_US.819    1
1048861  unlogged   informix  syscolumns                    en_US.819    1
1048878  sysutils   informix  syssynonyms                   en_US.819    1
1048881  sysutils   informix  sysreferences                 en_US.819    1
1048888  sysutils   informix  sysprocauth                   en_US.819    1
1048891  sysutils   informix  systriggers                   en_US.819    1
1048912  sysutils   informix  sysaggregates                 en_US.819    1
1048942  sysutils   informix   102_10                       en_US.819    1
1048952  sysuser    informix  syscolumns                    en_US.819    1
1048991  logged     informix  systabauth                    en_US.819    1
1049051  unlogged   informix  sysprocauth                   en_US.819    1
1049055  mode_ansi  informix  sysprocauth                   en_US.819    1
1049110  mode_ansi  informix  sysinherits                   en_US.819    1
1049128  logged     informix  syslogmap                     en_US.819    1
1049222  utf8       informix  sysobjstate                   en_US.57372  1
1049259  utf8       informix  sysseclabelcomponentelements  en_US.57372  1
1049269  utf8       informix  syssecpolicycomponents        en_US.57372  1
1049297  unlogged   jleffler  dual                          en_US.819    1
1049298  stores     jleffler  dual                          en_US.819    1
1049303  stores     jleffler   100_1                        en_US.819    1
1049309  stores     jleffler  elements                      en_US.819    1
1049313  stores     jleffler   101_4                        en_US.819    1
1049318  stores     jleffler   101_5                        en_US.819    1
1049321  stores     jleffler   101_6                        en_US.819    1
1049341  stores     jleffler  compound                      en_US.819    1
1049343  stores     jleffler   102_18                       en_US.819    1
1049346  stores     jleffler   102_19                       en_US.819    1
1049351  stores     jleffler  compound_component            en_US.819    1
1049353  stores     jleffler   103_22                       en_US.819    1
1049356  stores     jleffler   103_23                       en_US.819    1
1049358  stores     jleffler   103_24                       en_US.819    1
1049367  stores     jleffler  isotopes                      en_US.819    1
1049368  stores     jleffler   104_30                       en_US.819    1
1049371  stores     jleffler   104_31                       en_US.819    1
1049382  stores     jleffler  named_isotopes                en_US.819    1
1049383  stores     jleffler   105_37                       en_US.819    1
1049385  stores     jleffler   105_38                       en_US.819    1

The 'table' such as " 105_38" are not tables but indexes.  There may be other tables or views in sysmaster which satisfy your question better — but it is likely that querying sysmaster is the way to get the information you're after.


Oracle Function Accepting Precision 2 Number

in oracle plsql function or procedure prototype you can just specify datatype and not length or precision and so you want control length or precision you should handle it in your function or procedure body for example:

create or replace FUNCTION ftest(x NUMBER) RETURN NUMBER IS
BEGIN
    if length(x) > 2 then
    return;
    end if;
    RETURN 42; -- body of function is not relevant for this question
END;

Or

create or replace FUNCTION ftest(x NUMBER) RETURN NUMBER IS
t number(2);
BEGIN

    t:=x;
    RETURN 42; -- body of function is not relevant for this question
    EXCEPTION WHEN VALUE_ERROR THEN
    RETURN -1;

END;


How is INDEX on Composite Primary Key in mysql?

The composite primary index will always ensure that the combination of col1, col2, col3 values are unique. If the purpose of the index is to ensure that the values are unique then you will have achieved that
A composite index also provides for index matches on any combination of the columns col1, col2, and col3
You would create separate indexes on col2, and col3, if you join or filter using any of the columns without the others.
I always prefer a numeric primary key (with no business association), and unique indexes over a composite primary key where necessary.


This was what I finally figured out, in case someone else comes along and hits the same problem. It was very confusing because the corruption was coming from git, so when I did something like rollback, it got corrupted then, not with my own activities.

I am running PHPStorm on Windows, with a shared directory on a virtual linux machine.  I need line endings to be LF, but since PHPStorm is running in Windows, the CRLF and LF battles ensue.

I had long ago fixed it with .gitattributes.  But when I switched from MySQL to SQLite, I had not added .db as a file not to be messed with when it came to line endings.

Git was replacing a CRLF with an LF in the .db binary file

In .gitattributes, you need:

# Set the default behavior to always be linefeed for linux
* text=auto

# Definitively text files
*.txt text eol=lf
*.php text eol=lf
*.html text eol=lf
...

# Ensure those won't be changed
*.jpg -text
*.png -text
*.db -text

I added the last line and all was well, git is no longer trying to be helpful with the binary file and corrupting it with LFs.

One last thing I learned, you can set the gitattributes file, then go to your root directory and run the command:

git add --renormalize .
git status

You will then see any file that will be converted from CRLF to LF, a good way to fix an entire directory as well as making sure it is not doing to the SQLite database.


Determine the command type in a DDL trigger / get names and datatypes of new columns

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements.

Once a DDL Trigger is fired, you need to use the EVENTDATA() function to get the details, in XML form, of the event that fired the DDL Trigger. Different types of commands can have different data points related to the event, so we first need to see what is available for ALTER_TABLE. According to the linked documentation for EVENTDATA(), the various options can be found by looking at the following file:

C:\Program Files\Microsoft SQL Server\{version_number}\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd

Or, we can do a simple test by creating a DDL Trigger and having it do nothing more than return the EVENTDATA() value for us to see how it gets filled out:

CREATE TRIGGER [CaptureAlterTableAddColumn]
ON DATABASE
FOR ALTER_TABLE
AS
  SET NOCOUNT ON;

  SELECT EVENTDATA() AS [AlterTableEventData];

Once that DDL Trigger is in place, run the following to see what we have to work with:

CREATE TABLE dbo.AlterTableTest (Col1 INT);

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ADD Col2 INT;
ROLLBACK;

That should return the following:

<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-08-27T14:34:48.730</PostTime>
  <SPID>55</SPID>
  <ServerName>DALI</ServerName>
  <LoginName>Dali\Solomon</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>Test</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>AlterTableTest</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>Col2</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.AlterTableTest ADD Col2 INT;
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

So this looks promising. The <AlterTableActionList> element has sub-elements <Create>\<Columns> so now we have a way that doesn't require imprecise text parsing with SUBSTRING, etc to determine if this ALTER TABLE ADD [column]... vs ALTER TABLE ADD CONSTRAINT.... But what about ALTER TABLE ALTER COLUMN...? If you try:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ALTER COLUMN [Col1] BIGINT;
ROLLBACK;

It shows that the <Create> element is now <Alter>. Great. But why is there a sub-element for <Columns> with a <Column> sub-element instead of it just being:

<Create>
  <Column Name="Col2" />
</Create>

If you try:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ADD Col3 INT, Col4 DATETIME;
ROLLBACK;

It will show:

...
<Create>
  <Columns>
    <Name>Col3</Name>
    <Name>Col4</Name>
  </Columns>
</Create>
...

At this point we have the following:

SET NOCOUNT ON;

DECLARE @Columns TABLE ([ColumnName] sysname NOT NULL);

INSERT INTO @Columns (ColumnName)
  SELECT t.c.value(N'(./text())[1]', N'sysname') AS [ColumnName]
  FROM   EVENTDATA.nodes(N'/EVENT_INSTANCE/AlterTableActionList/Create/Columns/Name') t(c);

IF (@@ROWCOUNT = 0)
BEGIN
  RETURN; -- no columns added, so exit
END;

DECLARE @Query NVARCHAR(MAX);
SET @Query = EVENTDATA.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
                             N'NVARCHAR(MAX)');

Now you just need to parse @Query using the values in @Columns as guides for where to start looking for the datatype. Given the possible variations of how T-SQL can be structured, it will require Regular Expressions to properly extract the datatype(s). You might already have some SQLCLR functions for that, or you can get the Free version of SQL# (which I am the author of) which has several RegEx functions.

UPDATE (2016-07-08)

I originally had thought that the query from EVENTDATA() needed to be parsed in order to get the datatypes, but @ErikE had the great suggestion to check the actual columns that had been added. Since the column names naturally need to be unique, and we have the column names already, that makes it easy to JOIN to the system catalog view sys.columns** and then to a couple of others to get the rest of the info. The final result is:

CREATE
--ALTER
TRIGGER [CaptureAlterTableAddColumn]
ON DATABASE
FOR ALTER_TABLE
AS
  SET NOCOUNT ON;

  -- SELECT EVENTDATA() AS [AlterTableEventData]; -- uncomment line for debug

  DECLARE @Columns TABLE ([ColumnName] sysname
                                       COLLATE Latin1_General_100_BIN2
                                       NOT NULL,
                          [Datatype] NVARCHAR(50) NULL);
  DECLARE @Temp XML = EVENTDATA(),
          @SchemaName sysname,
          @ObjectName sysname;

  INSERT INTO @Columns (ColumnName)
    SELECT t.c.value(N'(./text())[1]', N'sysname') AS [ColumnName]
    FROM  @Temp.nodes(N'/EVENT_INSTANCE/AlterTableActionList/Create/Columns/Name') t(c);

  IF (@@ROWCOUNT = 0)
  BEGIN
    RETURN; -- no columns added, so exit
  END;

  -- SELECT * FROM @Columns; -- uncomment line for debug

  SET @SchemaName = EVENTDATA().value(N'(/EVENT_INSTANCE/SchemaName/text())[1]',
                                      N'sysname');
  SET @ObjectName = EVENTDATA().value(N'(/EVENT_INSTANCE/ObjectName/text())[1]',
                                      N'sysname');

  SELECT CASE
           WHEN scol.[user_type_id] IN (165, 167, 173,175)
             THEN styp.[name] + N'(' + CASE scol.[max_length]
                                         WHEN -1 THEN N'MAX'
                                         ELSE CONVERT(NVARCHAR(10), scol.[max_length])
                                       END + N')' -- max_length: 1 - 8000, -1 = MAX
           WHEN scol.[user_type_id] IN (231, 239)
             THEN styp.[name] + N'(' + CASE scol.[max_length]
                                         WHEN -1 THEN N'MAX'
                                         ELSE CONVERT(NVARCHAR(10),
                                                      (scol.[max_length] / 2))
                                       END + N')' -- max_length: (2 - 8000)/2, -1 = MAX
           WHEN scol.[user_type_id] IN (41, 42, 43)
             THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10),
                                               scol.[scale]) + N')' -- scale: 1 - 7
           WHEN scol.[user_type_id] IN (106, 108)
             THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10), scol.[precision])
                  + N', ' + CONVERT(NVARCHAR(10), scol.[scale]) + N')' -- prec. & scale
           ELSE styp.[name]
         END AS [DataType],
         scol.collation_name,
         scol.is_nullable,
         scol.is_rowguidcol,
         scol.is_identity,
         scol.is_computed,
         scol.is_sparse,
         scol.is_xml_document,
         scol.xml_collection_id
  FROM   @Columns col
  INNER JOIN sys.columns scol
          ON scol.[name] = col.[ColumnName] COLLATE Latin1_General_100_BIN2
  INNER JOIN sys.objects sobj
          ON sobj.[object_id] = scol.[object_id]
  INNER JOIN sys.schemas sscm
          ON sscm.[schema_id] = sobj.[schema_id]
  INNER JOIN sys.types styp
          ON styp.[user_type_id] = scol.[user_type_id]
  WHERE   sscm.[name] = @SchemaName
  AND     sobj.[name] = @ObjectName;

And a fairly complete test is:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest
  ADD Col3 INT, Col4 DATETIME, cOl5 NvARchar(13), col6 nvarchar(max),
      col7 datetime2(4), COL8 DEciMAL(18, 9), CoL9 NUMERIC(10),
      col10 sql_variant, col11 float, col12 float(2), col13 float(22),
      col14 varchar(1111), col15 VARchar(mAx), coL16 varbinary(max),
     col17 varbinary(333), col18 binary(334), col19 nchar(789),
      col20 char(77) sparse NULL, col21 xml NOT Null,
      col22 uniqueIdentifier ROWGUIDCOL, col23 AS ([Col3] * 1.2);
ROLLBACK;

** I am using the sys.columns system catalog view (and a couple of others) instead of INFORMATION_SCHEMA.ROUTINE_COLUMNS because INFORMATION_SCHEMA does not contain Microsoft SQL Server-specific properties. For example, the SELECT statement in the Trigger references the following columns (among others) that are not present in INFORMATION_SCHEMA.ROUTINE_COLUMNS:

is_rowguidcol
is_identity
is_computed
is_sparse
is_xml_document
xml_collection_id

This list was taken from SQL Server 2012 (columns are the same for SQL Server 2014). If you are running on SQL Server 2016 then you will have additional columns that might need to be incorporated into whatever this Trigger is doing. And if you are running on SQL Server 2005, 2008, or 2008 R2, then you will have a few less columns (e.g. is_sparse was added in SQL Server 2008).


Full text search over multiple related tables: indices and performance

Optimization

You're going down the right track.

You either need to

Denormalize
Cache

Caching the results

What you probably want is a MATERIALIZED VIEW. This is easy and works reasonably well.

CREATE MATERIALIZED VIEW foo
AS
SELECT t.id, to_tsvector(concat_ws(' ',a.name, o.address, t.description, a.name)) AS tsv
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id
;

Then just

SELECT * FROM foo WHERE tsv @@ plainto_tsquery('foo bar');

Denormalizing the table

This can take a lot of forms, you've got this right though..

Redesign

Searching everything in a fuzzy fashion like this is a losing game. Even this knock off of Dungeon and Dragons meets Yahoo Answers has rules.

It becomes a lot easier to generate a query when you introduce syntax likes [text] for tagging, and is:answer to search just answers, rather than rebuilding Google and normalized indexes.


Full text search error: Invalid locale ID was specified

The answer was to Load Licensed Third-Party Word Breakers (MSDN)

From that link:

  SQL Server 2008 R2 includes licensed third-party word breakers for the following languages:

  Danish
  Polish
  Turkish

  These word breakers are available but are not installed by default, and must be manually registered and then added to the list of LCIDs that are supported for full-text indexing and querying.

  These word breakers are not enabled by default because they are owned by third parties who have not yet provided the level of testing, security, and robustness that is required for them to be enabled by default.

The main steps involved are:

Obtain Instance Name for Each Instance of SQL Server
Obtain the FTData Path for Each Instance
Register a Word Breaker and the Related Linguistic Components

Add the COM ClassID(s) for the Word Breaker and Stemmer Interfaces
Add a Key to the <InstanceRoot>\MSSearch\Language Node
Add Configuration Values That Give the Location of Each Linguistic Component

Update the List of Languages Supported by Full-Text Search

See the linked documentation for the full details.


Where is the SQLS database backup virtual device string stored in the system tables?

This is old, but I had the same issue.  My SQL logs and backup tables were indicating that backups were happening, but even when I used the vssadmin command above, I could not locate the backup files.
I listed the writers, but none matched the long string in the SQL logs, so I tried some of the other "vssadmin list" commands:

---- Commands Supported ----

List Providers        - List registered volume shadow copy providers

List Shadows          - List existing volume shadow copies

List ShadowStorage    - List volume shadow copy storage associations

List Volumes          - List volumes eligible for shadow copies

List Writers          - List subscribed volume shadow copy writers

"List Providers" turned out to be the golden nugget.  Although nothing was returned for existing copies or storage associations, the Providers listed the info I needed - it named my SAN provider.  I looked in my storage tool and saw it was using Microsoft VSS, and the snapshot times correlated with what was in my SQL log.

Three things of note:

nowhere could I find anything to correlate to the  TYPE=VIRTUAL_DEVICE: number listed in my SQL Logs (even though there were plenty of guids all over)
"List Shadows" did not return anything, which was scary until I looked at my storage tool and saw the snapshots there.
My DBs are all currently in "SIMPLE" recovery model, so I never have to do anything but attach database mdf/ldf files from the snap.  This would be more complicated with databases that need to live in full recovery mode.

HTH future searchers - this has been bugging me forever.  Where were my backups?


INT or TIMESTAMP for the change time column in a logging table?

I have done it both ways. I prefer using TIMESTAMP because it makes adhoc queries easier. If you only use a GUI to query the logs, then use whatever that programmer wants.

This one boils down to a style question because there isn't much difference in the functionality or performance.

ed


If you want to erase all binary logs older than 30 minutes, do this

mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 30 MINUTE;

If you want to erase all binary logs except the last one, do this

mysql> PURGE BINARY LOGS BEFORE NOW();

If you want to erase all binary logs before midnight, do this

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND;

If you want to erase all binary logs before midnight 3 days ago, do this

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 3 DAY;

I have discussed deleting binary logs like this before

Apr 26, 2013 ; Is it safe to delete mysql-bin files?
May 28, 2014 : Freeing up disk space in 'mysql\data'

Give it a Try !!!


mysql cannot find database after dropped of it and restored

From the root account do:

  show grants for data@'localhost';

If it's empty then re-add the grants. Your dump file probably didn't contact the MYSQL db so it didn't restore the permissions.


How and where do I confirm TCP ports are open when performing mirroring? (Principal, Mirror & Witness)

Thanks to everyone who commented!  Your suggestions were all extremely helpful.  I was able to re-trace my steps and did the following:

Made sure certificates were created for both partners, AND Witness authorized using the correct logins and '.cer' files.

Allowed CONNECT permission on login for remote mirroring endpoint.


How to enable Toolbox window in SQL Server Management Studio?

After your updated comment, I believe my understanding is correct, that the Toolbox was only ever implemented to be used with the Maintenance Plan designer at this point. It's briefly mentioned in the Microsoft Books Online Use the Toolbox. My guess is they may have designed the Toolbox to be flexible should they implement it for other scenarios, but at this point I think Maintenance Plans is the only one, and I don't believe it's (natively) possible to enable it in other contexts of SSMS.
Sorry, that would've been cool. But something pretty similar to what you're trying to accomplish does exist in SSMS, called Code Snippets which that linked article goes through, and shows you how to customize with your own snippets via XML.


Cassandra "Operation timed out - received only 0 responses." during write

You will need to increase commitlog_segment_size_in_mb to a higher value. Try 64MB instead.

It increases the size of the commit record segment. This property determines the maximum mutation size (max_mutation_size_in_kb), defined as half of the segment size. By default is 32MB, which made your record were overflowing the maximum mutation size.

From the docs:

  (Default: 32MB) Sets the size of the individual commitlog file
  segments. A commitlog segment may be archived, deleted, or recycled
  after all its data has been flushed to SSTables. This amount of data
  can potentially include commitlog segments from every table in the
  system. The default size is usually suitable for most commitlog
  archiving, but if you want a finer granularity, 8 or 16 MB is
  reasonable.

  This property determines the maximum mutation size, defined as half
  the segment size. If a mutation's size exceeds the maximum mutation
  size, the mutation is rejected. Before increasing the commitlog
  segment size of the commitlog segments, investigate why the mutations
  are larger than expected. Look for underlying issues with access
  patterns and data model, because increasing the commitlog segment size
  is a limited fix.


How can I improve this slow query in my wordpress site?

'publish' is a string

+ is an _arithmetic operator

So  'publish' + _pad_term_counts()  is probably not anything useful.

How long does each SELECT take?

Please provide SHOW CREATE TABLE and EXPLAIN SELECT and how big the tables are.

Descending index -- Not a problem; MySQL simply scans through the data in reverse order.

However, no index is useful for the one ORDER BY you mentioned -- This is because the GROUP BY is needed first.  Can that be removed?


Input to be printed in different output format

With PL/SQL find the length of string first,use substring to extract each character and print with dbms_output

BEGIN
  FOR ctr in 1..LENGTH('ORACLE')
  LOOP
   DBMS_OUTPUT.PUT_LINE(SUBSTR('ORACLE',ctr,1));
  END LOOP;
END;

or with declaring variables

DECLARE
l_str VARCHAR2(20):='ORACLE';
l_len INTEGER;
BEGIN
  l_len:=LENGTH(l_str);
  FOR ctr IN 1..l_len
   LOOP
    DBMS_OUTPUT.PUT_LINE(SUBSTR(l_str,ctr,1));
   END LOOP;
END;

Here is running pl/sql in sql*plus

SQL> set serveroutput on
SQL> DECLARE
  2  l_str VARCHAR2(20):='ORACLE';
  3  l_len INTEGER;
  4  BEGIN
  5    l_len:=LENGTH(l_str);
  6    DBMS_OUTPUT.PUT_LINE('Output:');
  7    FOR ctr IN 1..l_len
  8     LOOP
  9      DBMS_OUTPUT.PUT_LINE(SUBSTR(l_str,ctr,1));
 10     END LOOP;
 11  END;
 12  /
Output:
O
R
A
C
L
E

PL/SQL procedure successfully completed.

SQL>


Join with comma separated values in SQL Server

You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to change your schema, but so is constantly dealing with the limitations of leaving it like it is.

Anyway, with that said, you need a split function. Since your comma-separated values are numeric, you can get away with a variation on my XML function; there are several others to choose from in this blog post.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1) = ','
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
      SELECT Item = y.i.value('(./text())[1]', 'varchar(8000)')
      FROM
      (
        SELECT x = CONVERT(XML, ''
          + REPLACE(@List, @Delimiter, '')
          + '').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Now, your query is:

;WITH x AS
(
  SELECT s.ROLLNO, s.Name, s.Address, c.CourseId, c.CourseName
  FROM dbo.StudentMaster AS s
  CROSS APPLY dbo.SplitStrings_XML(s.Course, default) AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.item = c.CourseId
)
SELECT ROLLNO, Name, Address, STUFF((
  SELECT ',' + CourseName FROM x AS x2
  WHERE x2.ROLLNO = x.ROLLNO
  ORDER BY CourseId FOR XML PATH,
  TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
FROM x
GROUP BY ROLLNO, Name, Address;

Again, this is a complicated solution, and because of your inferior database structure, the next query you have to perform will be equally convoluted and cumbersome. There's a reason this type of design is argued against in just about every blog, essay or book about the topic...


It depends on how strict you want to be, and on what you can expect from the form.

If all that's needed is the presence of 3 #'s, it would suffice to use

SELECT text_message
FROM incoming_sms
WHERE text_message LIKE '%#%#%#%';

If you want to have at least one character in each field, you can use (as you suggested yourself):

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#]+#){3}[^#]+$';

If you know that leading whitespace is trimmed from each field and you want to ensure you have at least one non-whitespace character, use:

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#\s]+[^#]*#){3}[^#\s]+[^#]*$';


SQL and NoSQL are different in many ways but the gist of it is that SQL is better at relational and NoSQL is better at non-relational. Each technology is suited for different purposes.

There are plenty of existing benchmarks and comparisons between the two such as here, here and here. Be wary of the bias in comparison articles and note that when someone says NoSQL is faster it could mean with less reliability.

To benchmark SQL queries you can look up the following:

SET STATISTICS TIME ON

Or use something similar to this:

DECLARE @DateTimeStamp DATETIME
SET @DateTimeStamp = current_timestamp
... --do some stuff to measure here
PRINT CAST(DATEDIFF(ms, @DateTimeStamp, current_timestamp) AS VARCHAR) + 'ms'

To benchmark MongoDB queries you can look up the following:

.explain("executionStats")
mongodb executionTimeMillis

Here are some comparisons between the two:

SQL

More expensive initial cost
Relational
Better data integrity with transactions ( more overhead )
More common so more developers have experience with it
More proven security
Paid tools for metrics and monitoring such as Solar Winds and Idera
There are likely free tools but I am not aware of them

NoSQL ( MongoDB )

Less expensive initial cost
Non-relational
No transactions ( less overhead )
Asynchronous INSERT and UPDATE
Depending on Write Concern Values it could be faster with less reliability or slower with more reliability
Possibly more scalable
Less proven security
Paid tools for MongoDB server metrics and monitoring such as Server Density.


Find minimum number of columns that identify uniqely all rows

It isn't reasonable to try to do this. I'm going to assume there is no existing primary key / unique constraints existing already. The problems with this approach are:

Data can change at any time. If you found a unique set of values, there is no guaranteeing that they will remain unique
Analyzing the data to find this could be extremely costly from a time/resources perspective and would require extensive customization for each specific table.
Figuring out how many columns actually make up the key by their values is extremely hard (you'd probably have to take a brute force approach) as you can add any x number of columns to a key and still have unique values when looking at them as a set.

In some cases there may be something obvious, like an ID column, which is probably a primary key candidate. However, if no primary keys or unique constraints are defined, it is always best to double check with a data owner / steward.

In most cases, context, the business, and/or requirements will define what unique keys are. It is not something I'd ever advise inferring just from the data unless you are extremely familiar with the data domain and even then you should refer to a subject matter expert to validate.


As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the select?

The optimizer is very good at removing subtrees that compute expressions that are not needed in the final result (top-level projection). When you remove the value from the select list, the work needed to compute that value is simply not done.

  Further, if the above is false, why would my function itself be faster but run significantly slower when used inside of a query?

This is difficult to assess in detail from an anonymized plan. Nevertheless, removing the scalar T-SQL functions allows the optimizer to consider parallel plans. You might like to test your rewritten query with an OPTION (MAXDOP 1) query hint to see how the serial plan selected compares with your original.

Parallel plans are not always better (though they are only selected if they appear to be lower cost to the optimizer). Your case has a relatively low-cost, so the optimizer does not believe it is worth exploring a tremendous number of alternatives. There are cases where the time spent considering both serial and parallel plans is counter-productive to final plan quality.

I do apologise if this is a little vague, but anonymized plans really do make it tough to be specific. All things being equal, in-line functions will currently out-perform scalar functions. Sadly, all things are rarely equal.


Load balancing incoming data using offset / increment vs group replication

Any multi-master setup has this limitation:  Every row inserted on the original master must also be inserted on each other Master (and each Slave, if you have them).

That is, without "Sharding", you cannot get much more write scaling than with a single Master.  (Read scaling is a different topic.)

Let me backtack a little bit.  If the Master needs to do more prep work than actual INSERTing, then having more Masters is beneficial.  (This does not sound like your situation.)

Yes (to one thing) -- you can do some load balancing with any multi-Master setup (Group Repl, Galera, M-M) -- both writes and reads.  It usually requires a proxy between the clients and Masters.  I strongly recommend a simple round-robin algorithm; other algorithms can't measure the "load" fast enough to make a difference.

All(?) multi-Master setups (assuming you use AUTO_INCREMENT) depend on auto_increment_increment being at least the number of Masters.  (So, 'yes' to your note about offsets and increments.)  But, that does not necessarily deal with other unique keys, or with normalization ids.

Give us some numbers.  You make it sound like you are inserting a terabyte every few weeks?  There are a lot things that need discussing is such a situation (SSDs, network bandwidth, contention, RAID, etc).  How many rows per second?  How much prep work?  Are you already "batching" the INSERTs?

Here are my recommendations High Speed Ingestion .


BigQuery - load data with changing schema

I'm reading your question as "how to detect schema changes in in-coming data and automatically change out-going data to match?"

Obviously, you're going to need to examine the meta-data of the in-coming records. Since its JSON this should be easily handled by whatever technology you use to accept these records. Then you'll need a local store holding the meta data your application is already aware of. As JSON is a tree structure your local meta data store will model a tree. As records with new schema are detected this local store can be updated.

You will need a second local store to hold the structure of your output document and a third to map the input fields to output fields.

The next three parts cannot be automated. If there are transformations from input to output a human will have to write them. Where in the output document structure the new values should appear will also have to be hand-written, as will the mapping from input to output.

My feeling is this will be horribly inefficient. It may be possible, depending on your technology, to use reflection to alter the run-time definition of your output class to accommodate new columns. Good luck.


Call Back In Union All Within the With-As-Table Itself

This is a Recursive Common Table Expression or Recursive CTE.

References:
Official documentation for SQL Server
(they are supported by other databases too)
A tutorial and another one, both from a very quick Google search so you might find better with more effort now you know the right terms to search for.


How to connect to remote PostgreSQL server

I suppose the simplest way is to let the tester use ssh to login to your server. Then the tester's execution environment is whatever your server exposes through the shell. (I presume that would include psql.)

As far as I know, all modern Unix derivatives install ssh by default. That would include Linux, Solaris, OS/X (Apple), and BSD. I believe that Windows does not ship with ssh, but Cygwin and PuTTY are commonly used to remedy that problem.


Difference between an Entity and a Relation

"Entity" is a real world concept, such as "a person" in your example.

"Relation" is a set of tuples (records), each representing a model of an entity. In your example each tuple represents attributes of a single person, and their collection is a relation.


Use backup file in order to recovery your data.


Index Strategy for MySQL Query that matches on one column and between two others

(Not an answer, but too clumsy for a comment.)

  have an index on process_group_id, start_timestamp, end_timestamp. However the query does not appear to use anything but the process_group_id part of the index.

It is actually using start_timestamp although it does not say so.  What was the key_len?  That may give a clue.  Also try EXPLAIN FORMAT=JSON SELECT ....

AND  1431388800 BETWEEN start_timestamp AND end_timestamp

Turn that into the following to see if it helps:

AND  start_timestamp <= 1431388800
AND  end_timestamp   >= 1431388800

I suspect it is identical, but I am not sure.

Caution.  The difference between 0.5s and 0.06s could a warmed up cache.  Run timings twice.  Also, use SQL_NO_CACHE to avoid the Query cache.

How wide are the timestamp ranges typically?  How precise is 1431388800?  The values sound like they have a resolution of 1 second.  What if we switched to 1 minute or 1 hour?

After you provide some answers, I will possibly suggest turning this into a Data Warehouse application and discuss Summary tables.

Edit

Consider this approach to storing the data.  (I still don't have enough details to determine what variant of the following would be optimal.)

Since you have a "processing" phase that leads to the table in question, I suggest rewriting it to store into a different table (either in place of the existing one, or in addition)

CREATE TABLE ByMinute (
    process_group_id ...
    ts TIMESTAMP NOT NULL, -- rounded to the minute
    sum_1 FLOAT  -- see below
    sum_2 ...,
    PRIMARY KEY(process_group_id, ts)
);

The table contains one row per minute.  That's 0.5M per year, not terribly big.  If converting from the existing structure do sum_1 += measurement_1 for each row BETWEEN starttime AND endtime.

That is possibly more processing than you are currently doing, but it should not be excessive.  And it makes the SELECT extremely efficient:

SELECT  sum_1, sum_2, sum_3, sum_4
    FROM  ByMinute
    WHERE  process_group_id = 5
      AND  ts = somehow round 1431388800 to a minute

You currently have a daily dump.  The processing should be obvious.  If you switch to "streaming" and use the ping-pong method I mentioned, then very similar code can be used for each transient table.  And you would probably have nearly up-to-the-second data all the time.


What reasons are there to stop the SQL Server?

Brent listed some invalid reasons for stopping the service, but there are valid reasons too:

Restart required by a service pack or other update
Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
In a cluster, a restart to force a failover or applying a rolling patch
Windows patching


Commercial database systems versus open source database systems

This really depends on your requirements. Many systems built on top of open source products are rock solid.

Note that your example does not impress me - Twitter is not reliable at all by my customers' standards - it has frequent downtimes, it loses tweets and followers, its search is not 100% correct etc. I work in finances, and my job is to develop robust and performant systems. In some (but not all) of the scenarios I encounter, open source PostgreSQL could get the job done just as well as closed source Oracle, in terms of speed and robustness.

If your needs are more or less standard, than you can save a lot of precious time if you go for Oracle or SQL Server, as you might spend less time on setting things up and maintenance. As such, your investment in licensing costs may pay off quite soon.

If, however, your needs are not completely mainstream, then open source is your friend. Open source products can innovate much faster. You don't have to persuade someone that your scenario is not "convoluted", you can get a fix right away instead of waiting for years. If you really need something, you can do it yourself.


You can use a recursive CTE to fetch Id at parent' levels:

DECLARE @Id int = 4;

WITH cte(level, Id, ParentId) AS(
    SELECT 0, Id, ParentId FROM @data WHERE id = @Id
    UNION ALL
    SELECT c.level+1, d.Id, d.ParentId FROM cte c
    INNER JOIN @data d ON c.ParentId = d.Id
)
SELECT Id, Level FROM cte
;

Output

With @Id = 4, This query returns the path with several Ids:

Id  Level
4   0
2   1
1   2

It only returns Ids on the path from the root up to the Id store in @Id. This is probably all is needed. I didn't try to update the @data table.

However this can easily be done by replacing the SELECT by an UPDATE:

WITH ...
...
UPDATE @data SET [UseRecord] = 1
WHERE Id IN (SELECT Id FROM cte)
;

SELECT * FROM @data;

Notes

It is uneasy to say whether it will be efficient and fast. There are not enough information and details in the question (data model, number of rows, quantity of data, indexes, etc.).

It is worth mentioning that it is probably a bad idea to update the temp table and it may also be a bad idea to use a temp table in the first place.

Perhaps the same result can be achieved with a single query (SELECT with cte) without creating a temp table. It could avoid tons of IO (read and write the temp table X times). The above query is a starting point anyhow.

Data used

DECLARE @data TABLE(Id int, ParentId int, [text] varchar(5));
INSERT INTO @data(Id, ParentId, [text]) VALUES
    (1, NULL, 'AAA')
    , (2, 1, 'aaa')
    , (3, 2, '111')
    , (4, 2, '222');

Replace @data by your temp table name in the query.


MySQL: Skip INSERT / UPDATE statement that triggers error but show them in output

it is 2 different problem

1st is possible errors, for this You can use --force option

mysql -u user -p --force database <

and 2nd is speed

for speed You can check server configuration and adjust it if necessary:

memory (innodb)
size of redo log file, default is too small, You can increase it

because You are already have dump file - no reason discuss about single transaction or group transactions.


SQL table with same name as catalog view

I also generally do my best to avoid using reserved words for user created objects, but sometimes it's really hard to avoid. Some very common terms are reserved words (e.g. User and Login, etc), and so sometimes being forced to avoid them at all cost means you need to choose a less meaningful, less intuitively obvious name for your project, and that really does not benefit your project or company.

So the only real "gotcha" is what you need to do in order to prevent the conflicts: always delimit object names that are reserved words (i.e. always specify the names surrounded by [ and ]).

Now, not all words that change color in SSMS will be a technical conflict, but that doesn't mean that you shouldn't delimit them. Those words could become a technical conflict in a future version, and that could complicate (i.e. increase the time, hence cost of) upgrading.

A simple example:

USE [tempdb];
CREATE TABLE dbo.Select ([Col1] INT); -- "Select" = blue
/*
Msg 156, Level 15, State 1, Line XXXXX
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near 'INT'.
*/

CREATE TABLE dbo.[Select] ([Col1] INT);
-- Success

CREATE TABLE dbo.Event ([Col1] INT); -- "Event" = blue
-- Success

CREATE TABLE dbo.Events ([Col1] INT); -- "Events" = green
-- Success

Again, even though "Events" isn't required to be delimited, if you do use it, I would highly recommend that you always specify it as [Events] to guarantee no conflicts.


(Not yet an answer, but some more steps toward understanding the task enough to provide an answer.)
2.5 billion rows will fit on a single machine.  (Perhaps 400GB, including ancillary tables.)  Have you determined that the activity will exceed a single server's capabilities?

Which table is the biggest?  (Please list the 3 largest and their projected sizes.  Also, please provide the full SHOW CREATE TABLE for each of them -- We need to consider shrinking their datatypes.)
Which column(s) in that table seem reasonable for sharding?
If you shard on that column, what other tables will also be split up?
Ignoring for the moment, provide the 6 most common SELECTs with JOINs and multi-table Updates/Deletes.
Which tables will be needed for JOINs?  Are they small enough to simply maintain copies of them in each shard?  How complex will that maintenance be?


There are several things you could try:

DELETE Style #1 : LEFT JOIN

DELETE e.*, m.*
FROM `entries` e
LEFT JOIN `magnets` m
    ON m.`eid` = e.`id`
WHERE e.`id` = %d

DELETE Style #2 : Two Independent DELETE queries
(without FOREIGN KEY defined)

DELETE FROM entries WHERE id = %d;
DELETE FROM magnets WHERE eid = %d;

DELETE Style #2b : Two DELETE queries : the order matters
 (when there is a FOREIGN KEY defined between the two tables
 with the ON DELETE NO ACTION property)

DELETE FROM magnets WHERE eid = %d;
DELETE FROM entries WHERE id = %d;

DELETE Style #3 : One DELETE query
 (when there is a FOREIGN KEY defined
 with the ON DELETE CASCADE property)

DELETE FROM entries WHERE id = %d;

Give it a Try !!!


Search for mysql-related packages that are now installed: rpm -qa | grep -i mysql

Delete any packages like mysql, mysql-server, mysql-client, mysql-shared, mysql-shared-compat, mysql-libs. yum remove mysql-server You do not need to remove mysql library bindings like python-mysqldb, php-mysql, etc., unless they had a dependency with some of the previous packages.

I recommend you to install the Oracle MySQL yum repositories, that will make the installing and maintenance much easier, unless you know what you are doing and want more control over your packages. An installation guide is detailed here: http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/index.html Basically, it involves creating a .repo file and yum update.


SSMS How do I open a new Query Window using Same Connection Instance of current Window

If I'm doing something wrong or missing some Point please let me know.

You're not missing anything.  SSMS always has a separate session per tab.

  it's SUCH a pain to run a script... and then from that same tab have
  to then write in other code when I don't want to edit/add any other
  code to the page that contains the script I ran.

In SSMS the Execute command (F5,Ctrl-e), always runs the highlighted text in the query window, if there is any.

And so the normal way people use SSMS query windows here is to highlight sections of code to run, often separating the main script from the test/debug bits by using multi-line comments.

For instance when developing a stored procedure, then query window might look like:

create or alter procedure SomeProc @param int
as
/*
  declare @p int = 12
  exec SomeProc @p
*/
begin
  ...
end

Then run the whole script to recreate the proc, and highlight the test harness lines and run to test it.


Create table shows ERROR 1005(HY000) errno 150

You are missing column name of parent table to which your child refer, while creating child table, do like below

create table department
(dept_name      varchar(20),
 building       varchar(15),
 budget             numeric(12,2),
 primary key (dept_name)
);

create table course
(course_id      varchar(8),
 title          varchar(50),
 dept_name      varchar(20),
 credits        numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department(dept_name)
);


Would there be a reason not to make primary keys unsigned?

Reasons you may be given include:

Suspicion that the unsigned type may not be portable, even among other non-portable code and even if no one's contemplating porting
Fear that since it deviates from the default it has the potential to distract, while not being strictly necessary, leading potentially to programmers' waste of time or errors
Fear someone may omit unsigned when copying it to other structures (ETL) and not realize the error until eventually the numbers do get over the other half of the range; that may take years and blow out much later
Unexpressed, vague fears that something might break, down the line, whether when not handled properly (in one's own codebase), or even if handled properly, if all the rules that make it work aren't clear (in the extreme, this may include fear of an error in the database engine itself)
Challenge from seniors, managers, or just people who like to challenge, who are in a position to stop things from happening by challenging them. This group might adduce it's not in the coding conventions to use unsigned, and insist you produce a very powerful reason if you wish to add it to the coding conventions. It won't be easy to prove your reason is strong when you're the only one against others who say otherwise and surrounded by a third group who just don't care.

Or, if you advocate to use the negative half of signed numbers:

Dislike of negative numbers / confusion
Adduce formatting problems. 'What if these needed to be printed? Would they be printed properly?'
Pose a valid question: What happens as the ID assign process has exhausted the negatives and hits 0? Will ID=0 be correctly handled by the application and downstream processes like any other ID's, or will they think it's special?

If you promote things that aren't the norm in a group without building a sound reputation first, you risk people attacking your initiatives and your good reputation will be harder to build and you might even get a bad reputation, even if you have done nothing wrong, or said or caused no difficulty, or been a factor or co-factor of anything gone wrong.

If you contest this, the powers that be will attack you also, and keep you in check, they'll get the 'your attitude' word out and you'll be down in no time ('opinion based' is such another key word they may use when referring to you or your contributions).

The problem isn't negative numbers or unsigned, it's group-thinking, and enforcement of 'normal' by people who set the rules, whoever they are and whether they truly know or not. Of this the database world is full, just as any other technical field where lots of males compete for preeminence and power.

You see, if this was merely a technical problem, it'd be easily solved. I'm not saying easily shared, but at least easily solved. It'd just require your skill, attention, carefulness, thoroughness.

Unfortunately as things depart from the average and even if they are 'better' in a sense, they become a human 'problem'. Now to solve it you need acceptance, patience, negotiation, persuasion, and a tad lots more. Things that are even harder to fix than technical problems.

Departures from the norm can be very good at times, but at times they can shake the ground and test the individual differences and assumptions and presumptions and the group's cohesion and their beliefs and culture and become factors on where the group is going, and its members. The dynamics between the technical and the human are complex, often we won't be able to say what's preferable, short term, long term; we won't even understand it all or have the same opinion.

A little technical issue, recipe, option or preference can be just nothing, or it can become the tip of the iceberg (for a majestic sail, or a ship wreck), or like stroking a genie lamp. We're all a box of surprises and you don't know what's behind the door until you press that button.


macOS homebrew: database system was not properly shut down; automatic recovery in progress

It looks like "brew services stop postgres" is sending a signal to shutdown, and then following up 5 seconds later with a hard-kill.  That seems like a rather hostile thing for it to do, but I have zero experience with brew.  Maybe that is configurable?

But the other question is, why does it take more than 5 seconds to shut down?  Unless you have a lot of dirty data in memory which needs to be written out, shutting down should be pretty quick.  When you tried to shutdown with the pg_ctl command, what ended up in the server log file after that attempt?  When pg_ctl reports that the shutdown failed, that just means that pg_ctl got sick of waiting for it, it doesn't mean the shutdown permanently failed (i.e. it doesn't shoot the postmaster in the head the way brew seems to).  So you can keep checking the log file even after pg_ctl reports a failure.  You can use the -t option to make pg_ctl more patient.

Also, you should turn log_checkpoints=on, and then try the pg_ctl stop -t 3600 ... again and look in the server log files.


Unusual column comparisons and query performance

I don't like ISNULL with sentinel values, it requires picking values that can't ever legitimately appear in the data now or forever after and personally I find expressions containing these more difficult to reason about.

For your test rig I tried four different ways of expressing the query and got the stated results.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  r.hash <> l.hash
        OR ( r.hash IS NULL
             AND l.hash IS NOT NULL )
        OR ( l.hash IS NULL
             AND r.hash IS NOT NULL )

  SQL Server Execution Times:    CPU time = 30968 ms,  elapsed time =
  8230 ms.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  ISNULL(r.hash, 0) <> ISNULL(l.hash, 0)

  SQL Server Execution Times:    CPU time = 31594 ms,  elapsed time =
  9230 ms.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  EXISTS(SELECT r.hash
              EXCEPT
              SELECT l.HASH)

  SQL Server Execution Times:    CPU time = 46531 ms,  elapsed time =
  13191 ms.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  NOT EXISTS(SELECT r.hash
                  INTERSECT
                  SELECT l.HASH)

  SQL Server Execution Times:    CPU time = 23812 ms,  elapsed time =
  6760 ms.

So on that basis the last one would be a clear winner - along with a code comment with a link to Undocumented Query Plans: Equality Comparisons for anyone unfamiliar with the pattern.

But you should test whether this pattern is reproducible with your actual MERGE query too.


How to Replace Hash Match (Partial Aggregate) with Stream Aggregate

Not a lot of information here, but to directly answer the question that was asked: you can use the ORDER GROUP query hint. From the documentation:

  { HASH | ORDER } GROUP

  Specifies that aggregations described in the
  GROUP BY, or DISTINCT clause of the query should use hashing or
  ordering.

That query hint may help you verify that a stream aggregate is a better choice than the hash aggregate. A few things to keep in mind are that it applies to the whole plan and it will continue to take effect even if there's an index change which means that an explicit sort is required.


Will dropping text column on SQL Server 2014 lock the table?

I performed the DROP command and it was instantaneous.

I did not attempt to wrap the DROP and ALTER PROC in a transaction however.


Mariadb Galera Cluster using Xtradb backup is not working

In my experience, it seems one failed run can leave fragments of config that block future runs.

Case in reference: Xbstream rather than tar is used, although the traceback is consistent in that it halts on writing to backup-my.cnf.

First run fails because my_print_defaults is reading from the wrong file, and this file was stored as the backup.

Once the --defaults-file flag is added to innobackupex, suddenly there is output on the other side of the xbstream.

xbstream: Can't create/write to file '././backup-my.cnf' (Errcode: 17 - File exists)
xbstream: failed to create file.

I believe this is an innobackupex safety feature and tar may be making the same checks as xbstream. I would have liked to comment on this rather than answer it but lack the reputation.


SQL Server 2014, Volumes in Resource Governor

It seems reasonable to assume, per the documentation and indeed the names of the parameters themselves, that MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME control the maximum quantity of I/Os per second per volume.
i.e. as in your question, if you have three volumes in the resource pool, and set MAX_IOPS_PER_VOLUME to 300, then you'd have a maximum total IOPS of 900.
Without source-code-level access to SQL Server, it's impossible to say for certain if that's how it actually works, however the documentation says (emphasis mine):

MINIMUM_IOPS_PER_SECOND:
Specifies the minimum I/O operations per second (IOPS) per disk volume to allow for the resource pool.
MAXIMUM_IOPS_PER_SECOND:
Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool.


Indexing for a table with many large subsets

Does it make sense to put an index on that column and if so which type of index should be used?

We can't answer that for you. It depends on if query performance is good enough without the index. If end user response time, transactions per second, overall server CPU, or whatever metric is the most important to you is fine without the index then you don't need it right now. You may need it in the future if the table grows bigger. With all of that said, the current query and table structure that you have won't give you the best performance. If an improvement is needed then you have three main options:

Create a nonclustered primary key on the id column and a clustered index on Category.
This will give you the best possible performance for the query that you listed in the question. The downside is that changing the clustered index may have negative impacts on other queries. Clustered indexes do not need to be unique. You also do not need a primary key on your table. I just proposed an example schema that had both (I assume that you have a primary key today).

Add a nonclustered index on Category that includes all other columns.
This will also give you the best possible performance for the query that you listed in the question. As a downside it will double the disk space required for the table and will slow down DML operations on the table.

Add a nonclustered index on Category that doesn't include all other columns.
This won't give you the best possible performance. It will improve performance as long as you're filtering on a value that's selective enough and SQL Server chooses to use the index. If you need to help the optimizer along, the techniques in this blog post by Distinguished Answerer Erik Darling may be helpful. There's still a DML impact to this option, but it should be significantly less than option 2.

Ultimately the only way that you can get a satisfactory answer is by looking into it yourself. We don't know what kind of importance that you put on DML performance, total disk usage, the performance of other queries, etc. All of the options that I mentioned above have their own drawbacks. Good luck!


Why is .bak file size increasing/doubling with each consecutive backup on SQL Server?

The backup file contains multiple copies of the database since it is not being "initialized" or "formatted" with each backup.

Go to the 'Options' tabl on the "Back Up Database" dialog box, and select the "Overwrite all existing backup sets":


How to get the greatest element of a GROUP BY?

Probably easiest to do with a join against a derived table:

select a.*
from PurchaseOrders as a
join (
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b
   on a.sku = b.sku
   and a.date = b.date;

The derived table b contains the max dt for each sku. To get the id we join that against the original table.

As Evan Carroll points out you can shorten this a bit since the name of the join attributes is the same for both left and right operand:

select a.*
from PurchaseOrders as a
join (
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b
   using (sku, date);


How to get SSIS to stop waiting/processing at task above time limit?

You can set timeouts on tasks; I don't know if you can on steps.


is it possible to show the dump history of MySQL 5.7

No, there is nothing in MySQL that stores mysqldump story automatically.
From the MySQL point of view, mysqldump is just another connection.


Which index types do not support online index rebuilds?

This is in the documentation:

  Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text.

  Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

  Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

Other limitations and considerations are listed as well.


sys.dm_sql_referencing_entities not returning results

Referenced/referencing entity functions return instances where an object, column etc. is referenced directly (usually in other objects like stored procedures, but Mark has the full list from the documentation).

These functions are not meant to analyze foreign key dependencies, which you can get from the foreign key catalog views. This simple query assumes all tables are in the dbo schema, but you would need to add joins to sys.schemas in order to flesh those out otherwise:

SELECT 'FK points from ', f.name, ' to ', t.name
  FROM sys.foreign_keys AS fk
  INNER JOIN sys.objects AS f
    ON fk.parent_object_id = f.[object_id]
  INNER JOIN sys.objects AS t
    ON fk.referenced_object_id = t.[object_id];

The query in your question will return results if you create an object that references the table, e.g.:

CREATE VIEW [Test].MyView_DeleteMe
AS
  SELECT * FROM [Test].Users;


Is there a way to query all error messages in Postgres?

It looks like there is no easy to use SQL to query error codes inside Postgres. However, the source does contain the following file:

src/backend/utils/errcodes.txt

Which has the code listed in an easily parsable format:

01000    W    ERRCODE_WARNING                                                warning
0100C    W    ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED                   dynamic_result_sets_returned
01008    W    ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING                      implicit_zero_bit_padding
01003    W    ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION          null_value_eliminated_in_set_function
01007    W    ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED                          privilege_not_granted
01006    W    ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED                          privilege_not_revoked
01004    W    ERRCODE_WARNING_STRING_DATA_RIGHT_TRUNCATION                   string_data_right_truncation
01P01    W    ERRCODE_WARNING_DEPRECATED_FEATURE                             deprecated_feature


ANSI_NULLS and QUOTED_IDENTIFIERS mismatch

so how does SQL determine whether to define on or off for the ansi nulls by default?

BOL quotes - For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

You can use the sys.sql_modules to check  the stored procedure or function uses uses_ansi_nulls and uses_quoted_identifier settings 1 (ON) or 0 (OFF)

SELECT OBJECT_NAME([object_id]) as ObjectName,
       uses_ansi_nulls,
       uses_quoted_identifier
FROM sys.sql_modules
--WHERE OBJECT_NAME([object_id]) = 'any_sp/function_name_your_Want_to_Check'
ORDER BY object_id

Both ANSI_NULLS and QUOTED_IDENTIFIERS should be ON so that you dont see any unexpected surprises. Also, in newer versions of SQL Server, this two settings are defaulted to ON.


If I had to guess I'd say that the datatype of transactionnotes does not match the datatype of code. I suggest trying ...

=iif(Fields!itemid.Value = 100785,
    "Allocated to: "
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!fname.value, "EmployeeInfo")
    & " "
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

or perhaps it should go the other way using CStr() instead of CInt() for both transactionnotes and code. Try it out and see.


Creating a database structure for a quiz

The correct answer is the property of the question.

Thus, the question table should be improved by a column named correct_answer_id, which will be a foreign key to answer(id_answer).

We can see some similar in the database of the Stack Exchange - here the accepted answers are in the AcceptedAnswerId column.

This structure has a possibility of inconsistence - nothing guarantees that the correct_answer_id refers to an answer which belongs to the current question. Ideally, you should use contraints to ensure that.


MySQL 5.6 events are created disabled and they don't run

You didn't specify the interval

create event p1
    on schedule
    every week
    at now()
on completion preserve
enable do call proc1();

I have several examples of how to set up events

BTW make sure you enable the croning mechanism in my.cnf by adding this line

[mysqld]
event_scheduler = 'ON';

You don't have to reboot. Just run this

mysql> SET GLOBAL event_scheduler = 'ON';

As the last step, you can create the event.


You can wrap a CASE inside the CAST(), so the CAST() is only performed on valid numeric values - the other ones will result in NULL unless you put an ELSE in there somewhere.

Also, your conversion error probably stems from the WHERE clause, where q.bar is implicitly converted to int in order to compare it to 20140401 (which is an int).

This will probably solve your problem:

SELECT q.foo, CAST((CASE WHEN q.bar NOT LIKE '%[^0-9]%' THEN q.bar END) AS int)
FROM ( SELECT foo,bar FROM table) as q
WHERE q.bar > '20140401';


Sure, you can use row-level locks to write-lock all rows currently in the table with:
SELECT * FROM tbl FOR UPDATE;

Or similar.
But that prevents all writes, including DELETE, not only UPDATE - for the duration of the lock. And there is the remaining question about rows inserted after that: shouldn't those be protected from UPDATE, too?
The way you phrased it ("INSERT should be allowed, but UPDATE should be disabled."), it sounds like a more general matter of privilege management with GRANT and REVOKE, rather than locking.
Something like:
GRANT INSERT ON TABLE public.tbl TO public;
REVOKE UPDATE ON TABLE public.tbl FROM public;

Or:
GRANT INSERT ON TABLE public.tbl TO specific_role;
REVOKE UPDATE ON TABLE public.tbl FROM specific_role;

The actual commands depend on who has been GRANTed privileges in the first place, and who shall be prevented to update now.
Don't forget the special case of the table owner. The manual on GRANT:

There is no need to grant privileges to the owner of an object
(usually the user that created it), as the owner has all privileges by
default. (The owner could, however, choose to revoke some of their own
privileges for safety.)

While locks only last for the duration of a transaction, the effect is permanent - until changed explicitly. Also, locks are expensive. GRANT / REVOKE is almost cost-free.
You need the necessary privileges. Either be the owner of the table, be member in the role owning the table, be a superuser, or you have been granted the same privilege WITH GRANT OPTION.


Is it possible to downgrade oracle 11.1.0.6.0 to 10.2.0.4.0?

Yes, it is possible, as stated in the 11.1 documentation:

Supported Releases for Downgrading

Oracle mentions 10.2.0, because ideally you need not to set anything more specific (more digits). This is explained in:

How To Change The COMPATIBLE Parameter And What Is The Significance? (Doc ID 733987.1)

  Use only the first 3 digits for the compatible parameter unless there
  would be some very specific instructions to do otherwise.  Patch sets
  are normally not intended to add/change actual functionality, hence
  there is no need to increase the compatible parameter and set the 4th
  digit.

If you set it to 10.2.0.4.0, the downgrade should be still successful to version 10.2.0.4, but not anything lower.


Is this normal binary logging behaviour - maintaining one ever growing file despite expire_logs_days?

If the file hasn't exceeded max_binlog_size or if a log flushing event hasn't occurred then it won't start a new file. expire_logs_days only removes non-active binary logs (i.e. files other than the one that shows up under a "show master status;").


TPC-DS query generator does not generate all queries

Try the -VERBOSE option to see if there are additional information generated by dsqgen.
Also, the version of templates downloaded from tpc.org are missing some DEFINES.


Postgres privileges: psql \dp - what is about the plus-sign

That plus sign is the indicator that the data spans more than one column. You'll get that with other multi-line values as well, not just the output of \dp


What to consider when generating rows via cross joins vs recursive unions?

Community wiki answer- please edit to improve:

Depending on how many rows you're generating, the second query may be slower as the recursive part of your CTE will be forced to run serially.

The recursive CTE solution may also have poorer cardinality estimates, because SQL Server finds it difficult to assess how many rows will be produced.

The query optimizer recognizes that the results of the first query are already sorted but does not recognize that for the recursive query. This means that using the recursive approach in a query that benefits from sorted data may lead to an unnecessary sort.

The recursive CTE as is can only generate up to 101 rows before hitting an error. To get a larger result set you need to add a MAXRECURSION query hint.

As an aside, regardless of which method you prefer, if you terminate every statement with a semicolon, you don't need to put another semicolon at the beginning, i.e. it's all right to have with … instead of ;with ….

Related:

SQL, Auxiliary table of numbers
What is the best way to create and populate a numbers table? on Stack Overflow