c*****t
发帖数: 1879 | 1 不清楚 SQL Server，但是俺从其他系统的角度来说一下。
每个 row 的最大是有限制的。比如说 32-64K 。这样的话，该 row 才有可
以放进一个 data block 里面。data block 是数据库的一个基本储存单位。
这个限制平常看不出来，但是当某个 table 有 1000 column 的话，就体现
出来了。数据库需要考虑到最糟糕的情况，所以如果超过了 row size limit，
就可以拒绝接受该 create table 。这时候就需要对 varchar 的长度精打细算。
PostgreSQL 里面的 text 是介于 varchar 和 clob 之间。当 text 的长度
较小的时候，该文字可以放进 row 里面。较大的时候（比如超过 2000），
就开始先 compress 一下。还是太大的话，用类似 clob 的办法存。当然
这几种办法就没有读写短的 varchar 那么快。
至于 index，其实也就是一个小点的 table ，读写的速度很重要。而越长
的 varchar 的读写也越花时间。事实上除非是 unique index，太长的
va... 阅读全帖 |
|
y****9
发帖数: 144 | 2 I don't think this is a valid explanation, bcoz varchar(800) won't use 10
times more storage than varchar(80). Based on BOL,
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through
8,000. max indicates that the maximum storage size is 2^31-1 bytes. The
storage size is the actual length of data entered + 2 bytes. The data
entered can be 0 characters in length. The ISO synonyms for varchar are char
varying or character varying.
So if I know my data will... 阅读全帖 |
|
g***l
发帖数: 18555 | 3 如果我记得不错，VARCHAR是用POINTER，不是直接存储在TABLE里的，虽然VARCHAR的空
间是可变的，你告诉存储每个80这样存储起来比较容易。比如有一百万RECORD，一百万
个火柴盒好管理好SEARCH呢，还是一百万个火柴盒和摩天大楼的混合体好存贮好SEARCH
呢，结论很清楚的。 |
|
j******f
发帖数: 228 | 4 All,
I'm writing a program to dynamically create tables in a Sybase database
through JDBC. Some of the fields of the tables are VARCHAR type. It seems that
the Sybase DBMS doesn't handle it correctly and keeps throwing exceptions. If
I remove those VARCHAR fields, the program executes correctly. Does any one
have any solution?
Many thanks!! |
|
n********6
发帖数: 1511 | 5 Survey family and the members
family (id, familyid, familymemberid, familyaddress, ...)
social scientists argue to set familyid and familymemberid in int format because they like keep most variable numeric (e.g. weighting.)
DBAs argue to have familyid and familymemberid in varchar format because there it does not make any sense to do calculation on familyid and familymemberid.
What do you think about this?
Street Smart Answer:
Listen to the Boss. If the Boss is a social scientist, do the numeric... 阅读全帖 |
|
S*****0
发帖数: 538 | 6 An index can't be built more than 900 bytes.
In SQL Server, the maximum length for a record is 8060 bytes. If a records
is larger than 8000 (for example, VARCHAR(MAX)), it will be placed on a text
or LOB storage. Someone did a test, and MAX-type column is about 10% skower
than non-MAX-type colum in performance.
To answer your question regarding to the 800 bytes vs 10 bytes. For sure,
the smaller, the better. More data in a data page, more data in memory/cache.
类的 |
|
j*****n
发帖数: 1781 | 7 MSSQL 2k5
Here is a table with about 400 columns, billions of rows; we have a view
does this:
SELECT .....,
CAST (col_n AS VARCHAR(11)) AS col_n, -- col_n is MONEY data type in the
table
...
FROM tbl
We have an SSIS ETL runs every day that pulling millions of rows based on
this view; it has been ran for couple years without problem.
Recently (started few weeks ago), we discovered that randomly this converted
col_n returns few 0's, no matter whatever the actual value is. say 20.12,
or -128.33... 阅读全帖 |
|
n********6
发帖数: 1511 | 8 family is based on the random selection.
familyid is based on time serial.
if varchar, it would be easy for web development
county
...
some |
|
i****a
发帖数: 36252 | 9 there are issue for lookup if you use varchar as ID
performance will be bad for large dataset, and you may run into lookup
errors |
|
y****w
发帖数: 3747 | 10 DBAs argue to have familyid and familymemberid in varchar format because
there it does not make any sense to do calculation on familyid and
familymemberid |
|
h*****p
发帖数: 1516 | 11 一定双黄包奉上，多谢！
(@P1 varchar(30),@P2 varchar(15),@P3 varchar(15),@P4 varchar(15),@P5 varchar
(15),@P6 varchar(15),@P7 varchar(15),@P8 varchar(15),@P9 varchar(15),@P10
varchar(15),@P11 varchar(15),@P12 varchar(15),@P13 varchar(15),@P14 varchar(
15),@P15 varchar(15),@P16 varchar(15),@P17 varchar(15),@P18 varchar(15),@P19
varchar(15),@P20 varchar(15),@P21 varchar(15),@P22 varchar(15),@P23 varchar
(15),@P24 varchar(15),@P25 varchar(15),@P26 varchar(15),@P27 varchar(15),@
P28 varchar(15),@P29 varchar(15)... 阅读全帖 |
|
g***l
发帖数: 18555 | 12 我找到了一个SCRIPT LOGIN的
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint in... 阅读全帖 |
|
s***u
发帖数: 41 | 13 有一个Employee table，很多栏（field)，包含了职员的基本信息和这个职员的badge
的信息。（见附件插图和下面的 table)
CREATE TAbLE Employee
{
emplLastName varchar(30) ,
emplFirstName varchar(30) ,
emplNickname varchar(20) ,
emplCategory varchar(2) ,
emplCampID varchar(10) ,
emplArea varchar(10) ,
emplDepartment varchar(20),
emplTitle varchar(50) ,
emplEmployeeID varchar(9),
emplCardNumber varchar(16),
emplActive tinyint(4) ,
emplComment longtext,
emplRecycleDate datetime,
em... 阅读全帖 |
|
w***a
发帖数: 313 | 14 TABLE employees
emplLastName varchar(30) ,
emplFirstName varchar(30) ,
emplNickname varchar(20) ,
emplCategory varchar(2) ,
emplCampID varchar(10) ,
emplArea varchar(10) ,
emplDepartment varchar(20),
emplTitle varchar(50) ,
emplEmployeeID varchar(9),
emplCardNumber varchar(16),
emplActive tinyint(4) ,
emplComment longtext,
emplRecycleDate datetime,
emplType int(10) ,
emplCompleteDate datetime,
emplIndexSent dateti... 阅读全帖 |
|
u*********e
发帖数: 9616 | 15 Thanks for the feedback.
I cleaned up the variables and use smalldatetime instead of nvarchar for the
comparison.
ALTER PROCEDURE [dbo].[SP_REPORT_GET_STATUS_DATES]
@Statement_Year SMALLINT,
@Statement_Month TINYINT
AS
BEGIN
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarch... 阅读全帖 |
|
p****1
发帖数: 275 | 16 有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
CREATE TABLE [tSWIFT_History_Trades](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](25) NOT NULL,
[TradeId] [varchar](25) NOT NULL,
[Account] [varchar](25) NULL,
[TranType] [varchar](50) NULL,
[TradeDate] [int] NULL,
[SettleDate] [int] NULL,
[Exch] [varchar... 阅读全帖 |
|
p****1
发帖数: 275 | 17 Question:
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
CREATE TABLE [tSWIFT_History_Trades](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](25) NOT NULL,
[TradeId] [varchar](25) NOT NULL,
[Account] [varchar](25) NULL,
[TranType] [varchar](50) NULL,
[TradeDate] [int] NULL,
[SettleDate] [int] NULL,
[Exch] [varchar](50) NULL,
[D... 阅读全帖 |
|
p****1
发帖数: 275 | 18 【 以下文字转载自 JobHunting 讨论区 】
发信人: peace1 (peace1), 信区: JobHunting
标 题: C#, recruiter发过来的面试题
发信站: BBS 未名空间站 (Thu Sep 8 22:17:38 2016, 美东)
有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
CREATE TABLE [tSWIFT_History_Trades](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](25) NOT NULL,
[TradeId] [varchar](25) NOT NULL,
[Account] [varchar](25) ... 阅读全帖 |
|
p****1
发帖数: 275 | 19 【 以下文字转载自 JobHunting 讨论区 】
发信人: peace1 (peace1), 信区: JobHunting
标 题: C#, recruiter发过来的面试题
发信站: BBS 未名空间站 (Thu Sep 8 22:17:38 2016, 美东)
有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
CREATE TABLE [tSWIFT_History_Trades](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](25) NOT NULL,
[TradeId] [varchar](25) NOT NULL,
[Account] [varchar](25) ... 阅读全帖 |
|
p****1
发帖数: 275 | 20 有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
We work with large datasets, and are always performance conscious since
extended processing times will impact our time to market. Keep this in mind
as you answer the following questions:
There is a table defined as:
CREATE TABLE [Positions](
[load_id] [int] NOT NULL,
[acct_cd] [varchar](20) NOT NULL,
[acct_num] [varchar](255) NULL,
[sec_id] [varchar](50) ... 阅读全帖 |
|
p****1
发帖数: 275 | 21 【 以下文字转载自 JobHunting 讨论区 】
发信人: peace1 (peace1), 信区: JobHunting
标 题: SQL, recruiter发过来的面试题
发信站: BBS 未名空间站 (Thu Sep 8 22:14:20 2016, 美东)
有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
We work with large datasets, and are always performance conscious since
extended processing times will impact our time to market. Keep this in mind
as you answer the following questions:
There is a table defined as:
CREATE TABLE [Positions](
[load_id] [int] NOT NULL,
[acct_cd] ... 阅读全帖 |
|
p****1
发帖数: 275 | 22 【 以下文字转载自 JobHunting 讨论区 】
发信人: peace1 (peace1), 信区: JobHunting
标 题: SQL, recruiter发过来的面试题
发信站: BBS 未名空间站 (Thu Sep 8 22:14:20 2016, 美东)
有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
We work with large datasets, and are always performance conscious since
extended processing times will impact our time to market. Keep this in mind
as you answer the following questions:
There is a table defined as:
CREATE TABLE [Positions](
[load_id] [int] NOT NULL,
[acct_cd] ... 阅读全帖 |
|
n****e
发帖数: 1403 | 23 (1). In database A, table `client`
CREATE TABLE `client` (
`pin` INT(11) NOT NULL,
`create_user` INT(11) NULL DEFAULT NULL,
`update_user` INT(11) NULL DEFAULT NULL,
`update_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`dob` DATE NULL DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`dob_type` INT(11) NULL DEFAULT NULL,
`ethnicity_code` VARCHAR(10) NULL DEFAULT NULL,
`gender_code` VARCHAR(10) NULL ... 阅读全帖 |
|
i*****w
发帖数: 75 | 24 The following code is not optimized for performance, I just would like to
show the steps to solve the problem.
HTH
-- Data Preparation for Table A
declare @TblA table(name varchar(20), project varchar(10), score int)
insert into @TblA(name, project, score)
select 'Jack', 'A', 100
union all
select 'Jack', 'B', 50
union all
select 'Jack', 'C', 50
union all
select 'Susan', 'A' , 50
union all
select 'Susan', 'B' , 50
-- ... 阅读全帖 |
|
k**g
发帖数: 1558 | 25 建立一个自己的procedure用来rank不同table里面的数据
Create Procedure ASCRanking
@variable varchar(20),
@Rnk_variable varchar(20),
@tablename varchar(50),
@numberofcategory varchar(5),
@Partition varchar(50)
As
Declare @sql1 varchar(200)
Declare @sql2 varchar(200)
BEGIN
set @sql1='Alter Table '+@tablename+'Add '+@Rnk_variable+' real'
EXEC(@sql1)
--set @sql2='Update '+@tablename+
--' Set '+@Rnk_variable+'=NTILE('+@numberofcategory+') OVER(PARTITION BY '+@
Partition+' ORDER BY '+@variable+' ASC)'
--EXEC(@sql2)
END
... 阅读全帖 |
|
y******8
发帖数: 40 | 26 Table: Person
Columns
Column Name Type Nullable
person_ID int No
FirstName varchar No
MidleName varchar Yes
LastName varchar No
Person_ID is the primary key column for this table.
Columns
Column Name Type Nullable
Address_ID int No
person_ID int No
StreetNumber varchar No
StreetName varchar No
City varchar No
State varchar No
Address_ID is the primary key column for this table.
题目是：
W... 阅读全帖 |
|
i*****w
发帖数: 75 | 27 This is a Pivot/UnPivot problem:
Declare @tbl Table (CM Varchar(10), CN Varchar(10), CA Varchar(10), CB
Varchar(10), CC Varchar(10) , CD Varchar(10))
INSERT INTO @tbl(CM, CN, CA, CC)
VALUES('M1','N1','A','C')
INSERT INTO @tbl(CM, CN)
VALUES('M2', 'N2')
INSERT INTO @tbl(cm, cn, cb, cd)
VALUES('M3', 'N3', 'B', 'D')
INSERT INTO @tbl(cm, cn, ca)
VALUES('M4','N4','A')
Select * FROM @tbl
SELECT CM, CN, Value as CL
FROM
(
Select CM, CN, CA, CB, CC, CD FROM @tbl
)p
UNPIVOT
(Value FOR CL IN ... 阅读全帖 |
|
i*****w
发帖数: 75 | 28 建议一种方法:
1) UNPIVOT TableA, You will get:
ID1, COL1, CONTENT1
ID2, COL2, CONTENT2
ID3, COL3, CONTENT3
2) Join TableA and TableB by LIKE.
EXAMPLE:
-- Prepare Source Table
DECLARE @tblA Table (ID int identity, col1 varchar(100), col2 varchar(100),
col3 varchar(100))
INSERT INTO @tblA (col1, col2, col3)
SELECT 'this is a test', 'I am not sure', 'Give it a try.'
UNION ALL
SELECT 'who cares', 'No one knows', 'Why not'
UNION ALL
SELECT 'it is impossible', 'please let me know', 'be honest'
-- ... 阅读全帖 |
|
r********3
发帖数: 2998 | 29 呵呵，一般考memory allocate等细节，可以看出这个人是工程派还是面试派的。
那个Predicate是javax.sql.rowset的interface, for all FilteredRowSet。考这道题
，就是看你真的熟悉JDBC不。熟悉JDBC的人，几乎背都可以背得出来。不熟悉的人，一
下子还很难明白题目的意思。这个题目，明显不是靠楼主算法，思维的能力，而仅仅也
是想鉴别楼主到底是工程派还是面试派。
LZ的sql方面问题不是很大，但是也不够好。首先你的课程号怎么没有年份？semester
这些。大家选课的时候，难道不考虑是哪个学期的课程吗？
其次varchar到底有多长？是varchar(10)还是varchar(4096)甚至还是long varchar?
是不是unique的？varchar在数据库里面的存储空间是固定的，所以用不好容易造成空
间浪费，而且在读数据的时候也造成Disk I/O的浪费。这个表很小，disk i/o上的浪费
可能比你实际要用的数据都还大。起码你的rowid应该用unique bigint?还有你的rowid
是auto-increm... 阅读全帖 |
|
S*******C
发帖数: 822 | 30 英文原文
Design database for querying interest/likes of site users.
Assume that number of users is over 6 billion.
下面的答案对吗？
create database usersByInterests;
use usersByInterests;
create table Users(
user_id numeric(10,0) PRIMARY KEY,
email VARCHAR(45) not NULL,
userPassword VARCHAR(45) not NULL,
firstName VARCHAR(45) not NULL,
lastName VARCHAR(45) not NULL
);
create table Interest(
interest_id int PRIMARY KEY,
interest VARCHAR(45) not NULL
);
create table UsersByInterest... 阅读全帖 |
|
i*****w
发帖数: 75 | 31 Simple SQL scripts should be able to handle this situation:
Set NOCOUNT ON
CREATE TABLE #tbl(id INT, LineItem Varchar(100))
DECLARE @i INT
SET @i =1
WHILE @i <= 2000
BEGIN
INSERT INTO #tbl
SELECT @i, 'This is line ' + CAST(@i as varchar(20))
SET @i = @i + 1
END
Declare @n1 varchar(20), @n2 varchar(20)
SET @n1 = '50'
SET @n2 = '700'
Declare @SQLToExecute varchar(4000)
SET @SQLToExecute = 'SELECT * FROM ( SELECT TOP ' + @n2 + ' * FROM #tbl
ORDER BY ID) a EXCEPT SELECT * FROM ( SELECT TOP ' + @n1 ... 阅读全帖 |
|
y****3
发帖数: 131 | 32 Again, thank you very much for everyone who kindly helped me in this. I am
already learning a lot as a db design newbie....
Yes, I totally agree with you.
2 is a very interesting point.Sql Server supports varchar(8000), and for
larger data you have to use varchar(max), which may not be in row.
I am thinking about adding a hash column so that I can do easy comparison
and also index.
1. is also very interesting. I am debating if I should add a table to
capture the sub-directory information like ge... 阅读全帖 |
|
l******9
发帖数: 579 | 33 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
w*i
发帖数: 63 | 34 A job must done before tomorrow, please help...
we have two tables here,
table 1,
CREATE TABLE dbo.eai_inv_hdr
(
invid numeric(9,0) IDENTITY,
vendorid varchar(10) NOT NULL,
invnbr int NOT NULL,
batchid varchar(10) NOT NULL,
status varchar(1) NOT NULL
)
table2,
CREATE TABLE dbo.eai_inv_dtl
(
invamtid numeric(9,0) IDENTITY,
invid numeric(9,0) NOT NULL,
invamtcd varchar(10) NOT NULL,
invamt decimal(13,2) NOT NULL
)
table example:
|
|
d****a
发帖数: 6 | 35 if in SQL Server, try:
create proc test
@title varchar(50),
@author varchar(50),
@date varchar(50)
as
declare @sql varchar(200)
set @sql = 'select * from book'
if @title = 'is not null'
set @sql = @sql + ' where title is not null'
else
set @sql = @sql + ' where title like ''%' + @title + '%'''
if @author = 'is not null'
set @sql = @sql + ' and author is not null'
else
set @sql = @sql + ' and author like ''%' + @author + '%'''
if @date = 'is not null'
set @ |
|
b**e
发帖数: 2 | 36 For your first question, and for the second onw it's pretty much same.
Sorry it's not simple. We have to wait for Yukon to write simple recursive
queries
(For microsoft sql server)
Create Table #tmpTABLE (ID int, parent_id int, lastName varchar(50), firstName
varchar(50) )
declare xTmp scroll cursor for SELECT * from tablename
open xTmp
declare @myid int
declare @pid int
declare @lName varchar(50)
declare @fName varchar(50)
declare @found bit
declare @tmpID int
declare @tmpPID int
declare @num i |
|
P****e
发帖数: 385 | 37 两个table如下
Table: NAMES
NI_NUMBER: varchar(20)
FULL_NAME: varchar(50)
Table: PHONE
NI_NUMBER: varchar(20)
TELEPHONE: varchar(50)
要找到所有没有电话号码的人：
这个办法是没问题的：
SELECT FULL_NAME FROM NAMES
WHERE NI_NUMBER NOT IN
(SELECT DISTINCT NI_NUMBERS FROM PHONE)
可是下面两个query，一个用minus,一个用outer join为什么不能work呢？应该怎么写？
SELECT FULL_NAME FROM NAMES
WHERE NI_NUMBER IN
(SELECT NI_NUMBER FROM NAMES
MINUS
SELCT DISTINCT NI_NUMBER FROM PHONE)
SELECT FULL_NAME FROM
(SELECT A1.FULL_NAME, A2.TELEPHONE
FROM NAMES A1, PHONE A2
WHE |
|
s**i
发帖数: 381 | 38 几乎完全新手
拿到一个stored procedure like this:
CREATE PROCEDURE [dbo].[nextUrl]
@asmx varchar(80)
AS
begin transaction
create table #thisUrl ([System Name] varchar(80) not null,url
varchar(4096) not null,asmx varchar(80) not null,invocations bigint not
null)
这里的#thisUrl是啥意思?
括号里那一大串又是什么呢?
thanks |
|
B*****g
发帖数: 34098 | 39 包子。回这篇文章被老板看见了
CREATE PROCEDURE sp_RandomLettersGenerator
( @randomLetters varchar(100) OUT)
AS
Declare @letters1 varchar(100)
Declare @letters2 varchar(100)
set @letters1='qwertyuiopasdfghjklzxcvbnm'
set @letters2='qazwsxedcrfvtgbyhnujmikolp'
set @randomLetters = substring(@letters1, cast(round(rand()*10,0) as int), 3)
+ substring(@letters2, cast(round(rand()*10,0) as int),3)
GO
declare @result1 varchar(100)
exec sp_randomLettersGenerator @result1 out
select @result1
from |
|
y****9
发帖数: 144 | 40 Googled and tested for a while, comp up with this method:
select cast(datepart(yy, getdate()) as varchar) + '-' +
cast(datepart(mm, getdate()) as varchar) + '-' +
cast(datepart(dd, getdate()) as varchar) + ' ' +
cast(datepart(hh, getdate()) as varchar)
Not sure this is the best idea in SQL Server. in Oracle it is easy:
select to_char('YY-MM-DD HH24', sysdate) from dual;
any suggestion? I am working on a query which need this conversion
Thanks, |
|
e****7
发帖数: 4387 | 41 -- CREATE TABLE STRUCTURE
IF OBJECT_ID('dbo.PEAK', 'U') IS NOT NULL DROP TABLE PEAK
GO
IF OBJECT_ID('dbo.CLIMBER', 'U') IS NOT NULL DROP TABLE CLIMBER
GO
IF OBJECT_ID('dbo.PARTICIPATED', 'U') IS NOT NULL DROP TABLE PARTICIPATED
GO
IF OBJECT_ID('dbo.CLIMBED', 'U') IS NOT NULL DROP TABLE CLIMBED
GO
CREATE TABLE PEAK
(
NAME VARCHAR(255),
ELEV INT,
DIFF INT,
MAP INT,
REGION INT
)
GO
CREATE TABLE CLIMBER
(
NAME VARCHAR(255),
SEX CHAR(1)
)
GO
CREATE TABLE PARTICIPATED
(
... 阅读全帖 |
|
i*******d
发帖数: 81 | 42 请问DB2中可以用table variable么？
SQL Server中很容易实现：
CREATE FUNCTION dbo.f_Name(
@List VARCHAR(2000)
,@Delim VARCHAR(10)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @t_Name TABLE (Item VARCHAR(2000))
.....................
.....................
RETURN @sth
END
GO
DB2有类似的功能么？多谢！ |
|
l******9
发帖数: 579 | 43 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
l******9
发帖数: 579 | 44 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
d****i
发帖数: 16 | 45 Do not why is not work?
maybe date type is wrong.
literal does not match format string
drop table employee;
create table employee( FNAME varchar(15),
MINIT char,
LNAME varchar(15),
SSN char(9),
BDATE date,
ADDRESS varchar(30),
SEX char,
SALARY decimal(10,2),
SUPERSSN char(9),
DNO int
);
insert into employee values('john','B','Smith','123456789',
'1956-11-11',
'731 Fondren, Houston,TX', 'M',
30000,'333445555',5);
|
|
a*****a
发帖数: 438 | 46 Memo field is Access only.
SQLServer doesn't have Memo field. It has TEXT/NTEXT though.
varchar is called varchar because its length is variable. so when storing
a 40-byte string into a varchar(4000)field, only 40 bytes are used. |
|
x******g
发帖数: 319 | 47 i have a table:
============
varchar NAME
varchar A
varchar B
============
我想查A的字符串含有B中的字符串，怎磨查询？
SELECT NAME
FROM TABLE
WHERE A LIKE '%abcd%';
可是如何查 WHERE A LIKE 'B'
谢谢！ |
|
a*******s
发帖数: 324 | 48 I have two tables. table email has 1 million records. table domain has 20
records. domain is part of email address.
for example: email: a*******[email protected]
domain: yahoo.com
create table email(email varchar(40) primary key, domain varchar(20), index(
domain));
create table domain(domain varchar(20) primary key);
case1:
select p.email from email p, domain q where p.domain = q.domain
select p.email from email p, domain q where q.domain = p.domain
Is it the same?
Does the databa |
|
i***c
发帖数: 301 | 49 以下是从xml插入表的sql:
CREATE TABLE emps (ID int, EmpName varchar(30))
GO
DECLARE @hDoc int
DECLARE @cDoc varchar(8000)
SET @cDOC = 'ASmithBOB
NewName>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @cDoc
INSERT INTO emps
SELECT *
FROM OPENXML(@hDOC, '/ROOT/NewName', 3)
WITH
(
ID int '@ID',
NewName varchar(30) '.'
)
EXEC sp_xml_removedocument @hdoc
go
SELECT * FROM emps
go
请教如何改xml内容为文件，试了以下sql报错:
CREATE TABLE emps |
|
M********g
发帖数: 793 | 50 Just a quick thought not test yet, wish can give you some fresh idea. I skip
to directly write cID rather and Title but I think you could change that
later easily.
if objective_id('new_table') is not null drop table new_table
create table new_table(RowID int identity(1,1), sID int, name varchar(20),
course varchar(255))
declare @rc int, @looper int
declare @course varchar(255)
Insert new_table(sID,name)select distinct r.sID, s.name from Registration r,
Student s where r.sID = s.sID
set @rc = @@R |
|
