Should the referencing table have primary key, auto increment, or index?

You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:

* Articles
* Tags

Next step is to think about what uniquely identifies a row (tuple) in  Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc. You should still declare them as UNIQUE though.

Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:

CREATE TABLE Articles
( article_id ... not null
, article_name ... not null
, publishing_date date not null
, ...
,    primary key (article_id) -- surrogate key
,    unique ( article_name, publishing_date ) -- natural key
);

CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);

How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometimes named junction or link table). Let's call this table:

CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)

);

Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:

CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
,     primary key (article_id, tag_id)
);

and not add an additional surrogate key (I'm not even sure what the purpose of such key would be, but far too often I see it added in a design by blindfold fashion). With the suggested composite key, you are guaranteed that the same tag won't be used more than once for each article.

If you sometimes want to retrieve all articles for a given tag you can add an index like:

CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);

EDIT: some info on indexes versus keys

An index is a physical concept and is in fact not even mentioned in the SQL standard, meaning each vendor can implement them as they please, whereas a key of some kind is a logical concept. Note that in some DBMS there's a fuzzy line between these two concepts, for example, it is possible to have a foreign key reference a unique index in SQL-server, and MySQL call indexes keys.

Most DBMS:s (and everyone I know of) implements logical keys (UNIQUE, PRIMARY KEY) with physical indexes, so if you add a key, an index is created under the hood. The following keys are redundant:

PRIMARY KEY (a,b) <=> UNIQUE (b,a)

since if (a,b) is unique, so is (b,a). It is a fat chance that your DBMS won't let you create one if the other is present. On the other hand:

INDEX (a,b) is not redundant with INDEX(b,a)

since a query like:

SELECT ... FROM T WHERE a = ?

can use the first index to efficiently find rows that match, but the second index can't. You can think of it as if you have an index organized as first-name, last-name and you try to find someone with a last-name Gates or Thorvalds. You basically have to scan through a lot of first-names before you find all persons with those last-names.

So why not create one single column index for every column used in searches? Many DBMS can do INDEX ANDING (but some can't) and it is anyhow less efficient than using one multi-column index.
Once again you can compare it with a process of first looking at an index for first-name and find out that there's a Bill on page 14, 50, and 126. Then look at another index for last-name and find out that there's a Gates at page 21, 50, 123 and 212, and finally intersect those two sets to conclude that Bill Gates can be found at page 50. Compare with an index for first-name, last-name where you find Bill and then can find Gates very easily.

You can use a multicolumn index a,b,c,d,e,f for finding a, ab, abc, etc, but it won't be very efficient if you look for WHERE d=? and f=?.

The above is much simplified but may give you an idea or two on how to start thinking on the design process.


Count number of duplicate rows, sort from most to least (statistic)

Have you tried something like:

SELECT COUNT(url) AS My_Count
FROM tablename
GROUP BY url
ORDER BY My_Count DESC
LIMIT 10 ;


CentOS and Ubuntu differences with information_schema

I see two possible reasons:

Your installation script is not correct and failed to create the tables correctly
The CentOS installation does not have InnoDB installed (or activated). MySQL will not tell you about it if you request a non-existing storage engine (or at least not loud and clear)


Cant connect to Server using SSMS on my desktop

Try to run telnet from your local machine and use the following from command line

telnet [servername/ip] port

ex:> telnet sql01 1433

This will at least verify that you can hit the server on the specified port. As well your local machine could have a firewall enabled or perhaps a 3rd party application that could be blocking your access?

You can consider it a successful test if after pressing ENTER your command window goes blank. (It has successfully started the telnet session - type some random characters to go back to your normal DOS prompt).

A failure would look like this:

  Connecting To sql01...Could not open connection to the host, on port
  1433: Connect failed


TRC: Select an ID if it appears on all measurements

I had to look up wikipedia because I did not know Tuple Relational Calculus. I did only  skim over that article. There are differnces in notation as far as I can  see.

But after activating my knowledge about formal logic I think you are right because

∀r ∈ Rainfall (...)

is true if Rainfall does not contain any tuples. In this case your query reduces to

{ t | ∃s ∈ Station (t[station_id] = s[station_id]}

and this describes all tuples of Station

What I am missing in your notation is that is unclear which attributes the tuple t of the solution contains. So if you want to select station_id  then the query should start with something like

{ t: {station_id} | ...}

if one uses the notataion of the wiki article.

Maybe you can supply some reference to your notation.


Org chart CTE with exact index but plan shows expensive sort node

Actually Erik's comment above was the most probative.
Creating a better index cut the time from 5 hours to a little over 3.
The other thing that made an even bigger difference was not updating columns on the main table; rather, I directed the hierarchy ids off into a normalized table instead.  That cut the time from a little over 3 to a little over 1


Insert characters into entire row and union with continue of row_number

You could use GROUP BY GROUPING SETS and the GROUPING() function:

SELECT
    idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id),
    a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END,
    number = COUNT(*),
    value = SUM(amount)
FROM #t
GROUP BY GROUPING SETS ((a_id), ())
ORDER BY idx ;

In older versions (that don't have GROUPING SETS) you could use ROLLUP instead. In even older versions (or any version if you prefer a simpler query), you could split the query into 2 parts and UNION ALL with:

SELECT
    idx = ROW_NUMBER() OVER (a_id),
    a = a_id,
    number = COUNT(*),
    value = SUM(amount)
FROM #t
GROUP BY a_id

UNION ALL

SELECT
    1 + COUNT(DISTINCT a_id),
    a_id,
    COUNT(*),
    SUM(amount)
FROM #t

ORDER BY idx ;

Tested at rextester.com


What is VPS\example.com_web identity in SQL server?

This would be either a user generated and used in your application, or an AD group a user in your application is a part of, or a special AD group your application is reliant on while running in IIS. It has nothing directly to do with SQL Server. You may be able to find better information out on ServerFault.com or possibly StackOverflow.com.


Constraint to enforce "at least one" or "exactly one" in a database

You don't need triggers or PL/pgSQL at all.
You don't even need DEFERRABLE constraints.
And you don't need to store any information redundantly.

Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, but using data-modifying CTEs we don't even need that.

This enforces exactly one active email per user at all times:

CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text NOT NULL
, email_id int NOT NULL  -- FK to active email, constraint added below
);

CREATE TABLE email (
  email_id serial PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE
, email    text NOT NULL
, CONSTRAINT email_fk_uni UNIQUE(user_id, email_id)  -- for FK constraint below
);

ALTER TABLE users ADD CONSTRAINT active_email_fkey
FOREIGN KEY (user_id, email_id) REFERENCES email(user_id, email_id);

Remove the NOT NULL constraint from users.email_id to make it "at most one active email". (You can still store multiple emails per user, but none of them is "active".)

You can make active_email_fkey DEFERRABLE to allow more leeway (insert user and email in separate commands of the same transaction), but that's not necessary.

I put user_id first in the UNIQUE constraint email_fk_uni to optimize index coverage. Details:

Is a composite index also good for queries on the first field?

Optional view:

CREATE VIEW user_with_active_email AS
SELECT * FROM users JOIN email USING (user_id, email_id);

Here's how you insert new users with an active email (as required):

WITH new_data(username, email) AS (
   VALUES
      ('usr1', 'abc@d.com')   -- new users with *1* active email
    , ('usr2', 'def3@d.com')
    , ('usr3', 'ghi1@d.com')
   )
, u AS (
   INSERT INTO users(username, email_id)
   SELECT n.username, nextval('email_email_id_seq'::regclass)
   FROM   new_data n
   RETURNING *
   )
INSERT INTO email(email_id, user_id, email)
SELECT u.email_id, u.user_id, n.email
FROM   u
JOIN   new_data n USING (username);

The specific difficulty is that we have neither user_id nor email_id to begin with. Both are serial numbers provided from the respective SEQUENCE. It can't be solved with a single RETURNING clause (another chicken-and-egg problem). The solution is nextval() as explained in detail in the linked answer below.

If you don't know the name of the attached sequence for the serial column email.email_id you can replace:

nextval('email_email_id_seq'::regclass)

with

nextval(pg_get_serial_sequence('email', 'email_id'))

Here's how you add a new "active" email:

WITH e AS (
   INSERT INTO email (user_id, email)
   VALUES  (3, 'new_active@d.com')
   RETURNING *
   )
UPDATE users u
SET    email_id = e.email_id
FROM   e
WHERE  u.user_id = e.user_id;

SQL Fiddle.

You might encapsulate the SQL commands in server-side functions if some simple-minded ORM isn't smart enough to cope with this.

Closely related, with ample explanation:

How to deal with mutually recursive inserts
Complex foreign key constraint in SQLAlchemy

Also related:

Enforcing constraints "two tables away"

About DEFERRABLE constraints:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

About nextval() and pg_get_serial_sequence():

PostgreSQL SELECT primary key as "serial" or "bigserial"
Duplicate row with Primary Key in PostgreSQL


Pointers to multiple index allocation mappings

sysindexes points only at first IAM page in IAM pages chain. IAM pages chain is a doubly linked list - ie. each page in chain (obviously except first and last ones) has pointers to the next page and to previous page in its header.

Look here: Managing Space Used by Objects

  An IAM page covers a 4-GB range in a file and is the same coverage as
  a GAM or SGAM page. If the allocation unit contains extents from more
  than one file, or more than one 4-GB range of a file, there will be
  multiple IAM pages linked in an IAM chain. Therefore, each allocation
  unit has at least one IAM page for each file on which it has extents.
  There may also be more than one IAM page on a file, if the range of
  the extents on the file allocated to the allocation unit exceeds the
  range that a single IAM page can record.


You can right click on the job in SSMS, go to properties, then to the 'schedule' option.  From the schedule add your daily schedule to the 4h schedule.  Default behavior of SQL Server jobs are that if a job is already running and the same job comes up again, it'll wait for the prior execution to finish before starting a new one.  What you're describing is the default behavior in SQL Server, you just need to give the job 2 schedules.


Return the most recent event in a series of same title events

Assuming an item moves between some type of state or location (From_Function, To_Function) and the timestamp of the move is in the route_date_time column, you could put your query in a common table expression, along with a ROW_NUMBER() function that calculates a sequence number, like so:

WITH cte AS (
    SELECT item_no,
           ToFunction AS current_function,
           route_date_time,
           ROW_NUMBER() OVER (
               PARTITION BY item_no
               ORDER BY route_date_time DESC) AS _rownum
    FROM some_complex_query
)

SELECT *
FROM cte
WHERE _rownum=1;

Because we've partitioned the ROW_NUMBER() function on item_no and ordered it by route_date_time descending, _rownum=1 will always return the most recent row for each item_no, which is what we're isolating using the WHERE clause at the end of the query.

If you need the time that each item has spent in a state, you could use LAG() (requires SQL Server 2012 or newer). Within the CTE, use the following expression:

    DATEDIFF(second,
             LAG(route_date_time, 1) OVER (
                 PARTITION BY item_no
                 ORDER BY route_date_time),
             route_date_time
            ) AS time_spent_in_function

This will return the difference in number of seconds between the previous route_date_time (the LAG() function) and the current route_date_time. Bear in mind the PARTITION BY clause, which defines how you partition your records.


Finding the row with the latest date for each row type

How about using windowing functions?

WITH Numbered AS
(
SELECT *, ROW_NUMBER() OVER (PARTITON BY Event ORDER BY DateTime DESC) AS rownum
FROM VehicleData
WHERE Registration = @Registration
AND DateTime < @DateTime
)
SELECT V.Registration, V.DateTime, V.Event, V.Data
FROM Numbered V
WHERE rownum = 1;

Make sure you have an index on: (Registration, Event, DateTime) INCLUDE (Data)

You can use your extra predicate on EventList too if you like, of course.


Creating index on computed field: string or binary data would be truncated

The error isn't caused by creating the index. The error is caused by TRY_CAST when the computed column values are evaluated at index creation.

If I run this:

SELECT TRY_CAST(REPLICATE(CONVERT(nvarchar(MAX), N'a'), 4001) AS bigint)

I get the same error.

The documentation says (emphasis mine):

  If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

Now, it's not exactly clear under which cases it will fail with an error (seems kind of asinine given the whole point of the function, but anyway...), so we can fix up the code by transforming the input values (use something reasonable for the data in your table), as there's no need to process a huge string when it won't fit in a bigint anyway:

SELECT TRY_CAST(LEFT(REPLICATE(CONVERT(nvarchar(MAX), N'1'), 4001), 100) AS bigint)

This returns NULL as the value isn't valid, but it doesn't bomb with an error.


MariaDB ALTER TABLE produced a separate .ibd file, how can I re-integrate the data into the shared table space?

While there are many reasons why you may want separate tablespaces, I will assume you are aware of all the disadvantages of having a single shared tablespace, specially in terms of administration (for example, how you can waste filesystem space when creating a temporary table on ALTER).

You probably got innodb_file_per_table changed automatically if you upgraded recently, as on MySQL 5.6 and some versions of MariaDB it is set by default to 1.

To reintegrate the table inside the main tablespace, execute the following on the running server:

SET GLOBAL innodb_file_per_table = 0;

Then recreate the table by doing either:

ALTER TABLE your_table ENGINE=InnoDB;

or

ALTER TABLE your_table ENGINE=InnoDB, ALGORITHM=COPY;

if you are using MariaDB 10/MySQL 5.6.

That will put your table back to the tablespace 0. Remember to change the innodb-file-per-table option on your my.cnf for it to survive a reboot!

Yes, DISCARD TABLESPACE is used for other reasons, mainly importing data with a transportable tablespace.


You don't need the select:

create or replace trigger insert_record
  after insert on tbl_user_order
  for each row
BEGIN
  insert into retailer_deliver_customer
    (prod_name,product_quantity,message,delivery_date,delivery_address,pin_code)
  values(
    :new.prod_name,
    :new.prod_quantity,
    :new.meessage,
    :new.delivery_date,
    :new.delivery_address,
    :new.pin_id);

end insert_record;
/

You can't commit in a trigger. The actions inside a trigger are always part of the transaction that fired the trigger.


How to grant access to SQL server agent to read network location file?

Under Windows Administrative Tools and Services, find the login account for the Sql Server Agent and grant that login the appropriate rights to the folder(s)/File(s)

You can also find the login account for the Sql Server Agent by using the sql Server Configuation Manager and clicking on the Sql Server Services node.


Keep total transaction in invoice application

The first question is: why do you want to store these values?

Typically this is done for performance, so you only have to recalculate them when edits are made.  If that's the case, I would also suggest caching the line item values in ia_invoice_items, so you can SUM those to compute the values in ia_calculations.

Usually, I see the ia_calculations values in the ia_invoices table.  Having them in their own, smaller table would make updates a little faster, but...

Managing them with triggers (rather than app code) will also protect you from changes made via any app (including the mysql command line tool, etc.)

And some comments that you didn't ask for:

Don't use INT as your id in your large tables, e.g. ia_users.  Use UNSIGNED INT instead.
Don't use INT to store dates, e.g. ia_users.createtime.  Use DATE or DATETIME instead.
Don't use INT to store booleans, e.g. ia_users.superuser.  Use TINYINT instead.
Don't use INT as the primary key in your small tables.  SMALLINT may be a better choice.
I wouldn't leave the length and precision of your DECIMAL columns to chance.  Define them explictly as DECIMAL(5,2) (or whatever length you need).
ia_invoices.updated_by only allows you to track the most recent update.  I would suggest an audit system, where you can track multiple edits, each with their own user_id, datetime, and notes.
Why don't you have product_id in ia_invoice_items?
Don't use VARCHAR(45) for postal code.  Nobody in the world has a 45-character postal code.  Since all customer records should have a postal code, I like to use CHAR here.


ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table

You are probably running a PostgreSQL version older than v12, because pg_policy has a TOAST table since v12, so that oversized attributes can be stored out of line.
Before that, you were limited to policies where the pg_policy row fits into a single table block. Your policy text is less than that, but PostgreSQL stored the parsed query tree, which can be larger than the text.
Upgrade to a later PostgreSQL release or use several PERMISSIVE policies, which will be connected with OR.


Database Design challenge: One (Table)-to-Many (Tables) Relationship?

This seems like a case where you'd want to use inheritance (subtype/supertype), but it isn't.

A company that manufactures engines won't necessarily always be an engine manufacturer, and might not only be an engine manufacturer. It could change what it makes, or it could make multiple things

"Fuel Supplier" or "Engine Manufacturer" are roles played by companies. A company has many roles. So you have a company table, and you have a company_role table with a foreign key to company. If roles have different columns, then this would be a case where you can use inheritance

While you're at it though, you should know that it's useful to use inheritance to create a super type for individuals (people) and companies (or organizations). This would let you have say a sales order linking to the "customer" which could be a person or company


How to disable the logging of parameter values in PostgreSQL

You can set log_error_verbosity = TERSE

The manual:

  Controls the amount of detail written in the server log for each
  message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE,
  each adding more fields to displayed messages. TERSE excludes the
  logging of DETAIL, HINT, QUERY, and CONTEXT error information.

Bold emphasis mine.


You need to restart mysql from the Linux command line like this:

service mysql restart --skip-grant-tables --skip-networking

If you are using Windows, add the two options in the mysqld section of my.ini:

[mysqld]
skip-grant-tables
skip-networking

and then run

net stop mysql (wait 10 seconds)
net start mysql

This will allow immedidate login without authentication and without any remoye connection sneaking in on you.

INSERT INTO mysql.user SET
Host='localhost',
User='mynewuser',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';

then

service mysql restart

At least you will have a localhost user named mynewuser with enough privileges to login.

You should then run this query:

select user,host,password from mysql.user;

and see if any new users were added by a hacker. If you see any remote users that should not be there, you can remove them with:

DELETE FROM mysql.user WHERE ...;
FLUSH PRIVILEGES;

If you are using Windows, please remember to remove skip-grants-tables and skip-networking from my.ini, then stop and start mysql service.

CAVEAT

Make a backup of /var/lib/mysql/mysql before you do anything.

UPDATE 2011-09-26 10:41 EDT

If the INSERT INTO mysql.user query does not work, it simply means the query is not compatible with the version of mysql I took this example from.

In order to know the correct INSERT query to user, please run this query:

desc mysql.user;

You will see all columns with the type ENUM('Y','N') for each privilege. Adjust the INSERT query to match all columns and then run that INSERT.

To get the exact columns to set to Y for all privileges, user this query:

SELECT CONCAT('SET ... ',GROUP_CONCAT(CONCAT(column_name,'=''Y'''),';'))
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name='user'
AND column_type='enum(''N'',''Y'')'
ORDER BY ORDINAL_POSITION\G

This will present to columns to set regardless of the version of MySQL you are using.


query for COLUMN_NAME LIKE "X" in sybase(Transact SQL)?

First: This isn't easy to implement and it's going to break very easily (by which I mean performance is going to be horrible, and it's a nice little route for SQL injection attacks if you're not very very careful). I strongly advise you to re-think what you're doing because there has to be a better way.

Second: The question as it stands doesn't actually make sense - what condition are you checking on the columns? NOT NULL? Also, do you want to AND or OR the conditions together?

To actually answer the question, though, to do this you'll need to write some dynamic SQL:

Link sysobjects to syscolumns, filter by the table name and the fields you're interested in.
Build a executable SQL statement using a cursor, then execute it.

Example below is, for each column in the Users table that starts with either 'A' or 'B', return all rows in the Users table that have a non-null value in any of those columns.

Note: I don't have a Sybase install handy to test this. Chances of it actually working are very low, but it should hopefully be enough to make you realise what a bad idea this is.

DECLARE @colname nvarchar(255),
        @sql nvarchar(4000)

SET @sql = 'SELECT * FROM Users WHERE '

DECLARE colcur CURSOR FOR
    SELECT sc.name
    FROM syscolumns sc
            INNER JOIN
         sysobjects so ON so.id = sc.id
    WHERE so.name = 'Users'
        AND so.type = 'U'
        AND (sc.name LIKE 'A%' OR sc.name LIKE 'B%')
    ORDER BY sc.name
OPEN colcur

WHILE 1=1
BEGIN
    FETCH colcur INTO @colname
    SET @sql = @sql + @colname + ' IS NOT NULL OR '
END

DEALLOCATE colcur

SET @sql = LEFT(@sql, LEN(@sql) - 3) -- need to trim off the trailing 'OR'

EXEC @sql


Not getting the expected result with 'IN' and subquery

You don't get results because the data type of the two tables' id are different and by that, cannot be compared (as 1 != '1'). Try this instead:

DECLARE @destlist varchar(max)
SET @destlist = SELECT destinations FROM tbl_rings_to_groups WHERE id = '1' AND user_id = '1'

SELECT count( id ) AS totalDestination, group_concat( ring_to_number ) AS phoneNumbers
FROM tbl_destinations
WHERE id IN (CONCAT('SELECT ', REPLACE(@destlist), ',', ' UNION ALL SELECT '))


What is wrong with table_cache hit rate?

One thing, here, is that you should be using this form, instead:

mysql> show global status like '%open%';

Some of these counters are global and some of them are session, so not using the GLOBAL keyword gives you a split set of numbers (especially the Opened_table* values).

The problem with tuning scripts is they can't possibly take into account all of the factors that need to be taken into account when deciding whether values are in a sane range... for example, if you use FLUSH TABLES, your Opened_files and Opened_tables counters will immediately increment because all of the tables that got flushed are re-opened as soon as they're accessed again... which, of course, means nothing at all negative.

Using mysqldump for backups will usually issue a FLUSH TABLES or FLUSH TABLES WITH READ LOCK at the beginning of the backup process, which means if you had been running daily backups and had a server uptime of even a few days, you could easily see a very poor "table_cache hit rate" and, once again, it doesn't mean anything.

"Table_cache hit rate" is not actually a value from MySQL.  It's a calculation from two other values.  All they are doing in mysqltuner is dividing open_tables by opened_tables (how many are open now, compared with how many have ever been opened).

badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";

So if you observe those two values over time and don't see Opened_tables rapidly incrementing except perhaps during the period after a backup when traffic picks up, then you don't have a problem.

This looks like a false alarm to me.


How to get last truncated time for each tables in SQL Server database

Remember that truncate is an efficiently logged operation.

Since you are running in Simple Recovery mode and provided your T-log is not truncated, you can get it using undocumented fn_dblog:

SELECT [Current LSN]
    ,[Operation]
    ,[Begin Time]
    ,[Transaction ID]
    ,[Transaction Name]
    ,[Transaction SID]
    ,SUSER_SNAME([Transaction SID]) as UserWhoIssuedTruncate
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_BEGIN_XACT' and
    [Transaction ID] IN (
        SELECT [Transaction ID]
        FROM fn_dblog(NULL, NULL)
        WHERE [Transaction Name] = 'TRUNCATE TABLE'
        )
GO

Click here to enlarge

Alternatively, you can run a server side trace or turn on SQL Audit.

Always give minimum permissions (principle of least privilege) to your users.

  Is it possible if I change it to FULL ?

Yes it is (and recovery model should be defined by your business - do you want to have point in time restore? How much data your business can afford  to lose? etc.) You have to start maintaining your T-log by running regular log backups. This will keep your T-log at a sensible size.

Edited
Query uses "IN" instead of "=" as multiple results in the sub-query are possible.


Is someone trying to hack into my SQL Server Database?

Looks like somebody is really trying to hack into your SQL Server with brute force attacks. I recommend taking a look at this Whitepaper on SQL Server security, that will help you a lot.

Since most attacks happen at the same time, make sure you don't have a security scanner on your network that runs its checks at that time. Products such as Nessus can perform security scans and simulate brute force attacks on your servers in order to help you fix the security issues they find.


Why SQL Server port number is not default 1433?

Contributed by AlwaysLearning:

Configure the Windows Firewall to Allow SQL Server Access:
By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port.


SQL Server drop and create all users in every DB

OK, there are several things that it could be but I'm not sure if we can determine which from here.  These include the following:

AD propagation issues
AD caching "stickiness" on the target SQL Server
Domain Trust issues (particularly if the target server is in a different domain)
Windows Group membership/visibility issues
UAC blocking users access to certain groups needed for the new server
Kerberos issues (very common)
Etc...

And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases.  It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.

In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act.  It may work, but it may also cause some bad side-effects.  Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.

Other (possible) solutions that you could employ include:

Automated procedure that does not drop the User, but does add and associate a server-level Windows Login for it.
Create a Windows AD group for access to the database, add all of the users to that, and then add that to the SQL Server as a Login and Database as a User. (this is the recommended approach for managing large-scale access anyway).
...


max_connections depends on RAM because each connection uses RAM for read/join/sort buffers. You can not set max_connections arbitrary high. In general the RAM consumption can be represented that way:

+------+-----------------------+---------------------+-------------+
|  OS  | MyISAM/InnoDB buffers | Connections buffers | Third party |
|      |                       +---------------------+             |
|      |                       | | | | | | | ...     |             |
+------+-----------------------+---------------------+-------------+

Host's OS need some RAM for the proper operation - usually 1-2GB+.
Third party services (apache/php/etc) requirements should be
considered.
DB engines are used RAM for caching. MyISAM caches
indexes only while InnoDB caches both data and indexes.
All active connections consumes a lot of RAM.

For maximum performance InnoDB should have innodb pool big enough to fit all the tables and their indexes in the RAM. max_connections should be tuned along with the read_buffer, join_buffer and sort_buffer values.

But the main attention should be paid to the queries optimization. You can't get a good overall peformance if the single query is slow. Even if you have the host with 256-512 GB of RAM it can be slow if the frequenlty executed queries are slow.


The name "creatorUser" fails when there is a multi-column foreign key relationship.

As with the other posters here, I strongly support using the same column name for the same attribute, regardless of what table it is in.

Imagine a table of books, called "Books". If the primary key is "ISBN" then the foreign key in referencing tables should be "ISBN" - not "BookID" or "BOOKISBNID" or "BookISBN".

My reference for questions like these is Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems).


Can I generate binary logs if they weren't turned on?

Can we generate the logs for the few hour gap? Or are they gone?

They are gone. You cannot do a sync with that gap. However, it should not matter.

You need to setup replication from scratch on DB1.

During off-hours, run this on DB2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS=""
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "SET SQL_LOG_BIN = 0;" > MySQLData.sql
echo "STOP SLAVE;" >> MySQLData.sql
mysql ${MYSQL_CONN} -ANe"RESET MASTER;"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "SET SQL_LOG_BIN = 1;" >> MySQLData.sql

You just setup replication on DB1 and load MySQLData.sql into it.

BTW Make sure log-slave-updates is configured on DB1 and DB2


sp_BlitzIndex only results in one row (with priority -1)

V4.1 is the last working version.  It is an issue in v4.2 - v4.8(current).  It works for some databases, but not all, when run with no parameters.  For example, it works in master, but fails in a Dynamics GP company database. @Mode = 4 still works fine though.

I tested versions 4.1 - 4.8 on SQL Server 12.0.4100.1 and 12.0.5511.0


MariaDB audit plugin logs all queries instead of DML only

As of version 1.4.4 of the plugin, there is now a QUERY_DML_NO_SELECT type that should give you what you are expecting.

  Same as QUERY_DML, but doesn't log SELECT queries. (since version 1.4.4) (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements)

This was introduced in MariaDB 5.5.42, MariaDB 10.0.17, and MariaDB 10.1.4.


Why use an int as a lookup table's primary key?

The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes your code simpler. If everything is integer → integer relations internally, you're covered.

Just read your comment to Sandy - perhaps in this case what you really want is a Check Constraint, not a foreign key/lookup table, e.g.:

create table icecream (flavour varchar(10))
go
alter table icecream add constraint ck_flavour check (flavour in ('Orange', 'Pista', 'Mango'))
go
insert into icecream (flavour) values ('Orange')
go
insert into icecream (flavour) values ('Vanilla')
go

Run this and you get:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_flavour". The conflict occurred in database "GAIUSDB", table "dbo.icecream", column 'flavour'.
The statement has been terminated.

This is an efficient, high-performance method, but the disadvantage of course is that adding a new flavour means a code change. I would advise against doing it in the application - because then you need to do it in every app that connects to this DB, this is the cleanest possible design because there is only a single code path for doing validation.


Count childs of parent with sql or save in column?

I assume you want to count the number of immediate childrent for each node in the tree. One easy solution is to do that with a sub-select:

WITH RECURSIVE cte (id, message, author, path, parent_id, depth)  AS (
    SELECT  id,
        message,
        author,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM comments
    WHERE parent_id IS NULL

    UNION ALL

    SELECT comments.id,
        comments.message,
        comments.author,
        cte.path || comments.id,
        comments.parent_id,
        cte.depth + 1 AS depth
    FROM comments
    JOIN cte
        ON comments.parent_id = cte.id
)
SELECT id, message, author, path, depth
     , (select count(1) from cte as cte2
        where cte2.parent_id = cte.id) as num_children
FROM cte
ORDER BY path;

An alternative is to use comments instead of cte:

     , (select count(1) from comments as c
        where c.parent_id = cte.id) as num_children

Personally I would stay away from storing this number.

If all you are interested in is the number of children for all nodes there is no need to recursively traverse the tree, a self join or a sub-select is sufficient:

select id, message, author
     , (select count(1) from comments as c2
        where c2.parent_id = c1.id) as num_children
from comments as c1

The transformation to use a join instead is straight forward.

If you are interested in alternatives to recursively traverse the tree there are options such as NESTED SETS. You can google for the details, but the idea is to store an upper bound and a lower bound for each node. A node is an ancestor to another node if it subsumes that node. Main drawback with this approach is that it is difficult to maintain the bounds.

Yet another approach is to store a MATERIALIZED PATH for each node (much like your array).

None of these have a parent_id attribute. A third approach that do use the parent_id attribute is to have a separate ancestor table. This is known as the TRANSITIVE CLOSURE of parent_id. It is suitible to maintain this relation with triggers.

Finally, all of these models (including the recursive one you mention) have benefits as well as drawbacks, choose the one that fits your scenario best.


How to Resolve a Case of "The tablix 'xyz' contains an invalid DataSetName in SSRS?

I found a solution, so am posting for others to benefit.

The tablix receiving the warning was a tablix nested inside of another tablix. I had checked and rechecked that nested tablix, and nothing in the Visual Studio GUI was amiss. It was reporting the correct dataset.

Finally I resorted to opening up an XML editor and opened the RDL file, and sure enough, the XML file had the wrong dataset in the XML.

It was in the definition of the tablix, at this point:

<DataSetName>DataSet2</DataSetName>
<Left>0.74in</Left>
<Height>0.875in</Height>
<Width>5.64866in</Width>
<Style>
  <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
  </Border>
</Style>

The solution was to delete the

<DataSetName>DataSet2</DataSetName>

line from the XML entirely, because a nested tablix should not specify a dataset name, because by definition a nested tablix must use the dataset from the outer tablix.

The reason why this warning had come about was because originally I created the tablix as a standalone tablix, so it did have a dataset. After some report changes, I copied and pasted the tablix into another tablix. At that point, apparently, visual studio kept the dataset name in the paste, but did not support changing it from the GUI.


Avoiding "too many active scans" on MySQL Cluster

"Too many active scans" error means that at a given moment there are too many queries running which are scanning the tables while trying to get answers.

Scanning means for example range scan queries (WHERE val > 20). And if I remember right scan also means fetch queries if index is not has.

SELECT * FROM tbl WHERE id=20;

Even such query counts as scan if id field has btree index.

In your case we almost all DELETE queries are running in loop with 'LIMIT 1000' or similar value.


Either use Trace Flag 1204 and Trace Flag 1222, or use Sql Server profiler to analyse and save deadlock graph information.

See also here


Indexing is always the first port of call. Ensure that you have the proper indexes in place before anything else.

Next run the query and study the actual execution plan which will give some much needed pointers.

http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

Finally these tricks can give a well needed performance boost and most if not all are good practice anyway:

Ensure All tables have a primary Key.
Ensure All tables have a relevant clustered Index.
Ensure All Tables have the appropriate non clustered index. Specifically all columns referenced in the Where / Order By and Join clauses should be covered.
Avoid Views where possible.
Do not use SELECT *
Ensure all of your query is relevant especially where it joins to other tables.
Avoid Cursors unless absolutely necessary.


Windows 2008 R2: Unable to upgrade MySQL 5.5.58

You have to follow the upgrade path and follow the upgrade insructions
From 5.5 to 5.6
https://dev.mysql.com/doc/refman/5.6/en/upgrade-paths.html
rom  5.6 to 5.7
https://dev.mysql.com/doc/refman/5.7/en/upgrade-paths.html
And finally from 5.7 to 8.0
https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html
Ther you will find also the upgrade instructions


What happens with statistics when reverting to a database snapshot

Yes you would:

  The original source database is overwritten by the reverted database, so any updates to the database since the snapshot's creation are lost.

and

  The metadata of a reverted database is the same as the metadata at the time of the snapshot.

Quotes from Revert a Database to a Database Snapshot in the MSDN Library.


Error when trying to add a shard set in Mongo db

You need to have at least the role of clusterManager to perform this action in 2.6 . If you are a dbadmin you can grantRole clusterManager to current user following this tutorial http://docs.mongodb.org/manual/tutorial/assign-role-to-user/


I am the author of the blog post mentioned above by Andrew.

The new service tiers scale (ie. limit) I/O, CPU and memory by tier.  Higher levels/tiers provide more resources.  There are no documented limits by resource type (only vague DTUs), though my tests provide some indicative I/O numbers.

UPDATE:  I have now tested the memory limits by Service Tier, see:
http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/

In terms of optimising design, I would first see which resource type I am hitting the limit on.  The sys.resource_stats DMV in the master database has columns that show the %age resource usage (e.g. % CPU) you are using over time in that service tier/level.  If you are able to run a test for say at least half an hour, then check that table and see if one of those columns is max'd at 100% then you have an idea of where you are bound.  Of course, resolving that may or may not be easy/possible, but you have a starting point.  The sys.dm_db_resource_stats is a newer DMV which has data for the last hour at 15 second intervals so would be useful for shorter tests.

If the I/O is the limiting factor, then for a small database I would expect performance to increase dramatically once the data is in the buffer pool.  That was a very clear pattern in the tests I undertook and blogged, e.g. see the dramatic performance increase in this sequential read test on Basic Edition:
http://cbailiss.files.wordpress.com/2014/07/2014-07-04-seqsel-metrics1.png
taken from this post:
http://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-sequential-selects/

Of course, if/when the buffer pool is cleared, then performance will drop off again.  That should in theory happen less with the new tiers - since the guaranteed performance levels in the new tiers are supposed to prevent noisy neighbours / load balancing database failovers that happen in Web/Business.
However, as far as I know, there are no documented numbers / guarantees about the buffer pool size that is allocated to each database in the new service tiers.

Hope that helps

Chris


Postgres's equivalent for Oracle's datafile recovery

PostgreSQL does not have datafiles in the same way as Oracle. PostgreSQL stores its contents in a directory (show data_directory;) and not in a tablespace that consists of 1 or more datafiles. You have no control over the naming and/or size of the files in there.


How to keep one query from slowing down entire database?

Unfortunately, as far as I know, there is no way to throttle either INSERT...SELECT or ANALYZE TABLE. I was going to suggest using INSERT DELAYED ... SELECT but came across this:

  DELAYED is ignored with INSERT ... SELECT.

So that's out the window. So, if you can't throttle these long-running queries, you're going to have to look at speeding them up so they don't take so long to complete.

I am assuming from one of your other questions that you are using MyISAM. This means your INSERT..SELECT will be locking the table you are writing to, so any attempt to access it will cause waits and long-running queries.

Speeding up the queries are going to depend on how fast your drives are, how much of your 16GB memory you are using effectively, etc. Without such detail, the rest is guess-work:

Do you really need to run ANALYZE so often? If so, how big is your key_buffer_size (again, assuming MyISAM)?
The SELECT portion of INSERT...SELECT is probably writing to a temporary file on disk, causing you to be IO-bound (waiting for the temporary disk file to be finished before even starting the INSERT portion). You can look at increasing max_heap_table_size and max_tmp_table_size to be larger than the size of your table you're selecting from (note MySQL will use the value that is least of these two variables).
You might be able to break the INSERT...SELECT statements into smaller chunk sizes so each one finishes faster, but it depends on your use case.

I'm sorry if this isn't much use at the moment, and will try to update as we get more information.


Delete duplicates and reroute referencing rows to new master

Can be a single statement with data-modifying CTEs:

WITH blacklist AS (
   SELECT *
   FROM  (
      SELECT id
           , first_value(id) OVER (PARTITION BY user_id, name
                                   ORDER BY j_ct DESC NULLS LAST, id) AS master_id
                                                               -- id as tiebreaker
      FROM   purposes
      LEFT   JOIN (  -- left join to preserve rows without any journeys
         SELECT purpose_id AS id, count(*) AS j_ct
         FROM   journeys
         GROUP  BY 1
         ) j USING (id)
      ) sub
   WHERE  master_id <> id  -- is not master_id
   )
 , del AS (
   DELETE FROM purposes p
   USING  blacklist b
   WHERE  b.id = p.id
   )
UPDATE journeys j
SET    purpose_id = b.master_id
FROM   blacklist b
WHERE  b.id = j.purpose_id;

This should do exactly what you need - even in cases of ties or where no journeys exist at all.

Step by step

The 1st CTE blacklist identifies all IDs in table purposes where another row exists with the same (user_id, name) but more associated journeys. Remember that other ID as master_id to reassign journeys further down the road. id serves as tiebreaker: the smallest id prevails.

The 2nd CTE del deletes the identified dupes.
The outer UPDATE reroutes journeys to their new master.

Note DESC NULLS LAST in the ORDER BY clause. Important for cases with no journeys at all, since that result in a NULL value for the journey count j_ct. See:

PostgreSQL sort by datetime asc, null first?

Use the window function first_value() to get the master_id for each group of peers - after forming groups with PARTITION BY and sorting properly with ORDER BY.


First and foremost do not use the IMAGE data type, is an old deprecated type:

  text , text, and image data types will be removed in a future version
  of Microsoft SQL Server. Avoid using these data types in new
  development work, and plan to modify applications that currently use
  them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

You need to store multiple images, one for each resolution desired (full/preview/thumb). In general these resized images must be generated during the upload of the image. Strictly speaking it is possible to generate the preview/thumb on-demand on first display, or even on each access (ie. not saved), but this is seldom the right choice. In general a file is uploaded once and viewed multiple times, so it makes more sense to pay the penalty of generating the thumbnail at upload (write) time. And users are perceive the site/application as 'slow' if the image is generated on-demand on first access. See Asynchronous Execution for how to run jobs like uploaded image re-size in an reliable asynchronous manner.

As for loading fat content into entity lists in the client (irrelevant whether is images, documents or whatever other BLOB), just don't do it. You will kill your server, your network, your appserver, your client app, everything. Load narrow lists with only the fields needed for display. Lazy load on-demand expensive content. This is CS101 and applies to every technology.

PS. I won't even go into the topic of whether is better to store images on disk or in db. I assume you already did due diligence for that.


sys.column_store_segments and sys.column_store_row_groups store some of the metadata information about the ColumnStore data, but I believe that ends up representing the compressed size. There are LOB structures that are allocated as well, and you can see the uncompressed size in the allocation unit / partition DMVs (and if you get to the pages somehow with DBCC PAGE, I bet you would see that they're relatively empty). In other words, sys.column_store_row_groups shows you how much data is stored on those pages, but doesn't add in the free space on those pages (which still take up space in the data file and in memory, like a fragmented index or an index with a really low fill factor).

I ran your repro and here is what I saw:

SELECT
  a.[type_desc],
  p.[rows],
  a.total_pages, reserved_kb = a.total_pages * 8,
  a.used_pages,  data_kb     = a.used_pages  * 8
FROM sys.allocation_units AS a
INNER JOIN sys.partitions AS p
   ON a.container_id = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID(N'dbo.IdsBin');

Results:

Other than unused, which I didn't bother calculating, the results match up with sp_spaceused:

And you can confirm these numbers in sys.dm_db_partition_stats as well:

SELECT
  lob_reserved_page_count, reserved_kb = lob_reserved_page_count * 8,
  lob_used_page_count,     data_kb     = lob_used_page_count     * 8
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID(N'dbo.IdsBin');

Results:

You mentioned this but I just wanted to call out explicitly that sp_spaceused and allocation_units reflect the page count, regardless of how full or empty any page might be. The row groups DMV is reflecting just the actual data. The documentation states (emphasis mine):

  Size in bytes of all the data in this row group (not including metadata or shared dictionaries)

Whereas sys.dm_db_partition_stats, for example, explicitly states pages throughout, not data, although I would argue that they should specify that each LOB is an 8K page here:

  Total number of LOBs used to store and manage columnstore index in the partition.

Which number you want to trust, well, that's up to you.

As an aside, Niko Neugebauer talked about the fact that only the compressed size of the dictionaries is exposed in the columnstore DMVs here, and raised a feedback item about it. Seems to me there is other information that could be exposed in the columnstore DMVs too.


Columnstore Aggregate Pushdown doesn´t work

The answer is actually quite simple, I should've read more about limitations. The problem is that I have Standard edition and this is one of standard edition limitations.


Set Value Based on Number of Records with Certain Values using SQL Server

This query will return PASS if there are at least three unique levels, otherwise FAIL.

SELECT PRODUCT, (CASE
       WHEN COUNT(DISTINCT [LEVEL])>=3 THEN 'PASS'
       ELSE 'FAIL' END) AS [STATUS]
FROM tablenamegoeshere
GROUP BY PRODUCT, [CONTROL];

COUNT(DISTINCT ..) counts the number of unique values of a column or expression. CASE returns different values depending on the outcome of different conditions. In this case, if the number of unique values is equal to or greater than three, we want to return 'PASS' otherwise 'FAIL'.

Obviously, if you want to return a bit value (i.e. boolean), you could change the strings 'PASS' and 'FAIL' in the code into 1 and 0 respectively and cast them to bit using CAST(... AS bit).


Oracle Materialized View Refresh Group - Montly Interval

Next month's 1st day 1am can be calculated using the following SQL:

SELECT TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH')+1/24 AS ANSWER FROM DUAL;

The most probably it can be simplified.


SSIS Excel Import - First row getting skipped

This is very easy using the Wizard:

In your Project, drag a data flow task onto the board

Double-click the data flow task. being inside the data stream, drag an excel source.

then we create a new connection to excel by clicking on excel source.

we look for the excel file that we want to import and select: "the first row has column names"

we select the table or the view that we have in the excel sheet.

With this we would already have the process to import the excel data to any destination ...

good Luck...


Query with CASE expressions on a Logger Devices database

You can get it by using PIVOT relational operator, but it requires a dynamic query due you don't know the name of SiteName in advance.

The STUFF query builds a coma-separated string with the names of the sites. The PIVOT query use its to get MAX(Depth) of each site.

DECLARE @col AS nvarchar(max);
DECLARE @cmd AS nvarchar(max);

SET @cols = STUFF((SELECT ',' + QUOTENAME(sName)
                   FROM loggerData2
                   WHERE sName = sName IN ('SiteName1', 'SiteName2', ...)
                   FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @cmd =    'SELECT DT, ' + @col
            + 'FROM
               (
                   SELECT
                      sName as SiteName,
                      CAST(readingDTTM AS DATETIME) DT,
                      lValue as Depth
                   FROM
                     loggerData2
                   WHERE
                     sName = sName IN ('SiteName1', 'SiteName2', ...)
                     AND Sensor = 'Depth'
               ) src
               PIVOT
               (
                    MAX(Depth) FOR SiteName IN (' + @col + ')'
            + ') pvt';

EXEC sp_executesql @cmd;


Yes, there is a difference.

In the 1st design, an author-paper combination is related to one editor. So a paper can have many editors, as many as its authors.

In the 2nd design, a paper is related to one editor. And all its authors are related  - through the paper - to the same editor.


sql server in docker using 15gb of disk space

The database files could be using those 15gb even in an newly created and empty database.

You can check the size of the databases using SSMS:

Or check the size of all databases at once using Alex Aza's code or some other code you like from SQL Server 2008: How to query all databases sizes? which is the source of this sample:

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

If you see a database that's bigger than expected, you can use DBCC SHRINKFILE (Transact-SQL) to reduce it. Using this command is ok in your specific situation though and not a recommendation to any situation of a big database file.


As DTest pointed out, provide more information about your problem.

Regarding caching, you could possibly increase your innodb buffer pool size to allow more data and indexes to be cached in memory. If you have frequent updates, you may not benefit from the query cache and might be better off using that RAM for innodb buffer pool.

If you want to put all your data and indexes in RAM, then MySQL Cluster might be your answer.

EDIT
It looks like your SELECT statements are using proper indexes. Could you provide an explain plan for your update statement? You will have to rewrite it as a SELECT statement to do that. Do a SELECT * FROM same table and with same where clause as your UPDATE statement.


MySQL Select all clients that searched for two different items or more

SELECT ip
FROM my_table
WHERE UPPER(term) NOT LIKE '%BAG%'
GROUP BY ip
HAVING COUNT(ip) > 1;

Normally I would recommend against using a function
in a search (UPPER()), but since the wildcard is there,
the expression is non-sargable in any case!

The db-fiddle is here!


Executing Stored Procedure that accesses another SQL instance

Try using EXECUTE AS LOGIN = 'DOMAIN\username' instead and see if that works.


Let me suggest to add a new column and then update this new column before to drop the old one.

Backup, backup, backup

create table mytable (ConstantValue varchar(100));

insert into mytable values
('1252.36'),('abc'),('55969'),('def'),('66955663'),('A8879'),('-41.25'),('+636');

Add a new column with a default value of 0.

alter table mytable
    add column NewConstantValue double default(0);

Set alphanumeric values to 0. I've borrowed this regex expression from MySql forums

update mytable
set    ConstantValue = '0'
where  ConstantValue not regexp '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

Update new column with current values.

update mytable
set    NewConstantValue = cast(ConstantValue as double);

Check new values before to drop old column.

select * from mytable;

ConstantValue | NewConstantValue
:------------ | ---------------:
1252.36       |          1252.36
0             |                0
55969         |            55969
0             |                0
66955663      |         66955663
0             |                0
-41.25        |           -41.25
+636          |              636

Drop ConstantValue column.

alter table mytable
    drop column ConstantValue;

Rename new column as ConstantValue.

alter table mytable
    change column NewConstantValue ConstantValue double default(0);

Check final result.

select * from mytable;

| ConstantValue |
| ------------: |
|       1252.36 |
|             0 |
|         55969 |
|             0 |
|      66955663 |
|             0 |
|        -41.25 |
|           636 |

dbfiddle here


What datatypes can cause "String or binary data would be truncated"?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Result:

0x1111    b

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

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


SSIS script task - Execute Dataflow task based on variable value

Precedence constraints should be able to handle this requirement. Obviously without seeing the package and how you're setting up the constraints, we can't tell you why it isn't working. However, I've found that using an Expression for the Disable property of the data flow task can also be a good way to control flow. Maybe that will work better for you.

To do this, right-click on the data flow task and select Properties. Go to Expressions and click the ellipsis (...) to open the Property Expressions Editor. Under Property, select Disable, and under Expression, click the ellipsis again to open the Expression Builder. Then write the expression that determines if the data flow task should be disabled or not.


Is Synonyms to tables cost effective within SQL Server 2016

According to this post -

  As a synonym is an abstraction/alternative name for an already
  existing database object, in the case of a table, index behaviour is
  identical to that of the underlying object i.e. when execution plans
  are generated, the same plan is generated irrespective of using the
  table name or corresponsing synonym.

You can verify the execution plans when using both the regular object and the synonym to see if they generate the same plan.

This post has a similar question:

  How does the use of synonyms effect performance?

  More specifically, are synonyms replaced with fully qualified names in
  execution plans? It would seem to me that any synonym has to be
  resolved to the actual object so any query or procedure that doesn't
  have a cached plan is going to have an extra step in mapping the
  synonym to an actual object. I'm thinking there would be a very small
  performance hit in this. Perhaps a slightly larger hit when the
  synonym references an object on a different database and slightly
  larger still when on a different server.

The answer given was

  They are swapped out in the Bind phase of query execution same as a
  view would be expanded. This happens before the Optimization phase
  where the execution plan is generated so while you'll still see a
  reference to the synonym in the query text you will only see
  references to the objects the synonyms point to in the execution
  plan's operators.

  Any performance hit, if you want to even call it that, that might be
  associated with using synonym is not at all worth being concerned
  about.

I'm sure Paul White (or another expert will provide a definitive response).


how to restore sqlite dump file to sqlite database using java?

If you're using JDBC with Java, have a look at sqlite-jdbc
 by xerial. They added the backup and restore API of sqlite a while ago.

I use this together with an sqlite in memory-database for higher performance and regularly backup to a file.

You can use it like this:

// Use this connection string for an in memory-database
Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

connection.createStatement().executeUpdate("restore from database.db");
connection.createStatement().executeUpdate("backup to database.db");


How to update a multiple rows all at once with a procedure.?

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer.

First i made one procedure to update one single row in the column:

delimiter //
create procedure update_amount_products (in id int)
begin
update categories set products_amount=
           (SELECT DISTINCT COUNT( products_to_categories.products_id )
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//

DELIMITER ;

Then i realized i could handle the whole column by calling the procedure with another procedure with a loop on it

DELIMITER $$
CREATE PROCEDURE update_products_amount()

   BEGIN
      DECLARE a INT Default 1 ;
      DECLARE category_length int;

      select max(categories_id) into category_length from categories_description;

      simple_loop: LOOP
         SET a=a+1;

         call update_amount_products(a);

         IF a >= category_length THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$
DELIMITER ;

commit;

Finally, all i have to do now, is to call my last procedure and voilá, all my column will be automatically updated.

call update_products_amount();


How to handle "many columns" in OLAP RDBMS

Regarding why you would want to do this, imagine you want to see which words/short phrases in customer emails are associated with costly repairs, and you want to be able to analyze this using OLAP. It can be costly to tokenize/grammify many documents, so you might want to store the tokens/grams in a form which your OLAP server understands, ie columns.

Consider MonetDB, which allows for a practically unlimited number of columns.

Redshift maxes out at 1600 columns.

Another option would be to use Principal Component Analysis and only choose the top 1600 components, but that can make interpretation difficut.

Another option would be to use Postgres and store the tokenized strings or n-grams in a string array field, but your OLAP server would need to support that.


The 24 is the storage overhead per row in a table is fixed for all tables ot it varies?

The table row layout describes a header of 23 bytes for each table row, and that is independent of the data types.
One item that is variable is the “null bitmap” that is technically part of the row data: it is only present if there is a nullable column in the table. Yet another reason to define most columns as NOT NULL!


How to order fields returned by a PIVOT query

You should order returned fields in your first SELECT statement in this way:
select Sno, IntroTop, TopEmp, [CH], [ED], [DIR], [GM], [AGM], [BDM], [SMM], EmpID
from
(
)
pivot
(
)

Example:

CREATE TABLE pvt (VendorID int, EmpID int, EmployeeID int, Orders int);

INSERT INTO pvt VALUES (1,1,10,5);
INSERT INTO pvt VALUES (2,1,11,5);
INSERT INTO pvt VALUES (3,2,10,5);
INSERT INTO pvt VALUES (4,2,11,5);
INSERT INTO pvt VALUES (5,3,10,5);

SELECT VendorID, COALESCE([10],0) [10], COALESCE([11], 0) [11], EmpID
FROM
(
    SELECT VendorID, EmpID, EmployeeID, Orders FROM pvt
) src
PIVOT
(
   SUM(Orders) FOR EmployeeID IN ([10],[11])
) pvt;

GO

VendorID | 10 | 11 | EmpID
-------: | -: | -: | ----:
       1 |  5 |  0 |     1
       2 |  0 |  5 |     1
       3 |  5 |  0 |     2
       4 |  0 |  5 |     2
       5 |  5 |  0 |     3

dbfiddle here


You can run sp_who2 or equivalent to find a connection that's using that database. Assuming you're a sysadmin on the box (and, if you restarted the SQL Server instance, I hope you are), then you should be able to kill the process that is the current single user.

I would enter the kill command in a query window, along with the command to restore multi-user mode:

KILL 123;
ALTER DATABASE <your_db_name> SET MULTI_USER;

It is still possible that someone will manage to grab the connection in between the execution of the two commands (or that the current connection will need to roll back some changes); repeat as necessary.

NOTE: You are risking wiping out someone's changes to the DB in question by killing their connection. Please pay attention to the user in question, how they're connected, and whether it seems safe to kill their connection; you may want to contact them before killing their process.


Constraint to prevent 'duplicates' only when column > 0

Add a conditional unique index

CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);

dbfiddle here


Best way to apply a "friend system"-like table?

First approach is the SQL approach. You need to make modification though. The relation that you are describing is called many to many - each player can have each other player as friend. To achieve this you need a separate table which connects player with his friends (you just need 2 IDs):

|Player_ID  | Friend_ID|
------------------------
|  1        |  2       |
|  1        |  5       |

This is the better approach if you need make a lot of queries on Player and list of his friends (this will give speed of queries).

If you just need list of names, the other approach is also possible.

So you have to think and decide which is better approach for your needs.


Merging adjacent date intervals into a single row

You could try this:

[edit] fixed a bug .. comment on line fixed [/edit]

with w_data as (
        select 'A' emid, to_date('01/01/2012','mm/dd/yyyy') fromdate, to_date('12/31/2012','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2012','mm/dd/yyyy') fromdate, to_date('12/31/2013','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2013','mm/dd/yyyy') fromdate, to_date('12/31/2014','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('02/02/2015','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('12/30/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('03/22/2014','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('01/10/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('04/03/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual
        ),
  w_sub as (
     select emid, fromdate, todate,
              sum(grp) over (partition by emid order by fromdate) grp_id  -- added partition by .. forgot to group it by employee
       from (
              select emid, fromdate, todate,
                     case when fromdate = lag(todate) over (partition by emid order by fromdate)
                       then 0 else 1 end  grp
                from w_data
             )
        )
select emid, min(fromdate) fromdate, max(todate) todate
  from w_sub
 group by emid, grp_id
 order by emid, grp_id, fromdate
/

E FROMDATE    TODATE
- ----------- -----------
A 01-jan-2012 31-dec-2014
A 02-feb-2015 31-dec-2099
B 12-may-2013 30-dec-2013
B 10-jan-2014 22-mar-2014
B 03-apr-2014 31-dec-2099
C 12-may-2013 31-dec-2099

6 rows selected.


Getting error while installing log4db2 utility

LOG_CONF_BP is a bufferpool. That is a chunk of memory dedicated to doing something (usually servicing one or more tablespaces, which is where tables reside). So what is happening is your stored procedure is trying to create an object that already exists.

So what is going to need to change is the stored procedure. I would ask if your stored procedure needs that exact bufferpool or not. If yes, then change the stored procedure to check for the existence of LOG_CONF_BP (you can check against syscat.bufferpools for that). If it doesn't exist, create it, otherwise don't.

Otherwise, even if you drop the LOG_CONF_BP, so your stored procedure succeeds, it will just fail again the next time you run it anyway....


Find and print the total number of menu items and the average price of the menu items for each restaurant

You are missing GROUP BY:

SELECT r.restaurantName, COUNT(m.itemNo), AVG(m.price)
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN Dish d
ON d.dishNo = m.dishNo
GROUP BY r.restaurantName;


Check if two datetimes are between two others

To check for overlaps, you only need 2 conditions, the start or interval A with the end of interval B, and vice versa:

WHERE
      '2019-04-08 13:30:00.000' < Stop
  AND Start < '2019-04-08 15:30:00.000'


SQL Server 2008R2 - Stored Procedure with 2 updates causing deadlocks

Using SentryOne Plan Explorer I was able to make a little more sense of the deadlock graph (click to embiggen):

The victim also called the stored procedure GetIsAccountLicenseConnectionValid, and I suspect this procedure has some touch against one or both tables affected by the updates in the question. It is certainly feasible that locks taken by that stored procedure could interfere with locks affecting another transaction that hasn't called that stored procedure yet.


How to get the foreign and primary key details for a table in SQL Server 2008

You should become familiar with the new catalog views. The old sysobjects, syscolumns etc. have been deprecated and are only provided for backward compatibility reasons - they shouldn't be used for new development and old code should eventually transition to the new objects.

Here is a query that will get both sides of the columns involved in foreign keys:

SELECT
        constraint_name   = OBJECT_NAME(fkc.constraint_object_id),
        foreign_schema    = SCHEMA_NAME(child.[schema_id]),
        foreign_table     = child.name,
        foreign_column    = child_cols.name,
        referenced_schema = SCHEMA_NAME(referenced.[schema_id]),
        referenced_table  = referenced.name,
        referenced_column = referenced_cols.name
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.tables AS child
    ON fkc.parent_object_id = child.[object_id]
    INNER JOIN sys.tables AS referenced
    ON fkc.referenced_object_id = referenced.[object_id]
    INNER JOIN sys.columns AS referenced_cols
    ON fkc.parent_column_id = referenced_cols.column_id
    AND referenced_cols.[object_id] = referenced.[object_id]
    INNER JOIN sys.columns AS child_cols
    ON fkc.referenced_column_id = child_cols.column_id
    AND child_cols.[object_id] = child.[object_id]
    WHERE referenced.name = 'table name'
    -- if you want the tables that a child table references, use this instead:
    -- WHERE child.name = 'table name'
    ORDER BY fkc.parent_column_id;

And this one is much simpler, just for the tables involved:

SELECT
    constraint_name = name,
    foreign_schema = OBJECT_SCHEMA_NAME(parent_object_id),
    foreign_table = OBJECT_NAME(parent_object_id),
    referenced_schema = OBJECT_SCHEMA_NAME(referenced_object_id),
    referenced_table = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'table name'
-- if you want the tables that a child table references, use this instead:
-- WHERE OBJECT_NAME(parent_object_id) = 'table name';

EDIT adding some links to documentation (these mostly fall within the SQL Server 2008 R2 hierarchy but they're roughly equivalent for SQL Server 2008 as well):

Catalog Views
Querying the Catalog Views
Querying the Catalog Views FAQ
Metadata Functions


PostgreSQL - duplicate data show and count

I think you just want a simple group by query, using the 3 expressions that get data out of the json column:

select
    config->>'external_http_port' as external_http_port,
    config->>'external_host'      as external_host,
    config->'snapshots'->>'jpg'   as jpg,
    count(*)                      as count
from
    cameras
group by
    config->>'external_http_port',
    config->>'external_host',
    config->'snapshots'->>'jpg'
-- having                              -- to show only those
--     count(*) > 1                    -- with count > 1
  ;

Tested in SQLfiddle.


How to correctly implement optimistic locking in MySQL

Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.

Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.

In (C) issue:

CREATE TABLE test(
    id integer PRIMARY KEY,
    data varchar(255) not null,
    version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
BEGIN;
LOCK TABLES test WRITE;

In both (A) and (B) issue an UPDATE that tests and sets the row version, changing the winner text in each so you can see which session is which:

-- In (A):

BEGIN;
UPDATE test SET data = 'winnerA',
            version = version + 1
WHERE id = 1 AND version = 0;

-- in (B):

BEGIN;
UPDATE test SET data = 'winnerB',
            version = version + 1
WHERE id = 1 AND version = 0;

Now in (C), UNLOCK TABLES; to release the lock.

(A) and (B) will race for the row lock. One of them will win and get the lock. The other will block on the lock. The winner who got the lock will proceed to change the row. Assuming (A) is the winner, you can now see the changed row (still uncommitted so not visible to other transactions) with a SELECT * FROM test WHERE id = 1.

Now COMMIT in the winner session, say (A).

(B) will get the lock and proceed with the update. However, the version no longer matches, so it will change no rows, as reported by the row count result. Only one UPDATE had any effect, and the client application can clearly see which UPDATE succeeded and which failed. No further locking is necessary.

See session logs at pastebin here. I used mysql --prompt="A> " etc to make it easy to tell the difference between sessions. I copied and pasted the output interleaved in time sequence, so it's not totally raw output and it's possible I could've made errors copying and pasting it. Test it yourself to see.

If you had not added a row version field, then you would need to SELECT ... FOR UPDATE to be able to reliably ensure ordering.

If you think about it, a SELECT ... FOR UPDATE is completely redundant if you're immediately doing an UPDATE without re-using data from the SELECT, or if you're using row versioning. The UPDATE will take a lock anyway. If someone else updates the row between your read and subsequent write, your version won't match anymore so your update will fail. That's how optimistic locking works.

The purpose of SELECT ... FOR UPDATE is:

To manage lock ordering to avoid deadlocks; and
To extend the span of a row lock for when you want to read data from a row, change it in the application, and write a new row that's based on the original one without having to use SERIALIZABLE isolation or row versioning.

You do not need to use both optimistic locking (row versioning) and SELECT ... FOR UPDATE. Use one or the other.


SQL Server Management Studio - connection with SQL Server database engine

The port 1433 is reserved for the default instance (MSSQLSERVER). When you have a named instance, where you can connect via "IP-Address"\"InstanceName", the port number is different. You can see the actual port number when you look into your SQL Server Logfile.


SQL query with "INTO OUTFILE" does not work on MariaDB 10.4 but does on 10.0

Use

SELECT *
FROM ( SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone',
              'SSN', 'Credit Card', 'County', 'Comment', 'Company'
       UNION ALL
       SELECT name, address1, address2, city, state, zip, phone,
              ssn, credit_card, county, comment, company
       FROM testcust
       WHERE type = '0'
     ) subquery
ORDER BY name = 'Name' DESC, name
INTO OUTFILE '/tmp/sellers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
);


mysql - merge two large instances to one server (40GB of binaries)

The right answer depend from - what is Your full task description?

is it 12 different databases, which just need to have on same
server?
is it the versions of same databases and You need merge data    in
each table?

in first - You still can use mysqldump, and it will be fasted method without additional steps and/or packages

if size of single dump is big - make dump by single database, table

user#> mysqldump db_name table_name > table_name.sql

Single huge file now must not be a problem with most of OS, but could be difficult for edit.

In second case, need analyse merging logic
simples way - mysqldump with --no-create-info (not CREATE TABLE), but could be problems with duplicate keys.

If some duplicate keys (indexes):

or edit dump (for big files - use sed) and change insert to INSERT IGNORE or UPDATE ON DUPLICATE

or create ETL Job with any of available package and manage merge logic.


Actually, on the link you have, it mentions that you can update only one record, you just have to use cursors to do so. It then points you to the DECLARE CURSOR page.

So first, you would have to declare a cursor from your table with the select statement that would give you what you want.

DECLARE C1 CURSOR
FOR (SELECT * FROM MYTABLE WHERE...);

Move to the value of the record in the cursor that you want.

Then run you update statement against the cursor using the link you mention

UPDATE MYTABLE
SET MYCOLUMN = ?
WHERE CURRENT OF C1;

I believe you need to issue a COMMIT (which closes the cursor unless you specify otherwise in the DECLARE CURSOR clause).

Check out the documentation in the link you have and in the one I have. You may end up having to use a stored procedure to run this.


Why store XML Schema in SQL Server 2012 database?

XML Schemas aren't stored in SQL Server so that an application can access them. The reasons for storing XML Schema Collections are found in how they improve working with the XML data.

According to the MSDN page for XML Schema Collections:

  SQL Server uses this type information to optimize data storage.

  The query-processing engine also uses the schema for type checking and to optimize queries and data modification.

  Also, SQL Server uses the associated XML schema collection, in the case of typed xml, to validate the XML instance. If the XML instance complies with the schema, the database allows the instance to be stored in the system with their type information. Otherwise, it rejects the instance.

And, according to the MSDN page for Compare Typed XML to Untyped XML:

  An XML schema provides the following:

  Validation constraints. Whenever a typed xml instance is assigned to or modified, SQL Server validates the instance.
  Data type information. Schemas provide information about the types of attributes and elements in the xml data type instance. The type information provides more precise operational semantics to the values contained in the instance than is possible with untyped xml. For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. Because of this, typed XML storage can be made significantly more compact than untyped XML.

So the reasons to use an XML Schema Collection are:

Increased functionality
Optimized storage (this one is "iffy": I tested with type="xsd:int" and it took up more space than the untyped, but perhaps other types such as datetime and/or float optimize better)
Increased performance
Validation (i.e. data integrity)

Below are two articles / posts related to the performance benefits of typed XML. There is mention of XML Indexes performing much better with typed XML than with untyped. But, there is also mention of typed XML being worse for DML operations.

Performance tips of using XML data in SQL Server
XML Schema Collections: the XML accelerator pedal


How should I store changes to items in the database?

I'm no expert but I see couple of potential pros and cons.

So if you use table1, and you list each row for after every data change, what will happen? Well if you have lot of small updates like only changing one field at a time, then you will have a lot of redundant data from the unchanged fields. If you only list the fields that have changed and leave the rest NULL, it might be tricky to figure out what your data was like, but not a bad solution. Table1 is ideal for changes to multiple columns

Table2 on the other hand is the opposite. Let's say you update 10 fields for 1,000 rows. Then in table 2, you get 10,000 rows. Each data change also ends up in 10 rows. If you are updating only one field at time for the most part, it will be quite comparable to table1. Table2 works better with updates that only affect one or tops a few columns at a time.

Personally, I'd use the table1 approach. It's simpler and more straightforward. If you do update multiple columns, it only records one row. And if you record only when a field is changed, then you eliminate storing redundant data although your scripts to figure out what the data changes were might be a more complex. On the other hand, if you only have maybe a few hundred thousand rows, then storing redundant data might not be so bad, but if you have a hundred million rows, then you might have some issues.

Hope this helps at least a little bit.


how to optimize grouped select from an already grouped select query

Simpler, just remove the click_value from the inner group and SUM it. No need for the outer sum:

SELECT s.shop_id as shop_id, s.shop_name as shop_name,
      s.pay_type as payment_schedule,
      res.num_clicks, res.cost
FROM Shop AS s
  INNER JOIN (
      SELECT c.shop_id
            count(c.id) as num_clicks,
            SUM(click_value) as cost
      FROM Clicktracker c
        INNER JOIN Shop AS s ON c.shop_id=s.shop_id
      WHERE c.domain='mobi.mysite.org'
      GROUP BY s.shop_id, c.domain
      ) AS res ON s.shop_id=res.shop_id

just something extra, you have "shop_name" and id on the Shop table. Check if you really need the shop_name in your clicktracker table or if it is redundant.


Transaction Log Shipping Primary Server Status not Shown on Secondary Monitoring Server

Have you configured your log shipping correctly?

I've reproduced the issue on SQL Server 2014. So to fix it, you need to setup the correct connection of your secondary/monitor instances using SERVERNAME\INSTANCENAME. Check this link.

  ROOTCAUSE: When the setting up logshipping, for remote monitor server, we check the output of SELECT @@SERVERNAME with that of the name provided in remote server. So if the two names do not match, for example if you are using an alias or a port number or a tcp protocol, then logshipping status report will not show any configuration information. The report will not even depict the primary\secondary server or any files being backed up, copied or restored depending on whether the monitor is a remote monitor or local to Primary/Secondary.

  This behavior is observed when the monitor server is a Remote SQL Instance and we are not connecting to it via servername\instance name which is also the output of SELECT @@SERVERNAME query executed on monitor server itself, but rather connecting via an alias or a protocol or a port number. This happens in SQL Server 2005 as the Primary/Secondary server instances use Linked Servers to connect to the Monitor Server instance.

Once you properly configure log shipping use the follow command below to check the report:

SELECT @@SERVERNAME;
GO
EXEC sp_help_log_shipping_monitor;
GO


Insert RECORD into a table in a trigger function

Actually, you do not have to expand the record manually. As long as number, sequence and types of columns match between the two tables, you can use this much simpler form:

EXECUTE 'INSERT INTO my_table SELECT ($1).*'
USING NEW;

Since your table name seems to be stable, you do not even need dynamic SQL with EXECUTE. Just a plain INSERT:

INSERT INTO my_table SELECT (NEW).*

The parentheses are required for a composite type to make the syntax unambiguous.

Ask yourself what may change in your app in the future:

If the target table should stay in sync with the source table (or target and source are the same), then it's better not to list columns explicitly.
If you want to select only certain columns and do not want to change that with possible updates to the source or target table, it's better to supply a column list.


find the beginning and ending values of sequential runs

To keep all the state information with the current row to avoid having conditions you can so something like:

SELECT start, max(end) FROM (
    SELECT
        CASE WHEN blog_id = @prev + 1 THEN @start:=@start ELSE @start:=blog_id END AS start,
        blog_id AS end,
        @prev:=blog_id
    FROM (SELECT @prev:=NULL, @start:=NULL) AS vars
    JOIN wp_blogs
) a
GROUP BY start;

+---------+------------+
|   start |   max(end) |
|---------+------------|
|       1 |          2 |
|       4 |          4 |
|       6 |          8 |
|      10 |         10 |
+---------+------------+
4 rows in set
Time: 0.003s


Shrinking can have a massive impact on the transaction log, which in turn can dramatically impact the performance of mirroring and, I think to a lesser degree, replication.

My suggestion is: don't try to wave some wand and recover 200+GB right now, but instead, do it in chunks, gradually. Every night or every 6 hours or on some other reasonable interval, run one of these commands:

-- day 1
DBCC SHRINKFILE(data_file_name, 245000);

-- day 2
DBCC SHRINKFILE(data_file_name, 232500);

-- day 3
DBCC SHRINKFILE(data_file_name, 225000);

Yes, that is less satisfying, and obviously takes longer, but who cares? How badly do you need that space right now? Your features and users will be better off without that massive disruption IMHO.

An alternative would be to turn off mirroring and/or replication, and reinitialize them completely once the shrink operation is done (and you have backed up the log and made it the normal size, too). But I think just shrinking a bit at a time is far less complicated, disruptive, and risky.


Questions regarding removing a secondary node from an availability group

Yes.  Primary benefit is that you can keep the listener online.  Downside is that there is still a dependency on the cluster service to keep the listener online, but that shouldn't be a significant risk since there is only one node as it will always own quorum, assuming you remove all quorum votes other than the remaining node's vote.
The listener is a component of the AG, so if you delete the AG, it will delete the listener.  Options are to 1) just remove the DBs from the AG, 2) you can delete the AG and then create a new cluster role that just has the network name and IP address (which is really all that a "listener" is), or 3) delete the AG and add a DNS record for the host name that clients are currently using.  You could also do as you suggest to modify client connections, but I don't know how painful that would be for you.
If you leave it as a one-node cluster, you can simply join a node to it in the future if needed.  If you remove the cluster completely, you simply have to create a new cluster and join the two (or however many) nodes to it.
If you pointed the SSRS instances to the listener name, then you don't need to do anything if you keep the listener or add a DNS record for the listener name.

With your comments about possibly using an AG again in the future, I personally would not delete the AG and cluster.  I would remove the databases from the AG, remove the secondary node from the cluster, and remove all quorum votes except for the one node in the cluster.  Less work now, less work later.

You haven't mentioned anything about how backups are occurring so there's no way for anyone to know whether your backups will continue as expected.

Also, you'll first have to remove the databases from the AG prior to deleting the AG.


Can Postgres timestamp be used to implement client side caching?

I assume that your problem is that when you see a row with a certain timestamp in the table, you want to to be certain that a later query cannot find a new row with a smaller timestamp.
There is no good way to guarantee that unless you serialize processing, which would be bad for performance.
You might use a workaround: if you know that none of your transactions can take longer than 0.1 seconds, consider rows with a timestamp later than current_timestamp - INTERVAL '1 second' as not yet reliable.  This is a heuristic that can go wrong (I/O overload might make your transactions slower than you think), but it should work out most of the time.


How should I save apache logs into a mysql table?

Because this is really logging data that you are capturing, I would first store it in its raw form, then ETL/normalize it as needed.

For searching, you can index the front part of the URLs first few characters. Let's say the options are http:// and https:// - then limit the index size to three characters passed the protocol part.

CREATE TABLE log (
datetime_created DATETIME,
url VARCHAR(1024),
domainname VARCHAR(255),
someotherdata VARCHAR(255),
...
INDEX `idx_domain` (domainname(3)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

InnoDB will allow you to search the table w/o locking it.
Compression will help with disk space.
INDEX idx_domain (domainname(3)) will index the first 3 characters of the domain name column and speed up searches. However, the strategy for REGEXP will be to try to match on the front of the domain name.

Scale will eventually be an issue if the site becomes popular, so buyer beware.


Indexes are not disabled in InnoDB using DISABLE INDEX
(from http://dev.mysql.com/doc/refman/5.1/en/alter-table.html):

For MyISAM tables, key updating can be controlled explicitly. Use
ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique
indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing
indexes

For InnoDB, you're stuck with these hints: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

I think you should also remove the indexes and re-create them after you populated the table (the same thing you tried to do with DISABLE/ENABLE KEYS)