How do I change the default external text editor in psql? I want to change the external editor in psql. But the command select-editor does not work. How can I change it? How do I run the command?

There are environmental variables you can set, from man psql

PSQL_EDITOR, EDITOR, VISUAL
Editor used by the \e, \ef, and \ev commands. These variables are examined in the order listed; the first that is set is used.
The built-in default editors are vi on Unix systems and notepad.exe on Windows systems.

The PSQL_EDITOR is if you want to specify a unique editor for just PostgreSQL. You can probably ignore that as not many people have a different editor for that, but in the event you're using a SQL Editor, go for it.
On Linux, you probably want to change EDITOR. On Ubuntu and Debian, that's done like this,
sudo update-alternatives --config editor


Why is TOP operation in SQL execution plan After searching for a while, I decided to post this question for lack of finding an answer and apologize if there is a similar question/answer out there.

When running the query below on two similarly set up SQL servers, we encounter different execution plans which affects performance and we need help figuring out the cause.

The query:

SELECT process_id
INTO #temp
FROM revrep_revenue_fact
WHERE process_id = 284
DROP TABLE #temp

Execution plan of server A

Execution plan of server B
Server B http://s2.postimg.org/z9fjrfv4n/server_B.png

You'll notice that server B has the TOP physical operation in the actual execution plan and we're trying to figure out why.  Both queries use the same index in the Index Seek.

Here are some details of server A and server B

Server A and B are both

Windows Server 2008 R2 Standard Service Pack 1

24GB RAM

64-bit operating system

SQL Server 2012 Versions obtained by using (SELECT SERVERPROPERTY('ProductVersion'))

Server A SQL version 11.0.3000.0

Server B SQL version 11.0.5058.0

What we've tried

Clearing procedure caches
Rebuilding indexes
Refreshing statistics
SET ROWCOUNT 0

Why does server B have the TOP in the execution plan?  In this simple query example there is no real issues but in a larger query the cost rises for the TOP and we see a performance hit.  Any help debugging this would be much appreciated and we can get you any additional information you may need to help.

Check to ensure the database compatibility level is the same on the 2 servers.  I ran a quick test on a SQL Server 2012 instance and see the TOP operator is introduced if the compatibility level is 100 or lower.  Unless you have a specific reason to do otherwise, it is best to use the 110 (SQL Server 2012) compatibility level on a SQL Server 2012 instance.


Get date from SQL Server audit filename SQL Server audit filenames use the following pattern:

Name_GUID_PartitionNumber_Timestamp.sqlaudit

An example filename below:

Audit-ObjectChanges_2D94C1E8-CCAD-405F-A9F2-A45FA5EDAD8F_0_132327053636850000.sqlaudit

If I query sys.dm_server_audit_status I see the status_time column has a value of 2020-04-30 08:29:22.6070000 for four audits I have. All filenames have 132327053636850000 in the timestamp part of the filename.

So I assume 132327053636850000 = 2020-04-30 08:29:22.6070000, but how can I convert the filename timestamp into datetime format for previous audit files?

Thanks

This looks like number of 100 nanosecond ticks since 1601-01-01 (FILETIME)

So based on that the following works (125911584000000000 is the number of these ticks between 1601 and 2000. This is subtracted first to avoid overflowing an int)

It also looks like the time returned is UTC so you will need to convert to your local time zone (which I have made an assumption about).

DECLARE @value    BIGINT = 132327053636850000,
        @datetime DATETIME2

SELECT @datetime = DATEADD(MILLISECOND,
                           @value%10000000 / 10000,
                             DATEADD(SECOND,
                             ( @value - 125911584000000000 ) / 10000000,
                             CAST('2000-01-01' AS DATETIME2)))

SELECT CONVERT(DATETIME2(3),
                 SWITCHOFFSET(@datetime,
                 DATEPART(TZOFFSET, @datetime AT TIME ZONE 'GMT Standard Time')))

Returns

2020-04-30 08:29:23.685


Does restarting SQL Server speed it up? I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart query plans have to be recompiled, but even including that I wonder if there is a net benefit to this practice.

Is it true that restarting SQL Server daily makes it run faster?

Restarting the server is probably one of the most damaging things for performance. It means you force a cold cache for data, a cold cache for query plans, and all SQL Server internal caches are also nuked in the process. Not to mention that by throwing away all the statistics collected in the operational stats DMVs, you diminish your chances of ever investigating something successfully.

There is no official guidance backing this practice, I have never seen it mentioned in any good practice reputable work, I never hear of a reputable expert ever mentioning this as a practice. Don't do it.


using CASE with ISNULL I have a select statement which is followed by a union. Inside the select, there is a where clause. I am trying to add in the following code to the where clause but I'm having an issue getting it to work out:

declare @BudgetFilter int;
set @Budgetfilter = 0;

AND CASE @BudgetFilter WHEN 0 THEN ISNULL(Project.Contribution, 0) >= 25000 END

The error is with the >. Error message: "Incorrect syntax near '>'."

Some additional background. Budget filter may be one of 3 values. 0,1,2 which translate into >=25K, <25K, <0 respectively.
There are other filters(dropdown value selections) that i am working once I get past this issue.

Thanks.
A

CASE is an expression that returns a single value. You are attempting to use it as control of flow logic to optionally include a filter, and it doesn't work that way. Maybe something like this would work:

AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
  WHEN 0 THEN 25000 ELSE 2000000000 END
-- guessing that 2 billion is enough to always be greater
-- than the highest valid contribution

Expanding to your three conditions, you need to use multiple expressions because you can't use CASE, again, to change >= to <. So something like:

AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
  WHEN 0 THEN 25000 ELSE 2000000000 END
AND COALESCE(Project.Contribution, 0) < CASE
  @BudgetFilter WHEN 1 THEN 25000 WHEN 2 THEN 0 ELSE -2000000000 END)

Or you can use the OR conditionals as mentioned in the comments:

AND ((@BudgetFilter = 0 AND Project.Contribution >= 25000)
  OR (@BudgetFilter IN (1,2) AND Project.Contribution < CASE
    @BudgetFilter WHEN 1 THEN 25000 ELSE 0 END))

Or you can just use dynamic SQL, which may be useful if you have a lot of other criteria and/or you are having difficulty getting consistent behavior from the plans for the above variations:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ...
   WHERE ...
   AND COALESCE(Project.Contribution, 0) '
+ CASE @BudgetFilter
  WHEN 0 THEN ' >= 25000'
  WHEN 1 THEN ' < 25000'
  WHEN 2 THEN ' < 0' END + ';';

EXEC sp_executesql @sql;

(And in case you're wondering why I use COALESCE instead of ISNULL. That said, the COALESCE/ISNULL is really meaningless here, because the COALESCEd value (0) will only potentially meet the < 25000 criteria - and I'm not sure that's what you intended at all.)


How to insert values into a highly normalized database without excessive duplicate checking? The situation:

The lead for our project has decided to use a highly normalized database as our  database design. Meaning that literally every field of a big table is an ID instead of the actual value. His intention is to have no duplicates of any kind, even in places where duplicates don't hurt (peoples first names, that kinda stuff).

This does lead to a problem though: When inserting new data, we need to check every single subtable to see if the value exists (first query), then insert it if it doesn't (second query) or else retrieve the ID, do this for literally every column in the main table (so 30 times or something), and then we can create the object we actually wanted to get. (That's ~60 database hits for creating an object!).

We work in spring, so we use jdbcTemplate to actually build a database connection, and every query is expensive. When we're inserting thousands of new records, or updating them, this actually slows down the database heavily.

This entire process feels completely dirty and wrong to me, and thus I wanted to ask: Is there a better way? Is it possible to have an subquery insert an value if it doesn't exist, or not if it does, and return the actual key in both cases, which gets used immediatly to set the ID in the main table?
Is there an elegant solution to cut down on the number of queries without introducing too much complicated SQL (for team members sakes)?

A little thought...

Formally you have something similar to this simplified structure:

CREATE TABLE slave1 (id PK, value UNIQUE);
CREATE TABLE slave2 (id PK, value UNIQUE);
CREATE TABLE main (id PK, id_slave1 FK, id_slave2 FK);

When you need to insert 2 records (id_1, val_1_1, val_1_2) and (id_2, val_2_1, val_2_2), you execute:

CREATE TEMPORARY TABLE temp (val_slave1, val_slave2) [ENGINE=Memory];

INSERT INTO temp (val_slave1, val_slave2)
VALUES (val_1_1, val_1_2),
       (val_2_1, val_2_2);

INSERT IGNORE INTO slave1 (value)
SELECT DISTINCT val_slave1
FROM temp;

INSERT IGNORE INTO slave2 (value)
SELECT DISTINCT val_slave2
FROM temp;

INSERT INTO main (id_slave1, id_slave2)
SELECT slave1.id, slave2.id
FROM temp
JOIN slave1 ON temp.val_slave1 = slave1.value
JOIN slave2 ON temp.val_slave2 = slave2.value;

The engine of temp may be Memory when the amount of inserted values is low, and InnoDB or something else if inserted data array is huge.

INSERT IGNORE works fast enough over UNIQUE indexed field. It guarantees that no duplicates in slave tables, and that the values which must be inserted will exist in slaves while insert into the main table.

And final query must be fast too - especially when temporary table fields are indexed too.

If you need to insert one record only, then you can, of course, do not use table temp... but I think uniformity is more safe then a little simplification.

Of course, this may be optimized. For example, all inserts may be joined into one stored procedure, and you do not need in "60 database hits", one CALL is enough. Finally you must execute only 3 queries independent by the records count for to insert. And only one of them (inserting into temptable) may be huge (or even it can be divided to a lot of inserts).


Result on execution of any SQL is number 2 After successful execution of any sql in oracle sql command line it shows 2
like i want to see the table or description of any table it show 2 instead of showing the table.

SQL> select * from dual
  2
SQL> select * from user_objects
  2
SQL>

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here and just hit Enter, SQL*Plus will terminate your statement and return the prompt, it can not handle empty lines.


MySQL: Very slow query, not using index, no joins involved I am running what I think is a very simple query:

SELECT SQL_NO_CACHE m.mbr_nbr
FROM tran.member m
WHERE m.cardnbr = 000099999930

The table member is a poorly written one, with 133 columns and there are about 2 million rows. The query is being run in a program to gather the mbr_nbr for 20,000 card numbers. So, the query is being run 20,000 times. Is there a better way to handle this or should I try to optimize the query? Maybe by creating an index on the two fields I need?

Rebuilding a better database isn't an option.

Neither field is the primary key.

You may want the following indexes

ALTER TABLE tran.member ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.member ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);

Then, you could queries like these

Each Member and All Cards for That Member

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.member GROUP BY mbr_nbr;

Each Card and All Members Associated with that Card

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.member GROUP BY cardnbr;

So, that the size of tran.member does not slow things down, try creating a temp table for the purpose of gathering these two columns

DROP TABLE IF EXISTS tran.adhoc_members_cards;
CREATE TABLE tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENGINE=MyISAM;
ALTER TABLE tran.adhoc_members_cards ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.adhoc_members_cards ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);
ALTER TABLE tran.adhoc_members_cards DISABLE KEYS;
INSERT INTO tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.memberl
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENABLE KEYS;

Now, you can run the two queries I mentioned like this:

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.adhoc_members_cards GROUP BY mbr_nbr;
SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards GROUP BY cardnbr;

Search for the cardmember you want as well:

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards WHERE cardnbr = 000099999930;


Join three tables that share a common column I have three tables with the following columns:

Table 1: Hostname, OS, Confidence
Table 2: Hostname, Manufacturer, Model, Serial_Number, Architecture, Memory
Table 3: Hostname, MAC, Interface

It should be, but I can't be certain yet, that if a Hostname exists on one table it will be on all three tables.  I'm not sure why this data is separated into three different tables but for the moment it is, which is what leads to my question.

How can I combine these three tables into one?  I have the following that'll combine two of them but I'm not sure how to modify it to include Table3:

SELECT
    COALESCE(Table1.Hostname, Table2.Hostname) AS Hostname,
    Manufacturer,
    Model,
    Serial_Number,
    Architecture,
    Memory
FROM Table1
FULL OUTER JOIN Table2
ON Table1.Hostname = Table2.Hostname

There are (at least) two ways to write FULL joins between more than 2 tables. Using FULL JOIN multiple times, the expression in the ON condition gets a bit longer but it's pretty simple:

SELECT
    COALESCE(t1.Hostname, t2.Hostname, t3.HostName) AS Hostname,
    t1.OS,
    t1.Confidence,
    t2.Manufacturer,
    -- the rest, non common columns
FROM Table1 AS t1
  FULL OUTER JOIN Table2 AS t2
    ON t2.Hostname = t1.Hostname
  FULL OUTER JOIN Table3 AS t3
    ON t3.Hostname = COALESCE(t1.Hostname, t2.Hostname) ;

Using first a UNION of the common column, then LEFT joins:

SELECT
     u.Hostname,
    t1.OS,
    t1.Confidence,
    t2.Manufacturer,
    -- the rest, non common columns
FROM
    ( SELECT Hostname FROM Table1 UNION
      SELECT Hostname FROM Table2 UNION
      SELECT Hostname FROM Table3
    ) AS u
  LEFT OUTER JOIN Table1 AS t1
    ON t1.Hostname = u.Hostname
  LEFT OUTER JOIN Table2 AS t2
    ON t2.Hostname = u.Hostname
  LEFT OUTER JOIN Table3 AS t3
    ON t3.Hostname = u.Hostname ;


problem with query two different tables with foreign key relations I am having a problem with querying two tables with different foreign key relations... My first query works fine but when adding another table with a different set of relations I am having a problem... Can some one point me in the right direction.

First Query works:

select `title`,`txt`,`img`
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%';

Second query that I am stuck when adding another table:

select `title`,`txt`,`img`
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%' OR (
select `firstname`,`lastname`,`email`,`mi`,`dName`,`phnumber`
from `staff`
inner join `departments`
on `staff`.`department` = `departments`.`did`
inner join `phones`
on `staff`.`phone` = `phones`.`pid`
where `staff`.`keywords` like '%test%');

If you need to combine result sets from different tables, use the UNION operator with two queries. Since the sets have different and unrelated column names, you will need to select a placeholder (usually NULL) in each query for columns in the other query:

select `title`,`txt`,`img`,
NULL, NULL, NULL, NULL, NULL, NULL
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%'
UNION
select NULL, NULL, NULL,
`firstname`,  `lastname`, `email`, `mi`, `dName`, `phnumber`
from `staff`
inner join `departments`
on `staff`.`department` = `departments`.`did`
inner join `phones`
on `staff`.`phone` = `phones`.`pid`
where `staff`.`keywords` like '%test%';


What is InnoDB Buffer Usage and how to clear it? In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage"
It is "95%" even if there is no query running.
Why? It affects my performance in my new queries? There is a way to clear it?

Assuming that your dataset is larger than your buffer pool, having it at 95% usage is not only normal, but a desired state. You want as much information as possible on memory- hardware and resources are there to be used- so that both next reads and writes can be done faster than having to access disk.

A different thing is if that memory usage is being used effectively or not: if your read hit ratio is high and if you can buffer writes effectively, which you can check by observing your number of disk IOPS (by querying InnoDB and OS statistics) and buffer pool content (available on the information_schema tables).

If you server is idle, do not worry about memory usage (again, assuming your dataset is larger than your buffer pool and that your buffer pool is smaller than your physical memory)- the buffer pool will fill naturally as you read and write data; and it will be  use more or less as a more complex version of an LRU policy (data used less will be evicted first). Unless you want to use that memory for something else, because it is not a dedicated server (so, lower your buffer pool usage), most people will envy you as having the buffer pool fully utilized is a desired state.

Please note that most operating systems will cache (files) in the same way- unless memory is used for other things, they will try to fit as much data as possible in memory.

Restarting the server will usually clear most of the buffer pool unless configured to be preloaded.


Why can't I shrink my logfile below to the requested value? Experimenting VLFs behaviors with my test lab. Firsty, I have created two databases (BASE2 & BASE3) with different log files size (1024 & 2048 MO) :

Secondly, I'am running the following queries to determine log space usage and the active portion of the VLF :

USE BASE2
dbcc loginfo
GO
USE BASE3
dbcc loginfo
GO

DBCC SQLPERF(LOGSPACE)

So in my opinion there is plenty of space that I can reclaim. Now starting this query to shrink all databases log files :

DECLARE
@db nvarchar(255),
@fileName nvarchar(255),
@cmd nvarchar(255)

BEGIN

DECLARE dbcursor CURSOR
FOR

SELECT table2.name AS BASE, table1.name AS FICHIER_JOURNAL_BASE

    --table1.database_id,
    --table1.physical_name,
    --table1.type_desc
FROM sys.master_files AS table1

INNER JOIN sys.databases AS table2
ON table1.database_id = table2.database_id

WHERE table1.database_id > 4 AND table1.type_desc = 'LOG'
AND table2.state_desc = 'ONLINE' AND table2.recovery_model_desc = 'FULL';

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @db, @fileName;
WHILE (@@FETCH_STATUS <> -1)
BEGIN

--print @db;

 SET @cmd = 'USE ['+ @db +']; DBCC SHRINKFILE ('''+@fileName+''',512);'
      PRINT 'USE ['+ @db +']; DBCC SHRINKFILE ('''+@fileName+''',512);'
        EXECUTE (@cmd)

FETCH NEXT FROM dbcursor INTO @db, @fileName;
END
CLOSE dbcursor;
DEALLOCATE dbcursor;
END

end here the result :

The requested value was 512MO but the current value on disk is around 650MO. If I restart the query with

USE [BASE2]; DBCC SHRINKFILE ('BASE2_log',8);
USE [BASE3]; DBCC SHRINKFILE ('BASE3_log',8);

the value on disk will be around 250 MO. So why my firt query did not shrink the log files to the requested value 512 MO ? Indeed, There was no active VLF at the end of file and still a lot of unused space.

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

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

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


Postgresql AUTOVACUUM ANALYSE parameters for a specified table I would like to set autovacuum parameters for a specified table.

My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete.
This table has only 2 columns (including the primary key) and 150 rows but it is frequently updated. Each row gets updated every minute after the client process has started.

I've found out that the autovacuum process on this table is taking up to 20 seconds.
From what I've understood so far, this is because the autovacuum process is not executed as frequently as it should.

Following are the parameters set on the server configuration :

autovacuum_analyse_threshold => 50
autovacuum_analyse_scale_factor => 0.1

What parameters do you suggest I set specifically on this table ?

Thanks in advance.

UPDATE 1 : In production, the autovacuum analyse process runs on this table every 2 minutes and it take more than 10s.

UPDATE 2 : Table size is 15998 pages.

UDPATE 3 : I have 99 users (client connections) updating the table every 15 seconds.

Since your table has 15998 pages but only 150 rows, it must be quite bloated.  A one time VACUUM FULL or CLUSTER should fix that.  Then the question would be whether it will just get bloated again.

In order to make it more aggressive, you would probably need to lower autovacuum_naptime (unless you have already done so) because it is already being vacuumed very often.

10 seconds is about how long I would expect it to take to analyze a table of that size under default autovac io throttling settings.  But in that case, it is hard to believe that it is consuming a meaningful amount of CPU time.  Have you measured the CPU usage?

What is the lock interfering with?  If you are routinely running commands that conflict with an ANALYZE, that could explain why the table got so bloated in the first place.


documents don't go into newly added shard I have added a shard to an existing cluster, and when I do db.printShardingStatus() I see a distribution:

mongos> db.printShardingStatus()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5c84149e75f51a74e9796add")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "127.0.0.1:27023",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "127.0.0.1:27024",  "state" : 1 }
        {  "_id" : "shard0002",  "host" : "localhost:27025",  "state" : 1 }
  active mongoses:
        "4.0.6" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  3
        Last reported error:  Error connecting to localhost:27025 (127.0.0.1:27025) :: caused by :: Connection refused
        Time of Reported error:  Sat Mar 09 2019 20:55:55 GMT+0000 (UTC)
        Migration Results for the last 24 hours:
                8 : Success
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
                config.system.sessions
                        shard key: { "_id" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000   1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0000 Timestamp(1, 0)
        {  "_id" : "testdb",  "primary" : "shard0001",  "partitioned" : true,  "version" : {  "uuid" : UUID("68b7f8fe-17f6-4049-87ce-6c616c7746b7"),  "lastMod" : 1 } }
                testdb.testcollection
                        shard key: { "testkey" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000   3
                                shard0001   3
                                shard0002   1
                        { "testkey" : { "$minKey" : 1 } } -->> { "testkey" : "key0" } on : shard0002 Timestamp(9, 0)
                        { "testkey" : "key0" } -->> { "testkey" : "key20968" } on : shard0000 Timestamp(7, 1)
                        { "testkey" : "key20968" } -->> { "testkey" : "key4265" } on : shard0000 Timestamp(4, 0)
                        { "testkey" : "key4265" } -->> { "testkey" : "key53621" } on : shard0000 Timestamp(6, 0)
                        { "testkey" : "key53621" } -->> { "testkey" : "key64595" } on : shard0001 Timestamp(5, 1)
                        { "testkey" : "key64595" } -->> { "testkey" : "key8552" } on : shard0001 Timestamp(9, 1)
                        { "testkey" : "key8552" } -->> { "testkey" : { "$maxKey" : 1 } } on : shard0001 Timestamp(4, 1)

But when I access the newly added shard (shard0002), the collection is empty? Does anyone know why?

Add Shards to a Cluster

As mentioned in MongoDB documentation,

  Adding a shard to a cluster creates an imbalance since the new shard
  has no chunks. While MongoDB begins migrating data to the new shard
  immediately, it can take some time before the cluster balances.


Removing a FAILED node from repmgr cluster How do I remove failed node from a PostgreSQL cluster?

postgres@db-prod-ms47ox-10-3-104-92:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show
Role      | Connection String
* master  | host=db-prod-ms47ox-10-3-105-192.vandelay.io
  standby | host=db-prod-ms47ox-10-3-104-92.vandelay.io
  FAILED  | host=db-prod-lu8spc-10-3-104-145.vandelay.io

Running repmgr cluster cleanup does not remove this node and it appears to stop me from adding a new node into the cluster.

This is with repmgr 2.0 and postgres 9.1

As stated in https://groups.google.com/forum/#!topic/repmgr/R7osmnYLn4g :

  probably an UNREGISTER command is needed for that, for now... just
  delete the entry from repl_nodes

This is done by accessing the master node with the same connection details used by repmgr and issuing the following:

DELETE FROM repmgr_<cluster name>.repl_nodes WHERE name = '<name>';


Replace all rows in a table based on date I need to copy the values from a staging table into a production table in SQL Azure V12, considering the following rule:

  I need to replace all rows for a given day (see datefield column) in the production table with all rows for the same given day in the staging table provided that the extraction date/time (see extract_datetime column) of the rows in the staging table are newer than what is in the production table.

  EDIT: I also must include all new rows for days that don't already exist in the production table. See the purple rows in the table examples below.

The reason for this rule is that records tend to be amended and deleted from the system which we are extracting data from, so even though at one time a given day could have 200 entries for it, it might be that subsequently we only end up with 3 rows for that same given day.

If a day is included in a data extract, it will always have the most up-to-date records for that entire day (from 00:00:00 to 23:59:59 hrs).

Here is an example. Consider the following tables:

In this example, the production table has 9 rows in total, as follows:

01/07/2016: 4 rows
02/07/2016: 2 rows
03/07/2016: 3 rows

Then we do a load what is in the staging table into the production table, and the following happens:

01/07/2016: The 4 rows in production for this day are replaced with the 1 row in the staging for this day, because what is in staging has a newer extraction date/time.
02/07/2016: The 2 rows in production for this day are replaced with the 4 rows in the staging for this day, because what is in staging has a newer extraction date/time.
03/07/2016: The 3 rows in production for this day are kept and those in the staging table are discarded, because what is in staging has an older extraction date/time.
07/07/2016: The 1 row in staging for this day is inserted into the production table. This is a brand new row and there is no match for it in the production table.

So in the end we end up with the following production table:

These data loads are never bigger than a few megabytes, so at other tables with less complex rules what I tend to do is:

First delete all rows in staging for when there are newer records in production;
Delete all rows in production for when there are newer rows in staging; then
Insert the remaining rows from staging into production, then truncate staging for the next load.

In this case I am quick lost as on how to do this matching because it is not really in a row by row basis (so I think), so any advice on how to get this code working is greatly appreciated.

Regards,
P.

This is a potential solution, just based on your sample data only, if your data sample has more complexity, such as in ONE day, you have multiple records of different extract_datetime for both prod and staging tables, we may need to find other ways (but in such scenario, your business rules may need to be modified to remove confusions, currently your business rules and your sample data are excellently matched, i.e. no confusion or grey area)

use tempdb
--drop table dbo.Prod, dbo.Staging, #tmp;
--go

-- create sample tables
create table dbo.Prod  (datefield datetime, field1 varchar(20), extract_datetime datetime);
create table dbo.Staging (datefield datetime, field1 varchar(20), extract_datetime datetime);
create table #tmp (datefield datetime) -- holds the deleted [DateField] value form dbo.prod

go
-- populate sample tables
insert into dbo.Prod (datefield, field1, extract_datetime) values
('20160701', 'P11', '2016-08-02 00:10:00')
,('20160701', 'P12', '2016-08-02 00:10:00')
,('20160701', 'P12', '2016-08-02 00:10:00')
,('20160701', 'P14', '2016-08-02 00:10:00')
,('20160702', 'P21', '2016-08-05 00:10:00')
,('20160702', 'P22', '2016-08-05 00:10:00')
,('20160703', 'P31', '2016-08-05 00:10:00')
,('20160703', 'P32', '2016-08-05 00:10:00')
,('20160703', 'P33', '2016-08-05 00:10:00')
go
insert into dbo.Staging (datefield, field1, extract_datetime) values
('20160701', 'S11', '2016-08-25 00:10:00')
,('20160702', 'S21', '2016-08-25 00:10:00')
,('20160702', 'S22', '2016-08-25 00:10:00')
,('20160702', 'S23', '2016-08-25 00:10:00')
,('20160702', 'S24', '2016-08-25 00:10:00')
,('20160703', 'S31', '2016-07-05 00:10:00')
,('20160703', 'S32', '2016-07-05 00:10:00')
,('20160703', 'S33', '2016-07-05 00:10:00')
,('20160703', 'S34', '2016-07-05 00:10:00')
,('20160703', 'S35', '2016-07-05 00:10:00')
,('20160707', 'S41', '2016-07-05 00:10:00') -- new record as per new requiremnt, 2016/08/15
go

-- delete records from Prod table as per business rules, but log the deleted [DateField] into #tmp for next step use
merge dbo.Prod as p
using dbo.Staging as s
on (p.DateField = s.DateField)
when matched and p.Extract_dateTime < s.Extract_datetime
then
   delete
output deleted.datefield into #tmp;

-- re-populate the prod table with records from staging table
insert into dbo.prod (datefield, field1, extract_datetime)
select datefield, field1, extract_datetime
from dbo.Staging
where Datefield in (select distinct datefield from #tmp)
or Datefield not in (select datefield from dbo.Prod) -- added as per new requirement, 2016/08/15

-- check final result
select * from dbo.prod
order by  Datefield


NTFS filesystem fragmentation & other influence vs database files As probably everybody knows, NTFS has a flaw in design which causes fragmentation during filesystem usage. On the other side, MSSQL uses files as database storage method, and what is more, typical practice is to put several, big, databases on the same filesystem.  Database grow gives perfect example of fragmentation, and it definitely has some influence on SQL server performance.

Question: is there any information about fragmentation of filesystem influence on database/MS SQL Server performance? Or there are  hidden proprietary layers of optimisation of NTFS for MS SQL Server database storage? Are SQL server databases really ordinary files on NTFS or OS treated it as special objects?
What if disk checkdisk working on NTFS changes some filesystem structure? In typical situation it may change content of the filesystem data ( once I lost Oracle database during such, automatic after BSOD reboot check. Yes, it was on physical raid. Yes it was automatic checkdisk filesystem reparation which destroyed rollback logs). Is there any know safety mechanism preventing such accident for MS SQL to occur?
There are know technologies of raw partition storage, but MSSQL don't have such technology build in.

I do not want to start flame war on obvious statements, question is about technical details and about relationships between two addopted by MS products: NTFS and MS SQL Server.

is there any information about fragmentation of filesystem influence on database/MS SQL Server performance?

AFAIK the only thing we have is about error 665 (NTFS metadata fragmentation) which is more of an error than a performance issue.

  Or there are hidden proprietary layers of optimisation of NTFS for MS SQL Server database storage?

There are not. SQL Server is installed on any system just like it is. It also runs on ReFS.

  Are SQL server databases really ordinary files on NTFS or OS treated it as special objects?

Yep, treated as normal files. Nothing special going on. For real.

  Is there any know safety mechanism preventing such accident for MS SQL to occur?

Nothing stopping the underlying filesystem from doing something bad because of corruption, write caching, etc. We do have some built in mechanisms such as write ahead logging (WAL) but nothing specific for NTFS. That's below our purview as an application on Windows. We tell Windows we want to do something, using normal Windows API... what happens after that is anyone's guess.

Related documentation:

Database Files and Filegroups
SQL Server Transaction Log Architecture and Management Guide


SQL Authenticated Account is unable to select a View in MS SQL Server I am trying to select a view using a SQL authenticated account. This view is actually reading a spreadsheet from a local drive (through a LinkedServer). I am getting below error:
Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

Currently, the linked server is set to For a login not defined in the list above, connection will be made using the login's current security context. The spreadsheet is on the same server.
How may I resolve this error and select the view using the same account?
The account is added as db_owner.
P.S If I run the view using a windows authenticated account, it is working fine

If you check the How to Create a Linked Server doc, you'll understand that your current configuration works like this:

Be made using the login's current security context Specify that a connection will be made using the current security context of the
login for logins not defined in the list. If connected to the local
server using Windows Authentication, your windows credentials will be
used to connect to the remote server. If connected to the local server
using SQL Server Authentication, login name and password will be used
to connect to the remote server. In this case a login with the exact
same name and password must exist on the remote server.

Since your SQL Server login doesn't exist on windows you can't access the file. You should create a mapping to that specific login defining a remote user and password with permissions on that server to read the file.
Just beware that anyone logged in SQL Server with that SQL Server user will have the privileges of the Windows login you defined on the mapping.


Stop SQL Server from ordering results? I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server seems to return ordered results even when ORDER BY is not present in the query. This is a problem because I'm concerned that my students will become confused on this point.

Examples of observed behaviour:

By default, data appears to be ordered in primary key order, even if the primary key is nonclustered.
If I use GROUP BY, the result set appears to be in order of the last column.
For example GROUP BY this, that ends up with the data in order of that, which is weird, especially if there is no index on it.

I haven't observed this behavior in other databases such as MySQL, PostgreSQL and SQLite. Is there something fundamentally different about SQL Server which causes it to order results for some queries even without an ORDER BY?

Now it seems to me that ordering the data when not asked is an extra unnecessary step which wastes processing time. Is that true?

And is there some kind of global setting that disables this behavior? Is there a way to get SQL Server to stop ordering data?

It also makes it harder to demonstrate to students that data is not automatically ordered.

Can anyone give me a simple demo script that shows the order of the results changing from execution to execution?

Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It's organized by last name, first name.

If I asked you to get that phone book and read out the names:

SELECT FirstName, LastName FROM dbo.PhoneBook

You would usually read them out to me in last name order. You wouldn't have to sort them - they'd just come out that way because that's how they were already stored.

That's why SQL Server's results can seem sorted by default.

Thing is, it's not guaranteed, and several scenarios can change that. For example, say you walk over to the phone book to read the last names for me, and someone else is already reading through the white pages. You might choose to follow along with them, reading from the same area of the phone book. You would start where they already were (say the M's), and then when that other person reached the end of the phone book (the Z's), you'd circle back to the A's and read the part that you'd missed. That's called merry-go-round scanning, and SQL Server Enterprise Edition does that by default, and you can't turn it off. You can't predict when you're going to get it, either.

Another scenario - say that I only ask you for the first names in our city, not the last names:

SELECT FirstName FROM dbo.PhoneBook

Note that I didn't ask for the names to be sorted. At first, when we only have the white pages, you might use those white pages and yell the answer out to me - but they wouldn't seem sorted. (Our phone book's white pages are ordered by last name, first name, so if you just read from there, the first names will be all over the place.)

If later, someone creates an index just on first name, SQL Server is smart enough to use that for my first-name-only query because it'd be the narrowest/smallest object available to satisfy my query. Suddenly, the results might seem sorted by first name - but that's just a coincidence because there's a new index available to satisfy my query.

  For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

In order to do grouping, you may have to sort data. Taking our phone book example, if I asked you to group everyone together by first name, you would have to sort them by first name in order to accomplish that task. Note the resemblance is between the phone book and the index, not the table (except when the table has a clustered index, in which case the table is the clustered index, so the table/clustered index has a fixed logical order).

Now for some gentle career advice: because these concepts are new to you, I would humbly suggest that rather than forcing SQL Server to do your bidding, thinking that you're somehow going to tune it to go faster, that you take a few steps back. You're asking a great curiosity question here, so I'd suggest watching my free SQL Server training video series, How to Think Like the SQL Server Engine. I use pages from the Stack Overflow database to explain how SQL Server delivers your query results.

My suggestion might seem a little controversial - after all, I'm pitching my own training here - but it's free, and it's even open source, licensed under the MIT license for folks to reuse. After you've mastered it by watching me do it, you can take the slide deck and train your fellow teammates.


Can I use a rule to insert aggregate data into a new table? I can use a RULE for bulk inserts into partitioned tables. For that I used something like

DO INSTEAD
INSERT INTO child_table (col1, col2)
VALUES (NEW.col1, NEW.col2)

From the RULES docs:

  command
  The command or commands that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

How do I write something like?

DO ALSO
INSERT INTO agg_table (col1, agg_col)
VALUES (NEW.col1, AVG(NEW.col2))
GROUP BY NEW.col1

Which gives me a syntax error at GROUP

Trigger Transition Tables

First you should be using TRIGGER and not rules when you can. Second, what you want is possible with version 10's, REFERENCING

  The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

CREATE TRIGGER AFTER INSERT
  ON tblfoo
  REFERENCING NEW TABLE as new
  FOR EACH STATEMENT
  EXECUTE PROCEDURE whatever();

Now whatever has access to new. You can select the avg() and put it wherever you want.

See also,

What is a “transition table" in Postgres?


How does adding LIMIT work with Where conditions in mysql? In my database, there is only one record which satisfies the condition iv_title = 'ball skills'.
Now I'm executing the following query :

SELECT
    *
FROM
    `imageAndVideo`
WHERE
    `iv_title` = 'ball skills' AND `iv_file_type` = 2
ORDER BY
    `iv_create`
DESC
LIMIT 1, 6

This above query doesn't output any record, I don't know why? Can somebody please explain this to me?
If I execute the below (removing LIMIT) query I get the desired limit.

SELECT
    *
FROM
    `imageAndVideo`
WHERE
    `iv_title` = 'ball skills' AND `iv_file_type` = 2
ORDER BY
    `iv_create`
DESC

Your LIMIT 1, 6 is short for LIMIT 6 OFFSET 1, in MySQL parlance. OFFSET means skip this many records from the start of the result set. So what you're saying is, give me 6 records after skipping 1 (records 2 through 7, starting at 1). With only 1 record, and skipping that 1, you have no results.


Find status of shutdown immediate I just issued a shutdown immediate command, and it's taking a lot of time. Is there any way, I can find the status of the shutdown task in terms of percentage completed? Or, any kind of status update. It's driving me crazy not knowing what's happening. I know it rolls back uncommited transactions, but I want to know how much of that has been done, how much is left etc.

Assuming you are running some flavour of UNIX, tail the alert log to see what is happening with your shutdown


Query three tables result into one row Table 1 - users

------------
id  | name
------------
 1  | foobar
 2  | barfoo

Table 2 - locations

-------------
id | loc_name
-------------
 1 | Location1
 2 | Location2
 3 | Location3
 4 | Location4

Table 3 - users_location

-----------------
user_id | loc_id
-----------------
 1      | 3
 1      | 4
 2      | 1
 2      | 2

The output that I'm looking for is this one

-------------------------------
name    | loc_name
-------------------------------
 foobar | Location3,Location4
 barfoo | Location1,Location2

Currently here is the code I got

SELECT users.name,locations.loc_name
FROM users
JOIN users_location ON users_location.user_id = users.id
JOIN locations ON locations.id = users_location.loc_id

Here is my current output:

----------------------
 name   | loc_name
----------------------
 foobar | Location3
 foobar | Location4
 barfoo | Location1
 barfoo | Location2

You can use GROUP_CONCAT to get your expected result:

SELECT US.name, GROUP_CONCAT(LO.loc_name) AS loc_name
FROM users AS US
JOIN users_location AS UL ON UL.user_id = US.id
JOIN locations AS LO ON LO.id = UL.loc_id
GROUP BY US.name;

Also you can use table alias users AS US, for better readability.

As per the comments, for any separator other than comma, you may use [SEPARATOR str_val] also ORDER BY will ensure the expected output in the ascending or descending order.

Sample code for hyphen as separator:

GROUP_CONCAT(LO.loc_name ORDER BY LO.loc_name SEPARATOR '-') AS loc_name


How monitor TEMP space allocation for a particular object type like Clob\Blob I am new to Oracle and i wanted to know if there is a way to monitor Clob\Blob objects allocation in Oracle Temp Space.

With JDBC 4.0, we have clob.free() and blob.free() methods and i want a way to verify the benefits using these methods in my application.

Does anyone know how can i do that and/or possibly point me in the right direction?

If you are connecting to an Oracle instance 11.2+, you can fetch data from the column temp_space_allocated of the view v$active_session_history, which shows the amount of TEMP memory (in bytes) consumed by your session at the time the sample was taken.

Otherwise, you may look at the V$TEMPSEG_USAGE view, column blocks, and convert it to megabytes depending on your tbs block size.


How SQL Server knows when to implicitly convert the values Following my previous question: Implicit conversion does not affect performance

I used below simple query

select count(*)
from fpc
where SKey in ('201701', '201702A')

SKey is of type int and I have non clustered columnstore index on it.

obviously I cannot run this because the second value is not a number. so I hit Ctrl+L to see estimated execution plan and I see an interesting thing in Predicate property:

[mydb].[dbo].[fpc].[SKey]=CONVERT_IMPLICIT(int,'201702A',0) OR
[mydb].[dbo].[fpc].[SKey]=(201701)

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A'

what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not?

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A'
what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not?

SQL Server attempts to convert both strings to the correct type (according to the data type precedence rules) for comparison with the integer Skey column during the Constant Folding phase of query compilation. This activity occurs very early in the process, well before even the simplest of query plans is considered.
When constant folding is successful, the input tree contains the derived literal value (as the correct type) and optimization continues, just as if the query writer had used a constant rather than an expression.
When constant folding is unsuccessful (for example because the conversion would throw an error) the tree contains a conversion function. It would not be correct to throw an error at compilation time; an error should only occur when the query is executed, and the problematic expression is actually evaluated (if at all).
So, in your case, '201701' is constant-folded to integer 201701, but '201702A' becomes CONVERT_IMPLICIT(int,'201702A',0).
Constant folding is much more powerful and complete than the above simple example would suggest. For example:
LastName LIKE SUBSTRING(LEFT(NCHAR(UNICODE(NCHAR(68))), 1) + N'%', 1, 2)

is constant-folded to:
LastName LIKE N'D%'

In SQL Server 2012 and later, even deterministic SQLCLR scalar functions can be constant-folded.


Is postgres vacuum secure Postgres delete doesn't actually remove the record from the table file.

Once the delete is committed and the last transaction is closed, no postgres process can see the record any more, the deleted record is now liable to be vacuumed.

Does vacuum (or some other system) overwrite the deleted data or is some of it left sitting in the table file?

Obviously vacuum full and cluster will both re-write the files omitting the deleted record, but I'm particularly interested if there are any details about what ordinary vacuum will or, will not, do to fields stored in the heap and TOAST files.

Citing the reference:

  VACUUM FULL rewrites the entire contents of the table into a new disk
  file with no extra space, allowing unused space to be returned to the
  operating system

Without FULL, the database files are not even truncated, so data may still be in the files. With FULL, deleted data should be gone from the files (since they are recreated). However, data may still be in deleted parts of the underlying file system of course.

In any case, if it is important to you that data is destroyed, you must probably erase or destroy the disk.


SQL Server 2008 log shipping via JungleDisk Has anyone used Jungle Disk as a common location for SQL Server 2008 log shipping? We use it for backup already and I think it can be used for taking log backup to our off-site location.

It seems like it will work but I have no personal experience using it and would like to know more about Jungle Disk reliability/suitability for this.

I would like to answer my own question - Jungle disk can successfully be used as transport for logs to be delivered to off-site location. I have it working with SQL Server 2008.

Before following steps I outlined below I recommend familiarizing yourself with log shipping by first setting it up within the same LAN - I found that wizard works very well for that. This way you will know what to expect when log shipping works (or doesn't work) properly.

These are the steps (if you also want primary database to be mirrored it is best to set up mirroring first):

1) Take full database backup and log backup on your primary database. Bring these 2 files offsite and restore them using "RESTORE WITH STANDBY" mode.

2) Configure log shipping on primary only without a witness server (witness may work but in my case off-site location is not connected to primary via VPN, so secondary server would not be routable and witness server wouldn't be able to see it). Set logs to be saved to network share on your LAN. This share should point to "real" hard drive, not to Jungle Disk directly (even though this will work too but may slow things down).

3) In out setup in primary location Jungle disk is installed and mounted as a drive letter on the server that hosts file share for logs to be saved. In Jungle Disc Configuration go to "Network Drive/Sync Folders" and setup sync for local folder corresponding the share to be synced with folder on Jungle disk. I use "All Files in this folder" for "What to Sync" and "Make Network drive match this folder" for "How to Sync"

4) Wait a little to make sure log backups for primary database appear in folder on Jungle disk.

5) On off-site computer hosting SQL Server mount Jungle Disk as a drive letter (it is preferable to have a separate server for that  and exposed folder with logs via file share but I am describing configuration I have up and running). Make sure that log backups are visible and accessible on Jungle disk

6) Configure log shipping on the secondary.

The way I did this was to temporarily expose secondary via firewall/external IP (you can use SQL Server Auth to connect). Then you can use log shipping wizard in Database Properties to configure secondary (make sure to specify correct folder for log backup destination - this would be local folder on secondary server) but DO NOT PRESS OK to actually finish configuration and create jobs on secondary - if you do it will try to use network share from primary location as a source for log backups and it will not work. Instead select "Script Configuration to new Query window". This script will have several parts: one that should be run on primary - delete that one because primary is already configured; another is for secondary - it will have this text in the beginning:

-- Execute the following statements at the Secondary to configure Log Shipping
-- for the database [$PublicIP\$InstanceName].[$DatabaseName],
-- the script needs to be run at the Secondary in the context of the [msdb] database.

Copy/paste this part to new query window on secondary server. Search this script for @backup_source_directory - it will be set to file share in primary location. You need to modify it to point to the folder on Jungle Disk that contains your log backup

Run the script and it should create 3 jobs on the secondary server - LSAlert*, LSCopy* and LSRestore*

You log shipping now should start working on secondary. To be sure check job history.

If you ever need to modify log shipping parameters such as restore delay, threshold, history retention on secondary server - you need to modify these parameters directly in system databases (as opposed to using log shipping wizard because you log shipping configuration on primary from which you would ordinarily use wizard will not have secondary listed at all). Tables you are interested in are in msdb database, everything that starts from "dbo.log_shipping_" - the one I had to use so far is [msdb].[dbo].[log_shipping_monitor_secondary] for alert settings and [msdb].[dbo].[log_shipping_secondary] for some configuration settings (backup source/destination, file retention period)

Hope this helps.


LRM-00101: unknown parameter name '_first_spare_parameter' I am part-way through an upgrade from Oracle 11g to 18c on RedHat.

The Database Upgrade Assistant gives the message:

Errors encountered during the execution of "Database Components Upgrade".

Oracle_Server.log shows:

select count(*) from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
select count(*) from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
LRM-00101: unknown parameter name '_first_spare_parameter'
ORA-01078: failure in processing system parameters
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0

How can I resolve the issue LRM-00101: unknown parameter name '_first_spare_parameter'?

From the spfile create a pfile, which can be edited directly:

sqlplus / as sysdba
create pfile='/tmp/pfile.ora' from spfile;
exit

Edit /tmp/pfile.ora, remove _first_spare_parameter. Then create the spfile again:

sqlplus / as sysdba
create spfile from pfile='/tmp/pfile.ora';

Now you can try starting your instance.

Above should work in a standard single instance environment, in a customized or clustered environment you may need extra steps.


Generating a generic INSERT/UPDATE Trigger to conform data? I am trying to generate triggers on a number of tables to force data to upper-case and to trim leading and trailing spaces when data is inserted or updated.  The issue I am having is determining in the trigger a general way to tie the INSERTED pseudotable to the base table.  Not all of the tables have the same PK name although many do have a unique column called _DataChanges_RowID in each table which can be used.  I know that I can inspect the PK and generate teh triggers using those columns, but before I look at that, I wanted to know if there is a generic way to join the INSERTED pseudotable to the base table which is simpler and column agnostic.

DECLARE @TriggerTemplate AS VARCHAR(MAX) =
'CREATE TRIGGER <<SchemaName>>.<<TriggerName>> ON <<FullTableName>>
AFTER INSERT, UPDATE
AS BEGIN
    IF (ROWCOUNT_BIG() = 0)
        RETURN;
    IF TRIGGER_NESTLEVEL(( SELECT object_id FROM sys.triggers WHERE QUOTENAME(name) = ''<<TriggerName>>'' ), ''AFTER'', ''DML'') < 1
    UPDATE <<FullTableName>>
    SET <<SQLColumnUpdate>>
    WHERE <<FullTableName>>._DataChanges_RowID IN (SELECT _DataChanges_RowID FROM INSERTED);
END;
GO
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''INSERT'';
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''UPDATE'';
GO
';

WITH cols AS
    (
        SELECT
            FullTableName = QUOTENAME(S.name) + '.' + QUOTENAME(T.name),
            SchemaName = QUOTENAME(S.name),
            TableName = QUOTENAME(T.name),
            ColumnName = QUOTENAME(C.name)
        FROM
            sys.columns C
            INNER JOIN sys.tables T
                ON C.object_id = T.object_id
            INNER JOIN sys.schemas S
                ON T.schema_id = S.schema_id
        WHERE
            C.is_computed = 0
            AND C.system_type_id IN (
                    167,  -- varchar
                    175,  -- char
                    231,  -- nvarchar
                    239   -- nchar
                )
    )
, TablesAndColumns AS (
    SELECT
        cols.FullTableName
        , cols.TableName
        , cols.SchemaName
        , TriggerName = QUOTENAME('TRG_DATA_CONFORM_' + cols.FullTableName)
        , SQLColumnUpdate = STUFF((SELECT ', ' + c2.ColumnName + ' = UPPER(LTRIM(RTRIM(' + c2.ColumnName + ')))'
               FROM cols c2
              WHERE c2.FullTableName = cols.FullTableName
              ORDER BY c2.ColumnName
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')
    FROM cols
    GROUP BY cols.FullTableName, cols.SchemaName, cols.TableName
)
SELECT FullTableName
    , SQLTrigger = REPLACE(REPLACE(REPLACE(REPLACE(@TriggerTemplate, '<<SQLColumnUpdate>>', SQLColumnUpdate), '<<TriggerName>>', TriggerName), '<<FullTableName>>', FullTableName), '<<SchemaName>>', SchemaName)
    , KeyOK = CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE QUOTENAME(TABLE_SCHEMA) = SchemaName AND QUOTENAME(TABLE_NAME) = TableName AND COLUMN_NAME = '_DataChanges_RowID') THEN 1 ELSE 0 END
FROM TablesAndColumns
;

No, there is no generic, column-agnostic way to join the INSERTED pseudo-table to the base table. You will need to use a conventional inner join and reference the PK column (or columns if the key is composite) explicitly in the join predicate.

Therefore, if the PK column is not the same in each of the tables you are generating the triggers for, there is no way around querying metadata to find out the column name to use. You can do that using the SQL Standard-compliant (well, more or less) INFORMATION_SCHEMA views (example) or using the native SQL Server system catalogues (example).


Query to determine a count by rolling interval I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of our messageing.  Messageing is triggered based on orders in this case.  The idea is that a customer will reciev only one message regardless of how many orders placed untill the interval rolls over. for example

John orders on Monday,  message goes out.  (count = 1)
John orders on Thurday,
John orders on Sunday,
John orders on Tuesday  message goes out.  (count = 2)

To further complicate things there isnt a static set of intervals.  The interval begins for the customer at the time of their first order.  Thus Johns interval starts with Monday but Susans interval may start with Friday.

This is what I've got so far.  Any help would be appreciated.

DECLARE @Interval INT = 7

SELECT o.intappid,
       o.intCustomerID,
       MAX(o.intOrderID),
       MAX(o.dtshipdate),
       DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval
  FROM .dbo.[order] o
 WHERE YEAR(o.dtShipDate) = 2012
   AND o.intAppID = 18
   AND o.intCustomerID = 296
 GROUP BY o.intCustomerID, o.intAppID, DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval
 order by o.intAppID, o.intcustomerid, DATEPART(DAYOFYEAR, o.dtshipdate)/@Interval

sample output from this query

intappid    intCustomerID   (No column name)    (No column name)    (No column name)
18          296             21954               2012-03-07          9
18          296             22671               2012-04-25          16
18          296             22728               2012-04-30          17
18          296             23354               2012-06-14          23

Try this:

establish the base date per customer. Something like SELECT MIN(dtshipdate) ... GROUP BY intCustomerId You can do this as a CTE or put the data in some table.
JOIN order to the above and calculate the number of intervals passed since first order
get the ROW_NUMBER partition by the result of the calculation in step 2, order by the date.
any row with ROW_NUMBER() = 1 needs to send a message.

This should work if the date of the first order sets the intervals. If you need to establish a new interval base date with each new order after a break longer than the interval, leave a comment below.


Disk Read IO Throttle not showing in perfmon Windows Server 2012 r2 I am working on Windows Server 2012r2,SQL Server 2014.

I am trying to create a new data collector to review Disk IO in perfmon. When I get to the step to add the available counters there is no Disk Read IO Throttle per sec in the list to be added.

I did see it in screenshots in other web resources. Is there a way to add it in the perfmon so that it appears as a counter?

What you are referring is not related to windows. It's for *NIX systems.

For windows, you should monitor

•\LogicalDisk\Avg. Disk Sec/Read
•\LogicalDisk\Avg. Disk Sec/Write
•\LogicalDisk\Disk Bytes/Sec
•\LogicalDisk\Disk Reads/Sec
•\LogicalDisk\Disk Writes/Sec
•\LogicalDisk\Split IO/sec
•\LogicalDisk\Disk Transfers/sec

You can use PAL to analyze the .blg file for any bottlenecks.

Check : http://blogs.technet.com/b/cotw/archive/2009/03/18/analyzing-storage-performance.aspx


Connecting to an SQL server instance without using square brackets to avoid backslash In our company we have an old piece of software which was written by our previous I.T manager but was never finished.

The program connects to SQL server but we want it to point to a newer SQL server we have recently configured.

The old instance name (on the current server) is simply server1.

Our new instance name (on the new server) is server2\someinstance.

Changing the programs app.cfg file to point to server server2\someinstance isn't a problem until a certain stage where it joins to another database on the same server. The program then throws the below error:

  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '\'.

If we run the same query in SSMS, we get the same error but to get around this we can put square brackets around the name and then the query completes successfully, e.g [server2\someinstance].

The problem I have is I cannot edit his source code and when attempting to change the DB server in the app.cfg to [server2\someinstance] it won't connect to the instance at all.

The only way I see around this is to reinstall SQL server on the new server, without having any slash at all, e.g server2.

I thought I'd ask the question here in case anyone can offer any other suggestions. I've looked into SQL aliases but am not sure this is what I want (plus when trying it would never connect using the alias name anyway, but that's probably a separate issue).

Thanks.

Create a Server Alias to point to your named instance.

  In SQL Server Configuration Manager, expand SQL Server Native Client
  Configuration, right-click Aliases, and then click New Alias.

  In the Alias Name box, type the name of the alias. Client applications
  use this name when they connect.

  In the Server box, type the name or IP address of a server. For a
  named instance append the instance name.

  In the Protocol box, select the protocol used for this alias.
  Selecting a protocol, changes the title of the optional properties box
  to Port No, Pipe Name, or Connection String.

If you're having trouble getting the client to connect via an alias, I'd suggest detailing the error message in a separate question.


PostgreSQL stop streamig replication I've setup streaming replication between a master and a backup server. Now I want to stop the replication but I've noticed that every time I stop postgres in the backup server some old WALs files stay in the master server.

My question, is there a safe way to stop replication and avoid master accumulating WALs file in the master ?

You'll have to drop the replication slot that is associated with the replication. Check the recovery configuration for the name.

Note that replication will break soon after you do that.


Database scheme for web log analytics I analyse web logs (nginx) and want store (organize) result in DB.
After analyse and aggregate my result looks like:

Date and Time (Truncate it by hours)
Country
City
URL (Page on the website)
Referrer (source website)
UTM_Medium
UTM_Campaign
c_sessions
c_views
avg_session_time
c_js_event_1
c_js_event_2
c_js_event_3

And so on, about 30 columns.

For that data I prepare "big table"

CREATE TABLE public.cca_data_hours
(
  datetime timestamp without time zone NOT NULL,
  id_geo_country integer NOT NULL,
  id_geo_city integer NOT NULL,
  id_web_page bigint NOT NULL,
  id_web_referrer bigint NOT NULL,
  id_utm_campaign bigint NOT NULL,
  id_utm_medium bigint NOT NULL,
  c_sessions integer,
  c_views integer,
  avg_sd integer,
  c_jse_1 integer,
  c_jse_2 integer,
  c_jse_3 integer,
  ...
  CONSTRAINT cca_data_hours_pri PRIMARY KEY (datetime, id_web_page, id_web_referrer, id_utm_campaign, id_utm_medium, id_geo_country, id_geo_city)
)

I expect 100 000 000 records.

My selections

I want see all data by countries, cities
I want see data per page
I want see data per source (referrer)
I want see data per page, source and utm campaign
Some other combinations

My questions

Is it normal use so many columns as primary key (index)? Primary key now contains 7 columns, it may up to 10-15 columns in the future.
I think this is a simple solution how to organize this data in one  object-relational DB table. Do you know better solution, practice?

So many columns in a primary key sounds strange, at least.  That means to me that the data is not really adhering to a sensible uniqueness definition - this is more often than not like this in the case of logs.  For this reason, I wouldn't even try to find a PK here, just define indexes based on the queries used against the table.  One could call this opportunistic indexing ;)

Note that in general, in a relational models PKs are very important.  Here it is more the case the data is not really relational, only stored in a RDBMS.


When is a multi-attribute index less beneficial? I'm studying non-key organizations to know when are they better to use than classical key ones.
With the following SQL queries, which of them takes less benefices from the multi-attribute index on R with A as a first attribute and B as the second attribute ?

SELECT * FROM R WHERE A = 10;
SELECT * FROM R WHERE B = 20;
SELECT * FROM R WHERE A >B;
SELECT * FROM R WHERE A>C;
SELECT * FROM R WHERE C<100 AND A =10;

I would say that those which aren't equality searches are those that may have less benefices from the multi-attribute index, but I'm not sure why...

Here is an an example of a multi-attribute index (which remains as a link for I don't know which reason ... :

I'm going to have to make a couple of assumptions.  First that when you said + you meant =.  Next that when you say less benefits you mean less benefits than the other options.

First lets say there are four indexes (based on your image)

R(Age)
R(Sal)
R(Age, Sal)
R(Sal, Age)

And re-writing your queries to match:

SELECT * FROM R WHERE Age = 10;
SELECT * FROM R WHERE Sal = 20;
SELECT * FROM R WHERE Age > Sal;
SELECT * FROM R WHERE Age > Col3;
SELECT * FROM R WHERE Col3 < 100 AND Age = 10;

First of all let's look at the easy ones:

SELECT * FROM R WHERE Age = 10;
SELECT * FROM R WHERE Sal = 20;
SELECT * FROM R WHERE Age > Col3;
SELECT * FROM R WHERE Col3 < 100 AND Age = 10;

The reason these are easy is because they only reference one or the other of the columns being indexed.  That being the case I'm going to use one example to server for all of them.

SELECT * FROM R WHERE Age = 10;

In this case the R(Age) index will be best. It is the shortest possible index on that column.  Next R(Age, Sal). This is more than sufficient because Age is still first in the list, meaning that seeks will still work or even a scan of everything between two points. The only down side here is that it is slightly longer and so a bit slower.  The difference in speed however is pretty negligible so if I had a reason for creating R(Age, Sal) that's what I would create and I wouldn't mess with R(Age). Lastly R(Sal, Age)' might get used because it's smaller than the entire table, but it will still have to be scanned (just like the unindexed table) to get any useful information. I.E. If my indexed pairs for(Sal, Age)are(10,20), (11,5), (12,45), (12, 2)` and I'm looking for an Age of 45 I'm still going to have to scan through all of the pairs to get anywhere.

Now the last one (the one that references both columns)

SELECT * FROM R WHERE Age > Sal;

In this case the indexes R(Age, Sal) or R(Sal, Age) are equivalent.  Both values are indexed and since both are in the expression it doesn't matter which comes first. Given the expression you are going to have to scan the index (there is no way to use the order of either value to help) but it's better than scanning the whole table.  I'm honestly not sure if R(Age) or R(Sal) would be of any user or not.  They contain some of the required information, but you still have to go back to the original table to get the remaining information. It might be faster just to scan the table itself.

Just to add an example of a query that would benefit from a multi-attribute index.  Let's say your query looked like this:

SELECT * FROM R WHERE Age = 10 AND Sal = 40;

At this point the system can benefit from a multi-attribute index.  It can seek to find all rows where Age = 10 and then seek in those rows for cases where Sal = 40.  The best order (R(Age, Sal) vs R(Age, Sal)) is going to depend. For example: how many possible values are there? If there are 100 possible Ages, but only 5 possible values for Sal then R(Age, Sal) would probably work best.  And vise-versa of course.


Restaurant reservations - Tables combinations I have 3 tables in a reservation system for a restaurant. So far the software was used only by restaurant's staff, however we want to accept reservations online as well. We have some small tables for 2 that can be easily moved to each other and make room for bigger parties and I want to accept reservations automatically if all of the tables that can be combined are available.
tables: holds all tables for each area in the restaurant.
| id | min_capacity | max_capacity | name | area   |
|----|--------------|--------------|------|--------|
| 1  | 2            | 4            | #1   | Inside |
| 2  | 6            | 8            | #2   | Inside |

reservations: holds reservation details
| id | datetime            | name     | status   |
|----|---------------------|----------|----------|
| 1  | 2020-09-01 20:00:00 | John Doe | Upcoming |
| 2  | 2020-09-05 13:00:00 | Jane Doe | Upcoming |

And one pivot table that holds reservation <=> table relation:
| id | table_id | reservation_id |
|----|----------|----------------|
| 1  | 1        | 1              |
| 2  | 2        | 2              |

How can I store different combinations of tables (manually entered) and "attach" reservations to tables/table combinations (so I can check if tables are available for specific time) efficiently?
Edit: More details, as I am nowhere close to finding a solution:
I am trying to figure out a way of "describing" which tables can be combined with other tables and "discard" a combination if one of the tables in that combination is already reserved.
For example I have 3 small restaurant tables (not database tables :D):

#1 for 2-4 people
#2 for 2-4 people
#3 for 2-4 people

Tables #1 and #2 can be combined to make a table for 4-6 people and tables #2 and #3 can be combined to make a table for 4-6 people.
But! tables #1 and #3 cannot be combined, as they are too far away from each other.
Here's a little drawing of the situation: Floorplan & Combos
I want to manually "describe" which table can be combined with different table. I don't know how to store this data so I can reserve table #1 and that automatically "disqualifies" Combination #1 and Combination #3 (from the drawing) for the specific date & time.

It's a pretty interesting question and in general a very tricky one. For your question, there are some limitations that make it a lot easier. I'll assume here that a table can be combined with another table if there id:s are adjacent.
create table tables
( tabid int not null primary key
, min_capacity int default 2 not null
, max_capacity int default 4 not null );

insert into tables (tabid) values (1),(2),(3);

If such an assumption can't be made, there need to be additional information that describes which table combinations that are valid:
create table related_tables
( tabid int not null
, reltabid int not null
, primary key(tabid, reltabid)
-- anti-reflexive
, check (tabid <> reltabid));

-- symmetric
create view symmetric_related_tables as
select tabid, reltabid from related_tables
union all
select reltabid, tabid from related_tables;

I'll ignore the latter and also further assume that a maximum of 3 tables can be combined:
select x.tabid as tabcombo
    , sum(min_capacity), sum(max_capacity)
from tables x
group by x.tabid
union all
select 10*x.tabid+y.tabid
     , sum(x.min_capacity+y.min_capacity), sum(x.max_capacity+y.max_capacity)
from tables x
join tables y
   on y.tabid = x.tabid+1
group by x.tabid, y.tabid
union all
select 100*x.tabid+10*y.tabid+z.tabid, sum(x.min_capacity+y.min_capacity+z.min_capacity)
     , sum(x.max_capacity+y.max_capacity)
from tables x
join tables y
   on y.tabid = x.tabid+1
join tables z
   on z.tabid = y.tabid+1
   and y.tabid = x.tabid+1
group by x.tabid, y.tabid, z.tabid;

For the first leg of the union, the aggregation is not necessary, but I'll
leave it for generality.
For the sample data above this results in:
tabcombo    sum(min_capacity)   sum(max_capacity)
1   2   4
2   2   4
3   2   4
12  4   8
23  4   8
123 6   8

Some vendors support GROUP BY CUBE, which could have shortened the query, but AFAIK, MySQL does not.
The next question is how long a reservation lasts? If not a fixed amount of time, say 4 hours, we will need a length or a stop time. I'll assume:
create table reservations
( reservation_id int not null primary key
, start_time datetime not null
, end_time datetime not null );

I'm in a bit of a hurry so this will only be a sketch, but for a specific time, the available tables with :S seats at the time :T can be determined as:
select x.tabid
    , sum(min_capacity), sum(max_capacity)
from tables x
where not exists (
    select 1
    from reservations r
    join reservation_tables rt
        on ...
    where rt.tabid = x.tabid
      and #T between r.start_time and r.end_time
)
group by x.tabid
having :S between sum(min_capacity) and sum(max_capacity)
union all
...

for the remaining legs of the union, you will have to check that none of the involved tables are occupied.


Can I do a conditional JOIN or conditional SELECT based on the data in a table? I think I have a problem where EAV is the best choice.

I am planning my tables as follows.

motherboard

motherboard_id | model_name
int            | varchar

Example:

motherboard_id | model_name
----------------------------
1              | Asus Rampage
2              | Intel 3X
3              | Gigabit 22P

attribute

attribute_id  | attribute_name | attribute_type
int           | varchar        | enum('str','int','float')

Example:

attribute_id   | attribute_name| attribute_type
-------------------------------------------------
1              | form_factor   | int
2              | max_ram       | int
3              | ram_type      | str
4              | weight        | float
5              | CPU_socket    | str
6              | PCH           | str

motherboard_attribute_int

attribute_id  | motherboard_id | value
int           | int            | int

Example:

attribute_id   | motherboard_id | value
-----------------------------------------
1              | 1              | 1
2              | 1              | 16384
1              | 2              | 1
2              | 2              | 32,768
1              | 3              | 2
2              | 3              | 8192

motherboard_attribute_str

attribute_id  | motherboard_id | value
int           | int            | varchar

Example:

attribute_id   | motherboard_id | value
--------------------------------------------
3              | 1              | DDR3
5              | 1              | LGA2011
6              | 1              | Intel P55
3              | 2              | DDR3
5              | 2              | LGA3011
3              | 3              | DDR2
5              | 3              | Socket 2

motherboard_attribute_float

attribute_id  | motherboard_id | value
int           | int            | float

Example:

attribute_id   | motherboard_id | value
------------------------------------------
4              | 1              | 250.2
4              | 2              | 250.2
4              | 2              | 110.5

I will have to query on all attributes (use WHERE clause). For example -

  Find all motherboards with weight >150g and max_ram>16M

So, I need to query the attribute table and discover that max_ram in an int and weight is a float. So, I have to join motherboard with motherboard_attribute_int and motherboard_attribute_float

Is there a way to do this?

Is there an alternate database design that would serve this kind of data better?

What you really want is a table full of nullable fields, EAV is hard to get right, and with EAV you literally need three columns, not 6. It would be much easier to just keep adding nullable columns for your needs at this time.

  So there should not be a performance and scalability issue having a table with 20 or 30 columns? What am I trading off when I make a 30 column table?

What are you trading off? Nothing really. Performance and scalability issues are much harder to interpret and diagnose with an EAV format over the traditional wide-table format. You really only end up with tables with lots of null values that you just need good indexes to find the things you want quickly, which is a lot easier than done with an EAV.

Running 1 EAV table per data type is just as horrid as the five+ column design you were describing before from a perf and maint standpoint. You really don't want to do this. You really want to just add a lot of nullable columns to one wide table. You're shooting yourself in the foot and asking us how to bandage it before you begin. EAV is not the design pattern you want right now, in part because you don't understand how the model works. Model your business data, then model your database.

In your case, you want a table for motherboards with vendor, board name/model number, etc. I've modeled a simple table below. EAV would then be used with this table to model things like "link for driver downloads" which are very rarely going to be needed. Think about the main table as being searching details, and the EAV being details that you only need when you show a secondary tab on the UI.

create table motherboards (
   id bigint not null auto_increment
 , vendor_name varchar(100) not null -- would also be good as a lookup column to another table, making this an int or bigint instead
 , board_name varchar(100) not null
 , revision_number varchar(100) null
 , cpu_socket_type not null -- can be an int lookup to another table or a text field, I leave this to you based on the schema you want, but either is acceptable, as is an int with an in-app enum lookup
 , cpu_socket_count int not null default 1
 , ram_socket_type not null -- again, can be one of so many values, you decide the best type of storage
 , ram_socket_count tinyint not null default 1 -- here's where things get tricky, it's possible on some systems (enterprise grade) to have different RAM sockets for different reasons, but we skip that because that's a lot of detail. Wait till you have to cross that bridge, mkay?
 , northbridge_chipset varchar(100) not null -- this makes the most sense to me, but see the cpu_socket_type comment. There are a handful of common ones, so text may be easier to deal with.
 , southbridge_chipset varchar(100) null -- see above

-- This is now the additional details for others
 , rear_port_usb tinyint not null default 2
 , usb_headers tinyint not null default 0
 , upc_number varchar(20) null
 , pcie_socket_count tinyint not null default 0
 , pcie_x2_socket_count tinyint not null default 0
 , pcie_x4_socket_count tinyint not null default 0
 , pcie_x8_socket_count tinyint not null default 0
 , pcie_x16_socket_count tinyint not null default 0
 , sata_headers_count tinyint not null default 0
 , pci_socket_count tinyint not null default 0
 , isa_socket_count tinyint not null default 0
 , rear_port_matrix -- some set of fields to indicate what is on the back panel where people can have access. I would probably either make up a standard "dictionary" of these and do it like an enum/lookup table, or list one per port. Where do you put parallel ports on this lookup list? How many USB are exposed via the backplate? How many USB are exposed on headers on the motherboard?
)

create table motherboards_details (
   id bigint not null (fk back to motherboards)
 , name varchar(100) not null
 , value varchar(100) not null
 , create_date datetime not null default CURRENT_TIMESTAMP -- always good to have for later tracking
)

And that's it. No more EAVs. If you need to use it as a float or an int, you do that in the application.

I have some other suggestions, if you are just hell-bent on using an EAV for tracking details, such as moving all of those socket bits to an EAV, but trust me when I tell you that will become much more tedious than what I gave you. This format lets you search for the details you want (like in a picker UI) and lets you build indexes for commonly searched features once you get a very large table. The only indexes I would suggest are on the board name, the socket type and count, and the ID for lookups. Everything else I would index after putting the system under load with actual data.

The EAV model really doesn't fit this business model right now, so without seeing an actual EAV schema intention, I would recommend you approach it from the style I've outlined above, and ignore the column widths.

The approach I've outlined above is about 500 bytes per record so far, which is relatively tiny. The indexes alone would be hard to maintain.

I hope this helps you reconsider the approach to your problem design, and we as always as a community are happy to help you flesh out the details.


lost all my databases after reinstalling sql server management studio SQL server management studio was not working so, after googling this issue, I found that I have to reinstall it. So, after re installation, I lost my database and I didn't get a backup of it. So, is there any way to recover it back?

Background

Q1

Did you reinstall SSMS or your actual database engine? Can you add a picture with what you are seeing?

OP - A1

Q2

Did you use the same login as before? If you reinstalled your database engine the database files might still be there. Can you check your default Data / Log folders to see if there are any database files there? If yes, you could try Attaching these databases again. What does this return? SELECT IS_SRVROLEMEMBER ('sysadmin')

OP - A2

  Yes, I use the same login as I was using before. I pasted "SELECT
  IS_SRVROLEMEMBER ('sysadmin')" and run it on SQL query window and the
  result is " no column name (1) nothing else. I don't know where my
  database was saved to look on it if I can find any thing related to my
  lost database. Could you please tell us where our databases would be
  saved by default in C drive?

Q3£

This means that you are a sysadmin on the instance and that you should be able to see the database if it exists. You can try finding the system databases by using select physical_name from sys.master_files where database_id = 1 and find  the default data path by executing SELECT SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]

OP - A3

  Yes, I found two files: LostDatabaseName.mdf and
  LostDatabaseName_log.ldf. Now, how can I move them back to SSMS?

Solution

You can try attaching the database to your new instance.

EXEC sp_attach_db @dbname = N'LostDatabaseName',
    @filename1 =
N'C:\FoldersHere\LostDatabaseName.mdf',
    @filename2 =
N'C:\FoldersHere\LostDatabaseName_log.ldf';

An example from Microsoft docs

EXEC sp_attach_db @dbname = N'AdventureWorks2012',
    @filename1 =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf',
    @filename2 =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf';

Source

I would also advise to not keep all your database files onC:\ drive. If your drive fills up your server will crash and corruption can occur. It is also important to take backups of your databases, and putting these on separate locations.


How to log data changes in Access at the table level? I support an Access database for a non-profit organization.  We have some issues with data mysteriously changing, and there been some, um, friendly debate over whether this is caused by users who are not the most computer-literate or by some of my background processing.

I've added some checks at the UI level for these conditions, but I'd like to put some checks at the database table level to log when certain fields are changed.  This way I'll know about the change whether it was initiated by a user or just from an update query running in the background.  Normally I would use an on update trigger, but to my knowledge Access doesn't support triggers.  Is there any other logging or trigger-like functionality available in Access?

Unfortunately, Microsoft Access doesn't have triggers, or anything even resembling triggers.  Off the top of my head, you have a couple of unappealing choices:

Create DateModified and UserModified columns, and update your code to include these values.  The downside is that you'll only know the last person who updated a record, and won't have a history to look at..
Keep your Access front end, and port your tables over to a SQL Server Express back end.  This way, you'll be able to use triggers (as well as back-up jobs and other nice things).


Storing created/retired dates for a large amount of different entities In the db I'm working on now almost every entity has these 4 columns:

CreatedDate
CreatedBy
RetiredDate
RetiredBy

Usually this is used for logging purposes, and for some of the entities the usefulness of knowing when it was retired is arguable (but don't tell my boss). For some other things, (like a truck) it makes more sense because a "retired" vehicle may come back into service.

Anyway, I was wondering if it's a good idea to stick this information into one table since it's repeated all over the place. If it is, anyone have a good name for it..? Simply created_retired_dates?

It seems that you are involved in a project that requires the creation of a temporal database. You may as well find related information by searching for the terms auditable databases and database history tables.
I deem this exceptional Stack Overflow answer as the top material with respect to these topics. In such a post, @PerformanceDBA models an auditable relational database for a very interesting business environment, and the instructiveness contained in there encompasses multiple aspects that are pertinent not only to these themes, but also to database design and practice as a whole.
Background
As you know, a database is built to retain information that is relevant to its users, and information, naturally, can change as time passes. In this way, the values contained in a specific database can suffer successive modifications and, as a consequence, go ceasing to be “current”, but these circumstances do not imply that the previous “states” of the values in question become irrelevant after having undergone their corresponding chronological updates.
In this regard, yes, there are cases in which keeping track of the updates that affect an entity over time is paramount, and there are other cases where changes should be expressly forbidden and prevented, hence the retention of entity alterations would not apply. It may seem a cliché, but these points depend on your exact informational requirements, therefore you have to analyze each particular situation thoroughly so that you can define how to proceed. Then, once “audit trail” has been determined valid and necessary, it must be implemented.
Suggested approach
1. Illustrative IDEF1X diagram
Let us take the Truck entity type as a reference since, in accordance with your specifications, it is a good example of an aspect that entails enabling temporal capabilities. In order to illustrate the approach that I am going to propose to construct said capabilities, I have prepared an IDEF1X1 diagram that is shown in Figure 1 (and you can download it as a PDF from Dropbox, as well):

As demonstrated in the aforesaid diagram, apart from depicting the Truck and User entity types, I have included an additional one that represents the History of Truck (denominated, accordingly, TruckHistory).
The only difference between the Truck and the TruckHistory entity types is the PRIMARY KEY of the latter, since it consists of TruckNumber and a complementary property called AuditedDateTime which, of course, indicates the particular point in time when a given Truck occurrence was “audited” (or updated). It is important to note that TruckHistory.TruckNumber is defined as a FOREIGN KEY that points to Truck.TruckNumber, depicting the type of association that takes place between these two entity types.
2. Resulting expository SQL-DDL logical layout
Taking the present example to the logical level of abstraction, I have derived the following DDL statements from the IDEF1X diagram presented above:
CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        BirthDate,
        GenderCode
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Truck ( -- Contains the “current” versions.
    TruckNumber     INT      NOT NULL,
    OtherColumn     CHAR(10) NOT NULL,
    IsRetired       BIT      NOT NULL,
    CreatedUserId   INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Truck_PK              PRIMARY KEY (TruckNumber),
    CONSTRAINT TruckToUserProfile_FK FOREIGN KEY (CreatedUserId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE TruckHistory ( -- Holds the “past” versions.
    TruckNumber     INT      NOT NULL,
    AuditedDateTime DATETIME NOT NULL,
    OtherColumn     CHAR(10) NOT NULL,
    IsRetired       BIT      NOT NULL,
    CreatedUserId   INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT TruckHistory_PK              PRIMARY KEY (TruckNumber, AuditedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT TruckHistoryToTruck_FK       FOREIGN KEY (TruckNumber)
        REFERENCES Truck (TruckNumber),
    CONSTRAINT TruckHistoryToUserProfile_FK FOREIGN KEY (CreatedUserId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT DateSuccession_CK            CHECK       (AuditedDateTime > CreatedDateTime)
);

Sample data
Having enabled the logical design previously exposed, say that we are keeping the next two rows in the UserProfile table:
+-——————-+-—————————-+-————————-+-——————————-+-——————————-+-———————————————————————-+
| UserId | FirstName | LastName | BirthDate  | GenderCode | CreatedDateTime         |
+-——————-+-—————————-+-————————-+-——————————-+-——————————-+-———————————————————————-+
|      1 | James     | Smith    | 1985-06-30 | M          | 2013-02-12 07:32:04.000 |
+--------+-----------+----------+------------+------------+-------------------------+
|      2 | Nicole    | Johnson  | 1987-10-14 | F          | 2013-03-24 09:02:03.000 |
+--------+-----------+----------+------------+------------+-------------------------+
Then, suppose that the Truck table contains the (“current” or “present”) row that corresponds to the Truck identified by TruckNumber 1750 as displayed bellow:
+-———————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
| TruckNumber | OtherColumn | IsRetired | CreatedUserId | CreatedDateTime         |
+-———————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
|        1750 | Bar         | False     |             2 | 2015-06-30 16:58:12.000 |
+-------------+-------------+-----------+---------------+-------------------------+
And, finally, assume that the Truck identified by TruckNumber 1750 holds the TruckHistory rows that follow:
+-———————————-+-———————————————————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
| TruckNumber | AuditedDateTime         | OtherColumn | IsRetired | CreatedUserId | CreatedDateTime         |
+-———————————-+-———————————————————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
|        1750 | 2013-12-10 17:05:01.000 | Foo         | False     |             1 | 2013-06-30 10:34:12.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2014-03-22 14:08:08.000 | Bar         | True      |             2 | 2013-12-10 17:05:01.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2014-09-14 09:45:06.000 | Bar         | False     |             2 | 2014-03-22 14:08:08.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2015-06-30 16:58:12.000 | Foo         | False     |             1 | 2014-09-14 09:45:06.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
Advantages
Thus, in the TruckHistory table, there is a time series made up of each and every one of the changes related to the Truck identified by TruckNumber 1750. One can know

who are the Users that made such changes (via CreatedUserId);
the exact moment in which the values associated with these changes began to be “current” or “present” (by way of CreatedDateTime); and
the specific instant in which a certain row was modified receiving new values in the “current” Truck table (through AuditedDateTime).

I consider that this configuration is much more advantageous than maintaining only the most recent Date in which each instance of a Truck was updated along with the Identifier of the User who performed the respective update operation but, at the same time, losing every one of the previous entity “states”.
Data manipulation considerations
From a data manipulation point of view, this implies that every time that (1) a precise Truck row suffers an UPDATE, you also have to (2) INSERT into the TruckHistory table the corresponding Truck values that were “current” until the aforementioned Truck UPDATE took effect. I would decidedly carry out these operations with the aid of ACID TRANSACTIONS, so that they are either succeed or fail as a single Unit of Work.
Similar scenario
I do not know how the post revision history is set up by the Stack Exchange team throughout the network, but it provides a functionality that I find very similar to the one offered by the approach I suggest. It would be helpful to examine closely this Stack Exchange process so that you can get a broader perspective about the ways in which scenarios of this nature can be managed.

Endnote
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model of data, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.